I need to be able to concatenate one static column with a two-letter prefix, say XY, and then the auto-number. Easy, except that I need Airtable to add a variable quantity of zeros before the auto-number such that the output is a 7-digit string: XY00087
Currently auto-number does not offer fixed digit length…
I’d like this same formula to be future-proof, so that I can revise all past Job Numbers when we need to grow into an 8-digit Job Number schema.
All help appreciated!
Oh and one other bit… Our current Job Numbers, manually entered, are about 50 places above the auto-number in our base. So we also need a chunk of the formula that makes up for that difference.
So it’s: XY + Zeros + Auto-Number + 1 (or whatever)
Resolved!
Column 1, Title:
({Job Number - Auto} & " - ") & {Job Name}
Column 2, Record #:
Airtable’s native auto-number
Column 3, Legacy Numbering:
A manual checkbox to indicate that a given record requires an offset to maintain legacy numbering
Column 4, Manual #:
Our original, manual Job Numbers
Column 5, Offset:
{Manual #} - {Record #}
Column 6, Auto #:
IF({Legacy Numbering} > 0, {Record #} + Offset,{Record #} + 66)
Column 7, Prefix:
Static two-letter text field with default value
Column 8, Zeros:
IF({Auto #} < 10, “0000”,IF({Auto #} < 100, “000”,IF({Auto #} < 1000, “00”,IF({Auto #} < 10000, “0”,IF({Auto #} < 100000, “0”)))))
Column 9, Job Number:
(Prefix & Zeros) & {Auto #}
The 66 in Column 6 was the offset at the date we switched from manual to auto-numbering. And the extra IF statement in Column 8 is there for the day we cross 99999 jobs.
Resolved!
Column 1, Title:
({Job Number - Auto} & " - ") & {Job Name}
Column 2, Record #:
Airtable’s native auto-number
Column 3, Legacy Numbering:
A manual checkbox to indicate that a given record requires an offset to maintain legacy numbering
Column 4, Manual #:
Our original, manual Job Numbers
Column 5, Offset:
{Manual #} - {Record #}
Column 6, Auto #:
IF({Legacy Numbering} > 0, {Record #} + Offset,{Record #} + 66)
Column 7, Prefix:
Static two-letter text field with default value
Column 8, Zeros:
IF({Auto #} < 10, “0000”,IF({Auto #} < 100, “000”,IF({Auto #} < 1000, “00”,IF({Auto #} < 10000, “0”,IF({Auto #} < 100000, “0”)))))
Column 9, Job Number:
(Prefix & Zeros) & {Auto #}
The 66 in Column 6 was the offset at the date we switched from manual to auto-numbering. And the extra IF statement in Column 8 is there for the day we cross 99999 jobs.
Wow! Awesome, I’m looking for something like this. Thanks for sharing.
yup! wish they’d add a feature for fixing the number of digits 
this is great, but seriously complicated considering they can limit decimal significance and will round for you automatically.
wow thanks, nice workaround!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.