Advanced Filtering for Tabular Reports

Introduction

To search for a small number of records in a large list, users can employ a filter. When you define a filter, you are laying out a set of criteria that your result must match – effectively looking for a subset of some large pool of data. The eBMS platform provides a powerful filtering tool to users to construct very precise filters. When a filter is applied to a set of data, each record in the original data set is passed through the filter. Every record that suits the filter criteria is displayed in the result set; other records are not displayed. In short, filtering allows you to find a needle (your desired data) in a haystack (the entire set of data).

The Conditional Filter Editor is an advanced tool for creating complex filter statements, allowing users to filter their data according to powerful and specific rules. This page will explain some necessary concepts to help users understand the Conditional Filter Editor and its usage.

Using The Editor

When viewing a Tabular Report, you can view the Conditional Filter Editor by following these steps:

  1. Make sure the sidebar is open using the -> tab button
  2. Tick the box marked “Show Properties” in the Profile section of the sidebar
  3. Under the Selection heading, click the edit button for the Filter field.

openthefilter

If you’ve done that correctly you should be greeted with something that looks like this:
theeditor

The Filter Editor is made up of a number of parts:

  1. Filter Statements – This is a representation of a Filter Statement. Clicking on a Filter Statement will allow you to edit it using the Filter Type Selector and Filter Statement Editor
  2. Filter Type Selector – You can make a Filter Statement from a boolean eForm column (eg. Contract Closed), or make it into an operator statement (eg. ([Description]) Contains (‘Banana’))
  3. Filter Statement Editor – Select the single eForm Column for the filter when Source eForm Column is selected, or construct a Filter Statement when Operator is selected.
  4. Filter Groups – Each group has a boolean operator (pictured: AND), some filter statements, and the Add Buttons to add nested groups or filter statements.
  5. Add ButtonsClick these buttons to add Nested Groups (left button) or more Filter Statements (right button)

Keep reading for a detailed explanation of these concepts

Statements

A statement is an expression that takes the form: {code}[value] <operator> [value]{code}

Values

A value is a piece of information. It can be:

  • A number
  • A piece of text
  • A data field from a record
  • A statement

Operators

An operator is a kind of operation that is performed on values. In the context of filters, most of the time it’s a comparison that resolves as true or false.

  • The EqualTo operator resolves as true if the compared values are equal, eg. “Tiger” EqualTo “Tiger” resolves to true, but “Tiger” EqualTo “Lion” would resolve to false.
  • The Contains operator resolves as true if the left value is contained inside the right value – eg. “Elephant” Contains “ant” would resolve as true, whereas “Elephant” Contains “pant” would resolve as false.

Operators can also operate on a single value:

  • The IsEmpty operator resolves as true if the left value is an empty piece of text, eg. “” IsEmpty would resolve to true, but “Empty” would resolve to false.

Finally, some operators resolve to things other than true or false

  • The Plus operator works as you would expect the mathematical plus symbol to work – it adds numbers together. 10 Plus 10 would resolve as 20, and [Price] Plus 5 would resolve to the Price value of a record, plus 5.

Filter Statements

A filter statement (also known as a boolean statement) is a statement that evaluates to either true or false:

[Current User] <Is In Role> [Administrators]
[Contract Closed]
1 <LessThan> 3

Example 1

Most organizations keep a well-stocked address book for useful business contacts. Imagine in your eBMS platform you have an eForm called Contacts, which stores this contact information. If you view the Tabular Report of this eForm, you’ll see a massive list of Contact Records – most of the time a user will want a specific record or a small set of records from this large collection of data.

Using the concepts above, a user can construct a simple filter. Say you want to find Contacts whose first name starts with “Chris”, eg. Christopher, Christian, Christine, etc. Using this filter on your Contacts records would filter out any contact records whose First Name field did not start with “Chris”:

simplecriterion

In this example, there is only one filter that a record needs to pass in order to be displayed in the filtering result.

Record 1 – Christopher Columbus

Suppose there is a record in the Contacts eForm for Christopher Columbus, which is passed to the filter above.

The filter resolves to true, because the record’s First Name field is “Christopher”, which starts with “Chris”.

Since the filter has resolved to true, this record is displayed in the results set.

The next record to be passed to the filter in this example is a Contact record for Cookie Monster.

The filter resolves to false, because the record’s First Name field is “Cookie”, which does not start with “Chris”.

Since the filter has resolved to false, this record is not displayed in the results set.

Nested Statements

One or both values of a given Statement or Filter can be another Statement.

The statement

[Price] <Plus> [5]

Can be used as one of the values of a statement:

[Price Plus 5] <Is Less Than> [Money In Wallet]

Filter Groups:

A Filter Group is a collection of Filters with a boolean operator:

  • The AND boolean operator means that the group resolves as true if and only if all filters within that group resolve as true. In diagrams like those below, records are passed from the Source Data section downward to the Result Data section. Records can only pass through Filters that resolve as true for them – if a record can follow a complete path to the end, it will be displayed in the results set.
    Drawing1
  • The OR operator means that the group resolves as true if any of the filters within that group resolve as true.

Drawing2

AND {        
	[Current User] <Is In Role> [Administrators]
	[Today] <Is Equal To> [24/09/2013]
	[Product Name] <Contains> ["Banana"]
}

This statement group would only be true of all three statements are true.

Nested Filter Groups:

A filter group can be nested inside another filter group:

AND {
    [Current User] <Is In Role> [Administrators]
    [Today] <Is Equal To> [24/09/2013]
    OR {
        [Product Name] <Contains> ["Apple"]
        [Product Name] <Contains> ["Banana"]
    }
}

This statement would only be true if:

  • The current user is an Administrator, and
  • Today’s date is 24/09/2013, and
  • One or more of the following is true
    • The product name contains “Apple”, or
    • The product name contains “Banana”

Drawing4

This filter will refine the results set to results that:

  • Pass filter 1, OR
  • Pass filter 3, OR
  • Pass filter 2 AND filter 4

Drawing3

This filter will refine the results set to results that:

  • Pass filter 1, AND
  • Pass filter 2 OR filter 3, AND
  • Pass filter 4

Example 2

Say you want to find the contact information for a project manager you worked with in 2011, but you don’t remember her contact details. All you know is that:

  • She operates in Sydney
  • Her last name is Smith
  • Her contact details would have been entered in the first half of 2011
  • Her name was either Caitlyn or Catherine

You can use the simple filters on the tabular report to filter the last name and city, but there might still be a few hundred results. Using the Conditional Filter Expression Editor, you can construct a precise filter to narrow the search down to a very small result set. The filter expression might look something like this:

findingthatprojectmanager

Let’s break this filter down into small parts so it’s easily digestible. There are 6 filter statments in this filter expression, each one with its own condition for the record to pass that individual filter:

(DataSource[City]) EqualTo (‘Sydney’) – The City field in the record is equal to the text “Sydney”

(DataSource[Last Name]) EqualTo (‘Smith’) – The Last Name field in the record is equal to the text “Smith”

(DataSource[DateCreated]) GreaterThanOrEqualTo (#2011-01-01T02:35:00#) – The DateCreated field is greater than or equal to the date 01/01/2011 at 2:35AM (UTC)

(DataSource[DateCreated]) LessThanOrEqualTo (#2011-06-30T03:35:00#)  – The DateCreated field is less than or equal to the date 30/06/2011 at 3:35AM (UTC)

(DataSource[First Name]) EqualTo (‘Caitlyn’) – The First Name field is equal to the text “Caitlyn”

(DataSource[First Name]) EqualTo (‘Catherine’) – The First Name field is equal to the text “Catherine

The filter expression is structured so that not all of these filter statements have to be true – the filters related to the First Name field are in an OR statement, which means that the group resolves as true if at least one statement within it is true. The arrows on this chart indicate possible evaluation paths for the filter.

Drawing7

Step-by-step

Let’s look at how three example records might evaluate against our filter.

Record 1: Catherine Smith

Catherine Smith is an IT specialist from Melbourne. Her record in the Contacts eForm was created on 04/03/2011.

Filter 1 – (DataSource[City]) EqualTo (‘Sydney’). The record fails the city filter, because ‘Melbourne’ is not equal to ‘Sydney’.

Note: Because the filter can no longer be evaluated as true, the filter would normally stop evaluating here. We’ll proceed just to fully explore the result

Filter 2 – (DataSource[Last Name]) EqualTo (‘Smith’). The record passes the Last Name filter because ‘Smith’ is equal to ‘Smith’

Filter 3 – (DataSource[DateCreated]) GreaterThanOrEqualTo (#2011-01-01T02:35:00#). The record passes this Date filter because 04/03/2011 is greater (further into the future) than or equal to 01/01/2011.

Filter 4 – (DataSource[DateCreated]) LessThanOrEqualTo (#2011-06-30T03:35:00#) . The record passes this Date filter because 04/03/2011 is less (further into the past) than or equal to 30/06/2011.

Now the filter checks the nested OR block, containing the 2 filters about the First Name field.

Filter 5 – (DataSource[First Name]) EqualTo (‘Caitlyn’). The record fails this filter because ‘Catherine’ is not equal to ‘Caitlyn’

Filter 6 – (DataSource[First Name]) EqualTo (‘Catherine’). The record passes this filter because ‘Catherine’ is equal to ‘Catherine’.

The OR block is now evaluated. There are 2 values to check in the OR block:

  • Filter 5 – false
  • Filter 6 – true

Because at least one value is true in the block, the OR block resolves as true.

Finally, the main AND block is evaluated. There are 5 values to check in the AND block:

  • Filter 1 – false
  • Filter 2 – true
  • Filter 3 – true
  • Filter 4 – true
  • OR Block (Filters 5 & 6) – true

Because not every value in the AND block is true, the block resolves as false.

The filter returns false, and so this record is blocked from display by the filter.

Record 2 – Caitlyn Smith

Caitlyn Smith is a Project Manager from Sydney. Her record in the Contacts eForm was created on 20/05/2011

Filter 1 – (DataSource[City]) EqualTo (‘Sydney’). The record passes the City filter, because ‘Sydney’ is equal to ‘Sydney’.

Filter 2 – (DataSource[Last Name]) EqualTo (‘Smith’). The record passes the Last Name filter because ‘Smith’ is equal to ‘Smith’

Filter 3 – (DataSource[DateCreated]) GreaterThanOrEqualTo (#2011-01-01T02:35:00#). The record passes this Date filter because 20/05/2011 is greater (further into the future) than or equal to 01/01/2011.

Filter 4 – (DataSource[DateCreated]) LessThanOrEqualTo (#2011-06-30T03:35:00#) . The record passes this Date filter because 20/05/2011 is less (further into the past) than or equal to 30/06/2011.

Now the filter checks the nested OR block, containing the 2 filters about the First Name field.

Filter 5 – (DataSource[First Name]) EqualTo (‘Caitlyn’). The record passes this filter because ‘Caitlyn’ is equal to ‘Caitlyn’.

Filter 6 – (DataSource[First Name]) EqualTo (‘Catherine’). The record fails this filter because ‘Caitlyn’ is not equal to ‘Catherine’.

The OR block is now evaluated. There are 2 values to check in the OR block:

  • Filter 5 – true
  • Filter 6 – false

Because at least one value is true in the block, the OR block resolves as true.

Finally, the main AND block is evaluated. There are 5 values to check in the AND block:

  • Filter 1 – true
  • Filter 2 – true
  • Filter 3 – true
  • Filter 4 – true
  • OR Block (Filters 5 & 6) – true

Because every value in the AND block is true, the block resolves as true.

The filter returns true, and so this record is allowed to display by the filter.

Related Articles