If / And Formula Help

[Sal_Suissa]

Hello

I have in my base five columns:

Product Name (starting point) (ex: Apple Macbook)

Main Color (optional)

Secondary Color (optional)

Finish (optional)

Product Variant Name (formula)

I am trying to write the following formula to generate the “Product variant name” , but having a hard time:

Product Variant Name = Main Color + “,” + Secondary Color + “,” + Finish

However, IF ANY of those three optional fields is (blank) then skip it as well as the “,” corresponding to it

My hope is to get in “product variant name” a clean product name.

How can I do so? I get lost when trying to use the IF / AND formula :frowning:

[W_Vann_Hall]

Well, here’s the formula you asked for — but i suspect it’s not really what you need. (Are each of the components really optional? For instance, you would have a variant name that has no main or secondary color, but does have a finish?)

IF(
    {Main Color},
    {Main Color}
    )&
IF(
    {Secondary Color},
    IF(
        {Main Color},
        ', '&{Secondary Color},
        {Secondary Color}
        )
    )&
IF(
    {Finish},
    IF(
        OR(
            {Main Color},
            {Secondary Color}
            ),
        ', '&{Finish},
        {Finish}
        )
    )

I suspect what you’re looking for is more like this:

IF(
    {Main Color},
    {Main Color}&
    IF(
        {Secondary Color},
        ', '&{Secondary Color}
        )&
    IF(
        {Finish},
        ', '&{Finish}
        )
    )

This second formula will give you

Main Color
Main Color, Secondary Color
Main Color, Secondary Color, Finish
Main Color, Finish

[Sal_Suissa]

Thanks so much!! Will put it to test in the morning. Yes some products are made of materials that only have finish, not main or secondary colors, (Cast aluminum)

Thanks a million!!

[W_Vann_Hall]

Sal_Suissa:

Yes some products are made of materials that only have finish, not main or secondary colors, (Cast aluminum)

In that case, you might want to try the following. This will give you the four materials supported by the second formula above as well as a finish-only material. (It won’t allow a material with a secondary but no main color; if that’s a valid choice — a manufacturing version of ‘spot color,’ perhaps — you’ll need to use the original formula.)

IF(
    {Main Color},
    {Main Color}&
    IF(
        {Secondary Color},
        ', '&{Secondary Color}
        )
    )&
    IF(
        {Finish},
        IF(
            {Main Color},
            ', '&{Finish},
            {Finish}
            ),
        )

[Sal_Suissa]

Worked great. Thanks SO much for your help.

[momentsgoneby80]

Hi!
I have a problem really similar to this and though I can see that this has the solution I still manage to tangle myself somewhere in the formula. I weight the pros and cons of writing here vs. starting a new topic and I hope I made the right decision.

I want my primary field to display info from five fields; {Nickname}, {First Name}, {Last Name}, {Maiden Name} and {Title}

The fields {Nickname}, {Maiden Name} and {Title} are conditional and I only want them to display if not blank. No empty spaces in the output.

I would like outputs like below;
Nickname Last Name
Nickname Last Name (neé Maiden Name)
Nickname Last Name (neé Maiden Name) [Title]
First Name Last Name
First Name Last Name (neé Maiden Name)
First Name Last Name (neé Maiden Name) [Title]

I’ve managed to get {Nickname}, {First Name}, {Last Name} and {Title} to work, though likely not in a very elegant way, but get tangled on the {Maiden Name} part. As I’m relatively new to Airtable and this is my first post I would be beyond greatful for help with this.

[JonathanBowen]

Hi @momentsgoneby80 - something like this?

IF(Nickname, Nickname, {First Name}) & ' ' & {Last Name} & IF({Maiden Name}, ' (neé ' & {Maiden Name} & ')', '') & IF(Title, ' [' & Title & ']', '')

JB

[momentsgoneby80]

Thank you so much @JonathanBowen, that worked wonders.
Far more elegant than my attempts and far better - It works!

Now I’m just gonna study that and figure out where and why I tripped myself up so I don’t repeat it at a later date.

[JonathanBowen]

I always find it easier to build it up step by step rather than attempting to do this whole thing in one go. So I first did “if there’s a nickname, show the nickname, else show the first name”. Then added last name. Then added “if there’s a maiden name…” and so on.

JB

[McKenna_Johnson]

I know that an IF formula can only delivery a true/false answer, but I was wondering if there is another formula that delivers multiple values if you select multiple answers in a multi-select fields.

I have a {Product Size} multi-select category thats gives you the option to select Option 1, Option 2, Option 3, and/or Option 4. I would like the a {Imprint Size} category that will show the variety of imprints needed.

Option 1 = 10 inches
Option 2 = 15 inches
Option 3 = 20 inches
Option 4 = 10 inches

As you can see some options equal the same amount.

Example: If Option 2 and Option 3 are selected I want my Imprint Size formula category to read “15 inches, 20 inches.”

I have been trying to figure this out for a few hours now and have tried a few different formulas but I can’t figure out how to deliver different answers.

Thanks!