Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Chad MoutesChad Moutes 

Apex Trigger Count Help.

I have an Apex Trigger that counts the number of Projects that have been created on an Account, and i realized today that if you create the first Project on an Account the field displays 1, but then if you delete that Project the field still displays 1. I need it to go back to zero if there are no Projects on the Account anymore.

Any help would be greatly appreciated,
Best Answer chosen by Chad Moutes
SunidharSunidhar
Oh sry about that, Here is my modified code

trigger RollupActiveProjectsCountToAccountTrigger on Project__c (after insert, after update, after delete, after undelete) { 
    set<Id> set_id = new set<Id>();
    
    if(trigger.isInsert || trigger.isUpdate) {
        for(Project__c prj: trigger.new) {
            set_id.add(prj.Company_Name__c);
        }
    }
    else if(trigger.isDelete) {
        for(Project__c prj: trigger.old) {
            set_id.add(prj.Company_Name__c);
        }
    }
    
    if(trigger.isAfter && (trigger.isUpdate || trigger.isInsert || trigger.isDelete)) {
        acc_list = [SELECT id, Total_Active_Projects__c, (SELECT id,name FROM relationShipName) FROM Account WHERE Id IN :set_id];
        
        for(Account acc: acc_list) {
            if(acc.relationShipName.size() > 0) 
                acc.Total_Active_Projects__c = acc.relationShipName.size();
            else
                acc.Total_Active_Projects__c = 0;
        }
        if(!acc_list.isEmpty())
            update acc_list;
    }
}

All Answers

Frédéric TrébuchetFrédéric Trébuchet
Hi,

If you have a master-detail relationship between Account and Project, using a Roll-up Summary field of type Count should be the best solution.
If for any reason you have to use a trigger to count depending projects, you have to use a trigger to count down when projects are deleted or detached from their account.

Hope this helps,
Fred
 
Chad MoutesChad Moutes
The two objects are related by a lookup, that is why I had to write a trigger instead of using a normal Roll-up Summary Field.
Frédéric TrébuchetFrédéric Trébuchet
So you have to use a delete/undelete trigger on Project object in which you will update the Account's field where the number of dependent projects is stored. This can help you http://blog.jeffdouglas.com/2011/08/23/salesforce-trigger-when-rollups-summaries-not-possible/.

It seems there is also a "tricky" way to use roll-up summary on lookup relationships https://developer.salesforce.com/page/Declarative_Rollup_Summary_Tool_for_Force.com_Lookup_Relationships.

Fred
Chad MoutesChad Moutes
Here, this is my code. Maybe this will help you help me lol.
  
trigger RollupActiveProjectsCountToAccountTrigger on Project__c (after insert, after update, after delete, after undelete) {

   List<Id>AccountIds = new List<Id>();    
   
   If(Trigger.isInsert || Trigger.isUndelete || Trigger.isUpdate){
       for(Project__c p: Trigger.new){
           AccountIds.add(p.Company_Name__c);
       }
   }
   
   If(Trigger.isDelete){
       for(Project__c p: Trigger.old){
               AccountIds.add(p.Company_Name__c);
       }
   }
   
   AggregateResult[] groupedResults = [SELECT count(id)projectCount, Company_Name__c FROM Project__c WHERE Company_Name__c in :AccountIds and Active_Project__c = TRUE GROUP BY Company_Name__C];
   
   Map<Id,Account>accountMap = new Map<Id,Account>([SELECT Id, Total_Active_Projects__c FROM Account WHERE Id in :AccountIds]);
   
   for(AggregateResult ar: groupedresults) {
       accountMap.get((Id)ar.get('Company_Name__c')).Total_Active_Projects__c = (decimal)ar.get('Projectcount');
   }
   
   try {
       update accountMap.values();
   } catch(DmlException e) {
       System.debug(e.getMessage());
   }

}

 
SunidharSunidhar
I think This trigger will work for u

trigger RollupActiveProjectsCountToAccountTrigger on Project__c (after insert, after update, after delete, after undelete) { 
    set<Id> set_id = new set<Id>();
    
    for(Project__c prj: trigger.new) {
        set_id.add(prj.Company_Name__c);
    }
    
    if(trigger.isAfter && (trigger.isUpdate || trigger.isInsert || trigger.isDelete)) {
        acc_list = [SELECT id, Total_Active_Projects__c, (SELECT id,name FROM relationShipName) FROM Account WHERE Id IN :set_id];
        
        for(Account acc: acc_list) {
            if(acc.relationShipName.size() > 0) 
                acc.Total_Active_Projects__c = acc.relationShipName.size();
            else
                acc.Total_Active_Projects__c = 0;
        }
        if(!acc_list.isEmpty())
            update acc_list;
    }
}

Note: 'relationShipName' is a relation ship API name which exist between Account and Project.
Correct me if i am wrong, if it works fine for ur requirement select this one as best answer
Frédéric TrébuchetFrédéric Trébuchet
@Chad
No time to try but, you've declared variable "AccountIds" as a list of ids (line 3) but push "Company_Name__c" to it (lines 7 and 13) and use it for filtering on company_name__c (line 17) and on Account's id (line 19).
You have 2 variables, 1 for company_name__c and 1 for account's ids.
Not checked the rest of your code but I think you should start with that.
Suggestion: look at Jeff Douglas's blog (1st link) for a complete example with best practice (separating move business logic out of code trigger using Apex class).

@bsunidhar1
Trigger.new can be used only on insert and update trigger

Fred
SunidharSunidhar
Oh sry about that, Here is my modified code

trigger RollupActiveProjectsCountToAccountTrigger on Project__c (after insert, after update, after delete, after undelete) { 
    set<Id> set_id = new set<Id>();
    
    if(trigger.isInsert || trigger.isUpdate) {
        for(Project__c prj: trigger.new) {
            set_id.add(prj.Company_Name__c);
        }
    }
    else if(trigger.isDelete) {
        for(Project__c prj: trigger.old) {
            set_id.add(prj.Company_Name__c);
        }
    }
    
    if(trigger.isAfter && (trigger.isUpdate || trigger.isInsert || trigger.isDelete)) {
        acc_list = [SELECT id, Total_Active_Projects__c, (SELECT id,name FROM relationShipName) FROM Account WHERE Id IN :set_id];
        
        for(Account acc: acc_list) {
            if(acc.relationShipName.size() > 0) 
                acc.Total_Active_Projects__c = acc.relationShipName.size();
            else
                acc.Total_Active_Projects__c = 0;
        }
        if(!acc_list.isEmpty())
            update acc_list;
    }
}
This was selected as the best answer
Chad MoutesChad Moutes
When you say relationShipName, are you talking about the look-up field that connects these two. Because if you are that is Company_Name__c which is a look-up field on the object Project__c and looks up to the object Account.
Chad MoutesChad Moutes
@bsunidhar

If that is what you are talking about. I substituted that in, and this is what my code looks like now:
  
trigger RollupActiveProjectsCountToAccountTrigger on Project__c (after insert, after update, after delete, after undelete) { 
    set<Id> set_id = new set<Id>();
    
    if(trigger.isInsert || trigger.isUpdate) {
        for(Project__c prj: trigger.new) {
            set_id.add(prj.Company_Name__c);
        }
    }
    else if(trigger.isDelete) {
        for(Project__c prj: trigger.old) {
            set_id.add(prj.Company_Name__c);
        }
    }
    
    if(trigger.isAfter && (trigger.isUpdate || trigger.isInsert || trigger.isDelete)) {
        acc_list = [SELECT id, Total_Active_Projects__c, (SELECT id,name FROM Company_Name__c.size) FROM Account WHERE Id IN :set_id and Active_Project__c = TRUE];
        
        for(Account acc: acc_list) {
            if(acc.Company_Name__c.size() > 0) 
                acc.Total_Active_Projects__c = acc.Company_Name__c.size.size();
            else
                acc.Total_Active_Projects__c = 0;
        }
        if(!acc_list.isEmpty())
            update acc_list;
    }
}

But now I'm getting this error:

Compile Error: Didn't understand relationship 'Company_Name__c' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names. at line 16 column 20
SunidharSunidhar
Company_Name__c is a looup name not childRelation ship name, the image shows u the child realtionship name between account and contact,
likewise u need child relationship between Account and Project ObjectUser-added image
Chad MoutesChad Moutes
Oh alright, well i made that change and I'm no longer getting the error, but now I'm getting an error: Variable does not exist: acc_list at line 16 column 9

Any ideas?
Chad MoutesChad Moutes
I was able to fix the error, I just had to add List<Account>acc_list = new List<Account>(); at the top of the page, thank you for all of your help.
Chad MoutesChad Moutes
One more question, I have a field on the Project object called "Active_Project__c" and it is a check box field, and I only want this trigger to count the projects that have that field checked?
SunidharSunidhar
Here is the code

trigger RollupActiveProjectsCountToAccountTrigger on Project__c (after insert, after update, after delete, after undelete) { 
    set<Id> set_id = new set<Id>();
    
    if(trigger.isInsert || trigger.isUpdate) {
        for(Project__c prj: trigger.new) {
            set_id.add(prj.Company_Name__c);
        }
    }
    else if(trigger.isDelete) {
        for(Project__c prj: trigger.old) {
            set_id.add(prj.Company_Name__c);
        }
    }
    
    if(trigger.isAfter && (trigger.isUpdate || trigger.isInsert || trigger.isDelete)) {
        acc_list = [SELECT id, Total_Active_Projects__c, (SELECT id,name,Active_Project__c FROM relationShipName) FROM Account WHERE Id IN :set_id];
        
        Integer i;
        
        for(Account acc: acc_list) {
            i=0;
            if(acc.relationShipName.size() > 0) {
                for(Active_Project__c pr : acc.relationShipName) {
                    if(pr.Active_Project__c) {
                        i++;
                    }
                }
                    acc.Total_Active_Projects__c = i;
            }
            else
                acc.Total_Active_Projects__c = 0;
        }
        if(!acc_list.isEmpty())
            update acc_list;
    }
}