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 results must match – effectively looking for a subset of some large pool of data.
Nimblex provides a powerful filtering tool for 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.
Video Tutorial
Using The Editor in Common Section
When viewing a Tabular Report, you can view the Conditional Filter Editor by following these steps:
-
Make sure the sidebar is open using the icon in the top left corner.
-
Under the Common heading in the sidebar, click Edit for the Filter field.
-
This will open up the Filter Editor, which is made up of a number of parts:
-
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.
-
Filter Type Selector – You can make a Filter Statement from a boolean eForm Column (e.g. Contract Closed), or make it into an Operator Statement (e.g. ([Description]) Contains (‘Banana’)).
-
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.
-
Filter Groups – Each group has a boolean operator (pictured: AND), some Filter Statements, and the Add Buttons to add Nested Groups or Filter Statements.
-
Add / Delete Buttons – Click these buttons to add Nested Groups (left button) or more Filter Statements (right button) or to delete a Filter Statement.
-
The below explains these further:
Statements
A statement is an expression that takes the form: {code}[value] [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 is a comparison that resolves as 'true' or 'false'.
-
The EqualsTo operator resolves as 'true' if the compared values are equal, e.g. “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 – e.g. “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, e.g. “” 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] [Administrators]
[Contract Closed]
1 < LessThan > 3
Nested Statements
One or both values of a given Statement or Filter can be another Statement.
The statement:
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.
- The OR operator means that the group resolves as 'true' if any of the filters within that group resolve as 'true'.
Nested Filter Groups
A filter group can be nested inside another filter group:
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”
This filter will refine the results set to results that:
- Pass filter 1, OR
- Pass filter 3, OR
- Pass filter 2 AND filter 4
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 Scenario
Say you want to find the contact information for a project manager you worked with you 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:
Let’s break this filter down into small parts so it’s easily digestible. There are six filter statements 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 (#2010-12-31T15:30:00#) – The DateCreated field is greater than or equal to the date 31/12/2010 at 15:30 (UTC).
- (DataSource[DateCreated]) LessThanOrEqualTo (#2011-06-29T17:30:00#) – The DateCreated field is less than or equal to the date 29/06/2011 at 17:30 (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.
Step-by-Step
Let us look at how 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 (#2010-12-31T15:30:00#). The record passes this Date filter because 04/03/2011 is greater (further into the future) than or equal to 31/12/2010.
- Filter 4 – (DataSource[DateCreated]) LessThanOrEqualTo (#2011-06-29T17:30:00#) . The record passes this Date filter because 04/03/2011 is less (further into the past) than or equal to 29/06/2011.
Now the filter checks the nested OR block, containing the two 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 two 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 five 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 (#2010-12-31T15:30:00#). The record passes this Date filter because 20/05/2011 is greater (further into the future) than or equal to 31/12/2010.
- Filter 4 – (DataSource[DateCreated]) LessThanOrEqualTo (#2011-06-29T17:30:00#) . The record passes this Date filter because 20/05/2011 is less (further into the past) than or equal to 29/06/2011.
Now the filter checks the nested OR block, containing the two 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 two 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 five 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.
Using The Editor in Filtering Section
-
Select an eForm Profile [1] that you wish to edit the filter for.
-
Make sure the sidebar is open using the -> tab [2] button.
-
Under the Filtering [3] heading, you will see a few simple quick filters a user can leverage on.
-
Exclude Closed: Set this to 'Yes' to exclude records with 'Closed' Status. Note that some eForms might use a different status to mark a record as closed, e.g. 'Inactive' instead of 'Closed'. In this case, this filter will not work.
-
Priority: Enter a value to filter the Priority Column based on this value.
-
Show Only First Line?: Set this to 'Yes' to show just the first line of the records to avoid seeing multiple lines of the same record.
-
Status: Enter a value to filter the Status Column based on this value.
-