Kuovonne's Guide to Airtable

Pages
- Kuovonne's Guide to Airtable
  Fields
  Overview of fields types
  Editable Field Types
  Computed Field Types
  Field Types by Formula Data Type
  Field Types in Scripting
  Specific field types
  Formula fields
  Demystifying Formulas (DareTable 2022)
  Rollup fields
  Select fields
  Attachment fields
  Uploading Attachments
  Attachment Size
  Attachment URLS
  Thumbnails
  Converting Field Types
  Naming fields
  Metadata fields
  Simple Metadata Fields
  Data Validation
  Additional Dependencies
  Original Record URL (Thumbprint) Field
  Column Divider Fields
  DareTable 2023 Presentation
  Field dependencies
  Formulas
  Formula Field Reference
  Writing Formulas
  Using AI to Write Formulas
  General tips for writing formulas
  Testing for Blank Values
  Drafting Formulas in Parts
  Function versus Operator
  Typing Special Characters
  Commenting Formulas
  Formulas to avoid
  Formatting Formulas
  Basic Rules
  Nested IF formulas
  SWITCH Formulas
  Operators
  Specific Formulas
  Ranges of Numbers Values
  Comma Separated List of Fields Values with Optional Fields
  Earliest / latest date across columns
  List Fields Values (one per line) with Optional Fields
  Rounding a date to the half year
  Url of Interface Page for a record
  Removing varying items from a list
  Data Type Conversion in Formulas
  Text to Numbers
  Numbers to Text
  Data type conversions with Dates
  Troubleshooting Formulas
  Formulas that won't save
  Hiding or showing #ERROR!
  Troubleshooting date/time/duration formulas
  Troubleshooting numeric formulas
  Troubleshooting formulas with lookups
  Troubleshooting text formulas
  Automations
  Triggering automations
  Triggering automations for typed values
  Triggering automations on existing records
  Sending Emails
  Ways to send emails
  Using links in emails
  Emailing Attachments
  Accessing updated record values
  Third party automations
  Managing automation runs
  Templates
  Templates for record values
  Templates using field values
  Controlling Access
  Sharing a base
  Access control considerations
  Techniques for limited access
  Airtable interface
  Share link access
  Synced table
  Prefilled forms + Automations
  3rd party portal
  3rd party webhook
  Preventing base duplication
  Specific Solutions
  Most recent form submission for ermail
  Email digest of a person’s records
  Sum of Multiple Select choices
  Link to multiple records based on select choices
  Dropping Lowest Score (top 4 of 5)
  Identify the position of a record in a linked record field (top 4 of 6)
  Sequential Numbering of Records
  Ending a list of rollup items with ", and"
  Table ID in a formula
  Latest/earliest date across several fields
  Associating People with Records
  First Tuesday of the Previous Month
  Miscellaneous
  Web API Calls for External Integrations
  Getting paid to work with Airtable
  Hiring a consultant
  Airtable Communities
  Making major changes to a base
  Suggestion for this guide

Kuovonne's Guide to Airtable

...

Sequential Numbering of Records

Explore

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! ⁠ ⁠ ⁠ ⁠ ⁠ ⁠ ⁠ ⁠ ⁠ ⁠

⁠

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

Try clicking the ··· in the right corner or using a keyboard shortcut (

CtrlP

) instead.