function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
ekorzekorz 

Apex trigger to copy formula field, to allow roll-up

Hi Everybody,

 

I have a cross-object formula field, and my goal is to roll it up to the parent object.  Since roll-ups on those types of formula fields aren't allowed under standard SFDC practice, I thought I'd write a trigger that copies the field, after insert/update, to a separate blank field (currency type in my case) and use *that* field as the roll-up target.  I assume it has to be after-insert/update so the formual field can calculate.

 

This code worked fine for any given record, but I'm stuck at how to bulkify the trigger in case I need to mass insert/mass update.

 

Trigger CopyfromFormulaToBlankField on Custom__c (after insert, after update) {

    List<Custom__c> clist = [SELECT Non_Formula_Field_for_Rollup__c, Formula_Field__c FROM Custom__c WHERE Id IN: Trigger.newMap.keySet()];
    
    for (Custom__c c :clist){
    
        if(lh.Non_Formula_Field_for_Rollup__c != lh.Formula_Field__c){
        
            lh.Non_Formula_Field_for_Rollup__c = lh.Formula_Field__c;
            update lhlist;
        
        }
    }
}

 

 

Any ideas?

Thanks!

 

Best Answer chosen by Admin (Salesforce Developers) 
JWykelJWykel

From the looks of it, you are using an after insert/update to update the same object. That doesn't seem right to me.
Regardless of that fact, you're selecting a list and looping through it, but you never actually reference the items in the list. Also, the list you are referencing in the update is not defined.

My recommendation regarding attempting to roll up children to parents would be to gather the parent Ids of the children and call an update on them, and have any rollups be performed in the parent trigger.

Something along these lines:

Trigger ChildTrigger( after insert, after update, after delete ){
    Set<Id> parentIds = new Set<Id>();
    
    if(Trigger.new != null){
        for(Child__c c : Trigger.new){ 
            parentIds.add(c.ParentId__c);
        }
    }

    if(Trigger.old != null){
        for(Child__c c : Trigger.old){
            parentIds.add(c.ParentId__c);
        }
    }

    if(!parentIds.isEmpty()){
        List<Parent__c> parents = new List<Parent__c>();
        for(Id i : parentIds){
            parents.add(new sObject(Id = i));
        }
        update parents;
    }
}

 The above loops through all children being modified (inserted, updated or deleted) and gathers their parent ids, then performs an update on those parents.  That fires the update trigger of the parents, which would be something like below, which uses the AggregateResult to set the sum (or whatever aggregate you use) of the children to the appropriate field on the parents.

Trigger ParentTrigger(before update){
    for(AggregateResult ag : [SELECT ParentId__c, SUM(ChildAmount__c) s FROM Child__c WHERE ParentId__c IN :Trigger.new]){
        Trigger.newMap.get((Id)ag.get('ParentId__c')).SumOfChildren__c = (Decimal)ag.get('s');
    }
}

 

Be warry that the Aggregate query still follows the limits of SF's queries, which means if you have a parent with 10,000 children or 10 parents with 1,000 children each, you will run into issues.

 

Alternatively:

If you are using a formula field that, in the end, needs to be rolled up via master/detail, you could also translate that formula into Apex and set a field via the trigger.

All Answers

JWykelJWykel

From the looks of it, you are using an after insert/update to update the same object. That doesn't seem right to me.
Regardless of that fact, you're selecting a list and looping through it, but you never actually reference the items in the list. Also, the list you are referencing in the update is not defined.

My recommendation regarding attempting to roll up children to parents would be to gather the parent Ids of the children and call an update on them, and have any rollups be performed in the parent trigger.

Something along these lines:

Trigger ChildTrigger( after insert, after update, after delete ){
    Set<Id> parentIds = new Set<Id>();
    
    if(Trigger.new != null){
        for(Child__c c : Trigger.new){ 
            parentIds.add(c.ParentId__c);
        }
    }

    if(Trigger.old != null){
        for(Child__c c : Trigger.old){
            parentIds.add(c.ParentId__c);
        }
    }

    if(!parentIds.isEmpty()){
        List<Parent__c> parents = new List<Parent__c>();
        for(Id i : parentIds){
            parents.add(new sObject(Id = i));
        }
        update parents;
    }
}

 The above loops through all children being modified (inserted, updated or deleted) and gathers their parent ids, then performs an update on those parents.  That fires the update trigger of the parents, which would be something like below, which uses the AggregateResult to set the sum (or whatever aggregate you use) of the children to the appropriate field on the parents.

Trigger ParentTrigger(before update){
    for(AggregateResult ag : [SELECT ParentId__c, SUM(ChildAmount__c) s FROM Child__c WHERE ParentId__c IN :Trigger.new]){
        Trigger.newMap.get((Id)ag.get('ParentId__c')).SumOfChildren__c = (Decimal)ag.get('s');
    }
}

 

Be warry that the Aggregate query still follows the limits of SF's queries, which means if you have a parent with 10,000 children or 10 parents with 1,000 children each, you will run into issues.

 

Alternatively:

If you are using a formula field that, in the end, needs to be rolled up via master/detail, you could also translate that formula into Apex and set a field via the trigger.

This was selected as the best answer
ekorzekorz

Hm, I like that approach better than what I was trying.  I'll give it a try and report back.  Thanks for the advice!
ekorzekorz

I think that worked as needed... still working on a few bugs (I don't think it updates if I delete the final child?).

 

Otherwise the code was pretty helpful.  for the next guy maybe you can update your solution to change your final "update parentIDs" line to read "update parents" 

 

That's what you meant right?

 

Thanks so much!
JWykelJWykel

Yes, that is exactly what I meant, good catch. I have edited that reply with the change.

Regarding the final child thing, you are correct, the parent trigger does not set it to '0', try this out:

Trigger ParentTrigger(before update){
    for(AggregateResult ag : [SELECT ParentId__c, SUM(ChildAmount__c) s FROM Child__c WHERE ParentId__c IN :Trigger.new]){
        Trigger.newMap.get((Id)ag.get('ParentId__c')).SumOfChildren__c = ag.get('s') == null ? 0 : (Decimal)ag.get('s');
    }
}

(That is untested and off the top of my head)  If that doesn't work, you should be able to come up with another way to set the parent to 0.

Also, here is a rollup class I made based on need and references to others that are out there (of which you can find links to on the page)

http://mochacidal.com/Projects/Salesforce/RollUp.aspx
ekorzekorz

Thanks again.  That addition didn't seem to do it, and I didn't understand the 3rd line entirly enough to modify it, so I just added a separate SF roll-up field on Parent checking the count of children, and adjusted the parent trigger to be "if childcount = 0, make my sumofchildren = 0, else your aggregatereult trigger.   Seems to work!