Edit table filter options

1: Trying to do it with filter button

I’m referring to the options that are set up here:

Edit Table > Settings > Filter Options > Use a Filter Menu

I’m working on a to-do app whose main table is Tasks. Tasks have three date fields: DoOn, DoBy, and DoneDate.

On the main page for Tasks, there’s a table showing task records. I have filter buttons defined above the table with these labels:

Do Today  |  Overdue  |  All Open  |  All Closed

The first, third and fourth work fine. But the second filter — Overdue — doesn’t. As far as I can tell, the filters in this menu can have only one criterion, so I set this filter to show tasks whose “Do Date” < today. But what I really want is to show tasks whose Do-Date < today and whose Done-Date is empty. Is this possible? This is something users will want to do all the time, and requiring them to create a two-field filter themselves constantly is really not satisfactory.



2: Trying to do it with calculation

It occurred to me that I might be able to create a formula field that calculates this and filter on that field. But I’d really like to know if it’s possible to do this with the filter menu.

And in any case, I cannot figure out how to write the equation that I need. If I were doing this in FileMaker, I’d create a calculation field that returns a text result, with a formula something like this:

If( DoDate < Get(CurrentDate) and IsEmpty( DoneDate ) , "Overdue!", "" )

But I can’t figure out how to do anything like that in Knack. Looks like Text equation fields are for concatenating text strings and the editor does not provide function that would allow me to compare two date values. The Number and Date equation fields won’t give me a text result like “Overdue”. I could live with a 1 or 0 result but I can’t see how to write the statement that needs to be tested, you know, something like

DoDate < Today and DoneDate != Blank()

As you state, the filter menu only allows one criteria, it’s a fairly simple feature.
You might find some inspiration with the below.

You may also want to explore using conditional rules:

Excellent. Thanks for the reply and for the video link. This idea has managed to get the job done.

Would still be nice if the filters here could be saved with multiple criteria, but this will do for now. Thanks!

Agreed, it would be good if there were more options around the custom filters. Until then, the filter scoring may help.
If you’re happy with this please mark as solved. :+1: