4D v14.3
About Record Numbers
About Record Numbers
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. 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 database is compacted or repaired.
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 (via the Autoincrement property, the SQL AUTO_INCREMENT attribute or the Sequence number command). It is not automatically stored with each record. It starts by default 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 database is compacted or repaired. 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.
Note: 4D does not carry out any check when you modify the automatic number internal counter of a table using the SET DATABASE PARAMETER command. If you decrement this counter, the new records created may have numbers that have already been assigned.
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.
| Data | Record Number | Selected Record Number | Sequence Number |
| Tess | 0 | 1 | 1 |
| Terri | 1 | 2 | 2 |
| Sabra | 2 | 3 | 3 |
| Sam | 3 | 4 | 4 |
| Lisa | 4 | 5 | 5 |
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 Design 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.
| Data | Record Number | Selected Record Number | Sequence Number |
| Lisa | 4 | 1 | 5 |
| Sabra | 2 | 2 | 3 |
| Sam | 3 | 3 | 4 |
| Terri | 1 | 4 | 2 |
| Tess | 0 | 5 | 1 |
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.
| Data | Record Number | Selected Record Number | Sequence Number |
| Lisa | 4 | 1 | 5 |
| Sabra | 2 | 2 | 3 |
| Terri | 1 | 3 | 2 |
| Tess | 0 | 4 | 1 |
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.
| Data | Record Number | Selected Record Number | Sequence Number |
| Tess | 0 | 1 | 1 |
| Terri | 1 | 2 | 2 |
| Sabra | 2 | 3 | 3 |
| Lisa | 4 | 4 | 5 |
| Liz | 3 | 5 | 6 |
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.
| Data | Record Number | Selected Record Number | Sequence Number |
| Sabra | 2 | 1 | 3 |
| Liz | 3 | 2 | 6 |
| Terri | 1 | 3 | 2 |
Product: 4D
Theme: Records
Record number
Selected record number
Sequence number
4D Language Reference ( 4D v12.4)
4D Language Reference ( 4D v11 SQL Release 6)
4D Language Reference ( 4D v14 R3)
4D Language Reference ( 4D v14 R2)
4D Language Reference ( 4D v13.5)
4D Language Reference ( 4D v14.3)
4D Language Reference ( 4D v14 R4)
