Skip to main content

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:

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 @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"

).....

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

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!

Reply


Terms & Conditions