Apply filters

Tom Santiago
  • Updated

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.

Contents

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.

exportFilter.png

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.

importFilter.png

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 .
FilterBubble.png

The Filter editor displays.

Navigating the Filter editor

FilterEditorLogicalOperators.png
  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.

  1. 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.

  1. 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.
  2. 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.

  1. 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.
  2. 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

["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.

To learn more about this topic, take the integrator.io Level 2: SEASONED course Advanced Flow Builder:

  • Advanced Flow Configuration
    • Filters

Related articles

Comments

8 comments

Date Votes
  • METER Group
    Celigo University Level 1: Skilled

    On the operand section,

    When set to 'Field' I want to know why no field values appear in the picklist.  For example in my custom flow when I have the filter in my previous lookup set to the variables I intend to use (actually, I'm not sure whether that is related).

    I need to know when to use 'value', and when to use 'expression'.  I have a response mapped field  called K_data (mapped from data), and the filter doesn't work when I set the operand type to 'value', but it does for 'expression'.  Why?  

    0
  • METER Group
    Celigo University Level 1: Skilled

    In the section "Navigating the filter editor", in the first picture, is 'NOT' enable or is 'NOT' disabled?

    0
  • METER Group
    Celigo University Level 1: Skilled

    Another thing that would be extremely helpful would be to describe the syntax/formating for the various Operand Type options.  Do I need to put Operands in quotes  (e.g. "Opp_data"), and does that answer change based on the Operand Type?  What about the values that I'm looking for (the right side or RVALs)? Do they need to be open? in single quotes? in double quotes?  and does the type of single quote matter like it does in SOQL queries where I can't paste in the fancy curved single quotes that a Google Doc generates? (That is a terrible thing to have to debug, by the way. I edited my query in a google doc and pasted it into the SOQL query window and spent a full day trying to figure out that it wouldn't work because google doc only allows you to type curved quotes, and the query ONLY understood straight ones).

    Can I put in an array name to see if 'is empty', or can I only put in individual values (array[0].member)? and, how do I format a member?  array.member?  array.0.member?  array[0].member?

    0
  • METER Group
    Celigo University Level 1: Skilled

    You mention the option of creating a javascript filter, but then you don't give any guidelines on how to save that javascript filter.  If this is not the best document for that information, then please put in a link that tells about creating js files and functions.

    0
  • Tom Santiago Principal Technical Writer Community moderator
    • Edited
    Engaged
    Top Contributor
    Celigo University Level 4: Legendary

    Hi METER Group,

    Thanks for all of this feedback on filters:

    • I've revised the article to explain that the NOT filter is actually enabled by default in the current UI, thanks for bringing this to our attention.
    • In testing, with a string value equals operator, no quotes, single quotes, or double quotes all produced the same results and the behavior of the filter operated as expected. Do not use smart quotes.
    • When accessing an array, I was able to successfully create an "is empty" filter using the syntax arrayName.0, but I am still researching the nuances of when to use alternate array referencing syntax (arrayName[0] for instance). I will revise this article according to my findings. 
    • We have an initiative to make our JavaScript content more robust throughout the knowledge base. I'll make sure to notify you of new content on that front as it occurs.

    Our Support team will be contacting you soon about the other issues you've brought up. This is great feedback, Kenneth. Thanks for your patience while we address the rest of your concerns.

    0
  • Steve Klett Strategic Partner
    Celigo University Level 4: Legendary
    Answer Pro
    Great Answer
    Top Contributor
    Awesome Follow-up
    Engaged

    I've skimmed this document and I don't see a complete example of what a Javascript filter looks like. Is it a series of nest arrays like NetSuite's filter expression? Is there a function I need to add that will be automatically called? A sample showing what is expected would be useful, without this I'm moving on and not able to use the feature.

    0
  • Stephen Brandt Documentation Manager Community moderator
    Celigo University Level 4: Legendary
    Engaged
    Great Answer
    Top Contributor

    Hi, Steve Klett. As mentioned, we still need to build out the documentation with sample JavaScript. I regret the slow pace and for not filling the hole that we let you fall into.

    The object passed and the expected return value are actually simpler than you proposed:

    /*
* filterFunction stub:
*
* The name of the function can be changed to anything you like.
*
* The function will be passed one 'options' argument that has the following fields:
*   'record' - object {} or array [] depending on the data source.
*   'pageIndex' - 0 based, context is the batch export currently running.
*   'lastExportDateTime' - delta exports only.
*   'currentExportDateTime' - delta exports only.
*   'settings' - all custom settings in scope for the filter currently running.
* The function needs to return true or false.  i.e. true indicates the record should
    * be processed.
* Throwing an exception will return an error for the record.
*/
function filter (options) {
  return true
}

    I suppose, there's not much advantage to a JS filter over a downstream hook, unless it's a matter of precisely where you want to remove the records within the flow. 

    0
  • Steve Klett Strategic Partner
    • Edited
    Celigo University Level 4: Legendary
    Answer Pro
    Great Answer
    Top Contributor
    Awesome Follow-up
    Engaged

    Thank you Stephen Brandt - I'm already crawling out of my hole. ;)

    I decided to add more to this comment. The reason I'm looking into JavaScript rules is due to the inflexibility of the rules editor. It's a good system, I wish I could use it more, but there are some limitations that can make it a real nightmare:

    1. Can't move a rule into a group
    2. Can't move a group into another group
    3. Can't re-order rules (nice to have)

    #1 and #2 are critical if the requirements changed. Let's say you started with an OR group and have several levels of child groups and rules. Then a new requirement to have a TOP level rule that must be AND'ed with all the other stuff. You can't do this, instead you need to either throw it all out and re-create it (very time consuming and error prone) or add that new top level rule to all of the groups that are in the top level OR group. Neither is a good choice.

    I don't mean this to sound so whiny - but if the Ruled editor got some developer love and supported re-organizing it would be MUCH better. Also, the group boxes are hard to see. It looks pretty, but it's difficult to see them, I'd prefer a much higher contrast UI.

    1

Please sign in to leave a comment.