Group filters and colors with nested logical operators (and/or)

Description

Problem

Our filters can do and & or but not a combination of both:

For example, it's not possible to show cards that are (not yet done) or (done and has been modified recently).

The go-to workaround is to create an auxiliary Checkbox Formula and filter based on it but this clutters the workspace. The Formula approach is also rarely discoverable without our help.

Solution

Eventually, any filter should be possible with Filter, sort, and color-code using formula (ad-hoc calculation). However, writing a Formula is a more advanced power user skill compared to setting up a View. Thus, it's great to have a UI-only way for the most popular scenario.

We allow groups of filters with an arbitrary combination of and & or operators, similar to Notion:

Terms: Filter rule + Filter group.

☝️ Before Generalize context filters into dynamic values, context filters serve like a hard-coded group joined with others using and operator.

Affected places:

View filters
View color coding
Smart Folder setup
Automation trigger filters
Report source filters
Filter and sort in relation Field selector
Whiteboard tree view setup
(only if it's for ~free)
~~Audit log filters~~
(audit log uses own query language with own filters and there is no support for OR at all)

Behavior

🦄 Drag'n'drop is nice to have
Automatically set Field values based on Rules: try the simplest heuristic to make sure a newly created card doesn't disappear
When a filter rule is broken (ex. Field is deleted), we ignore this specific rule
- it should be possible to easily remove this rule on UI
Export as a Template:
- sub expressions should be rooted out if some components are from not exported Spaces

References

Notion

https://www.notion.so/help/guides/using-advanced-database-filters

Airtable

https://support.airtable.com/docs/advanced-filtering-using-conditions

You can nest condition groups for a maximum depth of three levels

Other

Coda doesn't have an advanced filters UI but can do Filter, sort, and color-code using formula (ad-hoc calculation).

Solution - v1

Max three levels of nested filters
- The modal window width expands according to the number of levels

[???]Simplified prototype

Action menu for the filter item:

Remove - Filter or Filter Group

Duplicate - Filter of Filter Group (do we need it?)

Turn into Group - for an existing Filter if the nested limit isn't violated

Ungroup (Turn into Filter) - for an existing Filter Group

Wrap in a Filter Group - for an existing Filter Group if the nested limit isn't violated

[???]

Add Filter & Add Filter Group - Dropdown button vs Two separated buttons
- Decided to go with the Dropdown button - the layout is already heavy and combining these actions into one button seems to be a good solution. We can ask users for their feedback about it before the implementation btw :D

Figma link

[???]

Some options to make the layout less heavy by dropping "where" or changing the style of And/Or operators selectors. Not impressed, we're going to go with the options above but I'll add it just in case.

Next steps (Solution v2)

Color coding
Drag'n'drop (?)
Copy from another view / Filters template (?)