I couldn’t be happier that the new 18.0 (Spring ’10) API Release from SalesForce.com now supports aggregate functions in SOQL.

Links to resources:

• http://developer.force.com/releases/release/Spring10/Aggregate+Functions
• http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_select.htm

The new SUM() and GROUP BY features can really simplify writing custom Roll-up Triggers.

Below is a relatively simple Trigger that rolls up Budget numbers by the Previous, Current and Next year to the parent Program__c object. By using the Calendar_Year() function, you can see how easy it is to create a single SOQL statement that does the summing in a single statement.

trigger Program_Budget_Rollup on Program_Budget__c (after delete, after insert, after update) {

    Set<String> programIDs = new Set<String>();

    //************************************************
    // Build a LIST of Program ID's that will
    // need recalculating
    //************************************************
    if(Trigger.isInsert || Trigger.isUpdate){
        for(Program_Budget__c te : trigger.new){
            if(te.Program__c != null){
                if(!programIDs.contains(te.Program__c)) programIDs.add(te.Program__c);
            }
        }
    }  // INSERT/UPDATE Trigger

    if(Trigger.isDelete || Trigger.isUpdate){
        for(Program_Budget__c te : trigger.old){
            if(te.Program__c != null){
                if(!programIDs.contains(te.Program__c)) programIDs.add(te.Program__c);
            }
        }
    }  // DELETE/UPDATE Trigger

    if(programIDs .size() > 0) {

        Map<ID, Program__c> programs = new Map<ID, Program__c>();
        Program__c d = null;

        for (AggregateResult dr : [SELECT Program__c, CALENDAR_YEAR(Date_c) Year, SUM(Amount__c) Amount
        FROM Program_Budget__c GROUP BY Program__c, CALENDAR_YEAR(Date__c)]) {

            String dID = (string)dr.Get('Program__c');
            // get the record or create a new one
            if (programs.get(dID) == null)
                d = new Program__c(ID = dID,
                Previous_Year_Budget__c = 0,
                Current_Year_Budget__c = 0,
                Next_Year_Budget__c = 0,
                Total_Budget__c = 0);
            else
               d = programs.get(dID);

            // update the donation total fields
            Decimal amt = (Decimal)dr.Get('Amount');
            String fyr = (String)dr.GetDate('Year');
            Integer yr = Integer.valueOf(fyr);
            if (yr == Date.today().year()) d.Current_Year_Budget__c = amt;
            if (yr == (Date.today().year() - 1)) d.Previous_Year_Budget__c = amt;
            if (yr == (Date.today().year() + 1)) d.Next_Year_Budget__c = amt;
            d.Total_Budget__c = d.Total_Budget__c + amt;

            // push the record back into the Map
            programs.put(dID, d);
        }

        //commit the changes to Salesforce
        update programs.values();

    }

}

Tags: Apex Code, Apex Trigger, SalesForce.com
Posted in SalesForce.com