ShowAll Questionssorted byDate Posted
Lakshmi 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.

Best Answer chosen by Lakshmi S
Lakshmi S

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)
}
}
if(trigger.isUpdate || trigger.isDelete){
for(Contact oldcon: trigger.old){
if(oldcon.AccountId != null)
}
}

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();
}

update updateacc;

}

}

Naveen 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){

}
}
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;

}
update parentupdate;
}

Thanks,
Naveen

sukanya 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);
}
update ac;

}```

Please let me know if this works for you.

Thanks,
Sukanya
Lakshmi S
Hi Sukanya,

your code not meets the criteria.
Lakshmi S
Hi Naveen,

your code not satisfy my criteria.
Lakshmi S

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)
}
}
if(trigger.isUpdate || trigger.isDelete){
for(Contact oldcon: trigger.old){
if(oldcon.AccountId != null)
}
}

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();