Complex Filters
Overview
Complex filters allow you to use multiple criteria and logical operators to define a filter.
The Filter Data functionality is included as a step in the Import Wizard. After import is complete, Filter Data properties can be modified using the Refresh Properties function.
Defining or Editing a Complex Filter
To define a complex filter make sure the 'Complex Filter' checkbox is selected in the Filter Data Panel. Click on the 'Edit Rule…' button to define or modify a complex rule.
Note: Use the 'Test Rule…' button to make sure you haven't made an errors in a complex rule definition.
Comparison Operators
The following Comparison Operators are available:
| Operator | Description | Example |
|---|---|---|
| == | equal to | [Department] == "Strategy" |
| != | not equal to | [Department] != "Strategy" |
| > | greater than | [Salary] > 100000 |
| < | less than | [Salary] < 100000 |
| >= | greater than or equal to | [Salary] >= 100000 |
| <= | less than or equal to | [Salary] <= 100000 |
Logical Operators
The following Logical Operators are available:
| Operator | Description | Example |
|---|---|---|
| && | and | [Department] != "Strategy" && [Department] != "Planning" |
| || | or | [Department] == "Strategy" || [Department] == "Planning" |
| ! | not | !(StartsWith([CostCenter],"999") |
String Functions
The following String Functions are available:
| Operator | Description | Example |
|---|---|---|
| ToUpper() | Convert string to upper case | ToUpper([Department]) |
| [field].toLowerCase() | Convert string to lower case | [field].toLowerCase([Department]) |
| Contains() | Test if a string contains a string | Contains([Department], "001") |
| StartsWith() | Test if a string starts with a string | StartsWith([Department], "001") |
| EndsWith() | Test if a string ends with a string | EndsWith([Department], "001") |
Date Functions
The following Data Functions are available:
| Operator | Description | Example |
|---|---|---|
| new Date() | Converts string to date | new Date("12/1/2017") |
| TODAY | Today's date | new Date([TODAY]) |
| TODAYF | Today's date including time | new Date([TODAYF]) |
| TODAYF+N | Today's date + N days | ToDate([TODAYF+5]) |
| TODAYF-N | Today's date - N days | ToDate([TODAYF-5]) |
NOTE: TODAY should be used to test if a date is equal to today. Example: new Date([HireDate]) == new Date([TODAY]). TODAYF should be used for all other comparisons.
Examples
Some examples listed below:
| Example | Notes |
|---|---|
| [Department] != "Strategy" && ([Department] != "Planning" || [CostCenter] == "999") | [Department] != "Strategy" AND ([Department] != "Planning" OR [CostCenter] == "999") |
| new Date([HireDate]) <= new Date([TODAYF]) | True if "HireDate" is not in the future |
| EndsWith(ToUpper([CostCenter]),"SE9") | True if cost center ends with "SE9" or "se9" or "Se9" or "sE9" |