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
Olga Kim 5Olga Kim 5 

A field on a parent Object that shows AVERAGE amount from child object records?

I have a requirement to create a custom field called Average_Utility__c  on a parent object - Property__c that will show average sum of the child object records - Utility__c. 
  
I tried to create a trigger and a class that uses aggregate AVG function to fetch the records of amount_paid field from Utility__c.

But my problem is I don't know how to make it to recalculate Average_Utility__c  amount every time when new utility record is created. 

This is my code:         
public class AvgUtility {
public static void avgUtility (list<property__c> propList)   
        
        { 
      //   list <property__c> propList=new List<property__c>();
          
       
       
        list<property__c> PropertyList=[select id,name, Average_Utility__c from property__c]; 
                    
        for(property__c prop :PropertyList)
            
        {
            if (prop.Average_Utility__c==null || prop.Average_Utility__c!=null)
            { 
      AggregateResult[] groupedResults  = [SELECT Property__r.ID, AVG(Amount_Paid__c)  FROM Utility__c GROUP BY Property__r.ID];

         for (AggregateResult ar : groupedResults){
                prop.id =(id)ar.get ('Property__c');
                 prop.Average_Utility__c=(decimal)ar.get(('expr0'));
                propList.add(prop);
            }
         }            
         }
        insert propList;
        }
}


Trigger Class
trigger AvgUtilityTrigger on Utility__c (after insert) {
    
    for (Utility__c ut:trigger.new)
    {
        AvgUtility.avgUtility();
    }
        
   }

I will appreciate any advice!  
Best Answer chosen by Olga Kim 5
ayu sharma devayu sharma dev
Hello 

Replace trigger code with below code:
  
trigger AvgUtilityTrigger on Utility__c (after insert, after update, after delete) {
    
    if( Trigger.isInsert || Trigger.isAfter || Trigger.isDelete ){
        if( Trigger.isAfter ){
            AvgUtility.avgUtility();
        }
    }
        
}

Also made some changes in class code as :
  
public class AvgUtility {
    public static void avgUtility (){
        Set<Id> propertyIds = new Set<Id>();
        List<Property__c> propertyToUpdate = new List<Property__c>();
        List<Utility__c> utilities = Trigger.isInsert || Trigger.isUpdate ? Trigger.New : Trigger.Old;
        for( Utility__c ut : utilities ){
            propertyIds.add( ut.Property__c /*lookup field value of utilityrecord */ );
        }

        for( AggregateResult ag : [ SELECT Property__c, AVG( Amount_Paid__c ) avg FROM Utility__c 
                                    GROUP BY Property__c ] ){
            propertyToUpdate.add( new Property__c( 
                Id = (Id)ag.get('Property__c'), 
                Average_Utility__c = (Decimal)ag.get('avg') ) );                            
        }

        if( propertyToUpdate.size() > 0 ){
            update propertyToUpdate;
        }
    }
}

Try these codes and let me know the results.

Thanks and Regards
Ayush

All Answers

ayu sharma devayu sharma dev
Hello Olga

Please try the below code for your class, And also run the Trigger on After delete and After update.
  
public class AvgUtility {
    public static void avgUtility (){
        Set<Id> propertyIds = new Set<Id>();
        List<Property__c> propertyToUpdate = new List<Property__c>();

        for( Utility__c ut : Trigger.New ){
            propertyIds.add( ut.Property__c /*lookup field value of utilityrecord */ );
        }

        for( AggregateResult ag : [ SELECT Property__c, AVG( Amount_Paid__c ) avg FROM Utility__c 
                                    GROUP BY Property__c ] ){
            propertyToUpdate.add( new Property__c( 
                Id = (Id)ag.get('Property__c'), 
                Average_Utility__c = (Decimal)ag.get('avg') ) );                            
        }

        if( propertyToUpdate.size() > 0 ){
            update propertyToUpdate;
        }
    }
}

Please try the above code and let me know the results. If it solves your problem please close this thread with the best solution.

Thanks and Regards
Ayush Sharma
Olga Kim 5Olga Kim 5
Thank you, Ayush a lot. It did work!
Could you, please, help me to write the code for after update and after insert trigger? 
I just started to learn coding in Salesforce. I am not sure how the code should look like.

I really appreciate your help. Thanks again  
ayu sharma devayu sharma dev
Hello 

Replace trigger code with below code:
  
trigger AvgUtilityTrigger on Utility__c (after insert, after update, after delete) {
    
    if( Trigger.isInsert || Trigger.isAfter || Trigger.isDelete ){
        if( Trigger.isAfter ){
            AvgUtility.avgUtility();
        }
    }
        
}

Also made some changes in class code as :
  
public class AvgUtility {
    public static void avgUtility (){
        Set<Id> propertyIds = new Set<Id>();
        List<Property__c> propertyToUpdate = new List<Property__c>();
        List<Utility__c> utilities = Trigger.isInsert || Trigger.isUpdate ? Trigger.New : Trigger.Old;
        for( Utility__c ut : utilities ){
            propertyIds.add( ut.Property__c /*lookup field value of utilityrecord */ );
        }

        for( AggregateResult ag : [ SELECT Property__c, AVG( Amount_Paid__c ) avg FROM Utility__c 
                                    GROUP BY Property__c ] ){
            propertyToUpdate.add( new Property__c( 
                Id = (Id)ag.get('Property__c'), 
                Average_Utility__c = (Decimal)ag.get('avg') ) );                            
        }

        if( propertyToUpdate.size() > 0 ){
            update propertyToUpdate;
        }
    }
}

Try these codes and let me know the results.

Thanks and Regards
Ayush
This was selected as the best answer
Olga Kim 5Olga Kim 5
It worked!!! Thank you for your help!