function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
jvelazquezjvelazquez 

Trigger for Contact count on Account giving Exception: Too many code statements: 200001

Hi, 

I have trigger that rolls up total number of contacts on Account, This trigger was working fine in sandbox, But when moved to production it is throwing errors at the backend  saying 'System.LimitException: Too many code statements: 200001'. My company production enviroment is integrated with 3rd party system. So everytime there is an update from external system this trigger is firing errors.

 

Below is my code:

 

trigger ContactRollUpOnAccounts on Contact (after delete, after insert, after update) {
        
  
  set<ID> AccountIds = new set<ID>();
  set<ID> conIDs = new Set<ID>();
 
  
  if(trigger.isInsert || trigger.isUpdate){
    for(Contact c : trigger.new){
      AccountIds.add(c.AccountId);
    }
  }
 
  
  if(trigger.isDelete){
    for(Contact c : trigger.old){
      AccountIds.add(c.AccountId);
    }
  }
 
 
   Map<ID, Contact> AccountMap = new Map<ID, Contact>([select id,AccountId
    from Contact where AccountId IN :AccountIds]);
 
  List<Account> AccountsToUpdate = new  List<Account>();
 
 
  for(Account a : [Select Id, Contacts__c from Account where Id IN :AccountIds ]){
        
      for (Contact con : AccountMap.values()) {
            if (con.AccountId == a.Id)
                conIDs.add(con.Id);
        }   
       if (a.Contacts__c!= conIDs.size())
            a.Contacts__c= conIDs.size();
              AccountsToUpdate.add(a);
    } 
 
  update AccountsToUpdate;
 }

 

 I understand the problem is because I am writing for loop within a for loop, but I am not aware of the workaround to avoid this.

 

Please help me with your valuiable thoughts

Best Answer chosen by Admin (Salesforce Developers) 
Peter_sfdcPeter_sfdc

I wrote that free hand from memory.

 

I forgot, the get method returns Object as data type. 

 

You'll need to do something to cast the result of get('AccountId') to Id or String. You will need to do the same for get('ContactCount') to cast it to a number data type (Integer or Decimal typically). So try something like this: 

 

conCountMap.put(String.valueOf(agg.get('AccountId')),Integer.valueOf(agg.get('ContactCount')));

 

All Answers

Peter_sfdcPeter_sfdc

You can prevent this by using Aggregate SOQL.

 

Instead of querying every contact for every Account in the trigger context and then counting in your code, you should be let SOQL count the number of contacts per account. You query will instead look something like this: 

 

List<AggregateResult> aggs = [select AccountId, count(ID) ContactCount 
from Contact where AccountId in : AccountIds Group By AccountId];

 Aggregate result is just a special sObject type that you can then use the alias "ContactCount" as a field name using a standard getter method like this. 

Map<Id,Integer> conCountMap = new Map<Id,Integer>();
for (AggregateResult agg : aggs) {
    conCountMap.put(agg.get('AccountId'),agg.get('ContactCount'));
}

 Once you have your aggregate count in a map, then you loop over the relevant accounts, thus avoiding the loop-within-a-loop antipattern that you identified. But here's the cool thing. Because you're not *reading* anything from those accounts in the database, you can do a little trick where you create a new account in memory only, and then update it. 

for(Id acctId : AccountIds ){
    Account newAcct = new Account(id=acctId);
    newAcct.Contacts__c = conCountMap.get(acctId);  
    AccountsToUpdate.add(newAcct);
} 

update AccountsToUpdate;

Hope this helps.  

jvelazquezjvelazquez

i Thanks for your reply, i tried to inserting your code  I am getting the following error at line 

                  conCountMap.put(agg.get('AccountId'),agg.get('ContactCount'));

 

and the error is 'Incompatible key type Object for MAP<Id,Integer> at line 25 column 13'

Peter_sfdcPeter_sfdc

I wrote that free hand from memory.

 

I forgot, the get method returns Object as data type. 

 

You'll need to do something to cast the result of get('AccountId') to Id or String. You will need to do the same for get('ContactCount') to cast it to a number data type (Integer or Decimal typically). So try something like this: 

 

conCountMap.put(String.valueOf(agg.get('AccountId')),Integer.valueOf(agg.get('ContactCount')));

 

This was selected as the best answer
isalewisalew

I'm building a test method for this trigger, but the trigger fails when I update a contact by moving it to another account. The other account contact rollup increases the number, but the old account does not decrease! How would I fix that?

 

Here is my line in the test class:

 

//Remove Contact 2
ct2.AccountID = ac2.ID;
update ct2;

// Verify
ac = [SELECT ID, Contact_Count__c FROM Account WHERE ID = :ac.id];
System.assertEquals(1,ac.Contact_Count__c);

 

And here is the trigger as I have it thus far:

 

trigger ContactRollUpOnAccounts on Contact (after delete, after insert, after update) {        
  
	set<ID> AccountIds = new set<ID>();
	set<ID> conIDs = new Set<ID>(); 
  
	if(trigger.isInsert || trigger.isUpdate){
		for(Contact c : trigger.new){
			AccountIds.add(c.AccountId);
		}
	}
  
	if(trigger.isDelete){
		for(Contact c : trigger.old){
			AccountIds.add(c.AccountId);
	    }
	}

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

    List<AggregateResult> aggs = [select AccountId, count(ID) Contact_Count__c 
                                from Contact where AccountId in : AccountIds Group By AccountId]; 
 
	Map<Id,Integer> conCountMap = new Map<Id,Integer>();

	for (AggregateResult agg : aggs) {
		conCountMap.put(String.valueOf(agg.get('AccountId')),Integer.valueOf(agg.get('Contact_Count__c')));
	}
 
	for(Id acctId : AccountIds ){
		Account newAcct = new Account(id=acctId);
		newAcct.Contact_Count__c = conCountMap.get(acctId);  
		AccountsToUpdate.add(newAcct);
	}

	update AccountsToUpdate;
	
}

 

 

 

isalewisalew

It also doesn't change the number when I delete. So basically, it is not counting down, only up.

jvelazquez@enablon.netjvelazquez@enablon.net

Hi, try the below code, it works for the old contacts also... 

 

I tested it also works when contact is deleted.

 

trigger ContactRollUpOnAccounts on Contact (after delete, after insert, after update) {
        
  
  set<ID> AccountIds = new set<ID>();
  set<ID> conIDs = new Set<ID>();
  Set<Id> OldAId = new Set<Id>(); 
   
  if(trigger.isInsert){  
    for(Contact c : trigger.new){
    if(c.AccountId!=null)
      AccountIds.add(c.AccountId);
    }
  }
 
  if(Trigger.isUpdate){    
        for(Contact opp : Trigger.new){         
            if(opp.AccountId != Trigger.oldMap.get(opp.id).AccountId){ 
                AccountIds.add(opp.AccountId); 
                OldAId.add(Trigger.oldMap.get(opp.id).AccountId); 
            }
        }
    }

  
  if(trigger.isDelete){
    for(Contact c : trigger.old){
     if(c.AccountId!=null)
      AccountIds.add(c.AccountId);
    }
  }
 
 /* for new Account Contacts */
 if(AccountIds!=Null){
  List<AggregateResult> aggs = [select AccountId, count(ID) ContactCount from Contact where AccountId in : AccountIds Group By AccountId];
  Map<Id,Integer> conCountMap = new Map<Id,Integer>();
        for (AggregateResult agg : aggs) {
            conCountMap.put(string.valueOf(agg.get('AccountId')),Integer.valueOf(agg.get('ContactCount')));
        } 
 
 List<Account> AccountsToUpdate = new  List<Account>();
     if(AccountIds != null){
     for(Id acctId : AccountIds ){
        Account newAcct = new Account(id=acctId,Skip_Validation__c = true);
        newAcct.Contacts__c = conCountMap.get(acctId);  
        AccountsToUpdate.add(newAcct);
        } 
    }
    update AccountsToUpdate;
 }   
  /* for old Account Contacts */
  if(OldAId!=Null){ 
  List<AggregateResult> aggsOld = [select AccountId, count(ID) ContactCount from Contact where AccountId in : OldAId Group By AccountId]; 
  Map<Id,Integer> conCountMap = new Map<Id,Integer>();
       for (AggregateResult agg : aggsOld) {
            conCountMap.put(string.valueOf(agg.get('AccountId')),Integer.valueOf(agg.get('ContactCount')));
        } 
   List<Account> AccountsToUpdateOld = new  List<Account>();
     if(OldAId!= null){
     for(Id acctId : OldAId){
        Account newAcct = new Account(id=acctId,Skip_Validation__c = true);
        newAcct.Contacts__c = conCountMap.get(acctId);  
        AccountsToUpdateOld.add(newAcct);
        } 
    }
    update AccountsToUpdateOld;
  }  
 }

 Queried Contacts bases on old Account Id. Hope this helps you

Let me know if you find any other solution

 

Thanks

isalewisalew

Thank you, that did the trick!

 

I modified some of the fields, so here is the unified trigger/test that worked for me:

 

Here is the trigger:

 

trigger ContactRollUpOnAccounts on Contact (after delete, after insert, after update) {

    set<ID> AccountIds = new set<ID>();
    set<ID> conIDs = new Set<ID>();
    Set<Id> OldAId = new Set<Id>(); 
   
    if(trigger.isInsert){  
        for(Contact c : trigger.new){
            if(c.AccountId!=null)
            AccountIds.add(c.AccountId);
        }
    }
 
    if(Trigger.isUpdate){    
        for(Contact opp : Trigger.new){         
            if(opp.AccountId != Trigger.oldMap.get(opp.id).AccountId){ 
                AccountIds.add(opp.AccountId); 
                OldAId.add(Trigger.oldMap.get(opp.id).AccountId); 
            }
        }
    }

    if(trigger.isDelete){
        for(Contact c : trigger.old){
            if(c.AccountId!=null)
            AccountIds.add(c.AccountId);
        }
    }
 
    /* for new Account Contacts */
    if(AccountIds!=Null){
    List<AggregateResult> aggs = [select AccountId, count(ID) ContactCount from Contact where AccountId in : AccountIds Group By AccountId];
    Map<Id,Integer> conCountMap = new Map<Id,Integer>();
    for (AggregateResult agg : aggs) {
        conCountMap.put(string.valueOf(agg.get('AccountId')),Integer.valueOf(agg.get('ContactCount')));
    } 
 
    List<Account> AccountsToUpdate = new  List<Account>();
    if(AccountIds != null){
        for(Id acctId : AccountIds ){
            Account newAcct = new Account(id=acctId);
            newAcct.Contact_Count__c = conCountMap.get(acctId);  
            AccountsToUpdate.add(newAcct);
        } 
    }
    update AccountsToUpdate;
 }   

    /* for old Account Contacts */
    if(OldAId!=Null){ 
    List<AggregateResult> aggsOld = [select AccountId, count(ID) ContactCount from Contact where AccountId in : OldAId Group By AccountId]; 
    Map<Id,Integer> conCountMap = new Map<Id,Integer>();
    for (AggregateResult agg : aggsOld) {
        conCountMap.put(string.valueOf(agg.get('AccountId')),Integer.valueOf(agg.get('ContactCount')));
    } 
    List<Account> AccountsToUpdateOld = new  List<Account>();

    if(OldAId!= null){
        for(Id acctId : OldAId){
            Account newAcct = new Account(id=acctId);
            newAcct.Contact_Count__c = conCountMap.get(acctId);  
            AccountsToUpdateOld.add(newAcct);
        } 
    }

    update AccountsToUpdateOld;
        
    }  
}

 

And the test class:

 

@isTest

private class ContactRollUpOnAccounts_TestClass{

    public static testMethod void validateContactRollUpOnAccounts() {

        //Create Account
		Account ac = new Account(
			Name='Test Account',
			Initial_Lead_Source__c='Cold Call',
			Initial_Lead_Source_Detail__c='Cold Call',
			Account_Type__c='Prospect',
			Account_Status__c='Not Contacted'
			);
		insert ac;

        //Create Account 2
		Account ac2 = new Account(
			Name='Test Account 2',
			Initial_Lead_Source__c='Cold Call',
			Initial_Lead_Source_Detail__c='Cold Call',
			Account_Type__c='Prospect',
			Account_Status__c='Not Contacted'
			);
		insert ac2;

        //Create Contact 1
        Contact ct1 = new Contact(
                LastName='Test Contact 1',
                Email='testcontact1@email.com',
                AccountID= ac.ID
				);
        insert ct1;
		
		// Verify
        ac = [SELECT ID, Contact_Count__c FROM Account WHERE ID = :ac.id];
        System.assertEquals(1,ac.Contact_Count__c);

        //Create Contact 2
        Contact ct2 = new Contact(
                LastName='Test Contact 2',
                Email='testcontact2@email.com',
                AccountID= ac.ID
				);
        insert ct2;

		// Verify
        ac = [SELECT ID, Contact_Count__c FROM Account WHERE ID = :ac.id];
        System.assertEquals(2,ac.Contact_Count__c);
        
        //Delete Contact 1
		delete ct1;

		// Verify
        ac = [SELECT ID, Contact_Count__c FROM Account WHERE ID = :ac.id];
        System.assertEquals(1,ac.Contact_Count__c);

		//Remove Contact 2
		ct2.AccountID = ac2.ID;
        update ct2;

        // Verify
        ac = [SELECT ID, Contact_Count__c FROM Account WHERE ID = :ac.id];
        System.assertEquals(null,ac.Contact_Count__c);
        ac2 = [SELECT ID, Contact_Count__c FROM Account WHERE ID = :ac2.id];
        System.assertEquals(1,ac2.Contact_Count__c);


		//Attach Contact 2
        ct2.AccountID = ac.ID;
        update ct2;

		// Verify
        ac = [SELECT ID, Contact_Count__c FROM Account WHERE ID = :ac.id];
        System.assertEquals(1,ac.Contact_Count__c);
        ac2 = [SELECT ID, Contact_Count__c FROM Account WHERE ID = :ac2.id];
        System.assertEquals(null,ac2.Contact_Count__c);
    }
}

 

 

Ryan YoungRyan Young
This is awesome, thank so much!