Solved

Formula that selects a category and/or two checkboxes

I have been trying to figure out how to write this formula:

Where Category is any of “macOS”
AND checkbox Universal is checked
OR checkbox Intel is checked.

Here are two of my approaches:

IF(Category = "macOS", AND(Universal = 1), OR(Intel = 1), "true")

IF(AND(Category = "macOS", OR(Universal = 1, Intel = 1)), "true")

Can anyone tell me what I’m doing wrong?

Best answer by momentsgoneby80

When I use that formula, I get a blank results field, see screenshot.

Like @ScottWorld says, your field Category doesn’t add upp to just “macOS”, it adds upp to “Application, macOS” which is not the same and Airtable, correctly, doesn’t apply the rule.
For this you need to use FIND(), so each time it finds that category anywhere (as first, second, :nth) it will apply the rule.

IF(AND(FIND('macOS',Category & ""), OR(Universal=1, Intel=1)), 'true')

In your screenshot the checked checkbox field is not called either “Universal” or “Intel”, but I asume that is just a screenshot matter. If not, you need to make sure that the name of the field matches the name in the formula exactly.

+14

Hi @Michael_Tchong!
Your formula works like you describe for me.

In what way do you find that it doesn’t, so I can begin to understand what you are after?

Hi @Michael_Tchong!
Your formula works like you describe for me.

In what way do you find that it doesn’t, so I can begin to understand what you are after?

When I use that formula, I get a blank results field, see screenshot.

+35

When I use that formula, I get a blank results field, see screenshot.

For those records that you’re showing in your screenshot, the category field is not equal to macOS.

For those records, the category field is an array of many strings, one of which is macOS.

So you can’t say:
Category = "macOS"

Instead, you will need to use the FIND function like this:
FIND("macOS",Category & "") > 0

(It might also work without the & "" part, but I’m not sure. Adding that definitely converts the array into a searchable string.)

+14

When I use that formula, I get a blank results field, see screenshot.

Like @ScottWorld says, your field Category doesn’t add upp to just “macOS”, it adds upp to “Application, macOS” which is not the same and Airtable, correctly, doesn’t apply the rule.
For this you need to use FIND(), so each time it finds that category anywhere (as first, second, :nth) it will apply the rule.

IF(AND(FIND('macOS',Category & ""), OR(Universal=1, Intel=1)), 'true')

In your screenshot the checked checkbox field is not called either “Universal” or “Intel”, but I asume that is just a screenshot matter. If not, you need to make sure that the name of the field matches the name in the formula exactly.

Bingo! That worked! Thanks heaps folks, this is a great community. :clap: :slightly_smiling_face:

Sign up