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