Lookup Function

The lookup function allows a form designer to retrieve data from a column within a SharePoint list and display that data on a form or use it in a formula.

A common requirement among form designers is the ability to surface SharePoint data from another list and optionally process that data. Displaying an entire list item or a list view is possible with the List Item and List View controls respectively, however these controls render html as their output which cannot be processed in a Nintex formula. In scenarios where you want to obtain a value from a list column, for display or processing in a formula, the lookup function is a viable approach.

Usage

Within a Nintex Form, the lookup function can be used within a:

  • Calculated Value control: Double click a calculated value control to access the Formula property.
  • Form Variable: Click on the Form Variables button in the ribbon menu to create a new Form Variable.
  • Rule: Click on the Rules button in the ribbon to create a new Rule.

How the lookup function obtains data

To understand how the lookup function determines what data to bring back, consider the following lookup function:

lookup(“listA”, “ID”, 1, “Title”)

This lookup will send a query to SharePoint, asking for list items within a list titled ‘listA’ and return the Title column value from any list items whose ID column value is 1.

Note: The match on the value is case-insensitive.

The lookup function does not support complex query construction; it determines what list items are included, as data to bring back, based on the filter column being an exact match to the value you specify. You can compose complex formulas with the formula builder if you need to build up a complex query, however be mindful that each lookup function will send an individual request to SharePoint for data.

Parameters

list title: The title of list that contains the data you are querying.

  • Required? Yes
  • Position: Must be first parameter in function.
  • Advanced usage: To point to a list in another site, precede the list title with the server relative url path of the site, then delimit the list title with a pipe ‘|’ symbol, e.g.“/sites/siteCollection/siteA|customList”


column to filter on: The name of the column in the list that you want to filter on. Specifically, this column will be used to filter which list items are treated as matches against the third parameter.

  • Required? Yes
  • Position: Must be second parameter in function.


value to filter on: The value you specify for this parameter is compared against each item in the list, if it matches the value for the column you specify in the second parameter, the list item data is returned.

  • Required? Yes
  • Position: Must be third parameter in function.


output column: The name of the column in the list that you want to get data from. Data from the output column is returned in the formula.

  • Required? Yes
  • Position: Must be fourth parameter in function.


multiple values <optional>: Used to signify that you want multiple values returned from the lookup function. This should be used if there are more than one list item which matches the filter you’ve configured and you want an array of values to be returned.

  • Required? No
  • Position: Must be fifth parameter in function.
  • Usage: Specify the value 'true', without the quotes.
  • Note: The maximum items number of items that can be returned in a lookup function is limited to 1000.

value data type <optional>: Use to manually set the underlying data type that SharePoint will compare your value as.

  • Required? No
  • Position: Must be sixth parameter in function.
  • Usage: The list of the most common SharePoint data types to query are: Text, Note, Number, Currency, Integer, Boolean, DataTime, Lookup, Choice, URL.

Additional Information

List location

The list you are querying­ can exist within the current site, or within sites within the current site collection.

Data In

The following types of data are supported for the ‘value to filter on’ parameter:

  • Text: Surround your text with a double or single quote.
  • Number: Does not need to be surrounded with quotes.
  • Date: Pass a date value from either a Date control, or use the date() or convertToDate() runtime function to create a new date value.
  • Boolean: A true/false value. Does not need to be surrounded with quotes.
  • Lookup: A SharePoint lookup value of the format – 1;#text. Alternatively, the text value by itself, i.e. 'text'.
  • User: A SharePoint person or group column type.
  • Managed Metadata: Specify the name of the term to match against.

Note:  Null is a valid input value.

The lookup function does not currently support multiple values being passed in as the input value parameter. Only a single value is supported. I.e. you cannot pass an array of values into a lookup function.

Data Out

The returned column value will not be formatted by the lookup function unless it’s one of the following SharePoint types:

  • Lookup
  • User

For Example: Given a SharePoint Person column, the displayed value of a Person column in SharePoint may appear as “User A”, but the underlying SharePoint text is of the format “1;#UserA”.

If a lookup function is configured to return a Person column’s value, it will convert it into text, using the internal format of “1;#UserA”.

By default, the data returned from this function is for a single column in a list. You may want to return multiple column values: For example, where multiple list items matched a given query. To do this, append ‘true’, without the quotes, as the last parameter of the function. For multiple output values, they are returned inside an Array.An array is of the format [value1,value2].

Inserting an existing control, property or variable

You can use a Named Control, Item Property, Form Variable, Workflow Variable or a pre-defined property as the input value of a lookup function. All types of data to insert are listed in the Formula Builder dialog.

Note: Quotes are not necessary when inserting a control, property or variable.

Runtime behavior

The lookup function is dynamic; if any parameters that are passed into the function change during runtime, the lookup function re-fetches the data and triggers the formula to re-evaluate.

All lookup data is cached for 2 minutes to reduce network traffic.

All lookup functions are evaluated on load of a form, unless they are configured to not recalculate on view mode. This option is per control in the control property window.

Lookup functions are processed asynchronously, meaning they do not halt the form from operating while they fetch the SharePoint data and evaluate. This helps in scenarios where the network between you and the data is slow or physically far apart.

Lookup functions currently do not work in Nintex external forms.

Dependent formulae

Formulae that depend on other formulae containing a lookup function will not wait before evaluating. Consider the following scenario of two Form Variables:

Variable A has the formula: Variable B + 1
Variable B has the formula: lookup(“listA”, “Title”, “Task1”, “ID”)

Variable A depends upon Variable B, however an individual formula only waits until all endogenous lookup functions are complete before evaluating. The result is that Variable A will momentarily evaluate to ‘1’, as Variable B has not yet completed. When Variable B does complete, it notifies Variable A to re-calculate, at which time Variable A displays the complete value of: ‘<Variable B’s lookup value> + 1’.

If you have multiple, dependent lookup formulae and require all lookup functions to be complete before displaying a result to the user, utilize a single formula with nested or chained lookup functions.

Security Considerations

The lookup function performs the lookup of SharePoint data as the current user. Therefore, if the user filling out a Nintex Form does not have the necessary SharePoint access to the list or list item(s), the lookup function will not return any data.

Nested Functions

Lookup functions can be nested inside of other lookup functions. Lookup functions will evaluate inside out, (for example, a lookup function inside another lookup function will evaluate first) to maintain formula correctness.

Circular Reference

As a form designer, be mindful not to construct a formula which contains a circular reference. A circular reference is where a formula is dependent on another formula to complete, but that formula depends upon the original formula completing.

If a circular reference is accidentally configured, the lookup function will stop evaluating after a fixed number of cycles.

Troubleshooting

To help with troubleshooting issues, preview your Nintex Form and press F12 to open the IE developer toolbar. Next, click on the Script tab and ensure that the Console tab on the right is selected. Refresh your preview window by right clicking and selecting Refresh.Logging will appear in the window, and help you to troubleshoot common misconfiguration/data access issues.