Accessing SOQL Aggregated Data in Cast Iron orchestration

Posted by jack on June 23, 2015

Salesforce SOQL aggregate queries allow you to summarise across objects very simply using syntax like:

Select COUNT(Id) contactCount, MAX(lastModifiedDate) lastModifiedDateContact,

Account.BillingCity AccountCity

FROM Contact GROUP BY Account.BillingCity  HAVING COUNT(Id) > 5

In Cast Iron Studio v7.0.0.2 using this query in a Salesforce Query Objects activity will return results as an AggregateResult type object – but will not let this be mapped to anything else……

Investigating this issue I discovered two blocks in this type of query

  1. Using aliases in the SOQL query
  2. Using fields from multiple Salesforce objects

Removing the aliases is simple:-

Select COUNT(Id) ,MAX(lastModifiedDate) , Account.BillingCity

FROM Contact GROUP BY Account.BillingCity HAVING COUNT(Id) > 5

Now the output has aggregated data elements named expr0, expr1, expr2…. and these will map – but Account.BillingCity will not. To handle this, I put a calculated contact field Account_City__c = Account.BillingCity in the Salesforce Org, then revise the query to be

Select COUNT(Id) ,MAX(lastModifiedDate) , MAX(Account_City__c)

FROM Contact GROUP BY AccountId HAVING COUNT(Id) > 5

Now all the required elements from the aggregate query map fine…..I can use a single tier XSD with these entries (mapped values in brackets) to collect the output.

  • contactCount (expr0)
  • lastContactModDate (expr1)
  • accountCity (expr2)

As a general point, Cast Iron does not handle multi-level xml schemas well, so whenever possible I decompose to a flat structure and then all the XPath filtering and other built-in features of Cast Iron work as expected.

Categories: Cast Iron

Comments are closed.