InfoPath Dev

 Hello - relatively new developer in this space, and have a how-to question.

I need to use WS.Query custom control to retrieve a value from a SQL table and populate a field (FieldB) based upon another field's (FieldA) change in value (Run when field changes).

Both fields are on the same InfoPath Form.

FieldA is populated by a WS.Lookup, and that works as intended.

To populate FieldB I am using a WS.Query SQL query that uses the literal value from FieldA as part of the comparison.

I have tried many different versions of this:  It is the below query to get the Profit_Center value (FieldA) that I need to use to return SEGMENT value to FieldB

• Select distinct Segment from ZCAG_PROFT_CTR_DESC where PRCTR = '[//my:Profit_Center]'

Is it possible to pull in the literal value of my:Profit_Center (FieldA)within the same form and use like this?

Any help with this is greatly appreciated.

Thanks in advance,

Jack

So - I'm sorry to be confused, but I'm confused. :)

I'm not sure what a WS.Query SQL query is?

If you are trying to set a query field in a custom web service to the select statement in your bullet point, you would want to use a concat formula to include the value from Profit Center:

concat("Select distinct etc....'", my:ProfitCenter, "'")

Hard to see, but I have some single quotes snuck in there...

I have a feeling I just don't understand what you are trying to do tho - so maybe post with a few more details, or attach a sample form under the Options tab.

 Thank-you very much for the reply.  This is the first time I posted on this forum so I am still learning how to use it.

I have attached a screen print of the WS-Query custom control properties, which includes the SQL query.  Please let me know if I need to make my dilemma easier to understand.

Hi Hilary,

I have a different issue I was hoping you could help me with if you get chance.  I'm using INFOPATH 2010.

I'm using the formula below; however, getting an "Expected value type, Actual value: name error". Any idea what I am doing wrong?    Thank you.

SNOWMAN 

concat(substring-before(.,";")+substring-after(.,";")*((translate(xdDate:AddDays(../my:startDate,substring-before(.,";")),"-","")<translate(../my:endDate,"-",""))*2-1),";",substring-after(.,";")div2)

 The full formula is:

startDate rule: condition expession: ../my:endDate != ""

field: dayDifference

string value: 0;4096

endDate rule: condition expession: ../my:startDate != ""

field: dayDifference

string value: 0;4096

dayDifference rules:

MainRule:

condition expession:xdDate:AddDays(../my:startDate, substring-before(., ";")) != ../my:endDate and contains(., ";")

field: dayDifference

xPath expression: concat(substring-before(., ";") + substring-after(., ";") * ((translate(xdDate:AddDays(../my:startDate, substring-before(., ";")), "-", "") < translate(../my:endDate, "-", "")) * 2 – 1), ";", substring-after(., ";") div 2)

finalize rule:

condition expession: contains(., ";")

field: dayDifference

xPath expression: substring-before(., ";") 

Sorry I didn't get back to you on the earlier issue - I've never seen that dialog before - is the form based off a web service, by chance?

You have a  "<" before your second translate in the formula that is giving you an error, I'd start with that.....