RECOFFSET()

RECOFFSET() returns a field value from a record that is a specified number of records from the current record.

You can use RECOFFSET() in an expression that compares a field in the current record with a field in another record. For example, you can add a computed field that calculates the difference between an amount in the current record and an amount in the previous record.

If the end of the file or the beginning of the file is encountered, then RECOFFSET() returns zero for numeric fields, an empty string for character fields or 19000101 for date fields.

A faster processing alternative to using RECOFFSET() may be to use a variable array populated with column values using the SAVE command provided that the number of values to be referenced is reasonable. For more information see Variable Arrays and Save.

RECOFFSET() includes special optimizations that make it substantially faster than other competitors implementations.

Function Format

RECOFFSET(field,number_of_records)

Examples

You can use RECOFFSET() to retrieve the value of the Amount field from the next record:

RECOFFSET(Amount,1)

You can also use RECOFFSET() in a computed field.

The computed field Next_Amount shows the value of the Amount field in the next record only if the next record has the same customer number. To define this computed field in a procedure, use the following syntax:

DEFINE FIELD Next_Amount COMPUTED¿

¿

RECOFFSET(Amount,1) IF RECOFFSET(No,1) = No¿

Amount¿

Next_Amount is the value of the next recordÕs Amount field only if the customer number in the next record is the same as the customer number in the current record; otherwise, Next_Amount is the Amount of the current customer record.

To find out how to create a multi-value computed field, see Multi-Value Computed Fields.

To find out how to use the Define command to create a computed field in a procedure, see Computed Fields.