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:

1. In Main, the primary field must be an Autonumber (call it {#})
2. Every Main record links to the lone record in [Control]
3. 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:

001-1
001-2
002-1
001-3

---

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.

---

Source

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