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 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’

 

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


2019 Summer Webinar Schedule
SmartConnect Bootcamp in Raleigh, NC – Don’t Miss It!
Tech Tuesday: Issues to be aware of when creating maps that use text ODBC drivers
Employee Spotlight: Devon Binstock, Support Intern
Q2 2019 Partner Call Recording

POPULAR POSTS


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

CATEGORIES

ARCHIVES

open all | close all

TAGS

CRM Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News eOne Spotlight ERP Events Excel Excel Report Builder Extender Extender Enterprise Flexicoder GP GPUG integration integration manager Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Office Relationships partners Popdock release Reporting SalesForce SalesForce.com scripting SmartConnect SmartConnect.com 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.