InfoPath Dev

Hi, I can't seem to find this anywhere, so I'm hoping someone here can help me. I have a custom infopath form for a sharepoint list, and I have a field that is a dropdown box to retrieve some data from a database. I use sql to concat some fields from the database to make the drop down easier to digest for the end user when making their selection, but store the client name in the actual value of the drop down field. My question is though: is there a way to retrieve the display name/display value of a field somehow? My google-fu skills are not finding anything, but I have to believe there is a way, how else does the form store it and redisplay.

For reference, I need it because I want to alleviate some typing for the person maintaining the list, and have some rules to autoset a few fields based on the users selection in that field, but the database allows for duplicate names (primary key is just a number). So in those corner cases when someone selects a client that exists in the list twice, if I use just the name (i.e. the value of the drop down field), the query will find just the first instance, so those dependent fields will be populated with incorrect data. I know I could change the drop down field to store the primary key, and then I would be guaranteed the correct info, but the list already has some 800 entries, and I don't want to mess with it if I can instead reference that display value. I am using infopath 2010 if that makes any difference in the answer.

Help me infopathdev forum community, you're my only hope. Thank you in advance for your time.

The display value is in the secondary data source - so IP is basing what is displayed on the first row it finds in that secondary data source that matches the value currently in the field. So - my guess here is if the user selects the second instance of something where there are more than one rows with the same value, when the form is re-opened, the first instance will probably show as selected...

Thank you Hilary for your response, but I am unclear on what you mean?

So, lets take one of my rules as an example. Triggers on field change. First action, query the data connection. Second action to set a field's value (client number). From here, I click fx, insert field or group, go to my secondary data source, and expand the data fields, I choose the info I want (clientnumber), filter data, so that myfield = title(drop down field). This is where I have the bug, that it only finds the first instance of the name. I can be a lot more exact if I can compare the display value of that title field (consists of a concat of client number, padded with leading zeros (so all are 4 digits in length) + client name = 100% unique). This is where I am stuck...

I was unclear. InfoPath is not storing the display anywhere. Only the value.

So, pretend this is your drop down data:

 

Value	Display
1	Apple
2	Pear
3	Grape
1	Fig
4	Plum

If you select Fig, the value will be "1" in your form. If you try to return the display from your 2ds, you'll always get Apple - the first item with a value of "1". I don't think there is any way around this. I think you need to have a list of unique values for the value in the drop down.

Yeah, I just did a test with my internet connection off, and opened a saved form. Sure enough it had the value, not the display name in the field. I guess I could have started with that test before opening this. *sigh* Well, thank you for your help. Guess I will have to redesign the form. I will find a workaround so I don't have to mess with the data they have already entered. Thank you so much for your help, I really appreciate it.

I'm not sure I understand what it is that you want to do:

1.Do you want to show SQL values in a drop down list in InfoPath?

Create a view in SQL to only show distinct values, then create an external list in SP and reference that in your drop down list.

2. Do you want to display a value from the form?

Publish the value to SharePoint

3. Push data back into SQL

Still struggling with that one myself.