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
Kamal Kishore SinghKamal Kishore Singh 

Self Hierarcky lookup ,Roll Up Summary

Hello Everyone,

I have a challenging requirement for one my project .I want to have a trigger where i need to sum up a field xxx from childs in self lookup .
For Example if A is parent of B and B is parent of C .If value of field xxx changes from x to y  in Record C.The sum of field xxx should be calculated for all parents.This is possible if we use recursive trigger .

The challege is to do it in ,one single update.

Has any body done this before.

Regards
 
JeffreyStevensJeffreyStevens
So, you're saying C is a child of B.  B is a child of A.  When a value changes in C, this should happen...

Total all children who have B as a parent - update B.
Total all children who have A as a parent - update A.

Is that what you're trying to do?
Kamal Kishore SinghKamal Kishore Singh

Yes ,I need to update a field on parent and value should be the sum of a decimal field from all children in self hierarchy,and I need to do it in single update.
JeffreyStevensJeffreyStevens

 
What about something like this?  It may not be totally correct, but I think it'd be a starting point.  

Basically - I think you'll want to think in terms of child, parents, and grand parents.  You'll want to gather the parent Ids for the children that fired the trigger.  Then with those IDs, get and total all of the child for those parents.  Keep those totals in a map of <ParentId,Amount>.  Then you'll want to get all of the grandparents IDs from the parents.  Then get ALL of the parents using the grandparent IDs, building another map of <grandparentId,amount>.  With those two maps, get a LIST of accounts and update the Amt__c field.

Make sense?  It is a bit of a complicated process.
 
trigger totalAmount on Account (after update) {

  // Will need to use a Run Once technique so it doesn't get recursively called - I can post something on that if needed.

  // Get parents of accounts that fired the trigger
  set<id> parentIds = new set<id>();
  for(Account a :trigger.new) {
    if(a.ParentId != null) {
      parentIds.add(a.ParentId);
    }
  }

  // Get children of those parents & sum amount
  list<AggregateResult> childARs = [SELECT ParentId parentId, SUM(Amt__c) amtTotal
                                                           FROM Account
                                                           WHERE ParentId in :parentIds
                                                           GROUP BY parentId


  map<id,decimal> mParentAmounts = new map<id,decimal>();
  for (aggregateResult ar :childARs) {
    mParentAmounts.put(string.valueOf(ar.get('parentId')), decimal.valueOf(string.valueof(ar.get('amtTotal'))));
  }

  // Now get the Grandparent Ids
  list<Account> parents = new list<Account>([SELECT id,parentId FROM Account WHERE Id IN :mParentAmounts.keyset()]);
  set<id> grandParentIds = new set<id>();
  for(Account a :parents) {
    grandParentIds.add(a.parentId);
  }

  // Get all the children of the grandparents & sum totals, replacing the total in the mParentAmounts map
  list<Account> parents2 = new list<Account>([SELECT id,parentId,Amt__c FROM Account WHERE id IN :grandParentIds]);
  map<id,decimal> mGrandParentAmt = new map<id,decimal>();
  for(Account a :parents2) {
    decimal grandParentAmt = 0.00;
    if(mGrandParent.containsKey(a.parentId) {
      grandParentAmt = mGrandParent.get(a.parentId);
    }
    if(mParentAmounts.containsKey(a.id)) {
      grandParentAmt = grandParentAmt + mParentAmounts.get(a.id);    // Can't use the amt from the SOQL, as we just created a new amount for the parents
    } else {
      grandParentAmt = grandParentAmt + a.Amt__c;
    }
    mGrandParentAmt.put(a.parentId,grandParentAmt);
  }

// At this point we have two maps....
//     mParentAmounts and mGrandparentAmts  
//     Now, get those account records and update the amount
list<account> accountsToUpdate = new list<Account>([SELECT id,Amt__c FROM Account WHERE id IN :mParentsAmounts.keyset() OR id IN :mGrandparentsAmts.keyset()]);
for(Account a :accountsToUpdate) {
  if(mParentsAmounts.containsKey(a.id)) {
    a.Amt__c = mParentsAmounts.get(a.id);
  }
  if(mGrandparentsAmt.containsKey(a.id)) {
    a.Amt__c = mGrandparentsAmt.get(a.id);
  }
}

Update accountsToUpdate;   

}

 
JeffreyStevensJeffreyStevens
for the Run Once techique........



Run Once technique (useful in triggers)
Create a utilities class/method like this…
Public with sharing class util_utilities {
  Public static Boolean alreadyExecuted;
}
Then use like this…
  If (util_utilities.alreadyExecuted != true) {
        Util_utilities.alreadyExecuted = true;
        // Other Code to be executed only once…


 
Kamal Kishore SinghKamal Kishore Singh
Hi  JeffreyStevens

Thanks for same.It is a good code to begin with and I have developed on a similar pattern.The problem is how to reach till ultimate parent.
Kamal Kishore SinghKamal Kishore Singh
and what happens if the parent changes in between
 
JeffreyStevensJeffreyStevens
Ya - that could be tough.  What about a series of @futures that call each other?  Each one would do only one level, until there isn't a next higher up level?
Kamal Kishore SinghKamal Kishore Singh
Ya I am also thinking on similar lines or to create a batch.Right now I am using to recursive trigger till I have no parent for child.Update fires an update till the time no parent is left.
Kamal Kishore SinghKamal Kishore Singh
I have some reads if you are intersted.
https://github.com/abhinavguptas/Salesforce-Lookup-Rollup-Summaries/blob/master/classes/LREngine.cls

https://github.com/afawcett/declarative-lookup-rollup-summaries
Kiran Hunnaragi 17Kiran Hunnaragi 17
Hi Kamal,

I have the same requirement, do you have the solution for the above question. 

Please help!

Thank you.