InfoPath Dev

LOL, I am also a bit OCD, which is why I have tried it all ways.  But it looks like what I missed before was the quotes around the '  because when I do it like "'" it now works.   I have several data connections, the main connectioned query worked perfectly, but the secondary one was where I was having issues.  I'm am moving my secondary info to a main connection so I can use the page buttons I received from Patrick last year.  Everything seems to be working now that I added the extra quote. 

 

Thank you. 

Hi, can you tell me if this query can be used for multiple selects, for example dsname=Genstates /where=Where StateID = '" , Field1, "and CityID = '", Field2, "'")

Your syntax looks incorrect there. Remember, we need to wrap our field values in single quotes. Also, there was no space before your "and".

  /where=Where StateID = '" , Field1, "' and CityID = '", Field2, "'")

So, that would be:

StateID = SingleQuoteDoubleQuote, Field1, DoubleQuoteSingleQuote and CityID = SingleQuoteDoubleQuote, Field2, DoubleQuoteSingleQuoteDoubleQuote

we want our result to look like:

StateID = 'WA' and CityID = 'Sea'

yours would have resulted in:

StateID = 'WAand CityID = 'Sea'

You can always paste your concat into a calculated value to easily check what your syntax is going to look like....

Hi.  I am at it again, and now I am trying to add another query.  The original Query:  concat("ModifySqlQuery /dsname=Main /where=where (Forecaster = N'", QueryChange, "')") works fine.  But the following query brings no resulta and checking the command and success of qrules shows no blank after it runs.  I have tried it several ways. 

concat("ModifySqlQuery /dsname=REVENUE /where=where (Forecaster = N'", QueryChange, "')")

concat("ModifySqlQuery /dsname=PB_VW_Inprogress_Revenue /where=where (Forecaster = N'", QueryChange, "')")

 

 Here is the SQL connection info.  I renamed the connection to REVENUE to make it work.  I also renamed it PB_VW_Inprogress_Revenue.

select "Part#" as "Part_","Forecaster","Division","Region","Company","Type","ForecastDate","M1","M2","M3","M4","M5","M6","M7","M8","M9","M10","M11","M12" from "dbo"."PB_VW_Inprogress_Revenue" as "PB_VW_Inprogress_Revenue"

I've tried it several ways and can't figure out why it's not working.   

 

I always know if I post a note, than I will figure it out.  I used another query that worked:  concat("ModifySqlQuery /dsname=REVENUE /where=where Forecaster='", QueryChange, "'") not sure why the N'" doesn't work in the revenue query but does in the main, but thanks anyway.  :)

Did you check the qRules Error node?

Yes, it was blank.  Current was view 1.  I did a pause after the bad command, but it showed no info.  I did get it figured out.  But I would love to figure out why the command didn't appear and why the error node was blank. 

Well, the command node is always cleared after the command runs, so you wouldn't see a value there. For this command, the error node should return any error that is returned to the data connection when it is executed, but it sounds like this issue doesn't actually return an error from the data connection.

 I'm currently testing out a trial version of qrules and am running into the some of same issues described in this post. I've put my concat statement into the command line formula and it verifies correctly. My statement is:

concat("ModifySqlQuery /dsname=VW_DIM_INF_APPR_CURRENT /where=where DEPT = '", DEPT, "'") 

My rule is based on a field change and when it does the first action sets the command field value to the above formula and the second action queries the data connection (which is a secondary data connection). There is no error message and the success box is checked. I had originally included only the command line action in the rule, but I could never get any values to be returned for the connection. Once I added the action in the rule to query the data connection I did get values, but they were not filtered per the WHERE statement. Nothing shows up in my result box either so I'm wondering how that relates to this particular command. The command seems to be executing, but my data connection results are not being filtered. Can anyone help?

Hi-

You should try setting the value of Command to your formula above, then checking the data connection secondary data source for results.

I don't believe there's a need to separately query the data connection.

Hope this helps.

 I tried that and I don't get any results for the data connection. The success box is checked though and there aren't any error messages. That must mean my formula is wrong even though the syntax verifies?

 This will work on a secondary data connection, correct? And I should be able to run it locally (in an unpublished form)?

 

So, that could mean that the data connection is returning no results.... Now the question is why. :)

So - I would probably make sure I knew exactly what the where clause is in this case. Do you know you have results for the selected Department? Are you able to run SQL trace to see the query being executed?

I'm getting results now. I looked at the SQL trace and that was valid fine so then I recreated my data connection just to be sure and voila. I can't believe how well this command works. What a find! Thank you! I appreciate your help.