Use the AUTONUM command to define, modify, or remove an autonumber formula from a column.
Options
colname
Specifies a column name. The column name is limited to 128 characters.
In a command, you can enter #c, where #c is the column number shown when the columns are listed with the LIST TABLES command.
DELETE
Removes a column's autonumber formula.
format
Defines the format in which values are displayed. This option is used only for columns with the TEXT data type.
You can use the following formatting characters:
Formatting Character |
Result |
9 |
Specify a numeric digit; leading zeros are suppressed. |
0 |
Specify a numeric digit; leading zeros are displayed. |
. (period) |
Aligns digits along a decimal point. |
[ ] (square brackets) |
Encloses literal text. |
For example, if the format is [MX]9999 and the numeric value is 123, the value entered will be MX123.
IN tblname
Specifies the table in which to autonumber the column.
increment
Specifies the value of the increment as each new row is added to the table. The default increment is 1.
NONUM
Leaves existing values unchanged and assigns autonumbered values to new rows as they are added to the table. NONUM is the default option.
NUM
Renumbers all the existing values in the column defined as an autonumbered column.
ORDER BY clause
Sorts rows of data. The ORDER BY clause is only used with the NUM option.
USING startnum
Defines or redefines the formula for an autonumber column. You must specify a starting value. Optionally, you can specify an increment, and for columns with the TEXT data type, a display format. For a column in a table that contains values, you can either renumber existing values or leave them as they are.
About the AUTONUM Command
An autonumbered column ensures that each row in that column has an incremental value. For example, use an autonumbered column to assign identification numbers, model numbers, or invoice numbers:
The following types of columns can be autonumbered:
•Columns that are not computed.
•Columns with DOUBLE, INTEGER, NUMERIC, REAL, or TEXT data types.
When you use the LIST command to list information about a column or table, autonumbered columns are described as AUTONUMBER in the attributes column.
Automatic Numbering
R:BASE automatically enters values in an autonumbered column when you add rows to a table using a form, the Data Editor, INSERT command, or LOAD command with the NUM option. When you import rows to a table that contains an autonumber column, you can either set autonumbering off and load imported values, or set autonumbering on and let R:BASE autonumber the values.
Capturing the Autonumbered Value
The next value for an autonumbered column can be captured for extended calculation or for display in a form. To capture the value, use the NEXT Function.
Changing Values
You can change the values in an autonumbered column by using a form, or the UPDATE or EDIT command. However, if you change a value in an autonumbered column, you could assign a duplicate number or disrupt the sequence of numbers. For more information about changing values in an autonumbered column, see the below guidelines in Renumbering Columns Containing Data.
Renumbering Columns Containing Data
If you renumber a column that contains data, use the following guidelines to decide whether to change the column's existing values.
Autonumber? |
Option to Use |
Conditions |
Yes |
NUM |
A column exists in only one table in the database. You can use the ORDER BY NUM clause to sort the rows in the order in which you want them renumbered. When you add new rows, values are numbered in the order in which the rows are added to the table. |
No |
NONUM (or do not specify) |
A linking (or common) column exists in more than one table. You will destroy the common column values that link your tables if you renumber the values in a linking column. R:BASE adds autonumbered values to new rows as you add them to the table. |
Redefining Formulas
You can redefine the formula for an autonumbered column. For example, use the AUTONUM command with the NUM option to change a column's display format from suppressing leading zeros to displaying them. For more information about redefining formulas of an autonumbered column, see "Renumbering Columns" earlier in this entry.
Removing Formulas
To remove an autonumber formula for a column, use the DELETE option. R:BASE removes only the formula, not the existing values in the autonumbered column. After you remove an autonumber formula, the user must enter values in the column as rows are added.
Autonumbering Tables Created with Relational Commands
When you create a table with one of the relational commands (INTERSECT, JOIN, PROJECT, SUBTRACT, or UNION), R:BASE transfers an autonumbered column as a regular column. You must define an autonumber formula for the column in the new table.
Database Access Rights with AUTONUM
When access rights for a table have been assigned using the GRANT command, AUTONUM requires either the database owner's user identifier, or the rights to alter a table.
Examples
The following command defines an autonumber formula for the custid column in the customer table. Existing values are renumbered starting at 100; assigned values increase by one for each row. Only use this command for a column that meets the renumbering guidelines in the section "Renumbering Columns Containing Data."
AUTONUM custid IN customer USING 100 1 NUM
The following command defines an autonumber formula for the model column in the product table. Existing values are not renumbered. Values in new rows are numbered starting with 100. Assigned values increase by one each time a row is added. The numbering format specifies that the letters MX always precede the numeric value. The 0000 provides space for a numeric value of up to four digits. When the value is less than four digits, R:BASE enters leading zeros.
AUTONUM model IN product USING 100 1 [MX]0000 NONUM
The following command assigns autonumbering to the empid column in the employee table. Existing values are renumbered starting at 100; assigned values increase by one for each row. The rows are renumbered by the employees' last and first names. Only use this command for a column that meets the renumbering guidelines in the section "Renumbering Columns Containing Data."
AUTONUM empid IN employee USING 100 1 ORDER BY emplname, empfname NUM
The command below deletes the autonumber formula from the empidcolumn in the employee table.
AUTONUM empid IN employee DELETE
