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
ilaiilai 

bulk update child accounts

Hi,

 

I am new to apex development. I got this error message when update a Parent account and the account hierarchy up to 4 levels.

 

I want to update a custom field value in all the child accounts if the parent account custom field value is changed.

 

Many Thanks.

 

---------------------------------------------------------------------------------------------------- 

Apex trigger setChildrenGSAFlag caused an unexpected exception, contact your administrator: setChildrenGSAFlag: execution of AfterUpdate caused by: System.DmlException: Update failed. First exception on row 0 with id 001R000000FYfiqIAD; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, setChildrenGSAFlag: execution of AfterUpdate caused by: System.DmlException: Update failed. First exception on row 0 with id 001R000000FYfrEIAT; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, setChildrenGSAFlag: execution of AfterUpdate caused by: System.DmlException: Update failed. First exception on row 0 with id 001R000000FYfx1IAD; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, setChildrenGSAFlag: execution of AfterUpdate caused by: System.DmlException: Update failed. First exception on row 0 with id 001R000000FYfzmIAD; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, checkAndSetGSAFlag: execution of BeforeUpdate caused by: System.Exception: Too many SOQL queries: 21 Trigger.checkAndSetGSAFlag: line 3, column 40 Trigger.setChildrenGSAFlag: line 43, column 5 Trigger.setChildrenGSAFlag: line 43, column 5 Trigger.setChildrenGSAFlag: line 43, column 5: Trigger.setChildrenGSAFlag: line 43, column 5 

-------------------------------------------------------------------------------------------------

trigger setChildrenGSAFlag on Account(after update){

    List<Id> AccountsUpdatedTrue = new List<Id>();  
    List<Id> AccountsUpdatedFalse = new List<Id>();  
    
      
    // List of parents where flag changed and became true  
    for (Integer i = 0; i < Trigger.new.size(); i++) {     
           if ((Trigger.new[i].GSA__c==true) && (Trigger.old[i].GSA__c==false))
           { 
                 AccountsUpdatedTrue.add(Trigger.new[i].Id);    
           } 
           else if ((Trigger.new[i].GSA__c==false) && (Trigger.old[i].GSA__c==true)){
                 AccountsUpdatedFalse.add(Trigger.new[i].Id);  
           }
    } 
    
    List<Account> updatedAccounts = new List<Account>();

    
    // Update the child accounts  
    Account[] ChildAccountsToUpdateTrue = [SELECT Id, parentId, GSA__c FROM Account WHERE parentId IN :AccountsUpdatedTrue];  
           
    for(Account pAcc: ChildAccountsToUpdateTrue)
    {   
                  
         pAcc.GSA__c=True;
         updatedAccounts.add( pAcc); 
    }
    
   
    
    Account[] ChildAccountsToUpdateFalse = [SELECT Id, parentId, GSA__c FROM Account WHERE parentId IN :AccountsUpdatedFalse];  
    for(Account pAcc: ChildAccountsToUpdateFalse)
    {   
         pAcc.GSA__c=False;
         //add GAM Team  for this children
         updatedAccounts.add( pAcc); 
    }
    
       
    update updatedAccounts;


}
BritishBoyinDCBritishBoyinDC

Looks like there might be a problem in a beforeupdate trigger called checkandsetGSAflag- check to see if you are using a SOQl statement in a loop - if so, you need to remove it from any loops and query for it separately.

 

CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, checkAndSetGSAFlag: execution of BeforeUpdate caused by: System.Exception: Too many SOQL queries: 21 Trigger.checkAndSetGSAFlag: line 3, column 40 Trigger.setChildrenGSAFlag: line 43, column 5 Trigger.setChildrenGSAFlag: line 43, column 5 Trigger.setChildrenGSAFlag: line 43, column 5: Trigger.setChildrenGSAFlag: line 43, column 5  
ilaiilai

this is checkAndSetGSAFlag trigger. The SOQL is not called inside a loop. >_<

 

Any helps.... Many Thanks.

trigger checkAndSetGSAFlag on Account (before insert, before update) {
    // Find accounts to update  
    Account[] ParentAccountsFlagTrue = [SELECT Id FROM Account WHERE GSA__c=true];
    
    for(Account a: Trigger.new){  
      
        //has a parent account and GSA is not flag
        if (a.parentId != NULL && a.GSA__C == false)  
        {     
            for(Account idCrt :ParentAccountsFlagTrue)
                if  (idCrt.id == a.parentID) {
                    a.GSA__c=True; 
            } 
        } 
    } 
}
jpwagnerjpwagner

This is a troublesome use case, because even if you cut down the number of SOQL queries in half (by changing your logic to make one instead of two), then you're still going to run into problems with governor limits.  One record update cannot update ~20 records (more with @future), so you have two choices IMO,

 

1.  talk to salesforce support about how you can achieve this with apex.  they may help you increase your governor limits (but i doubt it.)

 

2.  use a formula field to look up the value for instance: 

GSAFormula__c = if(parent.parent.parent.parentid != null, parent.parent.parent.parent.GSA__c, if(parent.parent.parentid != null, parent.parent.parent.GSA__c, if(parent.parentid != null, parent.parent.GSA__c, if(parentid !=null, parent.GSA__c, GSA__c))))

 

 

I know it's ugly, but your use-case is problematic.  Could you potentially rethink why you need to store the Top-most parent value on each record?  is this for reporting?
BritishBoyinDCBritishBoyinDC

Agree with jpwagner - I am not sure why you need a trigger for the beforeupdate - if the logic is just set GSA__C to true if account 'has parent and GSA__C == null', you can set that using workflow field update, which won't directly trigger apex limits.

 

If there are other reasons to use this trigger, you might be able to code this beforeupdate with a map or set - currently, you are looping through every account that is returned by the query for each account in the trigger, which is very inefficient. By putting the results of query into a map or a set,  you can just look directly for a match in the map using a 'contains' method, and execute the logic if it's present.

 

But I think the bigger issue it that that everytime afterupdate is executed, beforeupdate is called again, which in turn calls AfterUpdate again, and so depending on how many levels of accounts you have, you are quickly executing the three soql statements in the two triggers more than 6 times, which is how you are reach 21 queries. So I would try and restructure how beforeupdate is called, but even so, the recursive nature means you are always going to potentially end up at more than 21 queries unless you can restructure how the logic is executed...

jpwagnerjpwagner

"- if the logic is just set GSA__C to true if account 'has parent and GSA__C == null'"

 

no-because as soon as it is non-null it will not update (AND it is multilevel)... which is why you do the formula how i describe.

 

"...beforeupdate with a map or set"

 

no-because of the governor limit issues which i described and you cite in your last paragraph.

 

 

 

 

 

Message Edited by jpwagner on 04-14-2009 09:39 PM
ilaiilai

Any suggestion how to write a bulk update which will not reach the governor limit?

Anyone has a good bulk update solution to share with?

 

Many thanks.
Cool_DevloperCool_Devloper

Hi JP,

Can i use a forumla like what you have mentioned, if the number of levels in the hierarchy are not fixed?

Cool_D