Have you ever wondered how to use the WHERE clause in a Salesforce Integration CloudApp Import?

If you’re like most people, you see SOQL and aren’t certain how to proceed. Many people have heard of the more common SQL (Structured Query Language). SQL is a programming language used to directly query and update records in a database. However, SOQL (Salesforce Object Query Language) is Salesforce’s own version that’s optimized for their database structure.

In the past, Eloqua’s native integration used a more user-friendly option for the filter criteria. First, users would enter the criteria, and then behind the scenes, Eloqua would build the SOQL WHERE clause. Because this can be a powerful tool to limit records that enter Eloqua, Oracle decided to allow the full use of SOQL in the newer Salesforce Integration CloudApp. But this made it quite a bit more difficult for the standard Eloqua user.

Never fear! We’re here to help you get comfortable using SOQL for your WHERE clauses. Read on to learn how.

What’s Automatically Included in the Filter?

There are two items that are automatically included in the Filter. These are implemented behind the scenes:

1. Only records modified since the last successful upload are included. If the last successful upload occurred on 8/15/2023 at 10:03 a.m., the filter will only retrieve records with a Last Modified Date greater than or equal to 8/15/2023 at 10:03 a.m.
2. If Import Only Deleted Records has not been enabled, the filter will only retrieve records that have not been deleted. It looks for a value of true or false on the IsDeleted field on the Salesforce record and only retrieves records with a false value.

Equalities and Inequalities

Suppose you only want to retrieve Salesforce Lead records where the Country is equal to “United States” and the Lead Status is equal to “Open.” That’s easy with SOQL in the WHERE clause! Here’s how you would do that:

(Note that the values are contained within single quotes.)

And if you want to change that by only including Countries that aren’t “United States”?

Contains

Let’s say you want to pull Salesforce Lead records that have a Country that is equal to “United States” and the Title contains the word “government”:

To search for values that contain (rather than are equal to) a specific string, you’ll need to use the LIKE keyword and include percent symbols on either side of the search string. The percent symbols serve as wildcards so that any record with a title that contains the word “government” is retrieved.

But to exclude records with a title that contains “government”:

Or, if you’re looking only for records with a title starts with “VP,” you’ll only include the percent symbol after the search string:

Date and Time

Salesforce requires an explicit formatting of the date and time values and includes the time zone. For example, August 15 at 11:15 p.m. in the Eastern Time Zone would be:

2023-08-15T23:15:00-04:00 (Note the T between the date and time.)

Alternatively, UTC time would be formatted as:

2023-08-15T23:15:00Z (Note the Z after the time.)

Here’s an example scenario: let’s say there’s a custom field on the Salesforce Lead called MQL Date (MQL_Date__c). You would like to retrieve only records that were created since the beginning of this year in the Pacific Time Zone:

(Note that there are no single quotes on the datetime value.)

Final Comments

It’s very helpful to have access to somebody on your Salesforce Team. As you can see in the last example, it’s not always obvious which field name to use for your WHERE clauses. This person can tell you what the field name should be.

If you don’t have access to this person, select the Salesforce field in the mapping area. The field name you use in the WHERE clause should always be the value in gray displayed under the selected value (and is called the API or Database name).

For example, in this case, it would be Created_Date_Time__c.

There you have it—now you can start using WHERE clauses in Salesforce Integration CloudApp Imports! If you need more Salesforce expertise, don’t hesitate to reach out to our team at Definitive Results to discuss how we can help you meet your goals.