Submitted by RichBSD on 2022/05/28 16:15

How do I formulate a filter so that it will show all items in a grid that are marked done on that date or not yet marked done, and hide all items that are marked done yesterday or before?

Thanks.

RichBSD

Comments

Thank you, Pierre.

The filter string is a bit counter-intuitive to me-- "[Done] is null OR [Done] = today" seems more intuitive, but doesn't work.

Where can one find a cheatsheet with SQL query syntax? Or is that necessary?

"today" is not a defined variable (unless you define it in the Visual Basic Editor

"now" is defined however and represents the current date / time. You can also use "int(now)" (which is basically the same as "today")

([Done] > now-1 basically means "done in the last 24 hours)

HTH !

Pierre_Admin

Rich,

The link does go to the source bar explanation.  You actually have to back up one level in the user manual to see how to display this bar:

Use Grid > View Source bar or click the folder icon (top-left corner of the grid window).

The left box shows grid source, the middle box the filter to apply, and the right box the sort method for the results.

It gives some code examples as a starting point. 

When you get the result you want, you can save the grid (grid>save as) so you can launch the same set up by opening the grid.  It will remember your set up and and load the items that meet your source definition and filter. 

You can also, of course, create a set of links to these grids so you can launch any search/filter combination you need in the future, without having to go through set up again.

Anyway, that's one approach.

Wayne

In case it is helpful for anyone, here is my grid filter for tracking items due/done this week, since I manage tasks on a weekly, not daily basis.  The filter text is

([DueDate] >= #2022-06-12#) OR ([DueDate] is Null) OR (([DueDate] < #2022-06-12#) AND ([DoneDate] is Null)) OR ([DoneDate] >= #2022-06-12#)

There are 4 conditions separated by OR. The logic is:

  1. Due this week: ([DueDate] >= #2022-06-12#)
  2. No due date, i.e. newly created items: ([DueDate] is Null)
  3. Overdue from last week: (([DueDate] < #2022-06-12#) AND ([DoneDate] is Null))
  4. Done this week, i.e. could have been due before this week, so not covered in condition 1: ([DoneDate] >= #2022-06-12#)

The filter date is always the Sunday before the week starts, and once a week on Monday morning I update it.

How do I ?