Help

SKU number formula

Topic Labels: Formulas
1382 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Lisa_Hines
4 - Data Explorer
4 - Data Explorer

I have scoured the community and keep bumping up against an error when trying to create my formula for a SKU number.

I have multiple product categories: Mugs, Berry Bowls, Plates, etc. I would like to have a SKU number that displays the current year, current month, a truncated category, and the autonumber field.

The categories will change to a two or three letter combo, like Mugs becomes MUG, Berry Bowls becomes BB and so forth.

The SKU should end up like this:

2022-03-BB-00001

I’ve tried the SWITCH command, but kept getting an error. I don’t currently have a date column, but do I have to have one to include the year and the month?

Also, the autonumber column doesn’t add the zeros. How can I get the autonumber field to display a 5 digit number?

Thanks in advance.

Lisa

2 Replies 2

Hi @Lisa_Hines ,

Welcome to Airtable Community !

There are several ways to do that, it is not an easy formula for sure.

First, regarding the Current Year and Month, is this of the date you created the record or will it change monthly? Im assuming its the time you created the record?

Second, best solution for the truncated letter combo would be to add a field called Letter Combo to include in the SKU
Screen Shot 2022-03-22 at 2.48.19 PM

The Autonumber does not add zeros yes, but you can add it in the formula

So, the formula would look like this

DATETIME_FORMAT(CREATED_TIME(),'YYYY-MM')&"-"&{Letter Combo}&"-"&IF(LEN(Autonumber&BLANK())=1,"0000"&Autonumber,IF(LEN(Autonumber&BLANK())=2,"000"&Autonumber,IF(LEN(Autonumber&BLANK())=3,"00"&Autonumber,IF(LEN(Autonumber&BLANK())=4,"0"&Autonumber,Autonumber))))

Hope this helps

^ This portion of the formula could be simplified a lot:

REPT("0", 5 - LEN(Autonumber&"")) & Autonumber

The full formula would be:

DATETIME_FORMAT(CREATED_TIME(),'YYYY-MM') & 
"-" & {Letter Combo} & 
"-" & REPT("0", 5 - LEN(Autonumber&"")) & Autonumber