Target Records Filter.

Table of Contents



Overview ⇧

Purpose: This feature provides an additional method to filter out unwanted records just before they are sent to the Target, which is particularly useful when unable to apply the desired filters directly using a WHERE clause in SOQL queries.

Use case: In some scenarios, the data fetched from the source includes records that you may not want to export to the target. For instance, when importing from a CSV file, the SOQL query may only define the columns to be processed, ignoring the WHERE clause. This is where the Target Record Filter feature comes into play, allowing further refinement of which records are sent to the target.

Examples of Using Target Record Filter ⇧

Here are a few examples of how to implement the Target Record Filter:

Example 1: Exclude Records with Null Values ⇧

To exclude all records that do not have a value in the Account__c field:

{
  "query": "SELECT Id, Name, Account__c FROM MyObject__c",
  "targetRecordsFilter": "Account__c"
}

Example 2: Include Only Records with Null Values ⇧

To include only the records where the Account__c field is empty:

{
 "query": "SELECT Id, Name, Account__c FROM MyObject__c",
 "targetRecordsFilter": "NOT Account__c"
}

Example 3: Include Records Based on Multiple Conditions ⇧

To include only records where the Account__c and Account2__c fields are equal, and the Contact__c field is not null:

{
 "query": "SELECT Id, Name, Account__c, Account2__c, Contact__c FROM MyObject__c",
 "targetRecordsFilter": "Account__c = Account2__c AND Contact__c"
}

Understanding the TargetRecordsFilter Syntax ⇧

The targetRecordsFilter uses a syntax similar to regular SOQL, with some specific exceptions:

  • Non-null Values: To select records where a field is not null (e.g., Account__c is not empty), simply use "Account__c". This is shorthand for Account__c <> null in standard SOQL syntax.

  • Null Values: Conversely, to filter records where a field is null (e.g., Account__c is empty), use "NOT Account__c". This substitutes for Account__c = null in traditional SOQL syntax.

Last updated on 20th Apr 2024