Apex Code Development You need to sign in to do that
Don't have an account?
Samwisematrix 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;
}
}
}
*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
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
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
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?
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);
}
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.
*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;
}
}
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.
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;
}
}
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;
}
}
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);
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.