InfoPath Dev

I have forms that use SharePoint lists as lookup tables. The forms write to DBXL which then writes to a form library on SharePoint and a SQL Server database. The forms written to SharePoint work fine, but the data written to SQL Server for fields using name/value pairs is the value and not the display name.

How do I write the display name to SQL Server? My first guess is to alter the forms to set the field value to the display name column used in the SharePoint lookup. This seems less than elegant and I am afraid of breaking the lookup relationship when forms are later edited.

So, in the XML, InfoPath will be storing the value. DBXL saves the XML field value to the database - in other words, DBXL is working off the XML.

When you open the XML from SharePoint, since the drop down control is configured with a display and a value, you see the display in the form - that is all based on the form template itself - it can find a display match for your value, thus displays it.

Unfortunately, the only way to capture those display values in SQL will be to also store them in the form and write them to the DB. You can use a calculated default value for the new field so that when the XML is opened or the drop down is changed, the calculated default will update to whatever the correct display is for the selected value - let me know if that doesn't make sense or you need more info, okay?

Hilary,

I think you oversimplified your solution. If I create a new field and set its default value to that of the original "master" field then the new field is set to the ID value, not the display name of the lookup.

For anyone else reading this thread, I can use a second field's default value as described above if I set the default value to the display name field from the secondary data source that is my lookup list and filter that field by the ID selected in the "master" field.

Another way to do it is to set an action on the field displayed in the form that sets the value of the secondary field. That approach is described in the thread at http://social.msdn.microsoft.com/Forums/en-US/sharepointcustomizationlegacy/thread/ac0b3496-5ab7-41d5-920a-6aee6292d4cb. See Arylius's step-by-step on setting up the filter.