+ Start a Discussion
SamwisematrixSamwisematrix 

System.LimitException: Too many SOQL queries: 101 Please Help!

What I am trying to do is update a custom date field.  I want to put the date of the last activity that does not contain "Act-On Email" into the field "Last_Activity_by_Account_Owner__c".

 

The trigger works great until I do a mass update to trigger the trigger on previous tasks, but then I run into System.LimitException: Too many SOQL queries: 101.   I believe it is because I have a query inside the for loop, but I am new to Triggers and I dont know how to fix it.

 

My code:

 

trigger LastActDate2 on Task (after insert, after update) {
    
    
    //To do - If the subject of a completed task does not contain "Act-On Email", put the date of the completed task in the
    //"Last_Activity_by_Account_Owner__c" field on the account object

//Create a set of related account ID's
Set <ID> acctIDs = new Set <ID> ();


//For every task, add it's related to account ID to the set
 for (Task t: Trigger.new){
    if (t.accountID != NULL){
      acctIDs.add(t.accountID);
//Create a map to match the task related to ID's with their corresponding account ID's
      Map<ID, Account> acctMap = new Map<ID, Account> ([Select ID,Account_Owner_Role__c, Last_Activity_by_Account_Owner__c from Account where ID in :acctIDs]);
//Create the account object
      Account acctRec = acctMap.get(t.accountID);

//If the account ID isn't null, the subject line contains "Act-On Email", the account record owner's role matches the task record owner's role, and the task has been marked as completed
//Check to see if the Last_Activity_by_Account_Owner__c field is current compared with the latest completed activity    
    If (((!t.subject.contains('Act-On Email'))&&acctMap.get(t.accountID).Account_Owner_Role__c == t.Task_Owner_Role__c && (t.Status == 'Completed')) &&
    (acctMap.get(t.accountID).Last_Activity_by_Account_Owner__c  < t.ActivityDate || acctMap.get(t.accountID).Last_Activity_by_Account_Owner__c  ==null )){
//Update the Last_Activity_by_Account_Owner__c field on the account object with the task's end date  
          acctrec.Last_Activity_by_Account_Owner__c  = t.ActivityDate;
    
      }
    update acctRec;
  }
  }
  }

Best Answer chosen by Admin (Salesforce Developers) 
Michael_TorchedloMichael_Torchedlo

*Credit goes to Souvik for this solution*

 

 

trigger LastActDate2 on Task (after insert, after update) {

Set <ID> acctIDs = new Set <ID> ();

Set<Id> Ready4Update = new Set<Id>();

List<Account> accList = new List<Account>();

for (Task t: Trigger.new){

acctIDs.add(t.accountID);

}

Map<ID, Account> acctMap = new Map<ID, Account> ([Select ID,Account_Owner_Role__c, Last_Activity_by_Account_Owner__c from Account where ID in :acctIDs]);

for (Task t: Trigger.new){

if (t.accountID != NULL){

 

Account acctRec = acctMap.get(t.accountID);

If (((!t.subject.contains('Act-On Email'))&&acctMap.get(t.accountID).Account_Owner_Role__c == t.Task_Owner_Role__c && (t.Status == 'Completed')) &&

(acctMap.get(t.accountID).Last_Activity_by_Account_Owner__c < t.ActivityDate || acctMap.get(t.accountID).Last_Activity_by_Account_Owner__c ==null )){

 

acctrec.Last_Activity_by_Account_Owner__c = t.ActivityDate;

 

}

if(!Ready4Update.contains(accRec.id)){

 

   accList.add(acctRec);   

   Ready4Update.add(accRec.id);

 

}

 

}

}

if(accList.size() > 0){

update accList;

}

}

All Answers

souvik9086souvik9086

Man, you are using SOQL queries and DML statemnt within for loop. The error is bound to come for that. Try to avoid those within loop.

 

If this post is helpful please throw Kudos.If this post solves your problem kindly mark it as solution.

Thanks

souvik9086souvik9086

Modify it like this and your comments

 

trigger LastActDate2 on Task (after insert, after update) {
Set <ID> acctIDs = new Set <ID> ();
List<Account> accList = new List<Account>();
for (Task t: Trigger.new){
acctIDs.add(t.accountID);
}
Map<ID, Account> acctMap = new Map<ID, Account> ([Select ID,Account_Owner_Role__c, Last_Activity_by_Account_Owner__c from Account where ID in :acctIDs]);
for (Task t: Trigger.new){
if (t.accountID != NULL){

Account acctRec = acctMap.get(t.accountID);
If (((!t.subject.contains('Act-On Email'))&&acctMap.get(t.accountID).Account_Owner_Role__c == t.Task_Owner_Role__c && (t.Status == 'Completed')) &&
(acctMap.get(t.accountID).Last_Activity_by_Account_Owner__c < t.ActivityDate || acctMap.get(t.accountID).Last_Activity_by_Account_Owner__c ==null )){

acctrec.Last_Activity_by_Account_Owner__c = t.ActivityDate;

}
accList.add(acctRec);
}
}
if(accList.size() > 0){
update accList;
}
}

 

If this post is helpful please throw Kudos.If this post solves your problem kindly mark it as solution.

Thanks

SamwisematrixSamwisematrix

Wow, thanks!  

 

I am still getting a dataloader error when trying to insert new tasks

 

LastActDate2: execution of AfterInsert

caused by: System.ListException: Duplicate id in list: 001Z000000YMmIAIA1

Trigger.LastActDate2: line 22, column 1

 

 

Do you happen to know how to fix that?

Michael_TorchedloMichael_Torchedlo

Somewhere in your list of Tasks that you are inserting or updating, you have 2 tasks for the same account.  Later, you are creating a list of all the accounts to update, and that account gets added to the list twice.  It is impossible to update the same record twice in the same operation, which is why you got the error.  One way to fix it is to NOT update multiple tasks for the same account at the same time.

 

But if you want to add some code to avoid it, then what you need to do is create a second set of account ids to keep track of which accounts you have already added to the accList.add(acctRec);

 

Set<Id> Ready4Update = new Set<Id>();

...

...

 

if(!Ready4Update.contains(accRec.id)){

   accList.add(acctRec);    //this will keep the list of accounts clean so there are no duplicates.

   Ready4Update.add(accRec.id);

}

SamwisematrixSamwisematrix

Thanks again.   I tried playing around with the code, and i cant figure out where to put the additional code you wrote for me.  Can you append the original code with your additions for me?   Thanks again in advance.

Michael_TorchedloMichael_Torchedlo

*Credit goes to Souvik for this solution*

 

 

trigger LastActDate2 on Task (after insert, after update) {

Set <ID> acctIDs = new Set <ID> ();

Set<Id> Ready4Update = new Set<Id>();

List<Account> accList = new List<Account>();

for (Task t: Trigger.new){

acctIDs.add(t.accountID);

}

Map<ID, Account> acctMap = new Map<ID, Account> ([Select ID,Account_Owner_Role__c, Last_Activity_by_Account_Owner__c from Account where ID in :acctIDs]);

for (Task t: Trigger.new){

if (t.accountID != NULL){

 

Account acctRec = acctMap.get(t.accountID);

If (((!t.subject.contains('Act-On Email'))&&acctMap.get(t.accountID).Account_Owner_Role__c == t.Task_Owner_Role__c && (t.Status == 'Completed')) &&

(acctMap.get(t.accountID).Last_Activity_by_Account_Owner__c < t.ActivityDate || acctMap.get(t.accountID).Last_Activity_by_Account_Owner__c ==null )){

 

acctrec.Last_Activity_by_Account_Owner__c = t.ActivityDate;

 

}

if(!Ready4Update.contains(accRec.id)){

 

   accList.add(acctRec);   

   Ready4Update.add(accRec.id);

 

}

 

}

}

if(accList.size() > 0){

update accList;

}

}

This was selected as the best answer
SamwisematrixSamwisematrix

Thanks.  

 

Another question if you are up for it.  I need a way to update a custom checkbox on the task record when a custom formula number field on the related account record is more than 120.  The use case is that the formula number field Days_with_no_Activity__c is how many days since the last activiity (based on the last trigger we just wrote), and i want to update the task record prior to inserting it with a check box Unique_Activity__c.

Michael_TorchedloMichael_Torchedlo

I'm not sure that it makes much sense to have a checkbox like that on your Task, but it can be done.

 

You're trying to update the Task itself, basically at the same time that your trigger is updating the Account.  You could write a totally separate trigger, but you're going to be recreating some of the same code, and the more triggers you have on Activities, the slower your bulk operations could become.  Since you've already gotten this far, I would try modifying your existing trigger so that it also checks that box (when appropriate). 

 

One thing to change is make youre trigger a before trigger instead of after.  (**This could affect the order of workflow if you have other triggers or workflow rules attached to the Task object, but I'm hoping you don't have anything else.)

 

trigger LastActDate2 on Task (before insert, before update) {

Set <ID> acctIDs = new Set <ID> ();

Set<Id> Ready4Update = new Set<Id>();

List<Account> accList = new List<Account>();

for (Task t: Trigger.new){

acctIDs.add(t.accountID);

}

 

Map<ID, Account> acctMap = new Map<ID, Account> ([Select ID,Account_Owner_Role__c, Last_Activity_by_Account_Owner__c,Days_with_no_Activity__c from Account where ID in :acctIDs]);

 

for (Task t: Trigger.new){

if (t.accountID != NULL){

 

Account acctRec = acctMap.get(t.accountID);

If (((!t.subject.contains('Act-On Email'))&&acctMap.get(t.accountID).Account_Owner_Role__c == t.Task_Owner_Role__c && (t.Status == 'Completed')) &&

(acctMap.get(t.accountID).Last_Activity_by_Account_Owner__c < t.ActivityDate || acctMap.get(t.accountID).Last_Activity_by_Account_Owner__c ==null )){

 

acctrec.Last_Activity_by_Account_Owner__c = t.ActivityDate;

 

}

 

If(acctRec.Days_with_no_Activity__c > 120){

         t. Unique_Activity__c = True;

}

 

if(!Ready4Update.contains(accRec.id)){

 

                accList.add(acctRec);  

                Ready4Update.add(accRec.id);

 

}

}

}

 

   If((accList.size() > 0)){

      update accList;

   }

}

SamwisematrixSamwisematrix

I just got back to the office and I had a chance to update my trigger code, but it looks like I am getting an error

 

Variable does not exist: accRec.id at line 43 column 21

 

trigger LastActDate3 on Task (after insert, after update) {

Set <ID> acctIDs = new Set <ID> ();

Set<Id> Ready4Update = new Set<Id>();

List<Account> accList = new List<Account>();

for (Task t: Trigger.new){

acctIDs.add(t.accountID);

}

Map<ID, Account> acctMap = new Map<ID, Account> ([Select ID,Account_Owner_Role__c, Last_Activity_by_Account_Owner__c from Account where ID in :acctIDs]);

for (Task t: Trigger.new){

if (t.accountID != NULL){

 

Account acctRec = acctMap.get(t.accountID);

If (((!t.subject.contains('Act-On Email'))&&acctMap.get(t.accountID).Account_Owner_Role__c == t.Task_Owner_Role__c && (t.Status == 'Completed')) &&

(acctMap.get(t.accountID).Last_Activity_by_Account_Owner__c < t.ActivityDate || acctMap.get(t.accountID).Last_Activity_by_Account_Owner__c ==null )){

 

acctrec.Last_Activity_by_Account_Owner__c = t.ActivityDate;

 

}

if(!Ready4Update.contains(accRec.id)){

 

   accList.add(acctRec);   

   Ready4Update.add(accRec.id);

 

}

 

}

}

if(accList.size() > 0){

update accList;

}

}

Michael_TorchedloMichael_Torchedlo

Looks like a typo?

 

 

Account acctRec = acctMap.get(t.accountID);
...
...

...

 

if(!Ready4Update.contains(accRec.id)){

if(!Ready4Update.contains(acctRec.id)){


   accList.add(acctRec);   

   Ready4Update.add(accRec.id);
   Ready4Update.add(acctRec.id);
 

SamwisematrixSamwisematrix

I hate to throw around words like “genius”, but I have been banging my head on this for an hour and I didn’t think to check Typos...That’s why you make the big bucks.  Thanks again.