+ Start a Discussion
jonathanbernddf20131.387825590468351E12jonathanbernddf20131.387825590468351E12 

Help with apex for roll up summary count based on different picklist values (trigger)

Hi
As a newbie, I wonder if you could help me with this issue:
I'm trying to write a trigger that populates a number of fields on the contact object based on different picklist values in the same child object. I need a trigger as there are not enough roll-up summary fields.  There will be thousands in the counts of each value.
Here is what I am attempting to do:

SELECT Id, Picklist_value__c FROM ChildObject__c WHERE Picklist_Value__c ='value a'
Count the total of Child records put the COUNT number into a 'Count 'value a' Roll-up' field on the Contact Object.
SELECT Id, Picklist_value__c FROM ChildObject__c WHERE Picklist_Value__c ='value b'
Count the total of Child records put the COUNT number into a 'Count 'value b' Roll-up' field on the Contact Object.
SELECT Id, Picklist_value__c FROM ChildObject__c WHERE Picklist_Value__c ='value c'
Count the total of Child records put the COUNT number into a 'Count 'value c' Roll-up' field on the Contact Object.
SELECT Id, Picklist_value__c FROM ChildObject__c WHERE Picklist_Value__c ='value d'
Count the total of Child records put the COUNT number into a 'Count 'value d' Roll-up' field on the Contact Object.

Thanks in advance for any help
Best Answer chosen by jonathanbernddf20131.387825590468351E12
Cory CowgillCory Cowgill
If you are out of Roll-Up Summary Fields, and you are doing extensive rollups in a trigger, you should look at using Aggregate SOQL to perform this.

So this would really be like this one query in Aggregate SOQL:

List<AggregateResult> results = [Select Count(Id), Picklist_value__c  from ChildObject__c GROUP BY PIcklist_Value__c];

for (AggregateResult ar : results)  {
    System.debug('Picklist Value: ' + ar.get('Picklist_value__c'));
    System.debug('Count: ' + ar.get('expr0'));
}

http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_agg_fns.htm

All Answers

Cory CowgillCory Cowgill
If you are out of Roll-Up Summary Fields, and you are doing extensive rollups in a trigger, you should look at using Aggregate SOQL to perform this.

So this would really be like this one query in Aggregate SOQL:

List<AggregateResult> results = [Select Count(Id), Picklist_value__c  from ChildObject__c GROUP BY PIcklist_Value__c];

for (AggregateResult ar : results)  {
    System.debug('Picklist Value: ' + ar.get('Picklist_value__c'));
    System.debug('Count: ' + ar.get('expr0'));
}

http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_agg_fns.htm
This was selected as the best answer
jonathanbernddf20131.387825590468351E12jonathanbernddf20131.387825590468351E12
Thanks Cory
jonathanbernddf20131.387825590468351E12jonathanbernddf20131.387825590468351E12
Really enjoyed your developers session at DF14 by the way.