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
• Validations

Syntax

LOOKUP(objectName, fieldName, criteria)
LOOKUP(objectName, fieldName, criteria, sortBy, sortOrder, sortBy2, sortOrder2)

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'.

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 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:

LOOKUP('TAX_TABLE', 'tax_rate', 'state =' + customer.state)

Notes:

• Since the Order object has a customer Lookup, the Referential Field can be used to get the customer's state.
• Referential Fields can be used only on the right hand side of the criteria expression. They cannot be used on the left hand side.