Rollup via Lookup Issue

Question

I put together a trigger that will perform a rollup summary (SUM) on a record that is associated via lookup. The trigger works perfectly if I want to include all the lookup records in the calculation.

I want to alter the code to only include certain records in the rollup summary. In particular, I only want to sum the "Value_for_MA__c" field if that record also has "Include_in_Settlement__c" set to True.

My code:

    public class LookupCalculation{

    public enum Method {COUNT, SUM, MIN, MAX, AVG} 

    private string sobjectParent, 
                   relationName, 
                   formulaParent, 
                   sobjectChild, 
                   parentfield, 
                   fieldChild,
                   fieldChild2;

    public LookupCalculation(string mysobjectParent, string myrelationName, string myformulaParent,
                             string mysobjectChild, string myparentfield, string myfieldChild, string myfieldChild2){
        sobjectParent = mysobjectParent;
        relationName = myrelationName;
        formulaParent = myformulaParent;
        sobjectChild = mysobjectChild;
        parentfield = myparentfield;
        fieldChild = myfieldChild;
        fieldChild2 = myfieldChild2;                         
    }

    public void Calculate(Method calculation, List<sobject> childList){
        set<Id> parentIdSet = new set<Id>();
        for(sobject sobj : childList)
            parentIdSet.add((Id) sobj.get(parentfield));
        string soqlParent = 'select id, (select ' + fieldChild + ' from ' + relationName + ') from ' + sobjectParent + ' , (select ' + fieldChild2 + ' from ' + relationName + ') from ' + sobjectParent + ' where fieldChild2 = True';
        List<sobject> parentList = Database.query(soqlParent);
        for(sobject parent : parentList){
            List<sobject> children = parent.getSObjects(relationName);
            if(children == null)
                children = new List<sobject>();
            Decimal counter = (mustSum(calculation))? 0 : null;
            if(calculation == Method.COUNT)
                counter = children.size();
            for(sobject child : children){
                Decimal value = (Decimal) child.get(fieldChild);
                if(mustSum(calculation) && value != null)
                    counter += value;
                else if(calculation == Method.MIN && (counter == null || value < counter))
                    counter = value;
                else if(calculation == Method.MAX && (counter == null || value > counter))
                    counter = value;
            }
            if(calculation == Method.AVG && children.size() > 0)
                counter = counter / children.size();
            parent.put(formulaParent, counter);
        }
        update parentList;
    }

    private boolean mustSum(Method calculation){
        return (calculation == Method.SUM || calculation == Method.AVG);
    }

}

Trigger

trigger RecRollup on Receiving__c (after insert,after update,after delete,after undelete) {

    /*******************TO BE CUSTOMIZED*********************/
    string mysobjectParent = 'Field__c',      // Parent sobject API Name
           myrelationName = 'RecRpts__r', // Api name of the relation between parent and child (ends with __r)
           myformulaParent = 'Rollup_RecRpts__c',        // Api name of the number field that will contain the calculation
           mysobjectChild = 'Receiving__c',  // Child sobject API Name
           myparentfield = 'Field__c', // Api name of the lookup field on chield object
           myfieldChild = 'Value_for_MA__c',  // Api name of the child field to roll up
           myfieldChild2 = 'Include_in_Settlement__c'; // Api name of the child field to use as a qualifier for rollup

    LookupCalculation.Method method = LookupCalculation.Method.SUM; //Selected method: could be COUNT, SUM, MIN, MAX, AVG
    /*******************************************************/

    LookupCalculation calculation = new LookupCalculation(mysobjectParent, myrelationName, myformulaParent,
                                                          mysobjectChild, myparentfield, myfieldChild, myfieldChild2);
    List<sobject> objList = new List<sobject>((List<sobject>) Trigger.new);
    if(Trigger.isDelete)
        objList = Trigger.old;
    if(Trigger.isUpdate)
        objList.addAll((List<sobject>) Trigger.old);
    calculation.calculate(method, objList);
}

Any help would be greatly appreciated.

Answer 1

From the code it looks like you already did an attempt..

This line currently does the query for child records:

    string soqlParent = 'select id, (select ' + fieldChild + ' from ' + relationName + ') from ' + sobjectParent + ' , (select ' + fieldChild2 + ' from ' + relationName + ') from ' + sobjectParent + ' where fieldChild2 = True';

But the filter on fieldChild2 is currently on the parent record instead of on the child record. Also there are 2 subqueries on the child record, which is usually not a good idea. I suggest changing this to:

    string soqlParent = 'select id, (select ' + fieldChild + ',' + fieldChild2 + ' from ' + relationName + ' where fieldChild2 = True'+ ') from ' + sobjectParent ;