Filters
Overview
You can use filters in your source and target configurations to define specific subsets of data that you want to use in your syncs.
Source filters
When syncing a Data Source, you may have the option to add additional configuration sections, such as a Filter, under the "Add a Section" drop down tab in the Connection Experience (Image 1).
Note that if your source only has one of the listed options, it will appear by default instead of in a drop-down.
A filter on your source is optional. It relies on a source specific syntax for filtering out records from your source target. The filter can reference execution parameters.
| Source | Definition |
|---|---|
| File | For a file data sources (delimited, fixed width & Excel) the syntax conforms to the .NET frameworks RowFilter on a DataView |
| Salesforce | For Salesforce syntax is the SOQL where clause (without the where expression) |
| Dynamics | For Dynamics syntax is the OData $filter clause (without the $filter=expression) |
| Cinchy | For Cinchy syntax is the CQL where clause (without the where expression) |
| SqlServer | For SqlServer the syntax is the T-SQL where clause (without the where expression) |
This is only available if using a table, not a query. For queries, include the filter in the query itself.
There can only be one <Filter> for each source. To specify more than one condition, use AND/OR to allow logical combination of multiple expressions.
Conditional filters
For REST API, SOAP 1.2, Kafka Topic, Platform Event, and Parquet sources, there is a "Conditional" option for source filters in the Connections UI.
Once selected you will be able to define the conditions upon which data is pulled into your source via the filter. After data is pulled from the source, new conditional UI filters down the set of returned records to ones that match the defined conditions.
- Multiple Conditions can be added to a single data sync by using the AND/OR and +Rule buttons.
- You are able to group your Rules into a rule set by using the +Ruleset button.
- The left-most drop down is used to select either a source or a target column as defined in your Source and Destination tabs.
- The centre drop-down is used to select from the following options:
- =
- !=
- Contains
- Is Null
- Is Not Null
- The right-most drop-down can either be used for a plain value (ex: text, numerical, etc.) This will adjust based on the column data type picked in the left-most drop down. For example, if in the source schema the column is a date, then it renders a date picker.
For example, the below condition would only bring in records where the source column "Employee Status" is not null (Image 2).
Source filter examples
Example 1: Use a filter to sync only source records with [net worth] > 10000 (Image 3).
Example 2: Use a filter to sync only source records with a status like "Complete" (Image 4).
Target filters
A target destination filter is optional. It relies on a source specific syntax for filtering out records from your target. The filter can reference execution parameters.
| Source | Definition |
|---|---|
| Salesforce | For Salesforce the syntax is the SOQL where clause (without the where expression) |
| Dynamics | For Dynamics syntax is the OData $filter clause (without the $filter=expression) |
| Cinchy | For Cinchy syntax is the CQL where clause (without the where expression) |
| SqlServer | For SqlServer the syntax is the TSQL where clause (without the where expression) |
There can only be one <Filter> for each target. To specify more than one condition, use AND/OR to allow logical combination of multiple expressions.
Filter examples
Example 1: Filter only target records where the Assignee is Null (Image 5).
Example 2:Filter only target records where the Override ID is not Null (Image 6).
Example 3:Filter only target records from a specific source (Image 7).
