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:
- 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'.
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:
- <syntaxhighlight lang="java" enclose="div">
LOOKUP('TAX_TABLE', 'tax_rate', 'state =' + customer.state) </syntaxhighlight>
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.