Multiple Select Formula

[Blanche_M]

I’ve been trying to figure out a formula for a field that depends upon a Multiple Select field. The Multiple Select field is titled “Web Status”, and the formula field is titled “Done”. If ALL possible options under the Multiple Select field are chosen, I want it to be :white_check_mark: . If not all options have been selected yet, it should be :x: . Any ideas?

[Jeremy_Oglesby]

If you had 3 options in your Multi-Select field:

1. “Option 1”
2. “Option 2”
3. “Option 3”

Then this would be your formula:

IF(
   {Web Status} = “Option 1, Option 2, Option 3”,
   “✅”,
   “❌”
)

This works because the conditional is treating the contents of {Web Status} as an array of values separated by commas, where each multi-select option is a value. So putting them all into a string separated by commas allows the conditional to check if the string matches the array.

[Blanche_M]

Jeremy_Oglesby:

IF( {Web Status} = “Option 1, Option 2, Option 3”, “ :white_check_mark: ”, “ :x: ” )

That’s not quite working. If it helps, the options are (exactly as they are in the base):

Pasted in as draft
Proofread & 3rd person
Hyperlinks
Featured & in-set image
Categories & tags
Published

[Jeremy_Oglesby]

Ah, sorry - the issue there is the ordering, since, as you can see here using just the first four - it does work:

But I’m guessing you need to be able to add the options in any order. That’s a little more complicated - I’ll work on it.

[Jeremy_Oglesby]

Take two:

IF(
   AND(
      FIND(
         “Pasted in as draft”,
         {Web Status}
      ),
      FIND(
         “Proofread & 3rd person”,
         {Web Status}
      ),
      FIND(
         “Hyperlinks”,
         {Web Status}
      ),
      FIND(
         “Featured & in-set image”,
         {Web Status}
      ),
      FIND(
         “Categories & tags”,
         {Web Status}
      ),
      FIND(
         “Published”,
         {Web Status}
      )
   ),
   “✅”,
   “❌”
)

Make sure if you copy and paste this to check that the quotation marks are straight up and down (dumb) quotation marks in AIrtable, and not curved (smart) quotation marks. Airtable doesn’t accept smart quotation marks, and sometimes copying and pasting produces them. If so, you’ll have to replace them all with your quotation mark keystroke.

This method works because FIND() is looking for the presence of the string you pass in the field you tell it to search in. If it finds the string, it returns the starting place index number of the string, which is just a number value, but is considered “truthy” by the conditional (it evaluates to “true” rather than “false” if it is there). However, FIND() will return “false” if it doesn’t find the string in the field it searches - so if any one of those FIND() statements returns “false” (it didn’t find the string in the field), then it bypasses the green check.

[Blanche_M]

AMAZING!!! Thank you thank you thank you!

[Elias_Gomez_Sai]

If seems to me that the Status field is a secuence, reflecting “steps completed” and not status. Why not use a Single Select indicating the Status? This would be much easier and makes more sense to me.

[Avid_Huck]

And then you could set it up as a Kanban board!!!

[McKenna_Johnson]

I know this thread is old but I am a smidgen desperate and this thread was very helpful earlier in the day.

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!

[JonathanBowen]

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:

Then link to the options in the “products” table:

Finally, add a lookup field on the products table that references the size field of the options table:

Does that work for you?

JB