LookupNext

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 version 

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.

Example 1 

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 the Lookup function; because the weight of an item may not exactly match the maximum weight, you 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