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 an InfoPath form that stores form data in SQL

Part 1: Create a web-enabled InfoPath form that stores form data directly in SQL

Part 2: Create data view web parts in SharePoint Designer to display/update SQL data in SharePoint pages

Part 3: Filter SQL data with a form web part and create SharePoint list items from SQL data

This SharePoint video tutorial is based on a simple concept: Store customer suggestions for product improvement or new product ideas in SQL and create a SharePoint page to find, filter, view, and manage the task of implementing the good suggestions.

 
Web-enabled InfoPath form

 
Form data stored in SQL database table

Suggestion tracking dashboard page

 
View part 1 of the SharePoint training video series here

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.

A question came in to the MOSS Help and How To forum about creating a form that allowed multiple approvers to be specified at the time the form is being filled out.

I went ahead and created a form in InfoPath and a simple workflow in SharePoint Designer to allow the selection of the approvers and assign tasks for the approvers. It is a simple form and workflow both. For production you would want a more robust solution. If you have ideas for a better solution, please let me know in the comments.

Instead of writing out all of the steps I created this short video to demonstrate how the form, workflow, and SharePoint library work.

While teaching our online class on InfoPath and SharePoint Designer Workflows (Essentials of InfoPath and SharePoint Workflows), I was asked about using SharePoint list data to populate InfoPath drop-down list controls and filtering a list control based on the selection of another drop-down.

Goal: To create an InfoPath form that includes two drop down lists populated from an external source (SharePoint lists) with the result of the first list selection filtering the second.

Scenario: Adventure Works has four types of bikes that they manufacturer:

· BMX
· Mountain
· Racing
· Touring

They have many bike models for each type of bike (Mountain bikes for example are comprised of the following: MX-100, MX-200, MX-220, MX-300, etc.)

Customer service has a form to track issues from customers. They need to select the model from a list but would like to be able to filter the list of bike models based on the type of bike.

Overview of the steps

In SharePoint:

Create a custom list to maintain the bike types
Create a custom list to maintain the current bike models (with a lookup to the Bike Types list)

In InfoPath:

Create a form
Add a drop down list control and connect it to the bike types list
Add a list (or drop down list) control and connect it to the Bike Models list
Set a filter to filter the models based on the selection from the first control

Detailed directions

1. At the top-level site in the site collection (portal.awbikes.local in the classroom environment) create a custom list named BikeTypes

2. Change the ‘Title’ column name to ‘BikeType’

3. Add the following four items to the list: BMX, Mountain, Racing, Touring

4. Create another custom list named ‘BikeModels’

5. Rename the ‘Title’ column ‘ModelNumber’

6. Add a lookup column named BikeType and lookup to the Biketypes list, BikeType column

7. Populate the list with a number of model numbers and choose a type for each

8. In InfoPath, design a new form using a blank template

9. In the Layout task pane add a Table with Title then add a two-column table in the body

10. Resize the rows and columns and hit the tab key a few times to create more rows in the two-column table as follows:

This list will contain many fields including date, customer contact info, issue type, issue description, etc. We are only going to work with the bike type and model fields here.

11. Switch to the Data Source task pane and rename the ‘myFields’ group to CustomerService

12. Under Actions, click Add a Field or Group and add a Text type Field named BikeType

13. Add another text field named BikeModel

14. Drag the two fields into the form

15. Right-click the BikeType control and change to a Drop-Down List Box

16. Right-click the BikeModel control and change to a List Box

17. Double-click the BikeType control and under the section titled List box entries, select the radio button for ‘Look up values from an external data source’ and click Add

18. Select Create a new connection to: and Receive data, Next

19. Select SharePoint library or list, Next

20. Enter the URL of your site where you created the custom lists above (in class, http://portal.awbikes.local), Next

21. Choose the BikeTypes list, Next

22. Select the BikeType field, Next

23. Select ‘Store a copy of the data…’, Next

24. Enter a name for the connection, BikeTypes, select the ‘Automatically retrieve data…’, Finish

25. Click the XPath button for Entries

26. Expand the folders and select BikeType, OK, OK again to finish

27. Preview the form to test the connection to the Bike Types list

28. Close the preview and double-click the BikeModel list control and under the section titled List box entries, select the radio button for ‘Look up values from an external data source’ and click Add

29. Select Create a new connection to: and Receive data, Next

30. Select SharePoint library or list, Next

31. Enter the URL of your site where you created the custom lists above (in class, http://portal.awbikes.local), Next

32. Choose the BikeModels list, Next

33. Select both the ModelNumber and BikeType fields, Next
(you will just display the ModelNumber but you will use the type in the filter below)

34. Select ‘Store a copy of the data…’, Next

35. Enter a name for the connection, BikeModels, select the ‘Automatically retrieve data…’, Finish

36. Click the XPath button for Entries

37. Expand the folders and select ModelNumber, click Filter Data

38. Click Add

39. Set the filter to BikeType, is equal to, Select a field or group…

40. On the Data Source drop-down, select Main, Bike Type, OK, OK, OK, OK, OK already!

41. Preview the form to test the filtering

You can create the filtering using only one list (just BikeModels) by selecting the Show only entries with unique names option. For very large lists this may impact performance.

Happy filtering!