February 22, 2016

Using SOQL Queries

Creating Smart Sandbox Job Rules with SOQL (Salesforce Object Query Language) is a powerful way to pick which data to transfer to your sandboxes. Here is a short list of our favorite types of SOQL Queries to use in your Job Rules.

First off, here is the general structure of a SOQL query:

Select [fields] FROM [object] WHERE [criteria]

Because Smart Sandbox automatically transfers every field for a given Object, SOQL queries in Job Rules are simply the criteria part of a query, also known as the Where Clause. For example, if you want all Accounts that meet State__c = ‘NY’, that’s all you have to enter in the Job Rule. Here’s an example:

State__c

 

Basic Criteria for SOQL Where Clauses
Checkboxes

Select [fields] FROM [object] WHERE custom_checkbox__c = TRUE

Dates

Select [fields] FROM [object] WHERE custom_date__c > 2015-02-01

Select [fields] FROM [object] WHERE createddate=THIS_YEAR

Note: THIS_YEAR is called a SOQL Date Literal. A full list of date formats and literals can be found here.

Numbers/Currency

Select [fields] FROM [object] WHERE custom_number__c > 0

Note: A full list of comparison operators can be found here.

 

Logic for Multiple Criteria (AND/OR)

Select [fields] FROM [object] WHERE Criteria1 AND Criteria2

Select [fields] FROM [object] WHERE Criteria1 OR Criteria2

Select [fields] FROM [object] WHERE (Criteria1 AND Criteria2) OR Criteria3

Relationship Queries

Relationship queries allow you to look through object relationships to criteria on the parent record. For example, if you want to query Opportunities where the Account is part of a certain region, you can use a Relationship Query like this:

Select [fields] FROM [opportunity] WHERE Account.Region__c=’Northeast’

Relationship queries work with both standard relationships, like Opportunity to Account, and with Custom relationships like custom lookups or Master Detail relationships.  Standard relationships are typically denoted by a period, like these:

Select [fields] FROM [opportunity] WHERE Account.Region__c=’Northeast’

Select [fields] FROM [opportunity] WHERE Owner.IsActive=TRUE

Standard relationships are denoted by (__r). Here is an example:

Select [fields] FROM [object] WHERE custom_lookup__r.Amount__c > 0

Subqueries

Say you want a set of Accounts that have Closed/Won Opportunities.  You could set up an Account job rule and write a SOQL query that uses an Opportunity Subquery to return the Accounts you’d like:

Select id, name FROM Account WHERE id IN (select AccountId from Opportunity where Stage = ‘Closed/Won’)

This would essentially grab the IDs of the Accounts which have Opportunities that are closed/won.

Limiting Results of a Query

Some queries might produce more records than can fit in a Sandbox.  In order to limit the results of a query, you can use the LIMIT function. Here’s an example of restricting results to 100 records:

Select [fields] FROM [opportunity] WHERE RecordTypeId=’012U00000001dpx’ LIMIT 100

One great tips is to use Salesforce Workbench to create and test your Queries. Here are some additional resources to keep learning:

Relationship Queries

SOQL WHERE Clause Examples

0 Comments

Leave A Comment

Leave a Reply