Lookup()

Category: Logical

Syntax: Lookup ( sourceField {; failExpression })

Parameters:

sourceFieldAny related field.

failExpressionAn expression to evaluate and return if the lookup fails. This is an optional parameter.

Data type returned: Text, Number, Date, Time, Timestamp, Container

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 relationships match fields (in the current table, not the source table) are modified, or when a relookup is triggered on any of those fields.

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

Examples:

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

 Lookup (Customer::CustomerName;"")

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