Get InfoPath to display lookup column value, not ID

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.

image

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:

  1. Create data connections to the lists containing the lookup values

    image

  2. Change the repeating table field control from Text Box to Calculated Value

    image
  3. Edit Formula on the calculated field control (Right-click, Calculated Value Properties or double-click, Control Properties)

    image

  4. 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

    image
  5. 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

    image

  6. Preview the form to see that the categories are now showing instead of the lookup IDs

    image

  7. Following the same steps for the Item column allows the values to show there too instead of the lookup item IDs.

    image

Let me know if you have another way of accomplishing the same thing.

Related Posts

image.png image_thumb.png image.png image_thumb.png image.png image_thumb.png InfoPathSQLVideoTutorial.png SharePointDesignerSQLDataForm.png SharePointDesignerSQLDataForm_thumb.png SharePointDesignerDataViewSQL.png

SharePoint-InfoPath 2007 Video Tutorial: Storing InfoPath form data in SQL (Part 1)

image.png image_thumb.png SharePointDropDownFilter.png

SharePoint 2010 Tutorial Video: Drop-Down Filters

MOSSHelpPlay.png

InfoPath Form with Multiple, Dynamic Approvers

clip_image002.jpg clip_image002_thumb.jpg clip_image004.jpg clip_image004_thumb.jpg clip_image006.jpg clip_image006_thumb.jpg clip_image008.jpg clip_image008_thumb.jpg clip_image010.jpg clip_image010_thumb.jpg clip_image012.jpg clip_image012_thumb.jpg clip_image014.jpg clip_image014_thumb.jpg clip_image016.jpg clip_image016_thumb.jpg clip_image018.jpg clip_image018_thumb.jpg clip_image020.jpg clip_image020_thumb.jpg clip_image022.jpg clip_image022_thumb.jpg clip_image024.jpg clip_image024_thumb.jpg clip_image026.jpg clip_image026_thumb.jpg clip_image028.jpg clip_image028_thumb.jpg clip_image030.jpg clip_image030_thumb.jpg clip_image032.jpg clip_image032_thumb.jpg

Use SharePoint Lists to Create a Filtered Drop Down in InfoPath 2007

3 Comments

  1. Avatar of Patti Andrews

    Patti Andrews - January 11, 2012, 11:45 am

    Thanks! This works great on my repeating tables — a real life saver. Any idea how to do the same thing for a multi-selection checkbox using a secondary data source? The selections are working perfectly, but they are displaying the ID number rather than a display name. Changing the control to a calculated value would eliminate my checkboxes.

  2. InfoPath 2010 Form List vs Library Template Features | The SharePoint Effect - December 2, 2013, 9:10 am

    [...] Resolve Lookup Column ID [...]

Leave a Reply