Number Records Automatically

Goal

Give every new record of a table a reference number that numbers itself — an invoice number, a case number, a booking reference — so nobody has to make one up or look up the last one used.

Prerequisites

• You are signed in with a maker role, and the table's solution is open.

Steps

1. Open the table and go to Fields. Select New.

2. Enter a display name, such as Reference. The logical name fills itself in.

3. Choose Auto-number as the data type. A Number format box appears, already filled in with REF-{yyyy}-{SEQ:00000}, and under it a preview of what the first number will look like.

4. Change the format to suit you. Anything you type is kept as it is; three kinds of token are replaced:

   Token	Becomes	Example
   {SEQ}	The next number	417
   {SEQ:00000}	The next number, padded with zeros to the number of zeros you wrote	00417
   {yyyy} / {yy}	The year the record was created	2026 / 26
   {MM} / {dd}	The month and day it was created	09 / 05

   So CR-{yyyy}-{SEQ:000000} gives CR-2026-000417. The preview updates as you type.

5. Select Save.

Put the field on the table's form and in its views like any other field. Users see the number but cannot change it.

What happens behind the scenes

• The number is given when the record is saved. On a new record the field reads Assigned on save; after the first save it shows the record's number.
• Each field has its own counter, kept by the database. Two people saving at the same moment get two different numbers, and a deleted record's number is never given out again. That means there can be gaps — a record that was deleted, or a save that failed after its number was drawn — and that is expected.
• Nobody can type the number. A form shows it read-only, and the platform ignores a value sent for it by an import or a script, so no two records can be given the same one by hand.
• The date in the number is the creation date in UTC, in the Gregorian calendar, whatever calendar the person creating the record uses. A reference number is an identifier, and it must not depend on who is reading it.
• The counter does not travel with the solution. Importing the solution into another environment brings the field and its format, and that environment starts its own count at 1. Production does not continue from wherever Development had reached.

Changing the format

Select the field and select Edit. A new format applies to the next number only: records that already have a number keep it, because a reference somebody has already quoted must not change underneath them.

Troubleshooting

• The number shows {YYYY} or another token in braces. — The token is not one the format understands, so it was kept as written. Tokens are case-sensitive: the year is {yyyy}, the month {MM}. Fix the format; the next number will be right.
• The numbers skip. — See above: a deleted record or a failed save uses up a number. If gap-free numbering is a legal requirement, number the records in a process step instead.
• The number is longer than the zeros I wrote. — Padding is a minimum, not a maximum. The millionth record of {SEQ:00000} is 1000000, so it never collides with an earlier one.

Related scenarios

• Add a Field to an Entity.
• Prevent Duplicates and Double Bookings — to make a number someone types unique.