Formula to create a unique ID

Question

I use an algorithm for assigning a client ID which is the static “0660” followed by the 2-digit year the client started, followed by a 2 number sequential.

I am using the following formula.  How do I adjust this formula so that clients who start in 2021 restart the sequential numbering?
‘0660’& (Year)&-REPT(
‘0’,
5-LEN(
{AutoNbr}&’’
)
)&
{AutoNbr}

Justin_Barrett · Accepted Answer

There’s a way to pull this off, which I described in a post to the Show and Tell category a while back:              Automatic Sequential Numbering of Non-Sequential Items (READ JUN 9 2021 FOLLOW-UP) Show and tell        Summary This setup allows Airtable to automatically number collections of items that may not be in sequential order. Item length can be either fixed or variable, with only minor changes needed to the core system to accommodate the latter. Example To borrow from the thread that inspired this endeavor, say you’re building product codes that have a variant sub-code, and want that sub-code to automatically increment each time an item of a given product code is added in a new record. However, it n…

Zollie · Answer

It looks like your formula is relying on the Autonumber to come up with the sequential numbers. So your ask isn’t really to adjust the formula - but for autonumber to work differently. Autonumber is pretty dumb. It only does one thing - when a new record is created, the number goes up.

Unless Airtable adds features to autonumber, use an integer field. That’ll give you the ability to assign whatever sequential values you need. The big drawback of course is that it’s a manual rather than automatic assignment. So if you need to assign a whole batch of numbers, you can fill in the first couple rows (for example, 1 & 2), shift select both of those rows, and drag the small square down to automatically count upward.

Thanks!

Disappointing about the AutoNbr but good to know.

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded