Apply filters

integrator.io allows you to filter data as it progresses through a flow. You can apply an output filter to refine the data retrieved from your source application, or you can create a filter that selectively accepts incoming data for a lookup or import. The Filter editor is where you define the logic that excludes or includes matching records.

Example: You can use filters to exclude any ID values in customer ID fields generated before a specific date.

Export vs. import filters

Filters are available in additional resources, such as import mapping lookup criteria, and they apply as expected. (In fact, you can test-drive expressions in the Dev playground Filter editor with sample data, as long as you’re working in Developer mode.) There’s one important difference to keep in mind between how records are treated within flow processing, between export and import filters: 

Export (output) filters

Records from an export or lookup that do not meet the criteria defined in the Filter editor are discarded. The flow does not continue to process records discarded by an output filter.

The filter applied to the export will only send data that meets the filter criteria to any step throughout the rest of the flow.

Import (input) filters

An import processes records that meet the criteria that you define in the Filter editor. If there are other flow steps downstream, the flow continues to process the original data, regardless of whether they were excluded for use by an earlier import.

The filter applied on this step will only accept data the meets the filter criteria. All data initially sent from the export will continue through any additional steps included in the flow, but the filter rejects export data that does not fit the criteria from being included in this step.

Create filters

1. Open Flow Builder.
2. Click the Define options (+) button in a flow step. The available options are displayed alongside those that you have already selected.
3. Click the Filter option appropriate for the flow step’s context: output or input .

The Filter editor displays.

Navigating the Filter editor

1. Logical operators: You can use any of the following logical operators to define rules for data:
   • NOT: Select the checkbox if you want to use the NOT operator. NOT excludes any data that meets the criteria you define in the rules. If the checkbox is not selected, the filter includes any data that meets your defined rule criteria.
   • AND: Select AND if you want data that meets the combined criteria defined in the rules.
   • OR: Select OR if you want data that meets any criteria defined in the rules.

     Example: If you want to exclude all records that belong to a subsidiary company, select NOT. You can also select AND or OR to create filters that combine multiple rules for your filtration criteria. AND filters records that fit all listed rules in the group. OR filters records that fit any listed rules in the group.

2. Field name: Flow Builder allows you to select any field from your data record from this menu. Choose the relevant field for your filter. By default, a drop-down menu contains all available record fields. You can also use a value or an expression instead. To add a new field or edit an existing field, type the new field name (or modify the existing field name) in the Input text box of the Filter editor. Once the field exists in the sample record, it will be available in this drop-down menu.  

   Note: The Operand settings () button appears when you hover over the field name. Click it to change the input format to an expression or a specific value.

3. Operator drop-down menu: This menu allows you to apply conditional operators to the filter. This menu has the following operators:
   • equals: equal to specific date or numeric value.
   • not equals: not equal to a specific date or numeric value.
   • is greater-than: greater than a specific date or numeric value.
   • is greater-than or equals: greater than or equal to a specific date or numeric value.
   • is less-than: less than a specific date or numeric value.
   • is less-than or equals: less than or equal to a specific date or numeric value.
   • starts with: begins with a specific string of characters.
   • ends with: ends with a specific string of characters.
   • contains: has a specific string of characters at any point in the field value.
   • does not contain: does not have a specific string of characters at any point in the field value.
   • is empty: has no value in the field.
   • is not empty: has any value in the field.
   • matches: has a specific value in the field.
4. Value: Enter the appropriate value according to the selected operator.

   Note: The Operand settings () button appears when you hover over the field name. Click it to change the input format to an expression or a specific value.

5. Add rule: You can add as many rules to a filter as you need. After you’ve defined one or more rules for your filter and are ready to add one to another field, click Add rule.
6. Add group: Click Add group to create filters with a set of rules that use multiple Boolean conditions. Groups allow you to segment your filtration logic for complex circumstances. For example, you could use one rule group that uses the NOT Boolean operator to exclude records that were created before a certain date, then create another rule group to include records that use the AND or OR operator.

Operand settings

Click the Operand settings () button that appears when you hover on an operand field to open the Operand settings dialog, which allows you to change the operand type from a field to an expression or a value. These settings are dynamic; the available fields vary based on the operand and data types that you choose.

Operand type

• Field: Typically, this is the first field in a filter rule. Use the drop-down menu to select any field from your sample record. 
• Value: This allows you to define the value that you want to create a filter rule on. Set the second field in a filter rule to Value if the rule's behavior is defined by a number, string, or boolean value. 
• Expression: Use Expression if the filter rule's behavior is defined by a simple mathematical calculation. Note that you can't use handlebars expressions in the context of a filter rule. You can use this syntax to perform Add, Divide, Multiply, Subtract, or Replace calculations for the filter rule.

Data type

Data type is available only for field or value Operand types, for use in defining the format as a string, number, date-time, or Boolean.

Apply functions

Functions are available only for string or number data types. Number functions include ceiling (the least integer that is greater than or equal to the value), floor (the greatest integer that is less than or equal to a value), and absolute (the exact number value). String functions are uppercase and lowercase.

Using JavaScript instead of rules 

You can toggle between rules and JavaScript to implement filter logic with the JSON/JavaScript toggle switch at the top of the Filter editor.

Rules are the preferred option for simple filter logic, but JavaScript is an option for complex filter logic. You can’t define both rules and JavaScript in a filter. If you apply rules and then toggle Filter editor to JavaScript to enter filter criteria, JavaScript takes precedence, and any previously defined rules are ignored. 

Consider JavaScript filters for the following requirements:

• Concatenate multiple string fields, and then filter records based on the results of the concatenated value
• Parse a complex string field that contains multiple different values, and then filter based on the parsed value
• Add multiple numeric fields together, calculate an average, and then filter records based on the average
• Filter records where a date field in the record is older than a specific number of days ago

Filter expression operands

A filter expression operand provides useful shorthand for complex logic, such as in the following example:

["add",["number",["extract","number__a"]]]

Where:

• add is the arithmetic operation to be performed using the expression
• number__a is the field name
• number is the data type of number__a
• extract is the keyword that extracts a value from number_a

Important: Handlebars syntax is not supported by filter expressions. If you attempt to enter handlebars expressions as filter expressions, the expression values will neither be executed nor saved. Filter expressions only support logic using the following operators and syntax.

Supported filter expressions

• Add
• Divide
• Multiply
• Subtract
• Replace

Add

["add",20,["number",["extract","number__a"]],["number",["extract","number__c"]]]

Where:

• add is the arithmetic operation to be performed using the expression.
• number__a is the field name that can contain any value.
• number__c is the field name that can contain any value.
• number is the data type of number__a and number__c.
• 20 is the hard-coded value that will be added to the value extracted from number__a and number__c. For example, if number__a contains 15 and number__c resolves to 5, then the expression would mean 15+5+20 and the result will be 40.
• extract is the keyword that extracts a value from number__a and number__c.

Divide

["divide",["number",["extract","number__a"]],2]

Where:

• divide is the arithmetic operation to be performed using the expression.
• number__a is the field name that can contain any value.
• number is the data type of number__a.
• 2 is the hard-coded value that will act as the divisor for the value extracted from number_a. For example, if number__a contains 20, then the expression would mean 20/2 and the result will be 10.
• extract is the keyword that extracts a value from number_a.

Multiply

["multiply",4,["number",["extract","number__a"]]]

Where:

• multiply is the arithmetic operation to be performed using the expression.
• number__a is the field name that can contain any value.
• number is the data type of number__a.
• 4 is the hard-coded value that will act as a multiplier for the value extracted from number__a. For example, if number__a contains 20, then the expression would mean 20*4 and the result will be 80.
• extract is the keyword that extracts a value from number__a.

Subtract

["subtract",["number",["extract","number__a"]],["number",["extract","number__c"]]]

Where:

• subtract is the arithmetic operation to be performed using the expression.
• number__a is the field name that can contain any value.
• number__c is the field name that can contain any value. For example, if number__a contains 22 and number__c contains 2, then the expression would mean 22-2 and the result will be 20.
• number is the data type of number__a and number__c.
• extract is the keyword that extracts a value from number__a and number__c.

Replace

["replace",["string",["extract","string__c"]],"string/letter to be replaced","string/letter to replace with"]

Where:

• replace is the operation to be performed using the expression
• string__c is the field name that can contain any value
• string is the data type of string__c
• string/letter to be replaced is the string value that is to be replaced
• string/letter to be replaced with is the string value that replaces it

For example, if string__c contains:

AAAA BBBB CCCC DDDD 

and the expression is:

["replace",["string",["extract","string__c"]],"AAAA","CCCC"]

The result is:

CCCC BBBB CCCC DDDD

You can ask questions and get tips on filters in our Community forum.