Skip to main content

I’ve tried searching the forums, and while I’ve found posts similar to this, I haven’t quite found the formula that works for me.



I have a Job Number that is based off 3 other fields that I want to automatically generate when the other fields are filled manually.



Example



Job #: 72234-0001-001



Order #: 72234


Line Item: 1


Revision Level: 1



The order number typically does not need zeroes, but the line item fields and rev fields do, the zeros also need to adjust in the event there is lets say 10 line items, the center number still needs to be a 4 digits.



This seems doable, but I haven’t found the correct formula to do this. If anyone knows how to pull this off, thank you ahead of time!!

IF(LEN({Line Item})=1, “000”&{Line Item}, IF(LEN({Line Item})=2, “00”& {Line Item}, IF(LEN({Line Item})=3, 0&{Line Item}, {Line Item})))



IF(LEN({Revision Level})=1, “000”&{Revision Level}, IF(LEN({Revision Level})=2, “00”& {Revision Level}, {Revision Level}))

IF(LEN({Line Item})=1, “000”&{Line Item}, IF(LEN({Line Item})=2, “00”& {Line Item}, IF(LEN({Line Item})=3, 0&{Line Item}, {Line Item})))



IF(LEN({Revision Level})=1, “000”&{Revision Level}, IF(LEN({Revision Level})=2, “00”& {Revision Level}, {Revision Level}))


The rev level part didn’t work for some reason no matter how hard I tried, but the first IF statement worked great! Thank you so much Sam 🙂

Is there a PAD function in airtable? I’m looking for a similar solution to an ID number.



Thanks,


Erica

AFAIK, you can’t force a field to a certain length by default. But there is a simpler formula that doesn’t require any IFs:


RIGHT("0000"&{Line Item},4)


RIGHT("000"&{Revision Level},3)



Note: Make sure the prepended string is at least the length of the desired final result, otherwise it’ll throw an error if the value field you want to pad is empty.



If you’re curious, the definition of the function is:


RIGHT(string, howMany)


Extract howMany characters from the end of the string.

AFAIK, you can’t force a field to a certain length by default. But there is a simpler formula that doesn’t require any IFs:


RIGHT("0000"&{Line Item},4)


RIGHT("000"&{Revision Level},3)



Note: Make sure the prepended string is at least the length of the desired final result, otherwise it’ll throw an error if the value field you want to pad is empty.



If you’re curious, the definition of the function is:


RIGHT(string, howMany)


Extract howMany characters from the end of the string.




Thanks so much Andy! That solved my problem perfectly and I’ll be using that formula in Client IDs, invoice numbers and several other places.


Erica

AFAIK, you can’t force a field to a certain length by default. But there is a simpler formula that doesn’t require any IFs:


RIGHT("0000"&{Line Item},4)


RIGHT("000"&{Revision Level},3)



Note: Make sure the prepended string is at least the length of the desired final result, otherwise it’ll throw an error if the value field you want to pad is empty.



If you’re curious, the definition of the function is:


RIGHT(string, howMany)


Extract howMany characters from the end of the string.




Thanks Andy. That’s a great solution 🙂



Works in my slightly overcomplicated Client ID formula:


UPPER(LEFT(SUBSTITUTE({Customer Name}, " ", “”),4)) & RIGHT(“000”&{ID},3)

To left-pad {Number} with enough zeroes to make a string {Digit} digits long, use



REPT('0',{Digit}-LEN({Number}&''))&{Number}


(’LEN({Number}&'')’ returns the length of {Number} cast as a string.)

Reply


Terms & Conditions