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:

[JonathanBowen]

See my reply here:

Multiple Select Formula Views

Hi @McKenna_Johnson - I don’t think you should do this as a formula. Presumably a record could have up to 4 options, so the possible set of choices would become complex very quickly (and hence any formula would be complex). A better way to do this is to put your options into another table like this: [58] Then link to the options in the “products” table: [37] Finally, add a lookup field on the products table that references the size field of the options table: [49] [56] Does that work…

[momentsgoneby80]

@JonathanBowen Sorry for the late reply; it’s been one of those weeks.
I’m unfortunately still too new to Airtable, and more so formulas, that I’m not familliar with all the options and what they do. I find that once you get more familliar with code, any code as this applies to more than just AIrtable formulas, you can code from scratch, but until den you’re mostly finding and adapting existing code while learning. At least I’m like that when it comes to code/formulas and I’m just not good enough to get it all right on my own when starting from scratch. I’ll get there though.
I find I do your kind of thinking though when trying to adapt a formula to fit my needs.

I’ve often thought that it’d be great to be one of those that can learn and apply info from simply reading. Unfortuanately, or maybe fortunately dependeing on your outlook, I’m one of these that need to learn through application which then lead me to forums where I read thread upon thread in search of what I need. I have a bunch of testbases to try the different things I come across on my way there. Maybe that’s just how I work.

MGB

[Matthew_F]

This really helped, thank you! Heads up for anyone trying to do something similar but you have simpler needs and just want to combine a Title (if present), First Name, Last Name, and Suffix (if present), the formula I came up with based on Jonathan’s great post is:

IF({Formal Title}, {Formal Title} & ’ ’ & {First Name}, {First Name})
& ’ ’
& {Last Name}
& IF({Suffix}, ', ’ & {Suffix})

So if you have someone named John Smith with no formal title or suffix, it’d just show “John Smith”

Alternatively, if you had someone named Jane Doe who was a Dr. (in title field) with a PhD (in suffix field), it would show “Dr. Jane Doe, PhD”

If you had someone with a title of Mr. then it’d look like “Mr. John Smith” and if alternatively just a suffix would look like “Jane Doe, RN”