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
An_jaAn_ja 

need help adding conditional filtering to roll-up trigger

I am looking for a way to add conditional filtering to the "ctx.add( " portions below.

 

trigger OppRollup on Opportunity (after insert, after update, 
                                        after delete, after undelete) {
      // modified objects whose parent records should be updated
     Opportunity[] objects = null;   

     if (Trigger.isDelete) {
         objects = Trigger.old;
     } else {
        /*
            Handle any filtering required, specially on Trigger.isUpdate event. If the rolled up fields
            are not changed, then please make sure you skip the rollup operation.
            We are not adding that for sake of similicity of this illustration.
        */ 
        objects = Trigger.new;
     }

     /*
      First step is to create a context for LREngine, by specifying parent and child objects and
      lookup relationship field name
     */
     LREngine.Context ctx = new LREngine.Context(Account.SobjectType, // parent object
                                            Opportunity.SobjectType,  // child object
                                            Schema.SObjectType.Opportunity.fields.AccountId // relationship field name
                                            );     
     /*
      Next, one can add multiple rollup fields on the above relationship. 
      Here specify 
       1. The field to aggregate in child object
       2. The field to which aggregated value will be saved in master/parent object
       3. The aggregate operation to be done i.e. SUM, AVG, COUNT, MIN/MAX
     */
     ctx.add(
            new LREngine.RollupSummaryField(
                                            Schema.SObjectType.Account.fields.AnnualRevenue,
                                            Schema.SObjectType.Opportunity.fields.Amount,
                                            LREngine.RollupOperation.Sum 
                                         )); 
     ctx.add(
            new LREngine.RollupSummaryField(
                                            Schema.SObjectType.Account.fields.SLAExpirationDate__c,
                                               Schema.SObjectType.Opportunity.fields.CloseDate,
                                               LREngine.RollupOperation.Max
                                         ));                                       

     /* 
      Calling rollup method returns in memory master objects with aggregated values in them. 
      Please note these master records are not persisted back, so that client gets a chance 
      to post process them after rollup
      */ 
     Sobject[] masters = LREngine.rollUp(ctx, objects);    

     // Persiste the changes in master
     update masters;
}

 

Rahul_sgRahul_sg

need more info, did not undertand what is the actual requirement.

 

You can use If statement in APEX to put filters/validate data before adding into collection

An_jaAn_ja

Ultimately i will need about 12 more roll-up fields (at max already), all looking at records related to parent object. each is a sum of a certain record type, with one field that is a picklist.

 

each roll-up will count the number of records for that record type with a certain value from the picklist

 

I tried an if statement, but it could not do what I wanted.

 

I tried to modity the code in this portion: but was not successful - one example would be to get a rollup of related opps where the stage was prosepecting, one where stage was qualified, etc. - hope this makes sense

 

ctx.add(
            new LREngine.RollupSummaryField(
                                            Schema.SObjectType.Account.fields.AnnualRevenue,
                                            Schema.SObjectType.Opportunity.fields.Amount,
                                            LREngine.RollupOperation.Sum 
                                         )); 
An_jaAn_ja

Hello, this is the class for this trigger:

 

/*
Copyright (c) 2012 tgerm.com
All rights reserved.

Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions
are met:

1. Redistributions of source code must retain the above copyright
   notice, this list of conditions and the following disclaimer.
2. Redistributions in binary form must reproduce the above copyright
   notice, this list of conditions and the following disclaimer in the
   documentation and/or other materials provided with the distribution.
3. The name of the author may not be used to endorse or promote products
   derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE AUTHOR "AS IS" AND ANY EXPRESS OR
IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT, 
INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT
NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
(INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF
THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
/**
    LREngine("L"ookup "R"ollup Engine) : This class simplifies rolling up on the child records in lookup relationship.
*/
public class LREngine {

    /*
        Tempalte tokens
            0 : Fields to project
            1 : Object to query
            2 : Optional WHERE clause filter to add
            3 : Group By field name
    */
    static String SOQL_TEMPLATE = 'SELECT {0} FROM {1} {2} GROUP BY {3}';

    /**
        Key driver method that rolls up lookup fields based on the context. This is specially useful in Trigger context.

        @param ctx Context the complete context required to rollup
        @param detailRecordsFromTrigger child/detail records which are modified/created/deleted during the Trigger 
        @returns Array of in memory master objects. These objects are not updated back to the database
                because we want client or calling code to have this freedom to do some post processing and update when required.
    */
    public static Sobject[] rollUp(Context ctx, Sobject[] detailRecordsFromTrigger) {

        // API name of the lookup field on detail sobject
        String lookUpFieldName = ctx.lookupField.getName();

        Set<Id> masterRecordIds = new Set<Id>(); 
        for (Sobject kid : detailRecordsFromTrigger) {
            masterRecordIds.add((Id)kid.get(lookUpFieldName));
        }
        return rollUp(ctx, masterRecordIds);
    }

    /**
        Key driver method that rolls up lookup fields based on the context. This is meant to be called from non trigger contexts like
        scheduled/batch apex, where we want to rollup on some master record ids.

        @param Context the complete context required to rollup
        @param masterIds Master record IDs whose child records should be rolled up. 
        @returns Array of in memory master objects. These objects are not updated back to the database
                because we want client or calling code to have this freedom to do some post processing and update when required.
    */
    public static Sobject[] rollUp(Context ctx,  Set<Id> masterIds) {
        // K: Id of master record
        // V: Empty sobject with ID field, this will be used for updating the masters
        Map<Id, Sobject> masterRecordsMap = new Map<Id, Sobject>(); 
        for (Id mId : masterIds) {
            masterRecordsMap.put(mId, ctx.master.newSobject(mId));
        }

        // #0 token : SOQL projection
        String soqlProjection = ctx.lookupField.getName();
        // k: detail field name, v: master field name
        Map<String, String> detail2MasterFldMap = new Map<String, String>();
        for (RollupSummaryField rsf : ctx.fieldsToRoll) {
            // create aggreate projection with alias for easy fetching via AggregateResult class
            // i.e. SUM(Amount) Amount
            soqlProjection += ', ' + rsf.operation + '(' + rsf.detail.getName() + ') ' + rsf.detail.getName();
            detail2MasterFldMap.put(rsf.detail.getName(), rsf.master.getName());
        }

        // #1 token for SOQL_TEMPLATE
        String detailTblName = ctx.detail.getDescribe().getName();
        
        // #2 Where clause
        String whereClause = '';
        if (ctx.detailWhereClause != null && ctx.detailWhereClause.trim().length() > 0) {
            whereClause = 'WHERE ' + ctx.detailWhereClause ;
        }
        
        // #3 Group by field
        String grpByFld = ctx.lookupField.getName();
        
        String soql = String.format(SOQL_TEMPLATE, new String[]{soqlProjection, detailTblName, whereClause, grpByFld});
        // aggregated results 
        List<AggregateResult> results = Database.query(soql);
        
        for (AggregateResult res : results){
            Id masterRecId = (Id)res.get(grpByFld);
            Sobject masterObj = masterRecordsMap.get(masterRecId);
            if (masterObj == null) {
                System.debug(Logginglevel.WARN, 'No master record found for ID :' + masterRecId);
                continue;
            }

            for (String detailFld : detail2MasterFldMap.keySet()) {
                Object aggregatedDetailVal = res.get(detailFld);
                masterObj.put(detail2MasterFldMap.get(detailFld), aggregatedDetailVal);
            }           
        }
        
        return masterRecordsMap.values();   
    }




    /**
        Exception throwed if Rollup Summary field is in bad state
    */
    public class BadRollUpSummaryStateException extends Exception {}

   /**
       Which rollup operation you want to perform 
    */ 
    public enum RollupOperation {
        Sum, Max, Min, Avg, Count
    }
    
    /**
        Represents a "Single" roll up field, it contains
        - Master field where the rolled up info will be saved
        - Detail field that will be rolled up via any operation i.e. sum, avg etc 
        - Operation to perform i.e. sum, avg, count etc
            
    */
    public class RollupSummaryField {
        public Schema.Describefieldresult master;
        public Schema.Describefieldresult detail;
        public RollupOperation operation;
        
        // derived fields, kept like this to save script lines later, by saving the same
        // computations over and over again
        public boolean isMasterTypeNumber;
        public boolean isDetailTypeNumber;
        public boolean isMasterTypeDateOrTime;
        public boolean isDetailTypeDateOrTime; 
        
        public RollupSummaryField(Schema.Describefieldresult m, 
                                         Schema.Describefieldresult d, RollupOperation op) {
            this.master = m;
            this.detail = d;
            this.operation = op;
            // caching these derived attrbutes for once
            // as their is no view state involved here
            // and this caching will lead to saving in script lines later on
            this.isMasterTypeNumber = isNumber(master.getType());
            this.isDetailTypeNumber = isNumber(detail.getType());
            this.isMasterTypeDateOrTime = isDateOrTime(master.getType());
            this.isDetailTypeDateOrTime = isDateOrTime(detail.getType()); 
            // validate if field is good to work on later 
            validate();
        }   
        
        void validate() {
            if (master == null || detail == null || operation == null) 
                throw new BadRollUpSummaryStateException('All of Master/Detail Describefieldresult and RollupOperation info is mandantory');

            if ( (!isMasterTypeDateOrTime && !isMasterTypeNumber) 
                  || (!isDetailTypeDateOrTime && !isDetailTypeNumber)) {
                    throw new BadRollUpSummaryStateException('Only Date/DateTime/Time/Numeric fields are allowed');
            }
            
            if (isMasterTypeDateOrTime && (RollupOperation.Sum == operation || RollupOperation.Avg == operation)) {
                throw new BadRollUpSummaryStateException('Sum/Avg doesnt looks like valid for dates ! Still want, then implement the IRollerCoaster yourself and change this class as required.');
            }
        }
        
        boolean isNumber (Schema.Displaytype dt) {
            return dt == Schema.Displaytype.Currency 
                   || dt == Schema.Displaytype.Integer
                   || dt == Schema.Displaytype.Percent
                   || dt == Schema.Displaytype.Double;
        }
        
        boolean isDateOrTime(Schema.DisplayType dt) {
            return dt == Schema.Displaytype.Time 
                   || dt == Schema.Displaytype.Date
                   || dt == Schema.Displaytype.Datetime;
        }
    }
    
    /**
        Context having all the information about the rollup to be done. 
        Please note : This class encapsulates many rollup summary fields with different operations.
    */
    public class Context {
        // Master Sobject Type
        public Schema.Sobjecttype master;
        // Child/Details Sobject Type
        public Schema.Sobjecttype detail;
        // Lookup field on Child/Detail Sobject
        public Schema.Describefieldresult lookupField;
        // various fields to rollup on
        public List<RollupSummaryField> fieldsToRoll;

        // Where clause or filters to apply while aggregating detail records
        public String detailWhereClause;            

        public Context(Schema.Sobjecttype m, Schema.Sobjecttype d, 
                           Schema.Describefieldresult lf) {
            this(m, d, lf, '');                             
        }
        
        public Context(Schema.Sobjecttype m, Schema.Sobjecttype d, 
                           Schema.Describefieldresult lf, String detailWhereClause) {
            this.master = m;
            this.detail = d;
            this.lookupField = lf;
            this.detailWhereClause = detailWhereClause;
            this.fieldsToRoll = new List<RollupSummaryField>();
        }

        /**
            Adds new rollup summary fields to the context
        */
        public void add(RollupSummaryField fld) {
            this.fieldsToRoll.add(fld);
        }
    }    
}

 the recommendation for adding conditional filtering to the trigger was as follows:

LREngine.Context ctx = new LREngine.Context(Account.SobjectType, 
                            Opportunity.SobjectType, 
                            Schema.SObjectType.Opportunity.fields.AccountId, detailRecords,
                            'Amount > 200' // filter out any opps with amount less than 200
                            );

 the error I got when I tried to add something like this was that "Variable does not exist: detailRecords"