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.

A recent client project involved building a Nintex Form with the requirement of auto populating a List Lookup control. After some investigation, it didn’t look like this was possible using the out of the box settings. The solution for this need required using a List Lookup control and custom JavaScript to automatically set the value of the control.

In this example scenario, a user fills out a list form to enter their name and pick their favorite SEC football team via a drop down list. The drop down list control pulls from a Teams list and is auto populated with a default team value (i.e. “Alabama Crimson Tide” in this case).

So here’s an example of how to accomplish this solution. This solution works with both Nintex Forms 2010 and 2013.

1. Create a list called Teams and add the team names. Add the ID field to the default view (this will be referenced later). Also note the Title field display name was changed to “Team Name”.
2. Create a new custom list called “Favorite Teams” and add a new “Single line of text” column called “Favorite Team”. Also note the Title field display name was changed to “Your Name”.
3. Create a Nintex Form in the Favorite Teams list (List Tools > List > Nintex Forms > Customize the Item Form).
4. Delete the default Single Line Textbox control for the Favorite Team field.
5. Add a List Lookup control to your form in place of the Favorite Team control you just deleted.
6. Open the Control Settings for the new List Lookup field and configure it with the following settings.
   1. Name = FavoriteTeam
   2. Text connected to = Favorite Team
   3. Source SharePoint site = (Insert the address of your site).
   4. Click the Refresh button to the right of the Source List option.
   5. Source List = Teams
   6. List column name = Team Name
7. Click the Save button the save your changes to the Control Settings.
8. You can now use the form Preview to make sure the Teams are populated in the List Lookup control properly.
9. Next open up the form Settings configuration.
10. Expand the Custom JavaScript section and add the following code. This code allows you to set the default value (the item with ID = 1 from the Teams list in this case) for the Favorite Team list lookup field on the form and click the Save button. Note the value be changed as needed.
NWF$(document).ready(function() {
var defaultTeam = NWF$("#" + ddlFavTeam).val();
if (defaultTeam == '') {
NWF$("#" + ddlFavTeam).val('1');
}
});


    Note: You may have to delete and re-enter the quotation marks and/or the single quotes if you copy the above code directly off the page. Sometimes the formatting does not copy over correctly.

    

11. Go back to the FavoriteTeam List Lookup control and open the Control Settings.
12. Expand the Advanced section at the bottom of the Control Settings and enter the following values.
    1. Store Client ID in JavaScript variable = Yes
    2. Client ID JavaScript variable name = ddlFavTeam (this the variable name used in Step 10 above).
    3. Click the Save button.

    

13. You can now use the form Preview to make sure the Favorite Team field is now auto populated properly.
14. Publish the form to add items. You should be good to go!