Nintex Forms offers many great features for designing and customizing your SharePoint forms. There are times when you need to pull in data to your forms from other sources, including other SharePoint lists.  One way to accomplish this is to use the Lookup function, which allows you to retrieve data from a column within a SharePoint list and display that data on a form or use it in a formula. The Lookup function can be utilized within a Calculated Value control, Form Variable, or Rule.

Here’s the syntax for configuring the Lookup function:

lookup(List Title, Column to filter on, Value to filter on, Output column)

• List Title – The title of the list that contains the data you are retrieving.  The list can be in the current site or another site.  To configure with a list on another site, start by prepending the List Title with the server relative URL path of the site and then delimit the list title with a pipe (|) symbol.  For example, “/sites/siteCollection/siteName|ListTitle”.
• Column to filter on – The name of the column in the list that you want to filter on.  This column is used to filter which list items are returned as matches occur against the third parameter.
• Value to filter on – The specified value that is compared against each item in the list.  Be sure to use a Named Control instead of an item in the Item Properties tab for this value.
• Output column – The column name in the list from which the data is retrieved.

Lookup Function Configuration Example

For our example, we created an Order form using Nintex Forms that is used to create a customer order.  When a user enters a value into the Product Number field, the Orders form pulls back data from the Product Information list using the Lookup function.  The following two lists were created and configured for this example:

• Orders – This list houses the order form and stores the order information upon submission.  List columns include Order Number, Product ID, Product Name, Production Description, and Product Cost.
• Product Information – This list is used to provide information about the products offered.  List columns include Product ID, Product Name, Product Description, and Product Cost.

Now that we have the required lists and columns in place, here are the steps used to configure the Product Information lookups in the Orders form.

1. Open the Orders form in the Nintex Forms designer.
2. Add a Calculated Value column to the form to display the Product Name and configure the Control Settings with the following values:
   • Formula = lookup(“Product Information”, “Product ID”, ProductID, “Product Name”) where ProductID is the Name of the form control.
   • Name = ProductName
   • Connected to = Product Name
   • Save as data type = String
   • Recalculate formula on view mode = Yes
3. Add a Calculated Value column to the form to display the Product Description and configure the Control Settings with the following values:
   • Formula = lookup(“Product Information”, “Product ID”, ProductID, “Product Description”) where ProductID is the Name of the form control.
   • Name = ProductDescription
   • Connected to = Product Description
   • Save as data type = String
   • Recalculate formula on view mode = Yes
4. Add a Calculated Value column to the form to display the Product Cost and configure the Control Settings with the following values:
   • Formula = lookup(“Product Information”, “Product ID”, ProductID, “Product Cost”) where ProductID is the Name of the form control.
   • Name = ProductCost
   • Connected to = Product Cost
   • Save as data type = Currency
   • Use thousand separator = Yes
   • Value prefix = $
   • Recalculate formula on view mode = Yes
5. Publish the form and open it in the Orders list.
6. Enter a valid value into the Product ID field on the form and go to another field.  The form will automatically pull and display the data from the Product Information list into the Product Name, Product Description, and Product Cost fields.