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.
+1yup! wish they’d add a feature for fixing the number of digits :frowning: this is great, but seriously complicated considering they can limit decimal significance and will round for you automatically.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
