Lookup()

Lookup()

Category: Logical

Syntax: Lookup ( sourceField { ; failExpression } )

Parameters: sourceField ”any related field; failExpression ”an expression to evaluate and return if the lookup fails. This is an optional parameter.

Description: Returns the contents of sourceField , or if no related record is found, the result of the failExpression . The table containing the sourceField must be related to the table where the Lookup() is defined.

A calculation field that contains a Lookup() function can be stored or unstored. If it is unstored, then anytime the sourceField changes, the calculation field updates. If the calculation is stored, which is typically why you want to use a Lookup in the first place, then changes to the sourceField do not cascade automatically through to the calculation field. Lookup() is retriggered when any of the relationship's match fields (in the current table, not the source table) are modified, or when a relookup is triggered on any of those fields.

Examples:

Imagine you have a stored calculation field in an Invoice table called CustomerNameLookup , defined as follows :

 Lookup (Customer::CustomerName;"<Missing Customer>") 

Assume that the Invoice and Customer tables are related on the CustomerID . Whenever the CustomerID field is modified in the Invoice table, this triggers the lookup, and the name of the customer is copied into CustomerNameLookup . If an invalid CustomerID is entered, <Missing Customer> is returned. Because CustomerNameLookup is stored, indexed searches can be performed on it.

Be aware, however, that if the CustomerName field is updated in the Customer table, the change does not cascade automatically through to the Invoice table.

Comments:

Lookup() is invaluable for addressing performance issue caused by interacting with related (and hence unindexed) values.