Home > Reference > Functions reference > Logical functions > LookupNext

LookupNext

Purpose 

Returns the value specified in sourceField using the relationships in the relationships graph. If the lookup fails, the value from sourceField in the lower or higher matching record will be returned, as specified by lower/higherFlag.

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

Description 

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 

There are two tables, People and Company, in a database file containing data as shown below.

People table

 

CompanyID	Employee
100	John Smith
200	Peter Wong
300	Sally Anderson
377	Mary MacKenzie

Company table

 

CompanyID	CompanyName	Code
100	Apple	91234
100	Apple	82345
200	FileMaker	95054
300	Motorola	93456
400	Cisco	88123

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.

Related topics 

Functions reference (category list)

Functions reference (alphabetical list)

About formulas

About functions

Defining calculation fields

Using operators in formulas