Help

Formula to create a unique ID

Topic Labels: Formulas
Solved
Jump to Solution
2586 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Cindy_Hawkins
4 - Data Explorer
4 - Data Explorer

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.

image

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}

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

There’s a way to pull this off, which I described in a post to the Show and Tell category a while back:

See Solution in Thread

3 Replies 3
Zollie
10 - Mercury
10 - Mercury

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.

drag_count

Thanks!

Disappointing about the AutoNbr but good to know.

Justin_Barrett
18 - Pluto
18 - Pluto

There’s a way to pull this off, which I described in a post to the Show and Tell category a while back: