LOOKUP function

The LOOKUP function uses a field in the current record to get a value from an object that is not related by a Lookup relationship. (If a Lookup relationship exists, use a Referential Field, instead.)

In essence, the LOOKUP function is a search. You specify filtering and sorting criteria. along with a field to return. The function returns the first value found by that search.

Warning: Be careful when using this function. If a search returns multiple records, the first value encountered is returned. That may or may not be the behavior you want. To be safe, ensure that either: You use it only in objects that have a unique index, and only when specifying the unique index in the search criteria (to guarantee that only one value is found). If the search returns multiple records, the value of the target field is identical in each case. If the search returns multiple records that have different values, getting back the value in the first record found is the behavior you want.

Considerations

LOOKUP functions are available for use in these contexts:

• Formula Fields
• Triggering Criteria in Action Based Data Policies
• Update Record Action in Data Policies
• Validations

Syntax

<syntaxhighlight lang="java" enclose="div">

LOOKUP(objectName, fieldName, criteria) LOOKUP(objectName, fieldName, criteria, sortBy, sortOrder, sortBy2, sortOrder2) </syntaxhighlight>

where:

• Each of the values is a string--a set of characters surrounded by single-quotes, or a concatenation of such strings, like this: 'a' + 'b'.
• For the criteria, the string could also take the form 'targetObjectFieldName =' + currentRecordFieldName, where the equals sign could be replaced by any of the supported comparison operators.

And where:

criteria

A filter expression that specifies records to select.

(For criteria, it is also possible to specify a string of the form 'targetRecordfield =' + currentRecordField,
where the equals sign can be replaced by any of the Filter Expression Comparison Operators.)

sortBy

Sort the search results on the specified field.

sortOrder

Specify if the sort order is ascending ("asc") or descending ("desc"). Not case sensitive. The default is ascending.

sortBy2

Do a secondary sort on the specified field.

sortOrder2

Specify if the sort order on the second level is ascending ("asc") or descending ("desc"). Not case sensitive. The default is ascending.

Returns

• The value of the named field, taken from the first record found that satisfies the search criteria, after sorting.
• The target field's default value, if no records are found.

Example

Here is an instance of a function as it might be used for an Order in the Sample Order Processing System. The function looks up the tax rate for an order, based on the customer's state:

<syntaxhighlight lang="java" enclose="div">

LOOKUP('TAX_TABLE', 'tax_rate', 'state =' + customer.state) </syntaxhighlight>

(Since the Order object has a customer lookup field, it can be used in a Referential Field to get the customer's state.)