About Record Numbers

version 3

There are three numbers associated with a record:

• Record number

• Selected record number

• Sequence number

The record number is the absolute/physical record number for a record. A record number is automatically assigned to each new record and remains constant for the record until the record is deleted or the file is permanently reordered using 4D Tools. Record numbers start at zero. They are not unique because record numbers of deleted records are reused for new records. They also change when the file is permanently reordered using 4D Tools or when the database is compacted or repaired. New records added in transaction are assigned temporary record numbers, and are assigned final record numbers when the transaction is validated.

Note: Temporary record numbers that are created during transactions start at 18,000,000.

The selected record number is the position of the record in the current selection, and so depends on the current selection. If the selection is changed or sorted, the selected record number will probably change. Numbering for the selected record number starts at one (1).

The sequence number is a unique nonrepeating number that may be assigned to a field of a record. It is not automatically stored with each record. It starts at 1 and is incremented for each new record that is created. Unlike record numbers, a sequence number is not reused when a record is deleted or when a table is compacted, repaired, or permanently reordered using 4D Tools. Sequence numbers provide a way to have unique ID numbers for records. If a sequence number is incremented during a transaction, the number is not decremented if the transaction is canceled.

The following tables illustrate the numbers that are associated with records. Each line in the table represents information about a record. The order of the lines is the order in which records would be displayed in an output form.

• Data column: The data from a field in each record. For our example, it contains a person's name.

• Record Number column: The record's absolute record number. This is the number returned by the Record number function.

• Selected Record Number column: The record's position in the current selection. This is the number returned by the Selected record number function.

• Sequence Number column: The record's unique sequence number. This is the number returned by the Sequence number function when the record was created. This number is stored in a field.

The first table shows the records after they are entered.

• The default order for the records is by record number.

• The record number starts at 0.

• The selected record number and the sequence number start at 1.

Note: The records remain in the default order after a command changes the current selection without reordering it; for example, after the Show All menu command is chosen in the User environment, or after the ALL RECORDS command is executed.

The next table shows the same records sorted by name.

• The same record number remains associated with each record.

• The selected record numbers reflect the new position of the records in the sorted selection.

• The sequence numbers never change, since they were assigned when each record was created and are stored in the record.

The following table shows the records after Sam is deleted.

• Only the selected record numbers have changed. Selected record numbers reflect the order in which the records are displayed.

The next table shows the records after a new record has been added for Liz.

• A new record is added to the end of the current selection.

• Sam's record number is reused for the new record.

• The sequence number continues to increment.

The following table shows the records after the selection was reduced to three records and then sorted.

• Only the selected record number associated with each record changes.

Record number, Selected record number, Sequence number.