Posted on:
Categories: SharePoint
Description: We had a requirement to add a multiple-selection list box field to an InfoPath form with values populated from a SQL Server database. In this blog, we create a hidden text field that stores the display names of the list box selections.
​Scenario We had a requirement to add a multiple-selection list box field to an InfoPath form with values populated from a SQL Server database. First we created the data connections and added and configured a multiple-selection list box to get choices from the data source in InfoPath. The ID was set as the Value and the name of the items that you want displayed in the list box was set as the Display Name (example pictured below). It is important that you select the ID for the Value. We were encountering a bug when we specified the name of the item (rather than the ID) to be the Value (ie. setting @_LONG_NAME as both the Value and the Display Name was causing this bug). The bug would render duplicates of the multiple-selection list box selections when a saved form was being viewed or edited. Although setting the ID as the Value eliminated this bug, the ID numbers of selections were being displayed in the list view which is not very user-friendly. We would like to see their display names instead. Therefore, to address this issue we came up with a workaround to retrieve and store the display name of the selections in a separate text field. We are going to step through the configuration in this blog. Implementation In InfoPath, add a Rich Text Box field to the form. Remove the Rich Text Box field from the display of the form (the data field still exists, it is simply hidden in the form). Go to edit the Rich Text Box field's properties. Under the Default Value setting, click to Insert Formula. The formula essentially boils down to this eval(eval(X[Y = Z], 'concat(.,", ")'), "..") X is the data field from the data connection that you want displayed in the text field Y is the value of the selection from the multiple-selection box (which is an ID) Z is the ID data field from the data connection The double eval loops through X[Y = Z] and concatenates each item. In place of X[Y = Z], click Insert Field or Group, then in the dropdown under Fields, select your data connection, then select the data field property to be displayed (in our case it's "LONG_NAME"), then click Filter Data Add a filter with the conditions Value is equal to ID In the first dropdown, choose Select a field or group. Then select Value from the multiple-selection list box data field. For the last dropdown, choose Select a field or group again. Then select the ID from the data connection The formula expression for our case iseval(eval(@_LONG_NAME[Value= @PRO_BRAND_ID], 'concat(., ", ")'), "..") The XPath expression of this formula isxdMathEval(xdMathEval(xdXDocumentGetDOM("ProductsSQL2")/dfsmyFields/dfsdataFields/ns1PRO_BRAND/@_LONG_NAME[xdXDocumentget-DOM()/dfsmyFields/dfsdataFields/mySharePointListItem_RW/myProducts/Value= ../@PRO_BRAND_ID], 'concat(., ", ")'), "..") Publish the form. In the list view, ensure that it is showing the rich text field and hiding the multiple-selection field. The result is that you have a multi-selection list box with choices from a SQL Server database in your InfoPath form and a separate text field that shows the display names of all selections.