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.

edit.png




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])
ToLower() Convert string to lower case ToLower([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
ToDate() Converts string to date ToDate("12/1/2017")
TODAY Today's date ToDate([TODAY])
TODAYF Today's date including time ToDate([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: ToDate([HireDate]) == ToDate([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")
ToDate([HireDate]) <= ToDate([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"