LOOKUP function

From AgileApps Support Wiki
(Redirected from LOOKUP)

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.

Warn.png

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