KB484542: Salesforce SOQL gateway data connector

Description

Starting in Strategy 2020 Update 2, we have added a new data import icon for the Salesforce SOQL gateway data connector. Although this functionality was available through our Web Services gateway, we have added this new icon as an easier and more direct way to access the Salesforce SOQL capabilities.

This article details how to import data from Salesforce SOQL through Strategy, as well as the use of the Salesforce Workbench tool, to test SOQL statements directly against Salesforce.

What is SOQL?

SOQL stands for Salesforce Object Query Language. It is very similar to the widely used SQL (Structured Query Language), which is used to query database. SOQL is specifically designed for Salesforce data and is to query Salesforce platform to retrieve data.
 
SOQL queries uses REST API against the specified object and returns data that matches the specified criteria.
 
SOQL can be used when the user knows which objects the required data resides in and if. the user wants to:
 

• Retrieve data from a single object or from multiple objects that are related to one another.
• Count the number of records that meet specified criteria.
• Sort results as part of the query.
• Retrieve data from number, date, or checkbox fields.

Salesforce objects are database tables that permit users to store the data specific to an organization. Salesforce objects are of two types:

• Standard Objects: Standard objects are the kind of objects that are provided by salesforce.com, such as users, contracts, reports, dashboards, etc.
• Custom Objects: Custom objects are those that are created by users. It supplies information that is unique and essential to their organization. It is the heart of any application. It provides a structure for sharing data. The custom objects and the fields in these custom objects are identified in SOQL queries by ‘__c’ (two underscores and C/c).

Workbench in Salesforce

Workbench is a web-based tool which helps administrators and developers to interact with Salesforce for Data Insert, Update, Delete and Export. In Salesforce, it is easy to extract data related to users with the Roles, Profiles and the Permission sets. However, it is not possible to extract data for the users who are assigned to Permission sets with the Data Loader. Alternatively, this requirement can be fulfilled using Workbench. The steps to use Workbench for SOQL are as listed below:

• Log in to Workbench with Production or Sandbox Salesforce credentials.
• Then to go Queries menu on top and select SOQL Query.
• The Workbench view for Salesforce is as shown below.
  

Connecting to Salesforce SOQL in Strategy

In Strategy, there are two ways of connecting/using Salesforce SOQL.

1. Through Web Services
2. Through Salesforce SOQL

Connecting through Web Services

• In Strategy Web, click on Create and select Add New Data.
• In the Data Import dialog, search for Web Services and select it.
• The Import from Tables dialog opens up.
• click on the ‘+’ sign to add a new data source.
• In the Data Source dialog, create a new data source connection.
  
• From the Script Mode drop-down, select Salesforce SOQL.
• In the User field, enter the email address for the Salesforce account.
• In the Password field, enter the concatenation of password and token for the salesforce account.
• Enter a data source name and click Save
  

• .
• Select the created data source, type a SOQL query, and click on Execute SQL to run the query.
  

Connecting through Salesforce SOQL

• In Strategy Web, click on Create and select Add New Data.
• In the Data Import dialog, search for Web Services and select it.
• The Import from Tables dialog opens up.
• click on the ‘+’ sign to add a new data source.
• In the Data Source dialog, create a new data source connection.
  
• From the Database drop-down, select Salesforce SOQL.
• In the User field, enter the email address for the Salesforce account.
• In the Password field, enter the concatenation of password and token for the salesforce account.
• Enter a data source name and click Save
  

• .
• Select the created data source, type a SOQL query, and click on Execute SQL to run the query.
  

Similar steps can be applied to use Salesforce SOQL through Workstation and Desktop applications.

Sample SOQL Queries

• SELECT Salutations__c, First_Name__c, Last_Name__c FROM Tabular_Report__c; --> Custom Object
• SELECT Name, AccountNumber FROM Account; --> Standard Object
• SELECT Product_Name__c FROM Currency_Checkbox_String_Date_Email WHERE List_Price__c<1000 LIMIT 5;