4

In my data structure I have a several custom objects that form a relationship to the Standard Salesforce Account Object.

I have three custom objects:

  • L_Account
  • L_Contact
  • L_Enrollment

L_Enrollment -> L_Contact -> L_Account -> Standard SF Account

I want to get a Count per Standard SF Account for how many L_Enrollment exist that match a creation date (on the L_Enrollment level)

Can this be done? I am familiar with SOQL Relationships but need a great amount of help on the syntax.

4

You could do it with aggregate queries. Making some assumptions about the API Names of your relationship fields:

Map<Id, Integer> accountIdToEnrollmentCount = new Map<Id, Integer>();
for (AggregateResult aggregate : [
    SELECT count(Id) records, L_Contact__r.L_Account__r.Account__c accountId
    FROM L_Enrollment__c WHERE CreatedDate = :2016-10-21
    GROUP BY L_Contact__r.L_Account__r.Account__c
])
    accountIdToEnrollmentCount.put(
        (Id)aggregate.get('accountId'),
        (Integer)aggregate.get('records')
    );

What's going on here?

The map itself is fairly straightforward. Let's break down the query:

  • You can GROUP BY a specific field (even many relationships distant).
    • When you perform queries using this clause, they return List<AggregateResult>.
  • When you use this clause, each field must be grouped or aggregated.
    • With the relationship to Account you group it by including it in the GROUP BY clause.
    • With Id you count the number of populated values, which gives you the total number of enrollments you are looking for.
  • When you use this clause, you can alias any field in your SELECT clause, which can make your code a lot more concise and easier to wrap your head around!
    • Here we can now just call aggregate.get('accountId') even though the relationship is really L_Contact__r.L_Account__r.Account__c.
    • We can also get the count in an easy to understand way. Without aliasing, you would have to use aggregate.get('expr0'), which is ugly and doesn't scale very well.
  • Note that when working with AggregateResult, you have to use the SObject.get method, which returns an un-cast Object. That's why you have to cast the results ((Id), (Integer)).
|improve this answer|||||
  • Thanks Adrian, the logic makes sense. I swapped in my actual field names into the query successfully. To display the Map, will I need to create a VisualForce Page? I am less experienced with this, could you point me to some resources on building the remaining components to get this to display in a dashboard? – Jesse Miller Oct 21 '16 at 23:11
  • You will need Visualforce, yes. I'd recommend taking a look at this Visualforce Cheatsheet, playing around with it a bit, and asking more questions on here if you get stuck. :) – Adrian Larson Oct 21 '16 at 23:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.