function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
MissaDevLorealMissaDevLoreal 

Help with Sum in a Trigger

i'm not new to this board but had to create a new log in due to a new role with a different company.  Please excuse my newness LOL. 

 

I have the following piece of code and for the life of me can't figure out why count () will work but sum(fieldname) won't.  I haven't written code in some time so a refresher would be helpful.  I'm trying to sum all the child records sph value and place this value on the parent obejct in a field called sph.  what am I missing.

trigger Add_Sales_Actuals on Sales_Actuals__c(after insert, after update) {
 
    //Student_Call_Log are the related records
    //Student is the "main" record
    //Idea is to update a field on the main record whenever an edit is made to a related record   
    
    Sales_Actuals__c [] sa = Trigger.new;    
    String bapid = null;
    
    bapid = sa[0].BA_Position__c;        
    if(sa[0].Reporting_date__c != null  )  
        {               
            integer i = [select sum(sph__c) from Sales_Actuals__c where BA_Position__c = :bapid 
                                                                  ];           
            // update the master record  
          
           BA_Position__c [] baps =[select id, SPH__c     
                                          from BA_Position__c where id = :bapid];
                          
                            
               baps[0].SPH__c = i;               
             
             // write to database    
                 
             update baps[0];      
             i = 0;     
           }
 }

 

manuel-jose-condemanuel-jose-conde

You should use AggregateResult when calling queries with functions.

Also, your trigger is not prepared for bulk operations...

jwhartfieldjwhartfield

 Bulkification can get tricky, so for learning purposes here is how I would write it with the assumption that only one record is getting updated at a time.  If you are not sure what I'm talking about, check this out.

 

To use sum, you have to build what is called an Aggregate query.  Count() works because it is treated special, even though it is technically an aggregate type query.  They return results of type AggregateResult.

 

More here: Working with SOQL Aggregate Functions

 

WARNING:  COMPLETELY UNTESTED CODE BELOW - but it should get you started.

 

trigger Add_Sales_Actuals on Sales_Actuals__c(after insert, after update) {
 
    // JH - Since this is not bulkified, this makes sure that if someone updates a whole bunch
// of records at once this won't blow up your customer's ORG. But it also won't run the trigger.
if(trigger.new.size() > 1) return;

//Student_Call_Log are the related records //Student is the "main" record //Idea is to update a field on the main record whenever an edit is made to a related record Sales_Actuals__c [] sa = Trigger.new; String bapid = null; bapid = sa[0].BA_Position__c; if(sa[0].Reporting_date__c != null ) { //integer i = [select sum(sph__c) from Sales_Actuals__c where BA_Position__c = :bapid];
// JH - here is the aggregate part I was talking about. Note you need to 'group by' anything you
//are summing.
AggregateResult[] ars = [select sum(sph__c) SPHSum
from Sales_Actuals__c
where BA_Position__c = :bapid
GROUP BY sph__c
];

 integer i = (integer)ars[0].get('SPHSum');
// update the master record BA_Position__c [] baps =[select id, SPH__c from BA_Position__c where id = :bapid];
baps[0].SPH__c = i; // write to database update baps[0]; } }