Combining AND and OR Filters

Filters and formulas are used for two completely different things. Filters affect record visibility, whereas formulas affect field contents. Formulas cannot control record visibility.

You may be able to get what you want with a combination of filters and grouping, but it largely depends on what you’re doing in the {Status} field. If the only options for {Status} are “Archived” and blank, then you’re good to go. Group by {Status}, then filter WHERE {Release Date} is on or after 14 days ago, or {Release Date} is empty. That will give you two groups, one of which has the status not archived, and also matching your date-driven filter. The “Archived” group you just disregard.

However, if you have more than one choice for {Status}—maybe “Pending” and “In Progress” in addition to “Archived” for example—then you’ll get separate groups for those. Not as clean from a display standpoint if you’d prefer to have all non-archived stuff lumped together. Unfortunately there’s no way to group by exclusion (i.e. make a single group that has all status options except “Archived”).

Anyway, that’s my somewhat rambling take on one possible alternative.

Hi @Margaret_Wong - this might be another approach:

Have the “OR” element in a formula and then have the resulting formula and the status as “AND” conditions in the filter. I’ve mocked up this example:

The Release Date Formula field (which you could hide if you wanted to) is:

OR(DATETIME_DIFF(TODAY(), {Release Date}, 'days') > 14, NOT({Release Date}))

This is an OR formula where one of the following conditions needs to be true for the formula to be true (or “1”):

DATETIME_DIFF(TODAY(), {Release Date}, 'days') > 14 - i.e. The release date is more than 14 days before today
NOT({Release Date}) - i.e the release date does not exist.

So you can see from my mockup that no release date OR a date earlier than 14 days ago results in a “1” and a release date less than 14 days ago results in a “0”.

I’ve also added a simple select - Archived/Not Archived

You can then filter the view using:

Release Date Formula = 1 AND Status is Not Archived

Hope this helps!

JB

Hi all - I hope you’re doing well. I am happy to share that as of today, advanced filtering is now available in Airtable :tada:

You can check out the full details here, and we can’t wait to hear what you think!