How to simulate Excel's VLOOKUP function in InfoPath to look up data in a secondary data source using rules and filters

Learn how to use rules and filters to look up data in a secondary data source (here: a SharePoint list) when an item is selected from a drop-down list box and create functionality similar to Excel's VLOOKUP function, but then in InfoPath.

ADVERTISEMENTS

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 have the last name of the contact you selected in the drop-down list box to be displayed in a text box.

You basically want to have functionality similar to the VLOOKUP function in Excel, but then in InfoPath.

Solution

Use InfoPath Rules and Filters to look up data in a secondary data source (a SharePoint list in this case) and use this data to populate a field on an InfoPath form.

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 Text Box to the InfoPath form template and name it lastname.
  6. Double-click the Drop-Down List Box control to open its Properties dialog box.
  7. On the Drop-Down List Box 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, leave Set a field's value selected in the Action drop-down list box.
  11. On the Action dialog box, click the button behind the Field text box.
  12. On the Select a Field or Group dialog box, expand all of the nodes, select the lastname node, and click OK.
  13. On the Action dialog box, click the button behind the Value text box.
  14. On the Insert Formula dialog box, click Insert Field or Group.
  15. 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 Last_Name node, and click Filter Data
  16. On the Filter Data dialog box, click Add.
  17. 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.
  18. 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.
  19. On the Specify Filter Conditions dialog box, click OK.
  20. On the Filter Data dialog box, click OK.
  21. On the Select a Field or Group dialog box, click OK.
  22. On the Insert Formula dialog box, the formula should resemble the following:
    @Last_Name[@ID = contact]

  23. Click OK on all open dialog boxes to close them.
  24. Save your work and test the form.

Now whenever you select a contact from the drop-down list box, the last name of the contact will be looked up in the data of the SharePoint list and the contact that has the same ID as the selected item in the drop-down list box will be displayed in the lastname field on the InfoPath form.

Notes:

  • This solution works for InfoPath browser forms.
  • This technique is not restricted to secondary data sources that are connected to SharePoint lists, but can also be used with all types of secondary data sources.
  • This technique is not restricted to looking up display names in drop-down list boxes, but can be used to look up data in any other column in a secondary data source.
  • This technique is not restricted to drop-down list boxes, but can be used with all types of list controls in InfoPath.
 
 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