General

Re: Infopath 2010 - WS.Query property for SQL Select result

Hilary Stoupa — Mon, 24 Nov 2014 20:26:10 GMT

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.....

Re: Infopath 2010 - WS.Query property for SQL Select result

Snowman — Mon, 24 Nov 2014 20:14:43 GMT

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(., ";")

Re: Infopath 2010 - WS.Query property for SQL Select result

janderson021 — Thu, 20 Nov 2014 14:38:22 GMT

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.

I need this control to update the field 'BFBusinessSegment' with the results of the query (Segment) whenever the value in field 'Profit_Center' changes.

This query does not return any results even though the value in 'my:Profit_Center' is valid and has a match in the source table column PRCTR.

I think (hope) I have not formatted the '[//my:Profit_Center]' part of the query correctly, and am searching for the correct formatting.

Thanks again for any insight on this, and please let me know if I should post this on a different part of this forum.

Jack

Re: Infopath 2010 - WS.Query property for SQL Select result

Hilary Stoupa — Thu, 20 Nov 2014 01:41:44 GMT

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.

Infopath 2010 - WS.Query property for SQL Select result

janderson021 — Wed, 19 Nov 2014 22:36:15 GMT

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