RECOFFSET( ) function

Returns a field value from a record that is a specified number of records from the current record.

Syntax

RECOFFSET(field, number_of_records)

Parameters

field

Character, Numeric, or Datetime. The name of the field to retrieve the value from.

number_of_records

Numeric. Specifies the number of records from the current record. A positive number specifies a record after the current record, and a negative number specifies a record before the current record.

Output

Character, Numeric, or Datetime. The return value belongs to the same data category as the input field parameter.

Remarks

This function returns a field value from a record that is a specified number of records above or below the current record. The function is commonly used for advanced comparison testing.

For example, RECOFFSET(vendor_number, 1), returns a vendor_number value from the next record, and RECOFFSET(vendor_number, -1), returns a value from the previous record. If the beginning or end of the table is encountered, the function returns zero for numeric fields, a blank string for character fields, or 1900/01/01 for date fields. The function returns blank output in these instances because there is no further record to compare the current record to.

You can use this function to compare values in 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.

Examples

The following example uses the function to retrieve the value of the Amount field from the next record:

RECOFFSET(Amount,1)

You can also use the function 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 script, use the following syntax:

DEFINE FIELD Next_Amount COMPUTED
RECOFFSET(Amount,1) IF RECOFFSET(Customer,1) = Customer
0

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 assigned a value of zero.



(C) 2013 ACL Services Ltd. All Rights Reserved. | Send feedback