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
Synthia BeauvaisSynthia Beauvais 

No Errors - Apex Trigger for Contact Count

The triggers below count the number of contacts and active contacts on an account. I am not getting any errors with the below triggers however, after doing some testing I found that recalculation is not taking place when a contact is deleted and field "Number of Contacts" is not updating properly. How can I correct this problem? 
  
trigger NumberOfContacts on Account (before insert, before update) {
if(trigger.isinsert)
    for(account a:trigger.new)
        a.Number_of_contacts__c = 0;
else {
    List<AggregateResult> agResult = [SELECT Count(ID) conCount, AccountId accId FROM Contact WHERE AccountId IN :trigger.new Group By AccountId];
    for(AggregateResult result : agResult){
        trigger.newmap.get((Id) result.get('accId')).Number_of_contacts__c = (Integer)result.get('conCount');
    }

    for(Account act : Trigger.new){
        act.Number_of_active_contacts__c = 0;
    }
    agResult = [SELECT Count(ID) conCount, AccountId accId FROM Contact WHERE AccountId IN :trigger.new AND Inactive__c = false Group By AccountId];
    for(AggregateResult result : agResult){
        trigger.newmap.get((Id) result.get('accId')).Number_of_active_contacts__c = (Integer)result.get('conCount');
    }
}
  
trigger NumberOfContactsOnAccount on Contact (after insert, after update, after delete, after undelete) {
List<Contact> contacts = new list<contact>();
Map<Id,account> accounts = new map<id,account>();
if(trigger.new!=null)
    contacts.addAll(trigger.new);
if(trigger.old!=null)
    contacts.addAll(trigger.old);
for(contact c:contacts)
    accounts.put(c.accountid,new account(id=c.accountid));
accounts.remove(null);
update accounts.values();
}

User-added image


Thanks in advance!
Best Answer chosen by Synthia Beauvais
UC InnovationUC Innovation
Hi Synthia,

Try this code. All I did was add this loop:

for (account a: trigger.new)
    a.Number_of_contacts__c = 0;

So it will always set the number of contacts to 0, then update based on the agResult. If agResult is empty, it will stay as 0 and if it is not empty, it will be updated correctly. 
trigger NumberOfContacts on Account (before insert, before update) {
    system.debug('hi');
    if(trigger.isinsert)
        for(account a:trigger.new)
            a.Number_of_contacts__c = 0;
    else {
        List<AggregateResult> agResult = [SELECT Count(ID) conCount, AccountId accId FROM Contact WHERE AccountId IN :trigger.new Group By AccountId];

        for (account a: trigger.new)
            a.Number_of_contacts__c = 0;
        
        for(AggregateResult result : agResult){
            system.debug('num of contacts' + result);
            trigger.newmap.get((Id) result.get('accId')).Number_of_contacts__c = (Integer)result.get('conCount');
        }
    
        for(Account act : Trigger.new){
            act.Number_of_active_contacts__c = 0;
        }
        agResult = [SELECT Count(ID) conCount, AccountId accId FROM Contact WHERE AccountId IN :trigger.new AND Inactive__c = false Group By AccountId];
        for(AggregateResult result : agResult){
            system.debug('num of active contacts' + result);
            trigger.newmap.get((Id) result.get('accId')).Number_of_active_contacts__c = (Integer)result.get('conCount');
        }
    }
}

 

All Answers

UC InnovationUC Innovation
Hi Synthia, 

You need to query the accounts that the contacts look up to in your contact trigger. Try this code:
  
trigger NumberOfContactsOnAccount on Contact (after insert, after update, after delete, after undelete) {
	List<ID> accountIDs = new list<ID>();
	List<contact> contacts = new List<contact>();
	List<Account> accounts = new list<account>();
	
	if(trigger.new!=null)
		contacts.addAll(trigger.new);
		
	if(trigger.old!=null)
		contacts.addAll(trigger.old);
		
	for(contact c:contacts)
		accountIDs.add(c.accountid);
	
	accounts = [SELECT id FROM account WHERE id in: accountIDs];

	update accounts;
}

Hope this helps!
Synthia BeauvaisSynthia Beauvais
Hi! 

No errors, but still same thing when I delete a contact. 0 contacts in list view, however "Has Contacts?" field says yes and "Number of Contacts" field says 1.

 User-added image
UC InnovationUC Innovation
Hi Synthia,

So does your code work when you insert contacts under an account? It just doesn't work on delete?
Synthia BeauvaisSynthia Beauvais
Yes! That's correct!
Synthia BeauvaisSynthia Beauvais
This code works on add, delete and undelete however, it throws an apex error for one account that has over 3,000+ contacts. 

NumberOfContacts: execution of BeforeUpdate caused by: System.QueryException: Aggregate query has too many rows for direct assignment, use FOR loop External entry point
  
trigger NumberOfContacts on Account (before insert, before update) {
    if(trigger.isinsert)
        for(account a:trigger.new)
            a.Number_of_contacts__c = 0;
    else
        for(account a:[select id,(select id from contacts) from account where id in :trigger.new])
            trigger.newmap.get(a.id).Number_of_contacts__c = a.contacts.size();
            
        for(account b:[select id,(select id from contacts where Inactive__c = False) from account where id in :trigger.new])
            trigger.newmap.get(b.id).Number_of_active_contacts__c = b.contacts.size();
}

To fix the issue above this code was created and does not update the count when a contact is deleted.
  
trigger NumberOfContacts2 on Account (before insert, before update) {
    if(trigger.isinsert)
        for(account a:trigger.new)
            a.Number_of_contacts__c = 0;
    else {
        List<AggregateResult> agResult = [SELECT Count(ID) conCount, AccountId accId FROM Contact WHERE AccountId IN :trigger.new Group By AccountId];
        for(AggregateResult result : agResult){
            trigger.newmap.get((Id) result.get('accId')).Number_of_contacts__c = (Integer)result.get('conCount');
        }
        
        for(Account act : Trigger.new){
            act.Number_of_active_contacts__c = 0;
        }
        agResult = [SELECT Count(ID) conCount, AccountId accId FROM Contact WHERE AccountId IN :trigger.new AND Inactive__c = false Group By AccountId];
        for(AggregateResult result : agResult){
            trigger.newmap.get((Id) result.get('accId')).Number_of_active_contacts__c = (Integer)result.get('conCount');
        }
    }
}
And this is the code for the contact object.  
trigger NumberOfContactsOnAccount on Contact (after insert, after update, after delete, after undelete) {
List<Contact> contacts = new list<contact>();
Map<Id,account> accounts = new map<id,account>();
if(trigger.new!=null)
    contacts.addAll(trigger.new);
if(trigger.old!=null)
    contacts.addAll(trigger.old);
for(contact c:contacts)
    accounts.put(c.accountid,new account(id=c.accountid));
accounts.remove(null);
update accounts.values();
}

I hope this makes sense. I wish I could just have just one trigger to accomplsh this contact count. 

 
UC InnovationUC Innovation
Hi Synthia,

Ok so I think I know what's going on here. When your account has just one contact and you delete that contact, your agResult will return you no rows, which means that it never goes in your for loop where you set the Number of Contacts field. 

If you test using an account with 2 contacts, and you delete one of them, you should see that the trigger works fine. In order to resolve your issue, you need to check if agResult is empty, and if it is then set the Number of Contacts to 0. The only issue is how to bulkify your code...
Synthia BeauvaisSynthia Beauvais
Thank you! But I'm not quite sure how to do that. I was assisted here https://developer.salesforce.com/forums/ForumsMain?id=9060G000000XfQVQA0 ... after doing more testing I found that issue. 
UC InnovationUC Innovation
Hi Synthia,

Try this code. All I did was add this loop:

for (account a: trigger.new)
    a.Number_of_contacts__c = 0;

So it will always set the number of contacts to 0, then update based on the agResult. If agResult is empty, it will stay as 0 and if it is not empty, it will be updated correctly. 
trigger NumberOfContacts on Account (before insert, before update) {
    system.debug('hi');
    if(trigger.isinsert)
        for(account a:trigger.new)
            a.Number_of_contacts__c = 0;
    else {
        List<AggregateResult> agResult = [SELECT Count(ID) conCount, AccountId accId FROM Contact WHERE AccountId IN :trigger.new Group By AccountId];

        for (account a: trigger.new)
            a.Number_of_contacts__c = 0;
        
        for(AggregateResult result : agResult){
            system.debug('num of contacts' + result);
            trigger.newmap.get((Id) result.get('accId')).Number_of_contacts__c = (Integer)result.get('conCount');
        }
    
        for(Account act : Trigger.new){
            act.Number_of_active_contacts__c = 0;
        }
        agResult = [SELECT Count(ID) conCount, AccountId accId FROM Contact WHERE AccountId IN :trigger.new AND Inactive__c = false Group By AccountId];
        for(AggregateResult result : agResult){
            system.debug('num of active contacts' + result);
            trigger.newmap.get((Id) result.get('accId')).Number_of_active_contacts__c = (Integer)result.get('conCount');
        }
    }
}

 
This was selected as the best answer
Synthia BeauvaisSynthia Beauvais
I tested your code with the below scenarios and they all passed! Thank you so much for your help! 
  1. Add new contact
  2. Delete contact
  3. Undelete contact
  4. Mark contact as inactive
  5. Transfer contact to another account
  6. Lead Converted to Contact