+ Start a Discussion
Michael PuglieseMichael Pugliese 

Account Apex Roll Up

Recently we have been seeing this error:
Apex script unhandled exception by user/organization:
 
Visualforce Page: /apex/Case 
caused by: System.LimitException: Too many SOQL queries: 101
 
Class.AccountServices.rollupAssetAnnualSubscription: line 403, column 1
Trigger.AccountTrigger: line 11, column 1

The method is a rollup function designed to sum the asset values for Locations/Facilities (record type) to the parent Client (record type):
public static void rollupAssetAnnualSubscription(List<Account> accs){
      
      Set<Id> accIds = new Set<Id>();
      List<Account> accsToUpdate = new List<Account>();
      for(Account a : accs){
       if(a.ParentId != null && a.RecordTypeId == recordTypesNameMap.get(Constants.ACCOUNT_RECORD_TYPE_FACILITY).ID)
        accIds.add(a.ParentId);     
      }
      System.debug('**********accIds' + accIds);
      //A sub query has a limit of 200 records so I do not use one to query the related objects 
      //in place of a sub query I use a query of the objects based on the affected accounts
      Map<Id,Decimal> accountObjectSizeMap = new Map<Id,Decimal>();
      for(Account ob : [SELECT ParentId,Asset_Annual_Subscriptions__c FROM Account WHERE ParentId IN :accIds]){
       if(accountObjectSizeMap.containsKey(ob.ParentId)){
            Decimal i = accountObjectSizeMap.get(ob.ParentId);
            i = i + ob.Asset_Annual_Subscriptions__c;
            accountObjectSizeMap.put(ob.ParentId,i);
       }
       else
            accountObjectSizeMap.put(ob.ParentId,ob.Asset_Annual_Subscriptions__c);
      }
      System.debug('************accMap' + accountObjectSizeMap);
      //then query the accounts and set the count to the map value
      for(Account a : [SELECT Id,Locations_Asset_Annual_Subscriptions__c FROM Account WHERE Id IN :accIds]){
       a.Locations_Asset_Annual_Subscriptions__c = accountObjectSizeMap.get(a.Id);
       accsToUpdate.add(a);
      }

The issue seems to be that we now have some location record type accounts that have parents that are also locations (a recent change in protocol), so when this method tries to run it results in the SOQL query limit error. I'm trying to figure out if it's easier to just scrap this rollup entirely, or if it's possible to adjust to allow for locations to rollup to locations (or just prevent this error). 

Any info/direction would be much appreciated!

Thanks, 

Michael Pugliese
 

Raj VakatiRaj Vakati
Hi Michael , 
Can you modify your code like below and try to consolidate as one query 
public static void rollupAssetAnnualSubscription(List<Account> accs){
      
      Set<Id> accIds = new Set<Id>();
      List<Account> accsToUpdate = new List<Account>();
      for(Account a : accs){
       if(a.ParentId != null && a.RecordTypeId == recordTypesNameMap.get(Constants.ACCOUNT_RECORD_TYPE_FACILITY).ID)
        accIds.add(a.ParentId);     
      }
      System.debug('**********accIds' + accIds);
   

      Map<Id,Decimal> accountObjectSizeMap = new Map<Id,Decimal>();
	  
	  List<Account> accTemps =  [SELECT ParentId,Asset_Annual_Subscriptions__c FROM Account WHERE ParentId IN :accIds
	  OR
	  Id IN :accIds] 
      for(Account ob : accTemps){
// Chekck Relaction between ParentId and Id is same and Modify based on your bussiness logic
If(ob.ParentId!=null){
		if(accountObjectSizeMap.containsKey(ob.ParentId)){
            Decimal i = accountObjectSizeMap.get(ob.ParentId);
            i = i + ob.Asset_Annual_Subscriptions__c;
            accountObjectSizeMap.put(ob.ParentId,i);
       }
       else
            accountObjectSizeMap.put(ob.ParentId,ob.Asset_Annual_Subscriptions__c);
}
      }
      System.debug('************accMap' + accountObjectSizeMap);
      //then query the accounts and set the count to the map value
      for(Account a : accTemps){
		  // Check the values 
		  If(accountObjectSizeMap.contains(a.Id)){
       a.Locations_Asset_Annual_Subscriptions__c = accountObjectSizeMap.get(a.Id);
       accsToUpdate.add(a);
		  }
      }



 
Asif Ali MAsif Ali M
Hi Michael,
Is it the only code that is running in the Trigger? For me it seems like the problamatic code is not in rollupAssetAnnualSubscription but at some other place. Do you mind sharing all trigger code here? And also what is the maximum number of childs you have on an Account?

Thanks
-Asif