Formula to Concatenate Multi-Field "Job Numbers"

[Andrew_Enright]

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!

[Andrew_Enright]

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)

[Andrew_Enright]

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.

[Larsen_Garma]

Wow! Awesome, I’m looking for something like this. Thanks for sharing.

[Melissa_Frank]

yup! 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.

[dave_brand]

wow thanks, nice workaround!