Multiple Select Formula


#1

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?


#2

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.


#3

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


#4

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

49%20PM

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.


#5

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}
      )
   ),
   “✅”,
   “❌”
)

09%20PM
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.


#6

AMAZING!!! Thank you thank you thank you!


#7

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.


#8

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