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.
In InfoPath, add a Rich Text Box field to
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
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
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
The formula expression for our case is:
eval(eval(@_LONG_NAME[Value= @PRO_BRAND_ID], 'concat(., ", ")'), "..")
XPath expression of this formula is:
xdMath:Eval(xdMath:Eval(xdXDocument:GetDOM("ProductsSQL2")/dfs:myFields/dfs:dataFields/ns1:PRO_BRAND/@_LONG_NAME[xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:Products/Value= ../@PRO_BRAND_ID], 'concat(., ", ")'), "..")Publish the form.
In the list view, ensure
that it is showing the rich text field and hiding the
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