Functions Reference

Chapter 8
|
Logical functions 141
LookupNext
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 the lookup fails.
Data type returned
text, number, date, time, timestamp, container
Description
Returns the value specified in sourceField using the relationships in the relationships
graph. If the lookup fails, the value from the source field in the next lower or higher
matching record will be returned, as specified by lower/higherFlag. In order 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.
Example
There are two tables, People and Company, in a database file containing data as shown
below.
People table
Company table
CompanyID Employee
100 John Smith
200 Peter Wong
300 Sally Anderson
377 Mary MacKenzie
CompanyID CompanyName Code
100 Apple 91234
100 Apple 82345
200 FileMaker 95054
300 Motorola 93456
400 Cisco 88123