LookupNext
Purpose
Returns the next lower or higher value in sourceField when there isn't a matching related value.
Format
LookupNext(sourceField;lower/higherFlag)
Parameters
sourceField - the
field from which the
lookup value is taken
lower/higherFlag - the keywords
lower or
higher denote whether the value from the next lower/higher matching
record must be taken if no related record is found
Data type returned
text, number, date, time, timestamp, container
Originated in
FileMaker Pro 7.0
Description
Returns the value specified in
sourceField using the
relationships in the
relationships graph. LookupNext is similar to
Lookup, except that when the lookup fails, the value from
sourceField in the lower or higher matching record will be returned, as specified by
lower/higherFlag.
For this function to access the value in
sourceField, the
tables containing the source field and
calculation field need to be related. Calculations using the
LookupNext function won’t be forced to be
unstored calculations.
Notes
•LookupNext returns ? when the related table is an ODBC data source.
Examples
In this example, you are shipping several items and the cost of shipping is based on weight ranges. Use the LookupNext function to find which shipping rate applies for an item. Use LookupNext with the higher flag instead of Lookup because the weight of an item may not exactly match the maximum weight, therefore we want to find the next highest value.
There are two tables, Items and Shipping Costs, in a database file containing data as shown below.
Items table
Item | Weight | Rate Lookup |
Lamp | 8 | |
Chair | 22 | |
Desk | 60 | |
Bed | 120 | |
Shipping Costs table
Rate Code | Maximum Weight |
A | 25 |
B | 50 |
C | 100 |
D | 150 |
The two tables are related by Weight and Max Weight. The calculation field Rate Lookup is defined as Rate Lookup = LookupNext(Shipping Costs::Max Weight; Higher).
The Rate Lookup calculation field will return 25, 25, 100, and 150 for records 1 to 4. Rate Lookup can then be used to get the correct rate code (A, A, C, and D respectively).
Item | Weight | Rate Lookup |
Lamp | 8 | 25 |
Chair | 22 | 25 |
Desk | 60 | 100 |
Bed | 120 | 150 |
Related topics