Lookup runtime function

Using the lookup runtime function in a formula, you can retrieve data from columns in SharePoint lists according to the lookup criteria you specify. You can display the data as-is or process it further using the formula.

Default formula: lookup("list title", "column to filter on", "value to filter on", "output column")

Parameter	Description	Required?	Position	Notes
list title	Title of list containing data for query.	Yes	1
column to filter on	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.	Yes	2
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.	Yes	3
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.	Yes	4
multiple values	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.	No	5	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	Use to manually set the underlying data type that SharePoint will compare your value as.	No	6	The list of the most common SharePoint data types to query are: Text, Note, Number, Currency, Integer, Boolean, DataTime, Lookup, Choice, URL.

Additional Information

How lookup works

The lookup runtime function sends a query to SharePoint asking for list items within the specified list that match the specified filters; the function returns the value of the specified output column for any matching list items.

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. While complex formulas are possible, note that each individual lookup function sends an individual query to SharePoint.

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

List location

The list you are querying­ must exist within the current site.

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 anonymous forms (forms published to Nintex Live).

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 form does not have the necessary SharePoint access to the list or list items, 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 references

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 form in IE and then press F12 to open the IE developer toolbar. Next, click 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.