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.

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!