Filter Editor example

This a worked example of using the Filter Editor to create a more complex filter.

The requirement

In the Case List you only want to display the cases that were open at a certain date.

In this example that date is July 1, 2017.

The solution

The filter below can achieve this:

Analysis

We can't just select all cases opened on or before July 1st 2017 because that would include cases that had also been closed by that date. We are only interested in open cases.

Thus, this filter looks at two case fields:

It needs to select cases where:

  • the case was opened on or before July 1st 2017

    • AND

    • EITHER it was closed after July 1st 2017

      • i.e. it was open on that day and only closed later

    • OR it is still open

      • i.e. it hasn't ever been closed

Create and use filter

  1. Open the Case List Criteria and make sure that closed cases are not filtered out.

    2. Add columns to the grid to display the case open and close dates.

  2. Right-click the Date opened column heading and select the Filter Editor.

It opens already set up for an evaluation based on this column.

  1. Click Equals and select Is less than or equal to.
  2. Enter 1/07/2017 at <enter a value>

You should see the following:

Next you will be defining a group of conditions.

  1. Click And and select Add Group.

You should see the following structure:

  1. Click the second And and select Or
  2. Click the icon next to the second And to create a second condition in the group.

You should see the following structure:

The overall organisation of conditions is now in place. The last part is to customise the bottom two conditions.

In the group of two conditions:

  1. Click the first Date opened and select Date closed.
  2. Click the first Equals and select Is greater than or equal to.
  3. Click the first <enter a value> and enter 2/07/2017.
  4. Click the second Date opened and select Date closed.
  5. Click the second Equals and select Is nul.

The result should be our final filter:

It specifies that:

  • both B and C must be true

    • (And at A)

  • C is only true if either D Or E is true

    • (Or at F)

In this example we start with a Case List that includes all cases (closed cases are in blue) and displays columns with the opening and closing dates:

After the filter has been applied you can see that:

  1. Kate Abbott's closed case remains, because the case was opened before 1/07/2017 and closed after 2/07/2017.
  2. However Alfred Baxter's case disappears, even though it was opened before 1/07/2017. This is because the case was closed before 2/07/2017.

Of the open cases in the first screenshot:

  1. Kimberly Andersich's case disappears because it was opened after 1/07/2017.
  2. However, Ben Bennington's case remains because it was opened before 1/07/2017.

When you save a Case List Criteria saved view, the filter settings are also saved if the following user option is on:

To set this select Tools > User Options from the main menu. In the General tab click the checkbox for Restore case list grid filter.

What if you want see all the cases that were open in the month of July 2017?

In this situation you are looking for cases which are either:

  • Open cases and they were opened on or before the end of the month

    • In other words, they were opened during the month and they have never been closed.

  • Closed cases but they were opened on or before the end of the month and closed after the start of the month

    • In other words, they were open for at least some time during this month, even though they may have been closed in this month too.

The filter below approaches the logical decisions slightly differently:

It specifies that:

  • Either B and C must be true

    • (Or at A)

  • B is only true if both D And E are true
  • C is only true if both F And G are true

What if you wanted to design a filter that didn't use an exact date but looked at the date when the filter was run, i.e. 'today's' date?

The example below selects any case where its Refdate is before or equal to the date when the filter is running.

All these conditions should be included because date and time operators can be mutually exclusive, as explained in Date and time operators.