Skip to content
+1-888-319-3663

EONE BLOG

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  Salesforce Workbench .

1.Log into Workbench and go to queries > SOQL query

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

 

SQL vs SOQL

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
INNER JOIN Contact
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

Leave a Comment





RECENT POSTS


Happy Thanksgiving from eOne! - US Office Closed November 25 & 26
Popdock: What is your Locale?
Q4 2021 Partner All Hands Call - Recording Now Available
An Announcement regarding SmartList Builder and SmartView from eOne Solutions
Join us for the Q4 2021 Partner All Hands Call!

POPULAR POSTS


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

CATEGORIES

TAGS

Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships partners Popdock release Reporting SalesForce SalesForce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.