+ Start a Discussion
Blake TanonBlake Tanon 

AggregateResult Query - Bulkify?

I've got a trigger that currently only works on single updates - but really needs to be written to work in bulk.  The trigger is on a custom object called Transaction__c which houses our sales records, in regards to this case it's a child to Contacts.  The purpose of it is to get the number of Calls(Tasks), Successful Calls(Tasks), Meetings(Events) and Kits(Order__c) that where placed within a time frame related to the contact that is linked to the transaction record.

 

The issue is that the aggregate map queries are being run and storied for the first record in the batch, so the next 199 (or whatever the bacth size is -1) are receiving null values.  

 

Any points on the direction I need to take?

 

trigger TransactionScoring on Transaction__c(before update) {

// Build the maps for the Count and ActivityDate First
    
    Set<Date> PStart = new Set<Date>();
    for(Transaction__c t:trigger.new)
        PStart.add(t.Trade_Period_Start__c);
        
    Set<Date> PEnd = new Set<Date>();
    for(Transaction__c t:trigger.new)
        PEnd.add(t.Trade_Date__c);
      
    Set<Id> cid = new Set<Id>();
    for(Transaction__c t:trigger.new)
        cid.add(t.rep__c);
    

    Map<String,Integer> callCountMap = new Map<String,Integer>();
        for(AggregateResult r:[select COUNT(id), WhoId 
                               from Task 
                               where Whoid in:cid AND (Type = 'Cold Call' OR Type = 'Inbound Call' OR Type = 'Outbound Call') 
                               AND Status = 'Completed' AND isDeleted = False AND 
                               (Date_Completed__c >: PStart AND Date_Completed__c <=: PEnd) AND xOwnerWS__c = true 
                               GROUP BY WhoId 
                               LIMIT 100 ALL ROWS])
            callCountMap.put(String.valueof(r.get('WhoId')),Integer.valueof(r.get('expr0')));
    
    Map<String,Integer> scallCountMap = new Map<String,Integer>();
        for(AggregateResult r:[select COUNT(id), WhoId 
                               from Task 
                               where Whoid in :cid AND (Type = 'Cold Call' OR Type = 'Inbound Call' OR Type = 'Outbound Call')
                               AND Status = 'Completed' AND isDeleted = False AND Outcome__c = 'Success' AND 
                               (Date_Completed__c >: PStart AND Date_Completed__c <=: PEnd) AND xOwnerWS__c = true 
                               GROUP BY WhoId 
                               LIMIT 50 ALL ROWS])
            scallCountMap.put(String.valueof(r.get('WhoId')),Integer.valueof(r.get('expr0')));
    
    
    Map<String,Integer> meetingCountMap = new Map<String,Integer>();
        for(AggregateResult r:[select COUNT(id), WhoId 
                               from Event 
                               where Whoid in :cid AND Outcome__c = 'Meeting Occurred' AND isDeleted = False 
                               AND (Date_Completed__c >=: PStart AND Date_Completed__c <: PEnd) 
                               GROUP BY WhoId 
                               LIMIT 10 ALL ROWS])
            meetingCountMap.put(String.valueof(r.get('WhoId')),Integer.valueof(r.get('expr0')));
        
    Map<String,Integer> kitCountMap = new Map<String,Integer>();
        for(AggregateResult r:[select sum(number_of_kits__c), contact__c 
                               from Order__c 
                               where contact__c in :cid AND Status__c = 'Shipped' 
                               AND (Order_Date__c >=: PStart AND Order_Date__c <: PEnd) 
                               GROUP BY contact__c LIMIT 10 ALL ROWS])
            kitCountMap.put(String.valueof(r.get('contact__c')),Integer.valueof(r.get('expr0')));


    // Iterate over the Trigger.new List and check if the Id exists in  the Map and assign value from Map       
  for(Transaction__c c:Trigger.new)  {  
    c.Calls__c = 0;
    c.Successful_Calls__c = 0;
    c.Meetings__c = 0;
    c.kits__c = 0;
    //c.contact__c = cid;

    if(callCountMap.get(c.rep__c) != null)
        c.Calls__c = callCountMap.get(c.rep__c);
    
    if(scallCountMap.get(c.rep__c) != null)
        c.Successful_Calls__c = scallCountMap.get(c.rep__c);
    
    if(meetingCountMap.get(c.rep__c) != null)
        c.Meetings__c = meetingCountMap.get(c.rep__c);
        
    if(kitCountMap.get(c.rep__c) != null)
        c.Kits__c = kitCountMap.get(c.rep__c);
    
    }
}