Skip to main content

SKU number formula

  • March 22, 2022
  • 2 replies
  • 51 views
L

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

Mohamed_Swella1
Forum|alt.badge.img+17

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

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

L
Kamille_Parks11
Kamille_Parks11
Forum|alt.badge.img+27

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

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
L
Andrey_Kovalev
Terms & ConditionsAccessibility statement