Salesforce Connect allows you to get a 360-degree view of your customer within Salesforce, even if your data exists in another system. In this blog we’ll show you the queries that Salesforce Connect generates as you interact with the different capabilities of the platform. While you don’t need to know about these “black box” queries when using the standard Salesforce Connect integrations, you’ll need to know what’s going on behind the curtain if you create your own custom integration. And so this blog post is an essential guide for developing and troubleshooting a custom integration with Salesforce Connect.

Let’s get some basics out of the way – What is Salesforce Connect? Salesforce Connect provides seamless integration of data across system boundaries by letting your users view, search, and modify data that’s stored outside your Salesforce organization, without actually copying the data into Salesforce. Salesforce Connect is a point-and-click solution to your other databases, and it makes earlier integration scenarios seem archaic.

The adapter that does the actual integration can be either built on OData v2.0, v4.0, Custom Apex or the Salesforce adapter. Data from external systems are available as External Objects in Salesforce that are similar to Standard and Custom Objects so things like Tabs, Page Layouts, Custom Fields exist. But you can’t create formula fields, validation rules, and workflows. Let’s not get too far into the details, because all of this is documented in the Salesforce online help.

In this blog, we will cover the queries in the following areas where Salesforce Connect is used most commonly,

Prerequisites

To follow along step-by-step with this blog post you’ll need the Northwind data source, which is what you’ll use for Salesforce Connect.

  1. First set up Northwind OData 4.0 as your external data source.
  2. Now make sure you’ve set up your External Datasource configuration.
  3. Create a Tab to view the external data.
  4. Enable Debug Logs to capture these queries.

Searching External Data

As part of the External Datasource configuration, you can specify whether the external objects can be searched from the Salesforce Global Search using the option ‘Include in Salesforce Searches’. In global search, the actual search is performed against all the search-enabled external objects in addition to the standard and custom objects. From the search results you can click on a specific external object on the left hand side to do an object specific search.

For the purpose of this blog, say you intended to search for a Customer ID (external object ‘Customers’) “BERGS” from global search.

What’s Happening Behind the Curtain?

When you perform the global search, Salesforce Connect sends the following query  (for brevity, only a few columns are shown):

  • $top: The value of $top specifies to retrieve only the first “N” items which is 6 in this example. ‘6’ because, five record is what is normally displayed in Salesforce and knowing whether a sixth record exists determines whether to display a ‘Show more’ link.
  • $filter: Specifies the predicate expression to return results matching the condition. In this case, the “OR” expression across all text fields. Salesforce Connect searches only text fields.
  • $select : The value of $select specifies that a response from the service should return a subset of the Properties. In the case of Global Search all text type properties are selected. This can be modified in scenarios such as List views for example where we chose to show only a few of the properties we want. More on that in the following sections.

Using Free-Text Search Expressions

The previous query is what goes out if the option to use ‘Use Free-Text Search Expressions’ is not selected in the external data source setup screen. In the actual query, all the text fields in the External Object ‘Customers’ are sent in the ‘filter’ and the ‘select’ url parameters. Due to this , the backend service needs to extract the filter param from the URL and implement all the search values passed in it.

To pass only the search term, enable  ‘Use Free-Text Search Expressions’ in the External Data configuration. The above query would then look like this

List Views

In the Customers tab, let’s say you want to create a list view to filter Customers based out of France,

Salesforce Connect sends the following query.

  • $filter: Here the filter we applied is on Country(France) so, you can see the filter parameter reflects the same.
  • $select: The fields in this param are the fields you selected when you defined the list view.

Note: Default order by param is usually the first column in the list view.

Sorting

Let’s see what happens to the query when we click on one of the columns to sort the data, say ‘City’ column.

  • $orderby: Since we had clicked on City column to sort, the orderby parameter is passed with the value as City to the service.

Pagination

When on the list view, if we click on the next link to paginate, Salesforce Connect adjusts the top and skip parameter to achieve the same. Default number of rows in this listview is 10.

  • $skip: Since we are in the first page and by default are viewing 10 records per page, system directs the service to skip the first 10 records when querying. This is similar to the OFFSET feature in SOQL.
  • $top: Represents the number of records to retrieve. Since 10 is the number of records per page, Salesforce Connect asks for 11 records to be retrieved. This is similar to the LIMIT feature in SOQL.

Record View

Configure the page layout of an external object similar to how you would do for a standard or custom object. Keep in mind though these limitations:

  • You can’t make a field required/read-only.
  • You get the same look and feel for all the records in the external object; you can’t have record types.

OK, so let’s say you’ve searched for ‘LACOR’ (we know there is a customer with that external id) in the global search and clicked on the External Id under Customers.

  • $filter: Salesforce Connect uses the primary key defined in the metadata of the service to make the call on the Customer service which is CustomerID in this case.
  • $select: It will have the fields specified on the external object. So irrespective of what fields you have on the page layout, the select parameter will have all the fields of the external object.

Salesforce creates the external id based on the primary key definitions in the metadata. If the primary key is a composite primary key (a combination of more than one field), then Salesforce uses “-” as a delimiter between the values of the field while constructing the external id.

In Northwind – Order_Detail object has OrderID and ProductID as a composite primary key. This is seen in the metadata as shown below,

If by coincidence one of the keys also contains the “-” text then Salesforce escapes it to avoid ambiguity with its own delimiter. For example, if the OrderID is ABC-123 and ProductID is 11 then the External ID would be “ABC-123-11”. Salesforce Connect would automatically escape all dash delimiters in the data.

Another interesting observation was while viewing the Order_Detail record, since the primary key was composite, the filter parameter had an “&” operation between the fields of the primary key. So say you were clicking on the above record from global search, you’ll see the following query go out to service,

Using external relationships, say you have linked Customer and Order objects through CustomerID field. Modify the Customer page layout to show Orders as a related list. When we navigate to a Customer record, the related list for Order will display the line items corresponding to the displayed Customer. The Salesforce Connect query is as follows.

  • $filter: Passes the field that was set up as an external field lookup in Order which is CustomerID in our example.
  • $top: Specifies the number of records to show. Normally only five records are shown in a related list but it was observed that 51 records are being requested to be queried. Product team is looking into it as it should have been just 6 (5 to show and the additional sixth record to determine whether to show the more link or not).

Clicking On More Records In Related Lists

When the user clicks on show more records in the related list, Salesforce Connect requests 5 more records on the related external object. So the above query now becomes

  • $top: Clicking on more records per related lists brings up another five records so the top parameter is incremented by 5. So from 51 in the initial query, it is now 56. Another click on the more link would request 61 records and so on.

Summary

Salesforce Connect is one the biggest steps taken in the areas of system integration. Salesforce has brought in the same principles of point & click configurations in building business apps to the integration world. As a partner and a client, you will stand to benefit in architecting your integration using Salesforce Connect.

Additional Resources

About the Authors

As a Technical Architect at Salesforce, Deepu Chacko focuses on architecting Service Cloud and Community Cloud projects for customers in India. Sriram Seshadri, a Sr Consultant specializes in Analytics Cloud and Service Cloud. They are part of the Success Services team of Salesforce in India, an elite group of consultants focussed purely on designing solutions that help customers maximize their investment in Salesforce.

Get the latest Salesforce Developer blog posts and podcast episodes via Slack or RSS.

Add to Slack Subscribe to RSS