Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

Formula to create a unique ID

Topic Labels: Formulas
Solved
Jump to Solution
1717 3
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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

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.

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