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.
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.
You can accomplish this functionality as follows:
- 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.
- 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.
- On the Tools menu, click Data Connections.
- On the Data Connections dialog box, click Add, and add a Receive data data connection to the Fruits table you created in step 1.
- Double-click on the fruit drop-down list box to open its Properties dialog box.
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:
- While you are still in the Properties dialog box of the drop-down list box, click on the Rules button.
- On the Rules dialog box, click Add.
- On the Rule dialog box, click Add Action.
- 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.
- On the Insert Formula dialog box, click Insert Field or Group.
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.
- On the Filter Data dialog box, click Add.
- 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.
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:
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.
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.
Related InfoPath Articles:
- Auto-fill a repeating table with data from a SharePoint list when a row is inserted
- Simulate Excel's VLOOKUP function in InfoPath to look up data in a secondary data source
- Auto-fill InfoPath fields with Excel data in SharePoint 2013
- How to populate an InfoPath form when it is opened/loaded
- Auto-populate a drop-down list box with past and future dates