Functions Reference

Chapter 8
|
Logical functions 135
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.
Note LookupNext returns ? when the related table is an ODBC data source.
Example
There are two tables, People and Company, in a database file containing data as shown
below.
People table
Company table
The People and Company tables are related using the number field CompanyID. The
calculation CompanyName = LookupNext(Company::CompanyName;Higher)
defined in the People table will return Apple, FileMaker, Motorola and Cisco for records
1 to 4.
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