Functions Reference

FILEMAKER FUNCTIONS REFERENCE 168
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.
Note 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
Shipping Costs table
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).
Item Weight Rate Lookup
Lamp 8
Chair 22
Desk 60
Bed 120
Rate Code Maximum Weight
A 25
B 50
C 100
D 150