Skip to main content
Topics#ApexDomanda di Olga Kim
Olga Kim (Olga Kim) ha fatto una domanda in #Apex
23 mar 2020, 17:36

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!  
4 risposte
  1. Ayush Sharma (Appsavio Software Private Limited)
    24 mar 2020, 16:13
    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
Scrivi una risposta...
0/9000