Auto-fill fields in InfoPath from a drop-down list box selection and secondary data source

Use rules and filters on the secondary data source to retrieve information from the secondary data source and fill fields on the form with data related to the selected item in the drop-down list box.

Problem

You have a drop-down list box on your InfoPath form that is being populated with data from a secondary data source, i.e. an XML document, a database table, or a SharePoint list. Whenever an item is selected from the drop-down list box, you want to auto-fill fields on your form with data corresponding to the selected item in the drop-down list box.

Solution

Use Rules and Filters on the secondary data source to retrieve information from the secondary data source and fill fields on the form with data related to the selected item in the drop-down list box.

Discussion

You can accomplish this functionality as follows:

1. Create a Microsoft Access database with a Fruits table that has the columns ID (Autonumber), Name (text), and Colour (text) as shown in figure 1.
   
   Figure 1. Fruits table in Microsoft Access.
2. Design an InfoPath form template as shown in figure 2.
   
   Figure 2. The InfoPath form template in design mode.
   
   with a Main data source that resembles the following figure:
   
   
   Figure 3. The Main data source of the form template.
   
   Here you will populate the fruit drop-down list box with items from the database table and then fill the colour text box with a value corresponding to the selected fruit from the drop-down list box.
3. On the Tools menu, click Data Connections.
4. On the Data Connections dialog box, click Add, and add a Receive data data connection to the Fruits table you created in step 1.
5. Double-click on the fruit drop-down list box to open its Properties dialog box.
6. Bind the fruit drop-down list box to the data connection for the Fruits database table you created in step 4 as shown in the following figure:
   
   
7. While you are still in the Properties dialog box of the drop-down list box, click on the Rules button.
8. On the Rules dialog box, click Add.
9. On the Rule dialog box, click Add Action.
10. On the Action dialog box, select Set a field's value from the Action drop-down list box, select colour from the Main data source as the Field to set, and then click on the formula button behind the Value text box.
11. On the Insert Formula dialog box, click Insert Field or Group.
12. On the Select a Field or Group dialog box, select the secondary data source for Fruits from the Data source drop-down list box, click on the Colour node to select it, and then click on the Filter Data button.
    
    
13. On the Filter Data dialog box, click Add.
14. On the Specify Filter Conditions dialog box, select ID from the first drop-down list box, leave the second drop-down list box on is equal to, and select Select a field or group from the third drop-down list box.
15. On the Select a Field or Group dialog box, choose Main from the Data source drop-down list box, click on the fruit node to select it, and then click OK.
    
    
    
    Your Specify Filter Conditions dialog box should resemble the following figure:
    
    
16. Click on OK to close all open dialog boxes.
    
    The final expression for the rule your added should resemble:
    Set a field's value: colour = @Colour[@ID = fruit]

You should now have a fully functional form so that when you select a fruit from the drop-down list box, its corresponding colour should appear in the colour text box.

Related InfoPath Articles:

• Auto-fill a repeating table with data from a SharePoint list when a row is inserted
• How to simulate Excel's VLOOKUP function in InfoPath to look up data in a secondary data source using rules and filters
• Sequential database table record navigation