Sweet & Simple: Using SOQL Relationship Queries with Salesforce.com Connector

motif

The best things in life are often sweet and simple. However, “S & S” is an easy concept to understand and appreciate but often hard to implement. For example, a sweet and simple way to attract traffic to our blog would be to show women in bikinis playing with cats. In reality that is rather hard to pull off for a technical site. There simply is no budget to publish anything like “API Illustrated, Swimsuit Edition” or “ESBN, the Body Issue”. Instead, this article will focus on sweet and simple features in our products that can make life easier for integration developers.

With 100,000+ customers, Salesforce.com is one of the most popular integration endpoints for ESB implementations.  There are a couple of commonly asked questions when it comes to Salesforce.com: how do you reduce the number of API calls since there are daily limits per instance, and how do you retrieve all the related records in one query?  The SOQL Relationship Queries help accomplish both goals, as a developer can make just one API call against different SObject types that are also related.

Here we will illustrate how the Anypoint Salesforce.com Connector fully supports the .  We will use the archetypal Header-Line hierarchy data structure, for example, Opportunity and Opportunity Line Items.  This represents the classic use case in which an Order (i.e. opportunity) can contain multiple products (i.e. opportunity line items).  To use a relationship query, simply put the nested statement into the usual Query Text box: 

The relationship query used in this example is show below.  Note that the where clause here is applicable to the opportunity sobject, not opportunity line items:
SELECT Id, Name, Account.Name, (SELECT Quantity, UnitPrice, TotalPrice, PricebookEntry.Name, PricebookEntry.Product2.Family FROM OpportunityLineItems) FROM Opportunity WHERE LastModifiedDate >= #[flowVars[‘watermark’]]

Then to parse the results returned by relationship queries, note that the payload data will now have hierarchy, more than one level of element mapping inside the Data Mapper will be needed. For the example above, within the same Data Mapper transformer, there are two levels.  First, there is one level for the opportunity itself: 
Then another level for all the related opportunity line items: 

Here, JSON output used here strictly for demonstrative purpose. Any output types supported by the Data Mapper can be used. And that is everything you need to perform relationship SOQL queries. If you don’t find the capability to handle complex data structure with one simple query and transformation sweet, well then, there is always the Internet:


We'd love to hear your opinion on this post


6 Responses to “Sweet & Simple: Using SOQL Relationship Queries with Salesforce.com Connector”

  1. This doesn’t seem to be working for me. I don’t see the related object in the datamapper. Can you please upload a quick code sample?

    Thanks!

    Agree(0)Disagree(0)Comment
    • Forgot to mention, you cannot use DataSense for related query. You must construct the query manually (but you can use SFDC workbench to help you with that.)

      Agree(0)Disagree(0)Comment
  2. Thanks, Adrian. After writing the SOQL query, I drag the datamapper control into my flow. Should the datamapper control be intelligent enough to automatically pick up the related fields from the payload? Or do I need to manually configure the datamapper?

    Agree(0)Disagree(0)Comment
    • Ah, that part I don’t remember the details. How many fields you have? If data sense not enabled, it may not be able to pick up automatically but you can always manually construct the schema in Data Mapper via the UI.

      Agree(0)Disagree(0)Comment
  3. Hi Adrian,

    Datasense is enabled in the SFDC Connector. I tried a simple query like this: “Select Id, Name, (Select Id, Name From Contacts) From Account”.

    However, the payload is automatically set to List and the only fields I see in the Datamapper are all of the Account fields.

    Do you think I need to manually construct the schema in the DataMapper so I can see the related object?

    Agree(0)Disagree(0)Comment
    • Hi EK, if you want to use Related Queries, then don’t use datasense and yes, construct the schema manually.

      Agree(0)Disagree(0)Comment