Create a "part number" from various item properties


#1

Dear Airtable community,
I am not really technical and just discovered Airtable. I tried to research the forum, FAQ’s, documentation etc, but probably I am searching/ looking out for the wrong search terms or alike. So, if sth similar already happened, please don’t burn me to harsh, much appreciated :wink:

The task I try to achieve: I want to set up a little helper for me and my customers to order the correct way from my catalogue of hand made jewelry. For this, developing a proper and scalable/ appropriate item code methodology is key.

Example: I produce a ring from either silver, white gold, yellow gold or platinum as the main material. this could be extended with up to 2 inlays from e.g. clear, blue, red or green glass, 1-3 gemstones in white, green or blue and be ordered in sizes from 50 to 60.
So my internal order Code for a Silver ring with blue and red inlays, 1 white gemstone in size 52 would e.g. be SI-B-R-1W-52 (in reality, this list is more extensive :wink: ).

Can I somehow build a rule which creates the part number/ order code automatically from e.g. single-choice drop downs for the different properties? I tried IF formulas, and looked into rollup fields, but I just can’t figure it out.

Thanks a lot in advance for looking into this and ideally pointing me into the right direction if this would be achievable.
Best Regards,

tki


#2

To combine different field names into one, you’ll want to use the concatenation operator (&). Examples here: https://support.airtable.com/hc/en-us/articles/215436738-Using-a-formula-in-the-primary-field

To translate from a single select field into a corresponding code, you can use nested IF formulas. Examples here: https://support.airtable.com/hc/en-us/articles/221564887-Nested-IF-formulas


#3

Hey there,
wow, this was fast (actually went to bed after posting, haha). Will try and let you know, thanks a million!


#4

Hello again,

so I tried some stuff, but again am stuck in my ability to master my task :wink:

Using the “IF” and “&” functions, I was able to now “extract” 1 value out of each corresponding cell and merge them into a product number code. But I am not able to get beyond this and make it happen for the other values of a cell. I am wondering if this might be that I can’t have multiple IF"="'s in one formula for one cell? (Sorry if I explain to complicated, non-native English speaker :wink: ).
What I mean is:
I have 4 Single select fields:

  • “Material 1” with options “Silver”, “Gold” and “Platinum”
  • “Material 2” with options again “Silver”, “Gold” and “Platinum”
  • "Diamond Cut"with options “Solitaire”, “Brillant” and “none”
  • “Type” with options “Ring” and "Bracelet"
    plus a numbers field "Ring Size"
    Now, with

Type & "-" & IF({Material 1} = "Gold", "G") & "-" & IF({Material 2} = "Silver", "S") & "-" & IF({Diamond Cut} = "Solitaire", "S") & "-" & Ring Size

I can create a product code “R-G-S-S-52” by configuring a Ring in Gold and Silver with a Solitaire Cut Diamond in Size 52 using the fields.

BUT (taking material 1 as example to not become too messy trying to achieve all at once):
(How) can I call the different options of a single select field?

I’d think something like
IF({Material 1} = "Gold", "G"), IF({Material 1} = "Silver", "S"), IF({Material 1} = "Platinum", "P", "0")

would help me here to give out the appropriate value (or a 0 if nothing selected), but unfortunately this turns out to only generate an error. If I use this structure with numbers, and use >, <, = relations, all works fine. SO can’t I have multiple “=”'s for the same field? Or am I thinking all the wrong way around? The examples I found all only work with numbers and bigger, smaller, same relations to display multiple outcomes.

Thanks a million in advance again,

T


#5

You can use multiple ='s for the same field. However, if you have multiple IF statements, you need to nest them inside each other.

IF({Material 1} = "Gold", "G", IF({Material 1} = "Silver", "S", IF({Material 1} = "Platinum", "P", "0")))

Note the location of all of the )s at the end. Look at the Nested IF example link more closely.


#6

Argh, OK… this makes sense… actually I seem to nearly accidentally had solved it somewhen when trying to figure it out. My fault, thanks for the hint & explanation! Back to the drawing table it now is… Little by little, the bird builds its nest… wait for it …ed IF formulas :smiley: :smiley: :smiley:
Thanks!!!