May 21, 2020 11:50 AM
Hi everyone,
Looking for a way to do a multi-step filter within AirTable.
Here’s an example of the filters I would need:
Where Level 3 Org is Org1
OR
Where Level 3 Org is Org2
AND
Job Family contains Job1
OR
Where Level 3 Org is Org3
AND
Job Family contains Job2
Is this possible? It looks like no since you cannot nest filters together in that way. I can do these filters in individual views, but I need an aggregated list in the long run.
Any thoughts?
Solved! Go to Solution.
May 21, 2020 03:07 PM
Thanks for the breakdown. I just realized that my earlier formula was flawed, so I fixed it. However, your most recent breakdown clarifies your intention even more, so even after fixing my earlier flaw, what I wrote above isn’t really what you need.
I’ll show how to combine those tests you wrote into a single formula in a bit, but first some other comments.
For this you don’t need the AND()
wrapper. As my video above points out, AND()
is only needed when you are making more than one test, and need to ensure that all tests are true. Because that first line only contains a single test, all you need for that single formula is:
{Level 3 Org} = "Org1"
The next two pieces could also be simplified, but in a different way.
In this case, the “Software Development” family is part of both test groups. The only difference is the {Level 3 Org}
value. In other words, you want to return a 1 (true) if the {Job Family}
contains “Software Development,” and the {Level 3 Org}
value is either “Org2” or “Org3.” Because of that, these could be combined into a single test like so:
AND(FIND("Software Development",{Job Family}), OR({Level 3 Org} = "Org2", {Level 3 Org} = "Org3"))
Your fourth formula is fine, so now we’ll combine them all.
Based on the mix of AND and OR in your first post, I think you meant to say that you filtered by a 1 in any of the 4 formulas, not all of them. If you filter by all, it’s a much narrower end result, and the mix of criteria you list makes me feel like you don’t want the results that narrow.
Taking my tweaks above into account, here’s the final combo formula, broken up on multiple lines to (I hope) make the mix more clear.
OR(
{Level 3 Org} = "Org1",
AND(
FIND("Software Development", {Job Family}),
OR(
{Level 3 Org} = "Org2",
{Level 3 Org} = "Org3"
)
),
FIND("Technical Art", {Job Family})
)
However, if you actually do want to only show records that meet all tests, just switch the outer wrapping OR()
to AND()
:
AND(
{Level 3 Org} = "Org1",
AND(
FIND("Software Development", {Job Family}),
OR(
{Level 3 Org} = "Org2",
{Level 3 Org} = "Org3"
)
),
FIND("Technical Art", {Job Family})
)
May 21, 2020 11:54 AM
Hi @Travis_Groom1,
Welcome to Airtable Community! :grinning_face_with_big_eyes:
Directly you cannot do that in 1 view. However, how about creating a Formula field that filters those records then use the Filter option on this formula field?
BR,
Mo
May 21, 2020 11:58 AM
Not opposed to that, but I have no idea what the filter/formula would look like.
Alternatively, is it possible to grab the data from each view, and put into a new tab to aggregate it together?
May 21, 2020 12:06 PM
If I’m reading your logic flow correctly, I believe this formula will work:
OR(AND(OR({Level 3 Org} = "Org1", {Level 3 Org} = "Org1"), FIND("Job 1", {Job Family})), AND({Level 3 Org} = "Org3", FIND("Job2", {Job Family}})))
This will return a 1 or 0 (i.e. true or false). Set the filter for the view to only show records where this formula returns 1.
For more info on how the AND()
and OR()
functions work in Airtable, check this out:
May 21, 2020 12:45 PM
That does narrow it down, however now under Org1 it is not pulling everyone, just those under Software Development.
May 21, 2020 12:47 PM
Can you provide a screenshot showing the problem? There’s a lot of detail that you can see, but we can’t, so it’s tough to know how to modify this to meet your needs without more information.
May 21, 2020 01:16 PM
I would love to, but due to confidentiality, I cannot really share it. So I managed to get each individual statement to work.
AND({Level 3 Org} = “Org1”)
AND({Level 3 Org} = “Org2”, FIND(“Software Development”,{Job Family}))
AND({Level 3 Org} = “Org3”, FIND(“Software Development”,{Job Family}))
FIND(“Technical Art”,{Job Family})
I split this into 4 columns each returning a 1 in their respective columns, then filtered by a 1 in all 4 column and got the right sort.
So it kind or works, I am just unsure how to combine the statements into one so I only have one filtered field.
Very much appreciate the help and guidance here.
May 21, 2020 03:07 PM
Thanks for the breakdown. I just realized that my earlier formula was flawed, so I fixed it. However, your most recent breakdown clarifies your intention even more, so even after fixing my earlier flaw, what I wrote above isn’t really what you need.
I’ll show how to combine those tests you wrote into a single formula in a bit, but first some other comments.
For this you don’t need the AND()
wrapper. As my video above points out, AND()
is only needed when you are making more than one test, and need to ensure that all tests are true. Because that first line only contains a single test, all you need for that single formula is:
{Level 3 Org} = "Org1"
The next two pieces could also be simplified, but in a different way.
In this case, the “Software Development” family is part of both test groups. The only difference is the {Level 3 Org}
value. In other words, you want to return a 1 (true) if the {Job Family}
contains “Software Development,” and the {Level 3 Org}
value is either “Org2” or “Org3.” Because of that, these could be combined into a single test like so:
AND(FIND("Software Development",{Job Family}), OR({Level 3 Org} = "Org2", {Level 3 Org} = "Org3"))
Your fourth formula is fine, so now we’ll combine them all.
Based on the mix of AND and OR in your first post, I think you meant to say that you filtered by a 1 in any of the 4 formulas, not all of them. If you filter by all, it’s a much narrower end result, and the mix of criteria you list makes me feel like you don’t want the results that narrow.
Taking my tweaks above into account, here’s the final combo formula, broken up on multiple lines to (I hope) make the mix more clear.
OR(
{Level 3 Org} = "Org1",
AND(
FIND("Software Development", {Job Family}),
OR(
{Level 3 Org} = "Org2",
{Level 3 Org} = "Org3"
)
),
FIND("Technical Art", {Job Family})
)
However, if you actually do want to only show records that meet all tests, just switch the outer wrapping OR()
to AND()
:
AND(
{Level 3 Org} = "Org1",
AND(
FIND("Software Development", {Job Family}),
OR(
{Level 3 Org} = "Org2",
{Level 3 Org} = "Org3"
)
),
FIND("Technical Art", {Job Family})
)