Data Filters
When you use a Salesforce connection, you can create the following type of data filters for any type of task:
- •Simple
Create one or more simple data filters. When you create multiple simple data filters, the associated task creates an AND operator between the filters and loads rows that apply to all simple data filters. For example, you load rows from the Account Salesforce object to a database table. However, you want to load only accounts that have greater than or equal to $100,000 in annual revenue and that have more than 500 employees. You configure the following simple data filters:
Field | Operator | Field Value |
|---|
AnnualRevenue | greater than or equals | 100000 |
NumberOfEmployees | greater than | 500 |
- •Advanced
Create an advanced data filter to create complex expressions that use AND, OR, or nested conditions. You enter one expression that contains all filters. The expression that you enter becomes the WHERE clause in the query used to retrieve records from the source.
For example, you load rows from the Account Salesforce object to a database table. However, you want to load records where the billing state is California or New York and the annual revenue is greater than or equal to $100,000. You configure the following advanced filter expression:
(BillingState = 'CA' OR BillingState = 'NY') AND (AnnualRevenue >= 100000)
When you create a data filter on a Salesforce object, the corresponding task generates a SOQL query with a WHERE clause. The WHERE clause represents the data filter. The SOQL query must be less than 20,000 characters. If the query exceeds the character limit, the following error appears:
Salesforce SOQL limit of 5000 characters has been exceeded for the object: <Salesforce object>.
Please exclude more fields or decrease the filters.
You can create a set of data filters for each object included in a Data Replication or Data Synchronization task. Each set of data filters act independently of the other sets. You can use the following data filter variables in simple and advanced data filter conditions:
- •$LastRunDate
- •$LastRunTime
Note: Consider time zone differences when comparing dates across time zones. The date and time of the $LastRunDate and $LastRunTime variables are based on the time zone set in the Informatica Cloud Salesforce application. The date and time of the actual job is based on the GMT time zone for Salesforce sources.
Rules and Guidelines for Data Filters
Consider the following rules and guidelines for data filters for Salesforce Connector tasks:
- •Salesforce fields of LongTextArea datatype do not show up in the list of fields of a simple data filter.
- •When you include a Salesforce field of URL datatype, exclude “http://” from the value. For example, if the value is http://www.informatica.com, enter www.informatica.com.
- •When you include a Salesforce field with the Phone datatype, enter a value with the following syntax (XXX) XXX-XXXX. For example, enter (555) 555-1212. If you provide an incorrect syntax, the application ignores the filter.
- •When you include a Salesforce ID field in a filter, enter the exact ID value. If you enter a dummy ID value, the SOQL query fails.
- •When you write Salesforce data to a database target in a Data Synchronization task, verify that the Salesforce data uses the following required formats for date and time datatypes: Date (yyyy-MM-dd) and DateTime (yyyy-MM-dd HH:mm:ss).
If a record contains the date and time in a different format, the application rejects the row. If the Salesforce source contains a date and no time for the datetime datatype, the application appends ‘00:00:00' at the end of the date value to ensure the value is in the required format.
When you write to a database target, the application converts the Salesforce date and datetime data to the correct format expected by the database.