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!