Tech Tuesday: Understanding SalesForce queries in SmartConnect

This week we are going to go over something that can be very challenging for someone that doesn’t come from a SalesForce background. We will go into detail about how to build a SOQL query using the developer console in SalesForce and how to join multiple SalesForce Objects.


Where to build a SalesForce query

SmartConnect on-premise has a query builder for a SalesForce data source, but it can struggle to deal with complex situations. SmartConnect.com does not have a query builder. It is required that you write the query and paste it into the query window.


SalesForce has a couple of tools for building queries. The most user-friendly option is to use the Developer Console in SalesForce.

1.Select Developer Console from the top right corner of SalesForce

2. Select Query Editor in the bottom section of the Developer Console Window

3. Once you have entered a query, click ‘Execute’



SalesForce uses a custom query language called Salesforce Object Query Language (SOQL). There are a few key differences between SOQL and SQL.

  • No SELECT *
  • No Views
  • No Joins
  • SOQL is read-only
  • All relationships are pre-defined by object creation

I want to go in depth a little more about relationships within SOQL. This is the area that strongly impacts the way SmartConnect queries are written. Rather than defining a join as we would with a SQL query, when a second Object is added to our query SalesForce links it to the current Object based on the defined relationship. Here is a SalesForce help article about relationships.

While a normal SQL query would look like this:

SELECT Account.Name, Contact.FirstName, Contact.LastName
FROM Account
ON Account.Id = Contact.AccountId


A SOQL query of the same data using a Parent-to-Child query will not contain a join.

SELECT Account.Name, (
SELECT Contact.FirstName, Contact.LastName
FROM Account.Contacts)
FROM Account


This query can be flipped, and the query built from a Child-to-Parent perspective.

SELECT FirstName, LastName, Account.Name
FROM Contact


SalesForce queries in SmartConnect can only contain one Parent-to-Child Object, and one Child-To-Parent Object. What this means is we can’t query Grandparent or Grandchild record. An example would be selecting Order as the main object. I can pull in the Order Items, but I will not be able to retrieve the Products used in the Order Items.

Here is a SalesForce help article about SOQL Querying.


Retrieving Grandchild Data

At this point you’re saying, “but Ethan I need the grandchild data!” Don’t worry, SmartConnect can retrieve that data for you. If we can’t pull all the data we need in a single web request, we can instead use a multi-data source. A multi-data source creates a virtual staging table for us to combine data from multiple web calls. To ease the load on your SmartConnect server it is still important to pull the necessary data in as few calls as possible. Each source used in a multi-data source will pull in more data and increase the risk of running out of memory.


Use the SalesForce Id for the destination mapping

Depending on the destination and which program contains the master data, there is one more option. We can save the SalesForce Id for a related object so it can be used in place of the data we need.

Here is an example:

SELECT Order.AccountId, Quantity, Product2.Name, Order.TotalAmount
FROM OrderItem


We are pulling data from OrderItem which means we can pull data from the header, but not the lookup fields on Order such as Account, Contract, Opportunity. Instead we take the Id for these fields which is stored in the Order Object for reference.

For this example, we will say my destination is D365 Business Central. If I previously ran a map to set the External Id on all the Customer records in Business Central to the SalesForce Id, then I can now use a lookup field in my destination to retrieve the field I need to complete my mapping based on the External Id.


If you need help with connecting SmartConnect to SalesForce, email support@eonesolutions.com

1 Comment

  1. Mirketa on October 4, 2019 at 3:29 am

    thanks for sharing such a valuable information

Leave a Comment


eOne is now compatible with Microsoft Dynamics GP October 2019 Release
Change in eOne Product Registration Process
October 2019 Releases of Extender, SmartList Builder, SmartView, SmartPost, and Flexicoder are Available Today
Tech Tuesday: Creating Calculations in SmartList / SmartView
SmartConnect Office Hours for Friday, October 4th, 2019


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
New Releases of Extender and SmartList Builder
2017 SmartConnect Integration Bootcamps



open all | close all


Business Central CRM Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News eOne Spotlight Events Excel Excel Report Builder Extender Extender Enterprise Flexicoder GP GPUG integration Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Office Relationships partners Popdock release Reporting SalesForce SalesForce.com scripting SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Office Hours SmartList SmartList Builder SmartList Designer SmartView SQL Support Tech Tuesday Templates training Zendesk

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.