The Celigo platform allows you to filter data as it progresses through a flow. You can apply an output filter on a flow step's incoming data to selectively refine which records are passed on to subsequent steps of your flow, or you can create an input filter on a lookup or import flow step that only accepts incoming data that meets a specific criteria. The Filter editor is where you define the logic that excludes or includes matching records.
Tip
You can use filters to exclude any ID values in customer ID fields generated before a specific date.
Caution
If the fields you want to use to filter data aren't appearing, your mock output record doesn't meet the filter or branching conditions. Update your mock output record so it satisfies the filter or branching conditions, and the fields will appear.
You can add an output filter to a source (export), or to any flow step (import/lookup) that has subsequent flow steps. Records that meet the rules criteria you define in the output filter are passed into subsequent steps of the flow. Records that do not meet the rules criteria you define in the output filter are discarded and will not be processed by any subsequent flow steps in the flow.
If you apply an output filter to a source (export) flow step, the flow will only receive and process records that are not blocked by the output filter.
If you add an input filter on a flow step (import/lookup), the flow step will only process records that meet the input filter criteria that you define. Any subsequent flow steps will continue to process incoming data from previous flow steps, even if it was excluded by an input filter on a previous flow step. In other words, a record passing through a flow will skip a flow step that blocks it with an input filter, but will be processed by a subsequent flow step with no filter.
The filter applied on this step will only accept data the meets the input filter criteria. All data initially sent from the export will continue through any subsequent steps of the flow, but the flow step will ignore any record that does not fit the input filter's rules.
-
Open Flow builder.
-
Click the elipsis (...) on a flow step to add a filter. You can add an output filter to an export (or an import/lookup with subsequent flow steps). You can add an input filter on an import or lookup flow step.
-
Click the filter option appropriate for the flow step’s context: output (
) or input (
)
-
The filter editor displays.
-
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.
Notice
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.
-
-
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 list contains all available record fields. You can also use a value or an expression instead. Click + Add field at the bottom of the list to add a new field. You can modify existing field names 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 list.
-
Operand type and Data type: Operand types can be:
-
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 is available only for field or value operand types, for use in defining the format as a string, number, date-time, or Boolean.
-
-
Operator: This list 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.
-
between: between a defined range. Specify the
-
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.
-
null: has no value in the field.
-
is not null: has any value in the field.
-
matches: has a specific value in the field.
-
-
Value: Enter the appropriate value according to the selected operator.
-
Operand type and Data type: see step 3.
-
Condition handle (only visible on hover): If you have multiple conditions that must be organized in a specific chronological order to accurately filter your records, click and drag the handle (
) to rearrange the order of the conditions .
-
Plus sign (+ ) (only visible on hover): Click to add a new condition. This has the same function as clicking + Condition in step 10.
-
Elipses (...) (only visible on hover): Click to clone or delete a condition.
-
+ Condition: You can add as many conditions to a filter as you need. Click + Condition after you’ve defined one or more rules for your filter and are ready to add one to another field.
-
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.
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
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.
["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",["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",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",["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",["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.
To learn more about this topic, take the course Level 2: Exploring Flow builder.