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
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.
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.
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).