RECOFFSET( ) function
Syntax
RECOFFSET(field, number_of_records)
Parameters
| Name | Type | Description |
|---|---|---|
| field |
character numeric datetime |
The name of the field to retrieve the value from. |
| number_of_records | numeric |
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.
Examples
Basic examples
Returns an Amount value from the next record:
RECOFFSET(Amount,1)
Returns an Amount value from the previous record:
RECOFFSET(Amount, -1)
Advanced examples
Using 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 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.
Remarks
The RECOFFSET( ) function returns a field value from a record that is a specified number of records above or below the current record.
When to use RECOFFSET( )
This function is commonly used for advanced comparison testing.
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.
The beginning or end of a table
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.
ACL Scripting Guide 14.1