SQL Database Integration

Re: Fill in user ID in field only if exists in table

pelizdr — Tue, 24 Feb 2015 15:33:46 GMT

Hilary,

thanks for the quick response, I am trying to update a query field, so I only return the values for that user ID. After I posted the question, thinking I was stumped, I had an idea, I think near what you are suggesting, I should have put WAY more information in the original post (I blame the coffee, or lack thereof before posting). This is a query form that pulls back user information based on their user ID.

Create secondary data connection to same SQL table, retrieve on open

Create a temporary field with a default value of @customer_id[. = userName()]

set my query field (User ID) to that value, so if it is populated, the user ID fills in, otherwise, it is blank.

Re: Fill in user ID in field only if exists in table

Hilary Stoupa — Tue, 24 Feb 2015 15:22:50 GMT

So - your rule (I guess in the form load rules? I'm not sure where your rule needs to go...) could have the condition of:

First dropdown - Data connection, Customer_ID
Second dropdown - is equal to
Third dropdown - use a formula - userName() function

Then the actual action would be to set the field with the user name. Does that make sense?

Will the main connection have all the rows for the SQL table that is being updated? If not you may need to add a secondary data connection to query for that data so you can have all the Customer_ID values to compare to.

Another thing to be aware of is that XPath is case sensitive. After you have the rule condition worked out it can be modified to translate everything to lower or upper case to get around that....

Fill in user ID in field only if exists in table

pelizdr — Tue, 24 Feb 2015 15:07:58 GMT

We have a form that automatically populates the user ID (function UserName) into a form. This has been working well, but now I have an ask to only populate it if the UserName shows up in the SQL table that is being updated, otherwise, leave it blank. To add insult to injury, our company has decided to not add in VSTA by default to our image, so any programming outside of the basic form is out of the question. My pseudo logic is:

Get username

compare username to data connection (Data Connection - Main Connection; field - Customer_ID)

If exist (Username) in (Main Connection Customer_ID) Then set default value of field

Else NULL

Any assistance would be GREALY appreciated!