Skip to main content

Create a "part number" from various item properties

  • February 21, 2017
  • 5 replies
  • 43 views
T
  • tki
  • New Participant

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 :winking_face:

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 :winking_face: ).

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

    5 replies

    M
    Forum|alt.badge.img+12
    • Matt_Bush
    • Inspiring
    • February 21, 2017

    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

    T
    • tki
    • Author
    • New Participant
    • February 22, 2017

    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


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

    T
    • tki
    • Author
    • New Participant
    • February 23, 2017

    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


    Hello again,

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

    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 :winking_face: ).
    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

    M
    Forum|alt.badge.img+12
    • Matt_Bush
    • Inspiring
    • February 23, 2017

    Hello again,

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

    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 :winking_face: ).
    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


    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.

    T
    • tki
    • Author
    • New Participant
    • February 23, 2017

    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.


    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 :grinning_face_with_big_eyes: :grinning_face_with_big_eyes: :grinning_face_with_big_eyes:
    Thanks!!!

    Terms & ConditionsAccessibility statement