Category: Logical

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

Calculation Signatures

Calculation Functions

: Custom Functions

Custom Functions Primer

Useful Custom Functions

: Script Steps

Scripting Primer

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

FileMaker 8 Functions and Scripts Desk Reference
FileMaker 8 Functions and Scripts Desk Reference
ISBN: 0789735113
EAN: 2147483647
Year: 2004
Pages: 352

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net