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
Lakshmi SLakshmi S 

Calculate average value on lookup relation ship?

Hi Dev's,

I have a two objects Account and Contact
In account there is a custom field - contactscore__c
In contact there is a custom field - score__c
So, i want to calculate average value of all related contacts 'score__c'  and store the average value on account - 'contactscore__c' field;
How can we achieve this scenario using code?

Regards
Lakshmi.

Thanks in advance...
 
Best Answer chosen by Lakshmi S
Lakshmi SLakshmi S
Please check below code...

trigger AverageTrigger on Contact (after insert,after update,after delete,after undelete) {

        Set<Id> accids = new Set<Id>();
        Decimal contactscore;
        Decimal noofcontacts;
        if(trigger.isInsert || trigger.isUpDate || trigger.isUnDelete){
            for(Contact con : trigger.new){
                if(con.AccountId != null)
                accids.add(con.AccountId);
            }
        }
        if(trigger.isUpdate || trigger.isDelete){
            for(Contact oldcon: trigger.old){
                if(oldcon.AccountId != null)
                accids.add(oldcon.AccountId);
            }
        }
        
        if(accIds.size()>0){        
            List<Account> lst = [Select id,contactscore__c,no_of_Contacts__c,(Select id,score__c from Contacts) from Account where Id in : accIds];
            list<account> updateacc=new list<account>();
            list<account> updateacc2=new list<account>();
            for(Account acc : lst){
                
                List<AggregateResult> ar = [select avg(score__c)sc from contact where accountid = :acc.Id];
                for(AggregateResult a : ar){
                    contactscore = (Decimal)a.get('sc');
                }

                acc.contactscore__c = contactscore;
                acc.no_of_Contacts__c = acc.contacts.size();
                updateacc.add(acc);
            }
            
            update updateacc;
           
        }        

}
 

All Answers

Naveen KumarNaveen Kumar
Hi lakshmi,
Here the code for Your scenario

trigger CountContactsnew on Contact (After Update,After Insert ) {
   set<id> parentid = new set<id>(); 
   decimal all_subtotal;
    decimal all_subtotal1;
  
    for(Contact child:trigger.new)
    {
        if(child.Age__c!= NULL){
    
        parentid.add(child.AccountId);
    }        
    }
    list<Account> parent = [select Id,Contacts_sum__c,Contact_Rollup__c     from Account WHERE Id =:parentid];
  
    List<AggregateResult> pre_child_total = [select avg(Age__c),count(AccountId) from Contact where AccountId =:parentid];
 
    for (AggregateResult ar:pre_child_total) 
    {
    all_subtotal = (Decimal)ar.get('expr0');
        all_subtotal1 = (Decimal)ar.get('expr1');
       
         }

    list<Account> parentupdate = new list<Account>();
    
    for(Account pa:parent){
      pa.Contacts_sum__c = all_subtotal;
       pa.Contact_Rollup__c = all_subtotal1; 
       
        parentupdate.add(pa);
    }
    update parentupdate;
}

Thanks,
Naveen
 
Sukanya BanekarSukanya Banekar
Hi Lakshmi,

Try with following code
  
trigger contactAvgOnAccount on Contact (after  insert,after update,after delete,after undelete) {
    map<Id,Decimal> mapAccIdContactSum = new map<Id,Decimal>();
    List <Account> ac=new List<Account>();
    if(Trigger.isDelete) {
     Integer sumCon=0, cnt=1;
     Decimal Average= 0;
     for(Contact test: Trigger.Old) {
        if(string.valueof(test.numberofemployee__c) =='' && test.numberofemployee__c== NULL ){
         test.numberofemployee__c= 0;
         sumCon+= integer.valueOf(test.numberofemployee__c);
         cnt++;
        }
        else{
            sumCon+=integer.valueOf(test.numberofemployee__c);
        }
        Average= sumCon/cnt; 
        mapAccIdContactSum.put(test.accountid,Average);
        cnt=0;
     }   
   
   }
   else
   if
   (Trigger.isinsert || trigger.isundelete || trigger.isupdate) {
     integer sumCon=0,cnt=1;
     Decimal Average= 0;
     for(Contact test:Trigger.New) {
       if(string.valueof(test.numberofemployee__c) =='' && test.numberofemployee__c== NULL ){
         test.numberofemployee__c=0;
         sumCon+=integer.valueOf(test.numberofemployee__c);
         cnt++;
        }
        else{
            sumCon+=integer.valueOf(test.numberofemployee__c);
        }
        Average= sumCon/cnt; 
        mapAccIdContactSum.put(test.accountid,Average);
        cnt=0;
     }
   }

    ac=new List<Account>();
     for(Account c: [Select Id,Number_of_Contacts__c from Account where Id IN: mapAccIdContactSum.keySet()]){  
         c.Number_of_Contacts__c=mapAccIdContactSum.get(c.Id);
         ac.add(c);
     }
     update ac;
        
    }

Please let me know if this works for you.

Thanks,
Sukanya
Lakshmi SLakshmi S
Hi Sukanya,

Thanks for your reply.
your code not meets the criteria.
Lakshmi SLakshmi S
Hi Naveen,

Thanks for your reply.

your code not satisfy my criteria.
Lakshmi SLakshmi S
Please check below code...

trigger AverageTrigger on Contact (after insert,after update,after delete,after undelete) {

        Set<Id> accids = new Set<Id>();
        Decimal contactscore;
        Decimal noofcontacts;
        if(trigger.isInsert || trigger.isUpDate || trigger.isUnDelete){
            for(Contact con : trigger.new){
                if(con.AccountId != null)
                accids.add(con.AccountId);
            }
        }
        if(trigger.isUpdate || trigger.isDelete){
            for(Contact oldcon: trigger.old){
                if(oldcon.AccountId != null)
                accids.add(oldcon.AccountId);
            }
        }
        
        if(accIds.size()>0){        
            List<Account> lst = [Select id,contactscore__c,no_of_Contacts__c,(Select id,score__c from Contacts) from Account where Id in : accIds];
            list<account> updateacc=new list<account>();
            list<account> updateacc2=new list<account>();
            for(Account acc : lst){
                
                List<AggregateResult> ar = [select avg(score__c)sc from contact where accountid = :acc.Id];
                for(AggregateResult a : ar){
                    contactscore = (Decimal)a.get('sc');
                }

                acc.contactscore__c = contactscore;
                acc.no_of_Contacts__c = acc.contacts.size();
                updateacc.add(acc);
            }
            
            update updateacc;
           
        }        

}
 
This was selected as the best answer