One of the most frustrating parts of table filtering, is that there’s really no way to logically combine items in more than one way.
For example, I’d love to write a filter such as:
“(Field1 contains Red OR Field1 contains Blue) AND Field2 is_checked”
Simply allowing some sort of formula capability for filters might cover all possible cases like this. However, there is a simpler way that could at least cover some cases.
If I could have a filter criteria where I can specify a set of items (strings, multi-select labels, etc) for a single “contains” line, that would often do the trick.
For example:
“Where: Field1 contains_any_of (Red, Green, Blue, Yellow)”
“And: Field2 checked [x]”
So you can partially do that by using the or/and filter on the filter context itself. This feature has the limitation that all filters have to be either or or and but you can’t mix them, so for your example it wouldn’t work 100%.
Another thing you can do is create a formula field that calculates a boolean (true/false) based on your or/and condition.
Formulas allow you to use or and and in the same field, and you should be able to chain formulas like this one:
Yeah, I knew I could do both of these things. However, they’re annoying for multiple reasons…
With the first example, its very tedious to add several OR rows and then there’s really no way to add that AND clause to the filter. That’s kinda the whole problem I was presenting. I could supplement the filter with a sort on that second field, but I’d still want to be able to count the number of rows with a particular value.
One kludge is to simply select them, except Baserow doesn’t actually have any tooltip telling you the number of selected rows.
As far as formulas, if this was a more permanent attribute to the table then that would be the way to go. But sometimes I really just want to data-mine a free-form field to decide whether to add extra columns in the first place. Its often something I only need to know temporarily.
Okay I see, that makes sense to me, temporary filters that don’t get saved onto a view for everyone is btw also something we have thought about before, because we understand that sometimes you just want to filter something temporarily to check something, similar to how you would use search.
@nigel do you know why we only allow or or and on multiple filters and no mixing? Is this a feasible change or does that break a lot of existing logic?
We just haven’t yet gotten around to building a more powerful filter builder that allows more complex filters. I can’t find the issue for it right now but it’s definitely something we plan to add in the future.
Of course simply adding “contains one of” or “contains all of” as a filter option would be a good stop-gap. (and is probably a simpler way of explaining what I initially requested)
Then again, under the covers, those still involve parsing the arguments into a list.
