Basic Scripts operate on fields within a single record, but it is also possible to construct scripts that make use of information in other records. Scripts that refer to data in more than one record are called inter-record scripts. Inter-record scripts are defined just as basic scripts with two important differences. The most important of these differences, is the use of two special operators: ffr and sort. Simply using these operator in your script changes the script's type from a basic script to an inter-record script.
Operator & Args | Purpose | Explanation |
|---|---|---|
| sort [fld] [up?] | Sort | 2 arguments, each of which must be an integer. The first argument determines what field to use for sorting. The second argument is 0 if the records should be sorted from high to low (descending) |
| ffr [fld] [off] [d] | Field From Record | 2 or 3 arguments, the first 2 of which must be an integer. The first argument is the field to be retrieved. (As in the design view, fields are numbered beginning with 0). The second argument is the record offset. That is, the relative location of the target record from the current record. If a third argument is present, it is used as a default value for this function if or when the specified record does not exist (as may often happen for the first record in the sort order). |
The second important difference between an inter-record script and a basic script is that an inter-record script is made of TWO distinct parts. The first part is a single sort clause. The second part contains one or more additional clauses and is executed once for each record in the database.
Here is an example of an inter-record script:
(sort 1 1)(+ %0 (ffr 1 1))
This script sorts first sorts the database so that field 1 is ascending in value. Then for each record, it adds the value of field 0 in the current record to the value of field 1 in the next record.
The sort clause ensures that inter-record scripts will calculate the expected values even when records are temporarily arranged in an unexpected order.
In general, you cannot use references to inter-record scripts within another script. A special case, however, does allow circumventing this generality and comes in useful on occasion. When the 'ffr' function is used with a default value, it can be used to reference any field in the database. This makes possible operations such as accumulation for a specific type of record:
assume the database has three fields: [string] [int] [calc] the string is a person's name, the int is how much money they borrow on a particular occasion, and we want to have a running total of the amount each person owes at any given time.
(sort 0 1) (+ (? (seq (ffr 0 -1 na) %0) (ffr 2 -1 0) 0) %1)
This script deserves some explanation:
| The first clause "(sort 0 1)" sorts the data by the person's name (field 0). |
| The clause "(ffr 0 -1 na)" gets the name from the previous record, or uses the string "na" if there is no previous record. |
| The clause "(seq (ffr 0 -1 na) %0)" compares the name in the previous record with the name in the current record. |
| The clause "(? (seq (ffr 0 -1 na) %0) (ffr 2 -1 0) 0)" returns the accumulator's value (field 2) in the previous record if "(seq (ffr 0 -1 na) %0)" is true. Otherwise it returns 0. Note also, that it returns 0 in the special case when the previous record doesn't exist. |
| The clause "(+ (? ... ) %1)" adds the value described above with the amount borrowed (field 1) in the current record. |
Here is what a sample data base might look like:
| name | amount | accumulated |
|---|---|---|
| Scott | 4 | 4 |
| Scott | 2 | 6 |
| Marc | 3 | 3 |
| Marc | 4 | 7 |
| Marc | 5 | 12 |