Auto populate repeating table in InfoPath with SharePoint list data when a row is inserted
Learn how to auto populate a repeating table in InfoPath by using rules and filters on data from a SharePoint list when the row is inserted.
You have a drop-down list box in InfoPath, which is bound to a SharePoint list that contains names and email addresses of contacts. The values of items in the drop-down list box are bound to the IDs of the contacts and the display names of items in the drop-down list box are bound to the Last Names of the contacts.
You want to be able to select an item from the drop-down list box (bound to the SharePoint list) and then when you click to insert a row in an InfoPath repeating table, data from the SharePoint list (the selected item in the drop-down list box) is automatically populated in the repeating table fields in the newly added row.
Use Rules and Filters to copy data from a secondary InfoPath data source (a SharePoint list in this case) to be able to auto populate a repeating table row when that row is inserted.
You can auto populate a repeating table in InfoPath with data from a SharePoint list as follows:
- In SharePoint, ensure that you have a Contacts SharePoint list that you can use. This list must contain IDs, names, and email addresses of contacts.
- In InfoPath, add a Data Connection to the Contacts SharePoint list. Ensure you select the ID, First_Name, Last_Name, and E-mail_Address fields from the SharePoint list when creating the data connection.
- Add a Drop-Down List Box control to the InfoPath form template and bind it to the data connection for the SharePoint list. Set the @ID of the contact to be the Value of an item in the drop-down list box and set the @Last_Name of the contact to be the Display name of an item in the drop-down list box.
- Name the Drop-Down List Box control contact.
- Add a Repeating Table control with 3 columns to the InfoPath form template. Name the field in the first column firstname, the field in the second column lastname, and the field in the third column email.
The form template should resemble Figure 1.
Figure 1. The InfoPath form template in Design mode.
- Double-click the Repeating Table control to open its Properties dialog box.
- On the Repeating Table Properties dialog box, click Rules.
- On the Rules dialog box, click Add.
- On the Rule dialog box, click Add Action.
- On the Action dialog box, click the button behind the Field text box.
- On the Select a Field or Group dialog box, expand all of the nodes, select the firstname node, and click OK.
- On the Action dialog box, click the 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 Contacts SharePoint list (= Secondary data source) from the Data source drop-down list box, expand all of the nodes, select the First_Name node, and click Filter Data
(see Figure 2).
Figure 2. Selecting the First_Name node from the Select a Field or Group dialog box.
- On the Filter Data dialog box, click Add.
- On the Specify Filter Conditions dialog box, select ID from the first drop-down list box, select is equal to from the second drop-down list box, and select Select a field or group from the third drop-down list box.
- On the Select a Field or Group dialog box, select the Main data source from the Data source drop-down list box, select the contact node, and click OK.
- On the Specify Filter Conditions dialog box, click OK.
- On the Filter Data dialog box, click OK.
- On the Select a Field or Group dialog box, click OK.
- On the Insert Formula dialog box (see Figure 3), click OK.
Figure 3. The composed formula with filter in the Insert Formula dialog box.
- On the Action dialog box, click OK.
- Repeat steps 9 through 22 for the lastname and email nodes. Set the value of the lastname field to the value of the Last_Name node from the SharePoint list, and set the value of the email field to the value of the E-mail_Address node from the SharePoint list. The filter should be set up the same way as it was set up for the firstname field.
- Close all open dialog boxes when you're done.
- Save your work and test the form.
Now whenever you select a contact from the drop-down list box and then click the Insert item button on the Repeating Table, the data from the SharePoint list that has the same ID as the selected item in the drop-down list box will be copied over to the fields in the new row of the repeating table.
Caveat: The first row of the InfoPath repeating table will remain empty and you will have to manually delete it.
Note: This solution also works for InfoPath browser forms. This technique is not restricted to secondary data sources or SharePoint lists, but can also be used to copy data from other fields on an InfoPath form. This solution is ideal for when you have to insert the same data multiple times in rows of an InfoPath repeating table, but do not want to manually type in the same data every time you insert a new row.
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:
- 4 Ways to programmatically add a row to a repeating table in InfoPath
- InfoPath Video Tutorial: Auto-populate InfoPath repeating table row on insert
- Programmatically execute code when a repeating table row is inserted or deleted
- Update SharePoint tasks by looping through a repeating table on an InfoPath 2013 form
- Auto-populate InfoPath repeating table with dates but skip weekends