function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Mollie Bodensteiner 6Mollie Bodensteiner 6 

Aggregate Query Has Too Many Rows for Direct assignment, Use For Loop | Counting Tasks

Hello, 

I am stuggling with this trigger to count the number of open tasks on a lead. I am receiving the following error: 

UpdateContactClosedTasks: execution of AfterDelete caused by: System.QueryException: Aggregate query has too many rows for direct assignment, use FOR loop: External entry point

Here is the trigger: 

trigger UpdateLeadOpenTasks on Task (after insert, after undelete,
after update, after delete) {

// Declare the variables

public set<Id> LeadIDs = new Set<Id>();
public list<Lead> LeadsToUpdate = new List<Lead>();

// Build the list of Leads and Contacts to update
if(Trigger.isInsert || Trigger.isUnDelete || Trigger.isUpdate){
    for(Task t: Trigger.new){
     if(t.WhoId<>null){
    if(string.valueOf(t.WhoId).startsWith('00Q'))
    LeadIDs.add(t.WhoId);
   }
  } 
}

if(Trigger.isDelete || Trigger.isUpdate){
    for(Task t: Trigger.old){
     if(t.WhoId<>null){
    if(string.valueOf(t.WhoId).startsWith('00Q'))
    LeadIDs.add(t.WhoId);
   }
  } 
}

// Update the Leads

if(LeadIDs.size()>0){
for(Lead l: [Select l.Id, l.Open_Activities__c,
(Select Id From Tasks where IsClosed = False)
From Lead l where Id in :LeadIDs])
LeadsToUpdate.add(new Lead(Id=l.Id, Open_Activities__c = l.Tasks.size()));
update LeadsToUpdate;
}
if(LeadstoUpdate != null && !LeadsToUpdate.isEmpty())
Database.update(LeadsToUpdate);
}

Any help would be greatly appreciated. 
Best Answer chosen by Mollie Bodensteiner 6
Shivdeep KumarShivdeep Kumar
Hi Mollie,
Sorry for late response.
That was the simple logic trigger to count the child records.
As per your requirement please update the your trigger with below trigger. 
Trigger updateLeadOpenTask on Task(after insert, after update, after delete, after undelete) {
   List<Lead> leads = new List<Lead>();   
   Set<Id> leadIds = new Set<Id>();
   
   if(Trigger.isDelete) {
     for(Task t :Trigger.Old) {  
         If (t.whoid <> null || t.whoid <> ''){
             If (string.valueof(t.whoid).startswith('00Q')){
                leadIds.add(t.Whoid);
                system.debug('### leadid Del'+leadIds);    
             }      
        }
     }
   }else if(Trigger.isUpdate) {
     for(Task t :Trigger.New) {      
        If (t.whoid <> null || t.whoid <> ''){
             If (string.valueof(t.whoid).startswith('00Q') ){
                leadIds.add(t.whoid); 
                system.debug('### leadid new updat'+leadIds);   
             }      
        }     
     }
     for(Task t : Trigger.Old) {      
         If (t.whoid <> null || t.whoid <> ''){
            If (string.valueof(t.whoid).startswith('00Q')){
                leadIds.add(t.whoid); 
                system.debug('### leadid old upd '+leadIds);   
             }      
         }  
     }      
   }else {
     for(Task t : Trigger.New) {      
        If (t.whoid <> null || t.whoid <> ''){
             If (string.valueof(t.whoid).startswith('00Q')){
                leadIds.add(t.whoid);
                system.debug('### leadid else '+leadIds);    
             }      
        }    
     }
   }
   system.debug('### leadid '+leadIds);
   Integer count ;
   AggregateResult[] groupedResults = [SELECT COUNT(Id), whoId FROM task where whoid IN :leadIds AND Status <> 'Completed' GROUP BY whoid];
   system.debug('### ar '+groupedResults );
   if(groupedResults.size() > 0){
       for(AggregateResult ar:groupedResults) {     
         Id custid = (ID)ar.get('whoid');     
         count = (INTEGER)ar.get('expr0');     
         Lead l = new Lead(Id=custid);     
         l.open_activities__c= count;     
         leads.add(l); 
     
     }     
   }else{
       for(AggregateResult ar: [SELECT COUNT(Id), whoId FROM task where whoid IN :leadIds GROUP BY whoid]) {     
         Id custid = (ID)ar.get('whoid');     
         count = (INTEGER)ar.get('expr0');     
         Lead l = new Lead(Id=custid);     
         l.open_activities__c= 0;     
         leads.add(l); 
     
     }  
   }
   system.debug('### leads '+leads);   
If(leads.size()>0)
   update leads;
}

Please let me know if this help and choose the best answer.

Thanks
Shivdeep

All Answers

Shivdeep KumarShivdeep Kumar
Hi Mollie,
please try the below code... 
Trigger updateLeadOpenTask on Task(after insert, after update, after delete, after undelete) {
   List<Lead> leads = new List<Lead>();   
   Set<Id> leadIds = new Set<Id>();
   
   if(Trigger.isDelete) {
     for(Task t :Trigger.Old) {  
If (t.whoid <> null || t.whoid <> ''){
If (string.valueof(t.whoid).startwith('OOQ')){
        leadIds.add(t.Whoid);    
     }      
}
}
   }else if(Trigger.isUpdate) {
     for(Task t :Trigger.New) {      
        If (t.whoid <> null || t.whoid <> ''){
If (string.valueof(t.whoid).startwith('OOQ')){
        leadIds.add(t.whoid);    
     }      
}     
     }
     for(Task t : Trigger.Old) {      
   If (t.whoid <> null || t.whoid <> ''){
If (string.valueof(t.whoid).startwith('OOQ')){
        leadIds.add(t.whoid);    
     }      
}  
     }      
   }else {
     for(Task t : Trigger.New) {      
        If (t.whoid <> null || t.whoid <> ''){
If (string.valueof(t.whoid).startwith('OOQ')){
        leadIds.add(t.whoid);    
     }      
}    
     }
   }
   
   AggregateResult[] groupedResults = [SELECT COUNT(Id), whoId FROM task where whoid IN :leadsIds GROUP BY whoid ];
   
   for(AggregateResult ar:groupedResults) {     
     Id custid = (ID)ar.get('whoid');     
     Integer count = (INTEGER)ar.get('expr0');     
     Lead l = new Lead(Id=custid);     
     l.open_activities__c= count;     
     leads.add(l);      
   }   
If(leads.size()>0)
   update leads;
}
Please let me know, if this help you..!!!
 
Mollie Bodensteiner 6Mollie Bodensteiner 6
Shivdeep Yadav, 

I am getting an Error: Complie Error: unexpected token: else at line 28. Also does this take into account that the task has to be open?

Thoughts? 
Shivdeep KumarShivdeep Kumar
Hi Mollie,
Sorry for late response.
Yesterday I didn't check it, I wrote this on notepad and paste on the code section. there is some error while saving.
The error is :-
1-  replace " string.valueof(t.whoid).startwith('OOQ') " with " string.valueof(t.whoid).startswith('00Q') " on line no. 8,16,23,31.
2- on line no. 38 replace "leadsIds" with " leadIds " .

There is some points for task (activities), I think you know about that :
  •  You can not delete the open activity, you have to change the status to 'Completed' and then from activity history you can delete the activities.
if you do the same then this trigger will run fine.

please let me know if this help.

thanks
Shivdeep
Mollie Bodensteiner 6Mollie Bodensteiner 6
Thanks Shivdeep, 

It is working, except it doesn't decrease the open task count when the task is closed. I updated it to only count open tasks, but am just struggling with decreasing the value of the count once the task is closed. 

Thanks so much. 
Shivdeep KumarShivdeep Kumar
Hi Mollie,
Sorry for late response.
That was the simple logic trigger to count the child records.
As per your requirement please update the your trigger with below trigger. 
Trigger updateLeadOpenTask on Task(after insert, after update, after delete, after undelete) {
   List<Lead> leads = new List<Lead>();   
   Set<Id> leadIds = new Set<Id>();
   
   if(Trigger.isDelete) {
     for(Task t :Trigger.Old) {  
         If (t.whoid <> null || t.whoid <> ''){
             If (string.valueof(t.whoid).startswith('00Q')){
                leadIds.add(t.Whoid);
                system.debug('### leadid Del'+leadIds);    
             }      
        }
     }
   }else if(Trigger.isUpdate) {
     for(Task t :Trigger.New) {      
        If (t.whoid <> null || t.whoid <> ''){
             If (string.valueof(t.whoid).startswith('00Q') ){
                leadIds.add(t.whoid); 
                system.debug('### leadid new updat'+leadIds);   
             }      
        }     
     }
     for(Task t : Trigger.Old) {      
         If (t.whoid <> null || t.whoid <> ''){
            If (string.valueof(t.whoid).startswith('00Q')){
                leadIds.add(t.whoid); 
                system.debug('### leadid old upd '+leadIds);   
             }      
         }  
     }      
   }else {
     for(Task t : Trigger.New) {      
        If (t.whoid <> null || t.whoid <> ''){
             If (string.valueof(t.whoid).startswith('00Q')){
                leadIds.add(t.whoid);
                system.debug('### leadid else '+leadIds);    
             }      
        }    
     }
   }
   system.debug('### leadid '+leadIds);
   Integer count ;
   AggregateResult[] groupedResults = [SELECT COUNT(Id), whoId FROM task where whoid IN :leadIds AND Status <> 'Completed' GROUP BY whoid];
   system.debug('### ar '+groupedResults );
   if(groupedResults.size() > 0){
       for(AggregateResult ar:groupedResults) {     
         Id custid = (ID)ar.get('whoid');     
         count = (INTEGER)ar.get('expr0');     
         Lead l = new Lead(Id=custid);     
         l.open_activities__c= count;     
         leads.add(l); 
     
     }     
   }else{
       for(AggregateResult ar: [SELECT COUNT(Id), whoId FROM task where whoid IN :leadIds GROUP BY whoid]) {     
         Id custid = (ID)ar.get('whoid');     
         count = (INTEGER)ar.get('expr0');     
         Lead l = new Lead(Id=custid);     
         l.open_activities__c= 0;     
         leads.add(l); 
     
     }  
   }
   system.debug('### leads '+leads);   
If(leads.size()>0)
   update leads;
}

Please let me know if this help and choose the best answer.

Thanks
Shivdeep
This was selected as the best answer