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

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

clip_image002

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
clip_image004

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

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
clip_image008

13. Add another text field named BikeModel
clip_image010

14. Drag the two fields into the form
clip_image012

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

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
clip_image016

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
clip_image018

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

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

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)

clip_image024

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
clip_image026

38. Click Add

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

clip_image028

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

41. Preview the form to test the filtering
clip_image032

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!

Related Posts

image.png image_thumb.png image.png image_thumb.png image.png image_thumb.png image.png image_thumb.png image.png image_thumb.png image.png image_thumb.png image.png image_thumb.png image.png image_thumb.png

Get InfoPath to display lookup column value, not ID

SharePointDesignerSQLDataForm.png SharePointDesignerSQLDataForm_thumb.png SharePointDesignerDataViewSQL.png

SharePoint-InfoPath 2007 Video Tutorial: Displaying SQL Data in Data View Web Parts (Part 2)

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

Leave a Reply