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.

ADVERTISEMENTS

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.
    Table in Microsoft Access
    Figure 1. Fruits table in Microsoft Access.
  2. Design an InfoPath form template as shown in figure 2.
    The InfoPath form template in design mode
    Figure 2. The InfoPath form template in design mode.

    with a Main data source that resembles the following figure:

    The Main data source of the form template
    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:

    Drop-down list box Properties dialog box
  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.

    The Select a Field or Group dialog box displaying the structure of the secondary data source.
  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.

    The Select a Field or Group dialog box displaying the Main data source.

    Your Specify Filter Conditions dialog box should resemble the following figure:

    The Specify Filter Condition dialog box
  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.

 
 Subscribe for updates via RSS or Email

Copyright: This article may not be used on web sites (whether personal or otherwise), copied, disseminated, altered, printed, published, broadcasted, or reproduced in any way without an expressed written consent of S.Y.M. Wong-A-Ton. The techniques demonstrated in this article may be used within any Microsoft InfoPath project. This article is provided without any warranties. Copyright for this article is non-transferrable and remains with the author, S.Y.M. Wong-A-Ton.

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners
InfoPath 2013 Cookbook 2: 121 Codeless Recipes for SharePoint 2013
InfoPath 2010 Cookbook: 101 Codeless Recipes for Beginners
InfoPath 2010 Cookbook 2: 101 Codeless Recipes for SharePoint 2010
InfoPath 2010 Cookbook 3: 101 Code Recipes for C# Developers
InfoPath 2010 Cookbook 5: Integrating InfoPath with Excel and Excel Services


Related InfoPath Articles:

ADVERTISEMENTS

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners

InfoPath 2013 Cookbook 2: 121 Codeless Recipes for SharePoint 2013

InfoPath 2010 Cookbook: 101 Codeless Recipes for Beginners

InfoPath 2010 Cookbook 2: 101 Codeless Recipes for SharePoint 2010

InfoPath 2010 Cookbook 3: 101 Code Recipes for C# Developers

InfoPath 2010 Cookbook 4: 101 Code Recipes for VB Developers

InfoPath 2010 Cookbook 5: Integrating InfoPath with Excel and Excel Services