Skip to main content
Solved

SKU Creation Formula

  • September 21, 2022
  • 4 replies
  • 74 views
K

Hey all,

I’m attempting to create a formula that will automatically create SKUs from several different columns, but I’m running into a few issues.

Below is the formula I have so far:

SWITCH(

{Brand Code},

"Kala","KA",

"UBASS","UB",

"Makala","MK",

"Ukadelic","UK",

"Kala Percussion","KP",

"Kala Elite USA","ELT"

)&"-"&

SWITCH(

{Product Type},

"Acoustic-Electric","AE-",

"Guitar","GTR-"

)

&SWITCH(

{Type Code},

"Solid Top (2)","2",

"All Solid (3)","3",

"Solid Body (6)","6"

)

&SWITCH(

{Top Wood Code},

"Cedar","C",

"Spruce","S",

"Striped Ebony","SE",

"Koa","K",

"Acacia","A",

"Mahogany","M"

)

&SWITCH(

{Back & Sides},

"Mahogany","M",

"Rosewood","R"

)

&SWITCH(

{Size Code},

"Tenor","-T"

)Preformatted text

I want the {Back & Sides} portion of the formula to only show up if it doesn’t match with the {Top Wood Code}. For example in row 3 I have a ukulele that has its top, back, and side wood listed as Mahogany, but my current formula makes the SKU “MK-MM”. I want it to be “MK-M”. My formula knowledge is pretty basic so any help will be greatly appreciated.

Reference photo:

Best answer by Alexey_Gusev

Hi @Kevin_Harrison, I think we could just use an IF() to check whether the the top matches the back and sides and output the one switch instead of two

I’ve put something together below that should work I think, but as I can’t test it I can’t guarantee anything

SWITCH(
  {Brand Code},
  "Kala","KA",
  "UBASS","UB",
  "Makala","MK",
  "Ukadelic","UK",
  "Kala Percussion","KP",
  "Kala Elite USA","ELT"
) &
"-" &
SWITCH(
  {Product Type},
  "Acoustic-Electric","AE-",
  "Guitar","GTR-"
) &
SWITCH(
  {Type Code},
  "Solid Top (2)","2",
  "All Solid (3)","3",
  "Solid Body (6)","6"
) &
IF(
  {Top Wood Code} = {Back & Sides},
  SWITCH(
    {Top Wood Code},
    "Cedar","C",
    "Spruce","S",
    "Striped Ebony","SE",
    "Koa","K",
    "Acacia","A",
    "Mahogany","M"
  ),
  SWITCH(
    {Top Wood Code},
    "Cedar","C",
    "Spruce","S",
    "Striped Ebony","SE",
    "Koa","K",
    "Acacia","A",
    "Mahogany","M"
  ) & 
  SWITCH(
    {Back & Sides},
    "Mahogany","M",
    "Rosewood","R"
  )
) &
SWITCH(
  {Size Code},
  "Tenor","-T"
)

Hi,
Just curious.
Why repeat {Top Wood Code} twice? ))
sometimes in such cases people even rewrite whole formula, instead of shortest version - doing exactly task statement. I mean, substitute

SWITCH({Back & Sides},
"Mahogany","M",
"Rosewood","R")

for

IF({Back & Sides}!={Top Wood Code},
SWITCH({Back & Sides},
"Mahogany","M",
"Rosewood","R")
)


...SWITCH(
  {Type Code},
  "Solid Top (2)","2",
  "All Solid (3)","3",
  "Solid Body (6)","6"
) &
  SWITCH(
    {Top Wood Code},
    "Cedar","C",
    "Spruce","S",
    "Striped Ebony","SE",
    "Koa","K",
    "Acacia","A",
    "Mahogany","M"
  ) & 
IF({Back & Sides}!={Top Wood Code},
 SWITCH(
    {Back & Sides},
    "Mahogany","M",
    "Rosewood","R"
  )
) &
SWITCH(
  {Size Code},
  "Tenor","-T"
).....

4 replies

TheTimeSavingCo
Forum|alt.badge.img+31

Hi @Kevin_Harrison, I think we could just use an IF() to check whether the the top matches the back and sides and output the one switch instead of two

I’ve put something together below that should work I think, but as I can’t test it I can’t guarantee anything

SWITCH(
  {Brand Code},
  "Kala","KA",
  "UBASS","UB",
  "Makala","MK",
  "Ukadelic","UK",
  "Kala Percussion","KP",
  "Kala Elite USA","ELT"
) &
"-" &
SWITCH(
  {Product Type},
  "Acoustic-Electric","AE-",
  "Guitar","GTR-"
) &
SWITCH(
  {Type Code},
  "Solid Top (2)","2",
  "All Solid (3)","3",
  "Solid Body (6)","6"
) &
IF(
  {Top Wood Code} = {Back & Sides},
  SWITCH(
    {Top Wood Code},
    "Cedar","C",
    "Spruce","S",
    "Striped Ebony","SE",
    "Koa","K",
    "Acacia","A",
    "Mahogany","M"
  ),
  SWITCH(
    {Top Wood Code},
    "Cedar","C",
    "Spruce","S",
    "Striped Ebony","SE",
    "Koa","K",
    "Acacia","A",
    "Mahogany","M"
  ) & 
  SWITCH(
    {Back & Sides},
    "Mahogany","M",
    "Rosewood","R"
  )
) &
SWITCH(
  {Size Code},
  "Tenor","-T"
)
Adam, Airtable Consultant - thetimesaving.company
Alexey_Gusev
Forum|alt.badge.img+25
  • Alexey_Gusev
  • Brainy
  • Answer
  • September 22, 2022

Hi @Kevin_Harrison, I think we could just use an IF() to check whether the the top matches the back and sides and output the one switch instead of two

I’ve put something together below that should work I think, but as I can’t test it I can’t guarantee anything

SWITCH(
  {Brand Code},
  "Kala","KA",
  "UBASS","UB",
  "Makala","MK",
  "Ukadelic","UK",
  "Kala Percussion","KP",
  "Kala Elite USA","ELT"
) &
"-" &
SWITCH(
  {Product Type},
  "Acoustic-Electric","AE-",
  "Guitar","GTR-"
) &
SWITCH(
  {Type Code},
  "Solid Top (2)","2",
  "All Solid (3)","3",
  "Solid Body (6)","6"
) &
IF(
  {Top Wood Code} = {Back & Sides},
  SWITCH(
    {Top Wood Code},
    "Cedar","C",
    "Spruce","S",
    "Striped Ebony","SE",
    "Koa","K",
    "Acacia","A",
    "Mahogany","M"
  ),
  SWITCH(
    {Top Wood Code},
    "Cedar","C",
    "Spruce","S",
    "Striped Ebony","SE",
    "Koa","K",
    "Acacia","A",
    "Mahogany","M"
  ) & 
  SWITCH(
    {Back & Sides},
    "Mahogany","M",
    "Rosewood","R"
  )
) &
SWITCH(
  {Size Code},
  "Tenor","-T"
)

Hi,
Just curious.
Why repeat {Top Wood Code} twice? ))
sometimes in such cases people even rewrite whole formula, instead of shortest version - doing exactly task statement. I mean, substitute

SWITCH({Back & Sides},
"Mahogany","M",
"Rosewood","R")

for

IF({Back & Sides}!={Top Wood Code},
SWITCH({Back & Sides},
"Mahogany","M",
"Rosewood","R")
)


...SWITCH(
  {Type Code},
  "Solid Top (2)","2",
  "All Solid (3)","3",
  "Solid Body (6)","6"
) &
  SWITCH(
    {Top Wood Code},
    "Cedar","C",
    "Spruce","S",
    "Striped Ebony","SE",
    "Koa","K",
    "Acacia","A",
    "Mahogany","M"
  ) & 
IF({Back & Sides}!={Top Wood Code},
 SWITCH(
    {Back & Sides},
    "Mahogany","M",
    "Rosewood","R"
  )
) &
SWITCH(
  {Size Code},
  "Tenor","-T"
).....
TheTimeSavingCo
Forum|alt.badge.img+31

Hi,
Just curious.
Why repeat {Top Wood Code} twice? ))
sometimes in such cases people even rewrite whole formula, instead of shortest version - doing exactly task statement. I mean, substitute

SWITCH({Back & Sides},
"Mahogany","M",
"Rosewood","R")

for

IF({Back & Sides}!={Top Wood Code},
SWITCH({Back & Sides},
"Mahogany","M",
"Rosewood","R")
)


...SWITCH(
  {Type Code},
  "Solid Top (2)","2",
  "All Solid (3)","3",
  "Solid Body (6)","6"
) &
  SWITCH(
    {Top Wood Code},
    "Cedar","C",
    "Spruce","S",
    "Striped Ebony","SE",
    "Koa","K",
    "Acacia","A",
    "Mahogany","M"
  ) & 
IF({Back & Sides}!={Top Wood Code},
 SWITCH(
    {Back & Sides},
    "Mahogany","M",
    "Rosewood","R"
  )
) &
SWITCH(
  {Size Code},
  "Tenor","-T"
).....

Oooh, yes, you’re right, your way is a lot cleaner and wouldn’t require changes to be made in two places

Adam, Airtable Consultant - thetimesaving.company
K
  • Kevin_Harrison
  • Author
  • New Participant
  • September 22, 2022

Thanks for the assistance on this guys, I actually got pretty close to this yesterday, I was just missing the “!”. The rest of the formula should be a breeze!

Terms & ConditionsAccessibility statement