If you have ever used a SharePoint list containing lookup columns as a data source in your InfoPath forms, you may have been disappointed to see the value of the lookup is not displayed. Instead of the value, the list ID of the item containing the value is displayed.
My ‘Construction Line Items’ list contains three lookup columns: Project, Category, and Product.
When I connect to this list from InfoPath, my repeating table shows the lookup item IDs and not the values like I want.
This example uses the following four SharePoint lists: Line Items, Projects, Construction Category, Products and Activities.
The InfoPath form connects to the Line Items list and displays the data in a Repeating Table
So, how can you get InfoPath to display the lookup column value? One way is to add a data connection to the list (or lists) containing the lookup values and change the repeating table field controls from text boxes to calculated fields. Here are the steps:
- Create data connections to the lists containing the lookup values
- Change the repeating table field control from Text Box to Calculated Value
- Edit Formula on the calculated field control (Right-click, Calculated Value Properties or double-click, Control Properties)
- Delete the current formula and Insert Field or Group > change the Fields source to the data connection containing the lookup value (Construction Category) > select the lookup field column (Category) > click Filter Data
- Set the filter where the ID (on the Construction Category) > is equal to > Select a field or group > change fields to the Line Items data connection > select the corresponding column (Category on the repeating table connection) > OK
- Preview the form to see that the categories are now showing instead of the lookup IDs
- Following the same steps for the Item column allows the values to show there too instead of the lookup item IDs.
Let me know if you have another way of accomplishing the same thing.