Advanced filters with the Filter Editor

These more advanced filters are particularly suited to the creation of sophisticated ad hoc reports.

Review of filtering

As described in Filter grids, you can filter a grid using the quick filter row.

You can also move the mouse cursor over the top right hand corner of headings to see a tiny search icon appear:. If you select this icon a dropdown list appears with suggested filter values based on the contents of this column.

When you are filtering a grid and you look at the bottom of the grid, you can see that a filtering rule is dynamically created based on your filter selections:

If you click Edit Filter at the right of the rule you can open the rule in the Filter Editor. Here you can create sophisticated rules to precisely target members of the list.

The Filter Editor is a powerful tool that allows you create rules containing one or more logical conditions in order to filter a list in an extremely targeted manner. The conditions are combined using And (all the conditions must be true), Or (at least one condition must be true), NotAnd and NotOr. You may recognise that these are Boolean operations, see below.

Click Edit Filter as described above to view and edit the current rule in the Filter Editor.

You can also right-click any column header and select Filter Editor:

You may like to study the Filter Editor reference digram below first.

To explain the Filter Editor we will start with the quick filter seen above. We had entered co in the quick filter row of the Category heading.

Lets add Q in the quick filter row under the Client heading.

You can see that the filter rule has been updated because the bottom of the grid now shows:

In the Filter Editor we can see that the two filters became two conditions that must both be true (Boolean And) for the case to be displayed:

  1. The conditions are joined together by lines showing that they are at an equal level.

    2. The red And shows that they are combined using a Boolean And operation, i.e. they both have to be true.

    If you select And a dropdown list appears. You could select another Boolean option, e.g. Or , to combine these conditions together.

  2. Here you see the heading field that is evaluated.

    3. If you select Category a dropdown list appears and you could select a different column heading:

  3. Here you see the filter comparison operator.

    4. If you select Begins with a dropdown list appears and you could select a different comparison.

  4. Here you see what you are filtering for.

    5. If you select Q a text field appears and you could enter different text

  5. The pencil icon allows you to select another column heading at D.

    6. You could use this to compare two headings, for example whether the referral date and the start date are the same.

  6. Select the cross icon to delete this condition altogether.
  7. Select the icon to add a new condition at this logical level.

    8. You can customise it using the options described above.

Date formats

The display of dates depends on the regional settings your computer has been set to. If no one has modified them, the default date format is typically the US settings [mm/dd/yyyy]. You will not see the date format for your region unless your PC has those set as its default.

Date and time operators

Be aware that some date and time operators (particularly the later and earlier operators) are mutually exclusive, as the demo below shows.

In the video you can see that to filter for cases where the date opened is prior to today, but in the same year as today, you must tick Yesterday and Earlier this month and Earlier this year:

If you watch the bottom LHS of the screen you can see the filter rule being dynamically created. When the three options have been ticked, the following rule has been created:

There were no cases where the Date opened was earlier this week (but not yesterday) so the option was not provided here.

Thus, in the Filter Editor when you are creating time rules you must account for all possible date scenarios.

The rule below selects cases where the Referral date is any day before or equal to the date when the filter runs.

Definitions:

Beyond this year dates that follow the current year.
Later this year dates of the current year starting from the following month
Later this month dates of the current month that follow next week.
Next week dates that belong to the following week
Later this week dates of the current week starting with the day after tomorrow
Earlier this week dates of the current week that are prior to yesterday
Last week dates of the previous week
Earlier this month dates of the current month that are prior to the previous week
Earlier this year dates of the current year that are prior to the current month
Prior to this year dates that are prior to the current year

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.

The following diagram is adapted from the DevExpress documentation. It explains the elements of the Filter Editor.

Boolean operations are logical operations. They are fundamentally important in computing and electronics.

A joke may help you understand:

A Boolean logician has a baby. Her workmate phones and asks her if it’s a boy or a girl. She replies “Yes!”

That's because the Boolean Or has a different meaning from the everyday usage of the word. When you use a Boolean Or, if either statement is true then the whole statement is true.

A Boolean And is more familiar. If the new mother is asked if she had a boy and a girl, she could only reply "yes" if she had a boy and a girl.

This short explanation make it clear that very sophisticated filters can be created with the Filter Editor.

You will also find the worked example useful. It creates a filter for cases that were open on July 1, 2007.

Video

A video demonstration of advanced filters.