Syntax: LookupNext ( sourceField; lower/higher Flag )
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
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.
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.
: FileMaker Specifications
FileMaker 8 Product Line
Specifications and Storage Limits
Field Types and Import/Export Formats
: Calculation Functions
Working with Calculations Primer
: Custom Functions
Custom Functions Primer
Useful Custom Functions
: Script Steps
Script Step Reference
: Quick Reference
FileMaker Error Codes
FileMaker Keyboard Shortcuts
FileMaker Network Ports
FileMaker Server Command Line Reference
FileMaker XML Reference
: Other Resources
Where to Go for More Information