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:

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

   

2. Change the repeating table field control from Text Box to Calculated Value
   
   
3. Edit Formula on the calculated field control (Right-click, Calculated Value Properties or double-click, Control Properties)

   

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

   

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

   

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

How to video: Create Optional Cascading Drop-Down Filters on SharePoint List Forms

I knew I was going to like the ability to customize list item forms in SharePoint 2010 using InfoPath. The ability to filter drop down list choices based on a previous selection in the form is something many people ask for.

I have created a SharePoint 2010 tutorial/training  video that shows how to customize the new item form in InfoPath, wire up drop down choice lists to information in a SharePoint list and filter and have the choices in one drop down filter the choices in the next. And yes, the use of the filters is an option for the user.

The facilities department is responsible for equipment in offices, manufacturing and retail sales locations in thirty or more buildings located in about a dozen cities in three states. This solution gives users the choice of picking directly from the entire list of buildings or filtering first by state and city (see screenshot above).

The video below will walk you through the entire process from creating a custom list, adding a lookup column that pulls back multiple columns of meta data, customizing the list item form in InfoPath and wiring up the optional cascading filtered drop down choice fields.