LookupNext()

Category: Logical

Syntax: LookupNext ( sourceField; lower/higher Flag )

Parameters:

sourceFieldAny related field.

lower/higher FlagKeyword that indicates whether to take the next lower or higher value if no direct match is found.

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

Description:

Returns the contents of sourceField, or if no related record is found, the next lower or higher match value. The table containing the sourceField must be related to the table where the LookupNext() function is defined.

The LookupNext() function is very similar to the Lookup() function; they differ only in how they handle the case of no matching record. The Lookup() function returns a fail expression in such cases, whereas the LookupNext() returns the value associated with the next lower or higher match.

The Lower and Higher flags are keywords and should not be placed in quotation marks.

See the Lookup() function to learn about how a lookup is triggered and how the storage options determine how often the LookupNext() function is to be refreshed.

Looking up a value from the next higher or lower matching record is desirable when mapping a continuous variable onto a categorical variable. Think, for instance, of how student grades typically map to letter grades. A grade of 90 to 100 is considered an A, 80 to 89 is a B, 70 to 79 is a C, and so on. The percentage value is a continuous variable, whereas the letter grades are categorical.

Using the Lookup() function, if you wanted to use the students percentage score to retrieve the letter grade, you would need to have records for every possible combination of percentage and letter grade.

The LookupNext() function makes it possible to have records representing only the border conditions. For student grades, you would need to have five records in your lookup table: 90 is an A, 80 is a B, 70 is a C, 60 is a D, and 0 is an F. You could then relate a students percentage score to this table, and define the following formula as the StudentLetterGrade:

 LookupNext (GradeLookup::LetterGrade; Lower)

Given a percentage score of 88, which has no exact match, the next lower match would return a letter grade of B.

Examples:

Function	Results
Lookup (ShipRates::ShippingCost; Higher)	Returns the contents of the ShippingCost field from the ShipRates table. If no exact match is found, the next highest match is returned.