Kuovonne's Guide to Airtable

Explore

Kuovonne's Guide to Airtable

Specific Solutions

Sequential Numbering of Records
Sequential Numbering of Records

⁠

⁠ Tip Jar ⁠ Thank Kuovonne for creating this content! ⁠ ⁠ ⁠

⁠

Inspiration

This

post on the TableForums⁠

is one of many I have seen where uses want sequential numbering of records.

Possible Solutions

Autonumber Field

The easiest way to get sequential numbering of records is with an Autonumber field.

Each newly created record gets the next sequential number. Existing records are numbered, starting at one.

If you delete a record, you will have an hole in your numbering. There is no way for a new record to reuse that number.

If you want your numbering to start with a different number than 1, you can combine an autonumber field with a formula field. For example, if you want your numbering to start at 1001, use {Autonumber field} + 1000.

No-code Automation Numbering

Fields:

an editable number field for holding the {Sequence number}

an {isLatest} checkbox field that indicates which field is the latest field

a {Next Sequence Number} formula field that adds 1 to the the editable {Sequence number}, if the {isLatest} checkbox is selected

Setup for existing records:

Number any existing records. (Typing in the numbers for three records, selecting those cells and then dragging down works well. Or you can create an autonumber + formula field, and then convert the formula field to an editable number field.)

Select the {isLatest} checkbox for the current largest number.

Automation

Trigger: When a new record is created

Actions:

Do a “Find Records” action to find the record that has the checkbox selected.

Update the triggering record with the formula result of the next sequence number in the found record and also set the {isLatest} checkbox.

Update the found record to clear the {isLatest} checkbox, so only one record will have the {isLatest} checkbox selected.

Issues with this system

It will not fill in missing numbers for deleted records. (Although if deleting records is rare, you can manually change numbers.)

If multiple records are created before automations have time to run, you can end up with records with the same number and multiple {isLatest} records, which can break the system.

Scripting Automation Numbering

Another possibility is to have an automation that runs on record creation that runs a script that identifies what the next number should be. The script could be written to look for missing numbers in the middle, or find the next number in the sequence. However, this scripting method will also have problems if multiple records are created before the automations have time to run, resulting in multiple records with the same number.

Another possibility is to leave records un-numbered until a scheduled automation script numbers them, say once a day, or every hour. The script could handle filling in holes in the sequence and you wouldn’t have to worry about records accidentally ending up with the same number, but you would have to deal with un-numbered records until the next script run.

Other no-code systems

I’ve seen other systems that involve linking records to a control record that determines the next sequence number, and other systems that find record without using an {isLatest} checkbox. However, those systems have even more drawbacks than the systems that I have just described.

Padding a number with leading zeros

When using a sequence number as text, you may want to pad it with leading zeros so that you always have the same number of digits. For example, the following formula will pad the {Sequence Number} with leading zeros to get a five digit text string (assuming that the Sequence Number itself is 5 digits or less).

REPT("0", 5-LEN({Sequence Number} & "")) & {Sequence Number}

⁠

But why?

However, I also recommend that you think about why it is important to have a numbering system that you described. It can ‘look pretty’ and ‘feel nice’, but there rarely is enough business need to have sequential numbers and filling in gaps to make it worth the trouble to setup and maintain a numbering system.

If you simply need to ensure that all records have a unique value, an autonumber field does that well.

⁠ ⁠ Thank Kuovonne for creating this content! ⁠ Give Kuovonne a sandwich ⁠ Signup for Coda w/ referral link ⁠ ⁠ ⁠Buy from my Gumroad Store ⁠ ⁠ ⁠ ⁠ ⁠

⁠

Gallery

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.

Sequential Numbering of RecordsSequential Numbering of Records