We are creating a hierarchical structure for our products. They currently have details in the description (text), which we are moving to Categories using Integers. I have a monster If then statement. I believe there is probably a better way to do it.
For this small section: (for example)
01 | Air Master |
02 | Air Duct |
03 | Plant Master Xtreme |
04 | Plant Master Xtreme 300 AR |
05 | Plant Master Xtreme 300 Lock-On Braid |
06 | Plant Master Xtreme 325 Braid |
07 | Plant Master Xtreme 501AR |
08 | Plant Master Plus |
09 | Plant Master Plus 300 Lock-On |
10 | Plant Master |
11 | Plant Master 250 |
12 | Plant Master 300 |
13 | Plant Master 300 NC |
I've created this code in a Formula tool:
If Contains([PN & Desc],"Air M") then "01" elseif
Contains([PN & Desc],"Air D") then "02" elseif
(Contains([PN & Desc],"Plant") AND Contains([PN & Desc],"Xtreme") and Contains([PN & Desc],"AR") and Contains([PN & Desc],"300")) then "04" elseif
(Contains([PN & Desc],"Plant") AND Contains([PN & Desc],"Xtreme") and Contains([PN & Desc],"Lock") and Contains([PN & Desc],"300")) then "05" elseif
(Contains([PN & Desc],"Plant") AND Contains([PN & Desc],"Xtreme") and Contains([PN & Desc],"Braid")) then "06" elseif
(Contains([PN & Desc],"Plant") AND Contains([PN & Desc],"Xtreme") and Contains([PN & Desc],"501")) then "07" elseif
(Contains([PN & Desc],"Plant") AND Contains([PN & Desc],"Xtreme")) then "03" elseif
(Contains([PN & Desc],"Plant") AND Contains([PN & Desc], "Plus") and Contains([PN & Desc],"Lock")) then "09" elseif
(Contains([PN & Desc],"Plant") AND Contains([PN & Desc], "Plus")) then "08" elseif
(Contains([PN & Desc],"Plant") AND Contains([PN & Desc], "250")) then "11" elseif
(Contains([PN & Desc],"Plant") AND Contains([PN & Desc], "300") and contains([PN & Desc],"NC")) then "13" elseif
(Contains([PN & Desc],"Plant") AND Contains([PN & Desc], "300")) then "12" elseif
Contains([PN & Desc],"Plant") then "10"
else "99" endif
It would be useful if I there is nested if available:
If "Plant" (If "Extreme" (If "300" (If "AR" ) then "04" (If "Lock") then "05". . etc.. Realize my syntax is bad, just wanted to give an idea of what I'm thinking.
One more caveat. Sometimes the desc doesn't contain "Master" but may be "Mstr" or "Plant M". . etc.
Thank you for any help!!
Solved! Go to Solution.
Hi, the workflow attached might not make your workflow simpler. But I think it makes it more dynamic and easier to maintain. Say, when you need to add/remove/update the Categories, you do not need to update your formula but instead, you only need to update the Category mapping table.
This is much better! Very elegant. Thank you!!!