Auto-fill a repeating table with data from a SharePoint list when a row is inserted

by S.Y.M. Wong-A-Ton

Learn how to use rules and filters to automatically fill a repeating table row in InfoPath with data from a SharePoint list when the row is inserted.

Problem

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 an item into a repeating table, data from the SharePoint list (the selected item in the drop-down list box) is copied into fields of the new row in the repeating table.

Solution

Use Rules and Filters to copy data from a secondary data source (a SharePoint list in this case) to a new row in a repeating table when that row is inserted.

Discussion

You can accomplish this functionality as follows:

  1. 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.
  2. 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.
  3. 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.
  4. Name the Drop-Down List Box control contact.
  5. 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.

    The InfoPath form template in Design mode
    Figure 1. The InfoPath form template in Design mode.

  6. Double-click the Repeating Table control to open its Properties dialog box.
  7. On the Repeating Table Properties dialog box, click Rules.
  8. On the Rules dialog box, click Add.
  9. On the Rule dialog box, click Add Action.
  10. On the Action dialog box, click the button behind the Field text box.
  11. On the Select a Field or Group dialog box, expand all of the nodes, select the firstname node, and click OK.
  12. On the Action dialog box, click the button behind the Value text box.
  13. On the Insert Formula dialog box, click Insert Field or Group.
  14. 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).

    Selecting the First_Name node from the Select a Field or Group dialog box
    Figure 2. Selecting the First_Name node from the Select a Field or Group dialog box.

  15. On the Filter Data dialog box, click Add.
  16. 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.
  17. 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.
  18. On the Specify Filter Conditions dialog box, click OK.
  19. On the Filter Data dialog box, click OK.
  20. On the Select a Field or Group dialog box, click OK.
  21. On the Insert Formula dialog box (see Figure 3), click OK.

    The composed formula with filter in the Insert Formula dialog box
    Figure 3. The composed formula with filter in the Insert Formula dialog box.

  22. On the Action dialog box, click OK.
  23. 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.
  24. Close all open dialog boxes when you're done.
  25. 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 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 a repeating table, but do not want to manually type in the same data every time you insert a new row.

Related InfoPath Articles:

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. Usage of 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.

Working with InfoPath