Filter Tool
Use Filter to select data using a condition.
Rows of data that meet the condition are output to the T anchor. Rows of data that do not meet the condition are output to the False anchor.
The Filter tool can:
Select rows by comparing a column against a static value.
Select rows with or without missing data.
Select rows using date-time data.
Select rows using a multiple-column condition.
Select rows using a compound condition.
Examples of each of these scenarios can be found at the end of this page.
Tip
This tool has a One Tool Example. Visit Access Sample Workflows to learn how to access this and many other examples directly in Designer Cloud.
Tool Components
 |
Figure: Filter Tool with anchors.
The Filter tool has 3 anchors.
Input anchor: Use the input anchor to select the data you want to filter.
T (True) anchor: The True anchor outputs the rows of data that meet the filter condition.
F (False) anchor: The False anchor outputs the rows of data that do not meet the filter condition.
Configure the Tool
Select Basic Filter or Advanced Filter.
Use the Basic Filter to build a simple query on a single column of data.
Use the Advanced Filter to build complex conditions or conditions using more than 1 column.
Figure: Basic and Advanced Filters.
Build Basic Filter
Select the column of data to filter by in Column Name.
Select the Operator. Available operators depend on the data type of the column you selected to filter by. See Basic Filter Operators below.
Enter a Value to complete the condition.
Figure: Basic comparison.
Basic Filter Operators
Available operators depend on the data type of the column you select to filter by. Learn more about Supported Data Types.
Operator | Description | Available Data Types |
|---|---|---|
= | Value matches input. | Numeric, DateTime |
Equals | Value matches input. | String |
!= | Value does not match input. | Numeric, DateTime |
Does not equal | Value does not match input. | String |
> | Value is greater than input. | Numeric, DateTime |
>= | Value is greater than or equal to input. | Numeric, DateTime |
< | Value is less than input. | Numeric, DateTime |
<= | Value is less than or equal to input. | Numeric, DateTime |
Is null | Value is a missing or unknown value. | Numeric, DateTime, String |
Is not null | Value is not a missing or unknown value. | Numeric, DateTime, String |
Comes before (<) | Value sorts to come before input. | String |
Comes after (>) | Value sorts to come after input. | String |
Contains | Value is found in any part of the string. | String |
Does not contain | Value is not found in any part of the string (case sensitive). | String |
Is empty | Value is Null or "". | String |
Is not empty | Value is not Null or "". | String |
Range | Value is a selected start and end date. | DateTime |
Start date and periods after | Value is the selected first date and a set number of periods that follow. | DateTime |
End date and periods before | Value is the select last date and a set number of periods that precede. | DateTime |
Build Custom Filter Using Expressions
To build a custom filter condition, enter your expression syntax into the expression editor. There are also many tools to assist with building Custom filter conditions. See Expression Editor Tips.
 |
Figure: Advanced custom filter.
Expression Editor Tips
These tools are available within the expression editor to assist with building Custom filter conditions:
Press Ctrl+Space to view a list of all functions.
Type a word or phrase to view a matching list of functions.
Type a [ (left bracket) to view a list of variables to use in the expressions. Variables can be...
Data from an incoming connection or a column created in a previous expression.
Select the fx icon to search and browse functions.
Select the x icon to browse or search columns.
Note that the View Recentand Saved Expressions and Save expression options will be available at a later time.
Available Functions
These functions are available for use in the expression editor. For more information on these functions, go to Workflow Functions.
Function | Function Type |
|---|---|
IIF | Conditional |
SWITCH | Conditional |
IF condition THEN t ELSE f ENDIF | Conditional |
IF c THEN t1 ELSEIF c2 THEN t2 ELSE f ENDIF | Conditional |
/* block comment */ | Conditional |
// single-line comment | Conditional |
BinToInt | Conversion |
CharFromInt | Conversion |
CharToInt | Conversion |
ConvertFromCodePage | Conversion |
ConvertToCodePage | Conversion |
HexToNumber | Conversion |
IntToBin | Conversion |
IntToHex | Conversion |
ToDegrees | Conversion |
ToNumber | Conversion |
ToRadians | Conversion |
ToString | Conversion |
UnicodeNormalize | Conversion |
DateTimeAdd | DateTime |
DateTimeYear | DateTime |
DateTimeMonth | DateTime |
DateTimeDay | DateTime |
DateTimeHour | DateTime |
DateTimeMinutes | DateTime |
DateTimeSeconds | DateTime |
DateTimeDiff | DateTime |
DateTimeFirstOfMonth | DateTime |
DateTimeLastOfMonth | DateTime |
ToDate | DateTime |
ToDateTime | DateTime |
DateTImeParse | DateTime |
DateTimeQuarter | DateTime |
DateTimeFormat | DateTime |
DateTimeTrim | DateTime |
= | Filter Operators |
IsNotNull | Filter Operators |
!= | Filter Operators |
Contains | Filter Operators |
> | Filter Operators |
IsNotEmpty | Filter Operators |
IsNull | Filter Operators |
NotContains | Filter Operators |
>= | Filter Operators |
< | Filter Operators |
<= | Filter Operators |
IsEmpty | Filter Operators |
IsTrue | Filter Operators |
DateRange | Filter Operators |
IsFalse | Filter Operators |
PeriodBefore | Filter Operators |
PeriodAfter | Filter Operators |
FinanceCAGR | Finance |
FinanceEffectiveRate | Finance |
FinanceFV | Finance |
FinanceFVSchedule | Finance |
FinanceIRR | Finance |
FinanceMIRR | Finance |
FinanceMXIRR | Finance |
FinanceNominalRate | Finance |
FinanceNPER | Finance |
FinanceNPV | Finance |
FinancePMT | Finance |
FInancePV | Finance |
FinanceRate | Finance |
FinanceXIRR | Finance |
FinanceXNPV | Finance |
ABS | Math |
ACOS | Math |
ASIN | Math |
ATAN | Math |
ATAN2 | Math |
Average | Math |
AverageNonNull | Math |
CEIL | Math |
COS | Math |
COSH | Math |
EXP | Math |
Factorial | Math |
FLOOR | Math |
LOG | Math |
LOG10 | Math |
Median | Math |
Mod | Math |
PI | Math |
POW | Math |
RAND | Math |
RandInt | Math |
Round | Math |
SIN | Math |
SINH | Math |
SmartRound | Math |
SQRT | Math |
TAN | Math |
TANH | Math |
BinaryAnd | Math: Integer |
BinaryNot | Math: Integer |
BinaryOr | Math: Integer |
BinaryXOr | Math: Integer |
ShiftLeft | Math: Integer |
ShiftRight | Math: Integer |
Between | Min/Max |
Bound | Min/Max |
Max | Min/Max |
MaxIDX | Min/Max |
Min | Min/Max |
MinIDX | Min/Max |
Addition + | Operators |
Subtraction - | Operators |
Division / | Operators |
Multiplication * | Operators |
Boolean AND && | Operators |
Boolean AND - Keyword | Operators |
Boolean NOT ! | Operators |
Boolean NOT - Keyword | Operators |
Boolean OR || | Operators |
Boolean OR - Keyword | Operators |
Equal To = | Operators |
Not Equal To != | Operators |
Greater Than > | Operators |
Greater Than or Equal >= | Operators |
Less Than < | Operators |
Less Than or Equal <= | Operators |
value IN (...) | Operators |
value NOT IN (...) | Operators |
Coalesce | Specialized |
EscapeXMLMetacharacters | Specialized |
GetVal | Specialized |
Message | Specialized |
Null | Specialized |
RangeMedian | Specialized |
Soundex | Specialized |
Soundex_Digits | Specialized |
TOPNIDX | Specialized |
UrlEncode | Specialized |
Contains | String |
CountWords | String |
DecomposeUnicodeForMatch | String |
EndsWith | String |
FindNth | String |
FindString | String |
GetLeft | String |
GetPart | String |
GetRight | String |
GetWord | String |
Left | String |
Length | String |
LowerCase | String |
MD5_ASCII | String |
MD5_UNICODE | String |
MD5_UTF8 | String |
PadLeft | String |
PadRight | String |
REGEX_CountMatches | String |
REGEX_Match | String |
REGEX_Replace | String |
Replace | String |
ReplaceChar | String |
ReplaceFirst | String |
ReplaceString | String |
Right | String |
StartsWith | String |
STRCSPN | String |
StripQuotes | String |
STRSPN | String |
Substring | String |
TitleCase | String |
Trim | String |
TrimLeft | String |
TrimRight | String |
Uppercase | String |
UuidCreate | String |
CompareDictionary | Test |
CompareDigits | Test |
CompareEpsilon | Test |
EqualStrings | Test |
IsEmpty | Test |
IsInteger | Test |
IsLowerCase | Test |
IsNull | Test |
IsNumber | Test |
IsString | Test |
IsUpperCase | Test |
Tool Examples
Example 1. Select Rows Using Basic Comparison
Use the Basic Filter to select rows by comparing a column against a static value.
 |
Figure: Basic Filter comparison.
According to the condition of the above Basic Filter, rows with a UserID value greater than 30 are output to the T anchor. Rows with a CustomerID value that is not greater than 30 are output to the F anchor.
Example 2. Select Rows With or Without Missing Data
Use the Basic Filter to select rows without missing data.
 |
Figure: Filter missing data.
According to the condition of that Basic Filter, rows with a Last value that is not null (missing) are output to the T anchor. Rows with a null value in Last are output to the F anchor.
Example 3. Select Rows Using Date-Time Data
Use the Basic Filter to select rows with a relative date.
 |
Figure: Example - filter data.
According to the condition of the above Basic filter, rows with a Registration Date/Time that is less than or equal to today's date are output to the T anchor. Rows with a date after today's date are output to the F anchor.
Date-Time Filter Tips:
The dynamic options, today, tomorrow, and yesterday, update the workflow to that relative date when the workflow is run.
The Start date and periods after or End date and periods before operators allow you to specify a date range by selecting a specific date, a Period type (Days, Months, Weeks, Quarters, or Years), and the Number of periods.
Select Filter only Date data to truncate date-time data to use only the date data.
Example 4. Select Rows Using a Multiple-Column Condition
Use the Advanced Filter to create a condition that references more than 1 column to select rows.
 |
Figure: Example - advanced multi-column filter.
According to the condition of the above Advanced Filter, rows with a POP2005 value that is greater than or equal to the POP2010 value are output to the T anchor. Rows with a POP2005 value that is less than the POP2010 value are output to the F anchor.
Example 5. Select Rows Using a Compound Condition
Use the Advanced Filter to create compound conditions, in other words, more than 1 condition joined by AND or OR operators.
 |
Figure: Example - filter compound condition.
According to the condition of the above Advanced Filter, rows where the state is Alabama and the population is greater than or equal to 1500 are output to the T anchor. All other rows are output to the F anchor.
Troubleshooting
Treat numbers with more than 15 digits as strings to prevent a loss of precision. You can set the field type to a string using the Select tool.