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()
