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 Relationship Queries. 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 SOQL 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:
