Difference between revisions of "LOOKUP function"
From LongJump Support Wiki
imported>Aeric |
imported>Aeric |
||
| (2 intermediate revisions by the same user not shown) | |||
| Line 24: | Line 24: | ||
where: | where: | ||
:* Each of the values is a ''string''--a set of characters surrounded by single-quotes, or a concatenation of such strings, like this: <tt>'a' + 'b'</tt>. | :* Each of the values is a ''string''--a set of characters surrounded by single-quotes, or a concatenation of such strings, like this: <tt>'a' + 'b'</tt>. | ||
And where: | And where: | ||
| Line 30: | Line 29: | ||
;Returns: | ;Returns: | ||
:* The value of the named field, taken from the first record | :* 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. | :* The target field's default value, if no records are found. | ||
| Line 39: | Line 38: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
: | '''Notes:''' | ||
:* Since the Order object has a <tt>customer</tt> [[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. | |||
Latest revision as of 22:58, 9 November 2011
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
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.