Auto‑number variants per base item

Goal: for each base item (e.g., product code 001), automatically generate a variant sequence (001‑1, 001‑2, …) regardless of record order.

This guide summarizes a proven community approach that uses a helper [Control] table and string math to count prior occurrences of each item.

Core setup (fixed‑length items)

Tables:

Main: your records
[Control]: a single‑record helper table

Requirements:

In Main, the primary field must be an Autonumber (call it {#})
Every Main record links to the lone record in [Control]
The base item field has fixed character length across records (e.g., 001, ABC12)

Fields in Main (example names):

{Base}: your fixed‑length item (e.g., 001)
{Unique Base} (formula): append a rare separator to avoid regex cross‑matches

{Base} & "|"

{Lookup} (lookup): in Main, lookup the collection of {Unique Base} values from [Control]
{Truncated} (formula): keep only the left substring through this record, based on autonumber length

LEFT({Lookup}, {#} * LEN({Unique Base}))

{Substitute} (formula): remove all occurrences of the current {Unique Base} from the running string

SUBSTITUTE({Truncated}, {Unique Base}, "")

{Variant} (formula): count how many times {Unique Base} appeared up to this record

(LEN({Truncated}) - LEN({Substitute})) / LEN({Unique Base})

{Final} (formula): combine base and variant

{Base} & "-" & {Variant}

Result example, in creation order:

Variable‑length items

If {Base} varies in length (e.g., A, AB12, XYZ), create an Items table with:

Primary: item name
Autonumber: sequential ID
{Fixed ID} (formula): pad to a fixed width and append the separator

RIGHT("000" & {Autonumber}, 3) & "|"

Then in Main:

Convert {Base} to a link to Items
Add {Unique Base} as a lookup of Items → {Fixed ID}
Keep the rest of the fields the same, operating on {Unique Base}

This preserves the core counting logic while accommodating variable names.

Resetting cycles (optional)

To cycle a variant every 1,000 (i.e., 1001 → 1), modify {Variant}:

MOD((LEN({Truncated}) - LEN({Substitute})) / LEN({Unique Base}) - 1, 1000) + 1

Note: cycling can undermine uniqueness. Use only when older numbers are safe to reuse.

Caveats and tips

Deleting records: Autonumber never reuses numbers, so deleting a record can desync variant counts downstream. Prefer updating instead of deleting, or be ready to reset Autonumber temporarily.
Separators: use a character in {Unique Base} that never appears in {Base} (e.g., |) to prevent false matches.
Performance: hide helper fields after setup; the flow is robust to reordering because counts derive from Autonumber and string lengths, not position.

FAQ

Why do I need a separate Control table for sequential numbering?

The Control table acts as a single-record hub that every Main record links to. This link lets you create a lookup that pulls all {Unique Base} values into one string per record. Without it, there is no way to count how many times a base item has appeared using only formula fields.

What happens to variant numbers when I delete a record?

Variant numbers can shift because the counting logic relies on the Autonumber field, which never reuses values. Deleting a record leaves a gap in the Autonumber sequence, which may change downstream counts. Prefer marking records as inactive instead of deleting them.

Can I use this approach with variable-length item codes?

Yes. Create an Items table that assigns each item a fixed-width padded ID using a formula like RIGHT("000" & {Autonumber}, 3) & "|". Then link Main records to Items and look up the fixed ID instead of the raw item code. This keeps the string math accurate.

Is there a simpler alternative if I only have a few item types?

For a small number of base items, you can use filtered views and manual numbering, or a simple COUNT rollup through linked records grouped by item. The string-math approach is most valuable when you have many item types and need fully automatic numbering.

Build Better Airtable Forms with Filla

When your sequential numbering depends on clean, consistent data entry, the form experience matters. Filla helps you capture structured records that slot neatly into numbering workflows.

Dropdown and linked record fields to enforce valid base item codes
Required fields and validation rules to prevent incomplete submissions
Conditional logic to show relevant fields based on item type
Custom confirmation pages so submitters know their entry was recorded

Start free and connect your first form to any Airtable base.

Create Your First Form Free

Source

Community walkthrough and discussion: Automatic Sequential Numbering of Non‑Sequential Items

Airtable: Auto-Number Subgroups Sequentially