When to use custom SOQL


When you are connecting to Salesforce, you have two options for adding data. You can either select a single object or write a custom SOQL query. In some cases, it might be more appropriate to use SOQL instead of selecting a single object. This tip will discuss and demonstrate the benefits of using custom SOQL. For more information on establishing a Salesforce connection, see Connecting to Salesforce .

The benefits of custom SOQL

There are two primary benefits of using SOQL over selecting a single object:

Limiting imported data

When you connect to a single object, all of the object's full history and custom fields are imported. This can cause your dataset to become very large and exceed the capacity of the Déjà Vu cache. A large dataset will impact performance and processing speed when connecting directly to Salesforce.

Custom SOQL enables you to specify exactly what fields you want to include in your dataset by adding a limiting clause for a particular status, type, or date range. The example below demonstrates how you can use SOQL to import data only from a selected date range.

Select ActivityDate, event_stauts__c, subject from event where activitydate > 2016-01-01

With the custom SOQL, only fields after January first, 2016 will be imported.

Including field names

When you import a single object, the connection will only include the ID for look-up fields such as the account name on an opportunity or the owner name on a lead or contact. With custom SOQL, you can access the name of the look-up field, instead of a string of numbers and letters. This makes it easier to track how your team is performing by being able to identify each field.

The example below uses SOQL to access the name for the owner field.

Select ActivityDate, owner.name, event_status__c, subject from event where activitydate>2016-01-01

The custom SOQL uses object. prefix to access the field name. The next section will discuss how you can use object. prefix to import your field names.

Using object. prefix to import field names

You can use object. prefix to represent your look-up fields and import field names. Object. prefix can only access fields on the object record. For example, you can't access account.industry from the opportunity of a field if it isn't visible on the record, but you can pull more detail than when using the simple connection. This cuts down the need to use the Query Blender to access these fields.

When writing your SOQL query, you will need to use the field name as the object, not the label. If you don't know your field names, you can use the View Fields option in Salesforce. View fields will show you results resembling the image below:

Note: Any custom fields you include need to be suffixed with __c as shown in the event_status__c in the example above.

For more information on writing SOQL query, see SOQL syntax on Salesforce's website.

Have more questions? Submit a request


Please sign in to leave a comment.
Powered by Zendesk