2

I have following requirement in hand -

Update each account's field count__c in the Account Hierarchy.

where count__c = Count_of_Custom_Object_Records on Account + count from all Accounts who are child of Custom_Objects Parent Account.

Example :

              Acc1
            /     \
           /       \
          Acc2      Acc3
         /    \
        /      \
       Acc4     Acc5 
        |         | 
        |         |
   CustObjRec11    CustObjRec13--->Newly Inserted 
   CustObjRec12

Also --Acc1 , Acc2, Acc3 can also have one CustObjRecords each!!

Now, I have value in count__c as below -

(VALUES MANUALLY ENTERED / HAVE ONLY COUNT OF CustObjRecords of itself -Not added Child Accounts CustObjRecords)

Acc1 : 1

Acc2 : 1

Acc3 : 1

Acc4 : 2

Acc5 : 0

if I have above hierarchy with given counts - and I inserted new "CustObjRec13" for Acc5

then updated Count__c should be

Acc1 : 6= 1 + 4 + 1 (1 of Itself + 4 of Acc2 + 1 of Acc3)

Acc2 : 4 = 1 + 2 + 1 (1 of Itself + 2 of Acc4 + 1 of Acc5)

Acc3 : 1

Acc4 : 2

Acc5 : 1

Code I am trying (Excuse typo -if any) ** **

1) I can't use Rollup summary fields. (there is some sort of business reason)
2) I need to do this by writing trigger on "CustObjRecord" ONLY.

**

>     ** Few Parents are getting updated (upto 2 level - but in certain situations only) ------but not all accounts in hierarchy.
>     ** Getting 101 SOQL if try to call "getTopParentAccount" method.

**

  public with sharing class CustObjRecordTriggerHelper1 {

    public boolean flag;
    public static List<Account> accountHierarchyList = new List<Account>();
    public static Map<ID,Account> accountDetailsMap = new Map<ID,Account>();    
    public static Map<ID,List<Account>> parentChildLinkedListMap = new Map<ID,List<Account>>();
    public static Map<ID,Integer> accountIdLevelMap = new Map<ID,Integer>();
    public static Decimal totalcount;
    public void updateNoOfCustObjRecord(){

        try{

            Map<Account,List<CustObjRecord__c>> mapAccountCustObjRecord = new Map<Account,List<CustObjRecord__c>>();
            Map<ID,ID> Child_To_TopAccount=new Map<ID,ID>();
            List<Account> lstAccounttoUpdate = new List<Account>();
            List<Account> lstAcc1 = new List<Account>();
            Set<ID> setParentAccID = new Set<ID>();
            Integer finalCount=0;


           //Store Accounts Id's in set for all Triggered CustObjRecord
            for(CustObjRecord__c objRef : (List<CustObjRecord__c>)Trigger.new){
                 setParentAccID.add(objRef.CustObjRecord_By__c);
                /*if(objRef.Referred_By__r.ParentID !=null){
                    Child_To_TopAccount.put(objRef.Referred_By__c,objRef.Referred_By__r.ParentId);
                }
                */
            }

            /* Generate a Map which will store Account and its Lists of referrals
            //Query only Account (and all its child Referrals) who are immediate parent of Triggered Referral records
            */
            List<Account> listAccount = [SELECT id,count__c, (SELECT id,referral__c,Referred_By__c FROM CustObjRecords__r) FROM Account WHERE ID IN :setParentAccID];
            for(Account objAccount : listAccount){
                mapAccountReferral.put(objAccount, objAccount.CustObjRecords__r);            
            }
            System.Debug('----mapAccountCustObjRecord--'+mapAccountCustObjRecord);

            //Store Child - top Parent id's --- only those who has parent
            //**Child_TopAccount= getTopParentAccount(setParentAccID);

            for(Account acc: mapAccountCustObjRecord.keySet()){

                    /***** Get Count of Each immediate Account till topAccount. 
                     *
                     */

                    /***** GOT count of Top Parent Account
                    //Get the Top Parent Account of an Triggered Account -- and count for the same.
                    Account topAccountID=null;
                    topAccountID = Child_TopAccount.get(acc.id);
                    if(topAccountID==null){
                            topAccountID=acc.ID;
                    }
                    Decimal totalcount = getCountofCustObjRecord(topAccountID);
                        acc.count__c= totalcount;
                        lstAccounttoUpdate.add(acc);
                    */


                    // **** Get Count of Triggered Account and its childs 
                    //Self and immediate Childs count storing method call
                    totalcount = getCountofCustObjRecord(acc.ID);                    
                    acc.count__c= totalcount;
                    lstAccounttoUpdate.add(acc);
            }
            /*//**** TRY ->Once all Triggered records done with their count updates-
            // In the end run this to traverse hierarchy and update counts accordingly.
            for(ID aid: Child_To_TopAccount.keySet()){
                totalcount = getCountofCustObjRecord(aid);
                //if(mapAccountCustObjRecord.get(aid) == null){
                //mapAccountCustObjRecord.put(aid).of_Ref__c= totalcount;
                //lstAccounttoUpdate.add();  ---HOW to take record from MAP<id,id> into list<account> //UPDATE MAP itself
                // }
            }
            */
            if(lstAccounttoUpdate.size()>0){
                update lstAccounttoUpdate;
            }

        }
        catch(Exception e){            
            system.debug ('ERROR:CustObjRecordTriggerHelper=> updateNoOfCustObjRecord:' + e);
        }
    }


      //Method to get Top Level Parent Account in Hierarchy
    public static Map<ID,ID> getTopParentAccount(Set<ID> AcctIds){
        Map<Id,Id>Child2Parent = new Map<Id,Id>();
        List<Id> Accnts = new List<Id>(); 
        Accnts.addAll(AcctIds);
        Set<Id>Acts2 = new Set<Id>();
        Acts2.addAll(AcctIds);

        While(Acts2.size() > 0)
        { 
            List<Account>accnts2 = [SELECT ID, ParentId FROM Account WHERE ID IN :Accnts ]; 

            for(Account a:accnts2){
                if (a.ParentID != null) {  
                    //put record into map 
                    Child2Parent.put(a.Id,a.ParentId);
                } else if(a.ParentID == null) {
                    //remove account with parent from list of accounts to query
                    //since this means parentId == null
                    Acts2.remove(a.Id);
                }

            }

             if(Acts2.size() == 0) break;   
             Accnts2.clear();
        }
        // return map of accounts 2 ulitmate parent account
        return Child2Parent;

    }


    //Method to get count of Self CustObjRecord and immediate Child Accounts Referrals
    private static Decimal getCountofCustObjRecord(ID acctid){
         // Need to traverse Child Account Trail - to generate Level & Details Map
         getChildAccountsInHierarchy(acctid);

        Decimal count=0;
        for(ID accid : accountIdLevelMap.keySet()){
                /* $$$$ --- THIS MIGHT BE CAUSING AN ISSUE ---- $$$$  --- 0 & 1 Levels count only.
                 * but thought of adding so BECAUSE -> One should get count of CustObjRecord from its immediate childs 
                 * and himself
                 */
                if(accountIdLevelMap.get(accid)==0 || accountIdLevelMap.get(accid)==1){
                    count += accountDetailsMap.get(accid).of_Ref__c;
                }
        }
        return count;
    }

    public static void getChildAccountsInHierarchy(Id parentAcctId) {

        Boolean endTraversing = false;
        Integer accountLevel = 0; 
        String query;
        List<Account> acctList = new List<Account>();
        Set<ID> parentIdList = new Set<ID>();


        parentIdList.add(parentAcctId);

        while(!endTraversing) { 
            // observe closely at the WHERE clause, that's the key
            if(accountLevel == 0) {
               query = 'select Id, Name, ParentId, from Account WHERE Id IN :parentIdList';
            } else {
               query = 'select Id, Name, ParentId from Account WHERE ParentId IN :parentIdList';
            }
            acctList = Database.query(query);

            if(acctList.size() == 0 ) {
               endTraversing = true;
            } else {
               parentIdList.clear();
               acctList.sort();
               Account a;
               for(Integer i = 0; i < acctList.size(); i++ ) {
                   a = acctList.get(i);
                   parentIdList.add(a.Id);

                   // populate the account id and level and details maps to be later used
                   accountIdLevelMap.put(a.Id, accountLevel);
                   accountDetailsMap.put(a.Id, a);

                   if(accountLevel > 0) {
                       // create the map of parent and related child accounts
                       // this is the key part of the iteration which takes
                       // care of adding the parent and related child
                       if(parentChildLinkedListMap.get(a.ParentId) == null) {
                           parentChildLinkedListMap.put(a.ParentId, new List<Account>()); 
                       }
                       parentChildLinkedListMap.get(a.ParentId).add(a);
                       parentChildLinkedListMap.get(a.ParentId).sort();
                    }
               }
               accountLevel++;
            }
        }

        // populate the list in the way they appear in the hierarchy
        addChildAccountsToList(parentAcctId);
        System.Debug('--------accountHierarchyList---'+accountHierarchyList);
        //return accountHierarchyList;
    }

    private static void addChildAccountsToList(Id parentAccountId) {
        Account a = accountDetailsMap.get(parentAccountId);
        accountHierarchyList.add(a);

        // get all associated child accounts for this parent from the map populated
        // then call the method recursively till the last node of the account is reached
        // below we add records in order of their appearance in tree
        List<Account> childAccounts = parentChildLinkedListMap.get(parentAccountId);
        if(childAccounts != null) {
            childAccounts.sort();
            for(Account acct : childAccounts) {
                // recursion
                addChildAccountsToList(acct.Id);
            }
        }        
        // finally return to get out from the stack
        return;
    }
}
  • (please execuse for the messy Code)
  • How is Acc3's count = 1 while Acc5's count = 0 before you insert CustObjRec13? Both are leaf nodes in your hierarchy. Shouldn't the before counts be Acc1 = 2, Acc2 = 2, Acc3 = 0, Acc4 = 2, and Acc5 = 0, and the updated counts be Acc1 = 3, Acc2 = 3, Acc3 = 0, Acc4 =2, and Acc5 = 1? – Derek F Jan 12 '16 at 15:22
  • That Count__c is not updated all the time with structure I am asking for -- THIS IS NEW REQUIREMENT ---- (because its there/updated/manually done- only with no.of child account has on itself) – Salesforce Steps Jan 12 '16 at 16:17
  • 1
    Ah, I see now. The Acc1, Acc2, Acc3 can also have one CustObjRecords each made me think that these accounts could have a CustObjRecord associated to them, and not that they do have a CustObjRecord associated to them. Now that I know these records do have associated records, the counts make sense. I see that they were left out of your diagram for sake of being able to keep the diagram readable and reasonably short. – Derek F Jan 12 '16 at 16:28
  • "Will Start bounty on this one in an hour-- Need urgent solution" – Salesforce Steps Jan 14 '16 at 13:21
7

I ran into a similar issue for a project that I was on last year in which I was dealing with managing the blocks of IP Addresses that my company controls. In my case, the hierarchy could be up to 17 levels deep (with some 2^16 possible records indirectly related to a single block of IP Addresses).

You really have 2 issues to tackle in this question:

  1. How do I get a count of all CustObjRecords directly related to this account?
  2. How do I get a count of all CustObjRecords related to child accounts?

For the sake of this answer, I'll assume that the relationship between accounts is a field called Parent_Account__c (self-relationships must always be lookup relationships), and the relationship between your CustObjRecord and Account is a master-detail relationship on CustObjRecord called Account__c.

I'll also assume that you have 3 fields on Account, Direct_Count__c, Indirect_Count__c, and Total_Count__c. Total_Count__c would simply be a formula field that adds Direct_Count__c and Indirect_Count__c.

In this situation, you can make a rollup summary field to count the number of CustObjRecords directly related to each Account.

If Account and CustObjRecord have a lookup relationship, you could accomplish this step by using Andrew Fawcett's Declarative Lookup Rollup Summary Tool

That takes care of the first part. The second part is a bit...thornier.

If you have the Master-Detail relationship between CustObjRecord and Account, or if you're using the declarative rollup summary tool, you will have a trigger fired on Account before/after update.

Supporting a hierarchy of arbitrary height means that traversing up the hierarchy using Parent_Account__r multiple times is not viable (limited to 5 levels). Using additional queries for each level of the hierarchy is also ill-advised (queries are a precious resource!).

You could possibly use the declarative rollup summary tool for this part as well, but that would still use 1 query per level of your hierarchy.

To allow for a hierarchy of arbitrary height, you'll need an additional piece of information. You'll need to, on every Account record, keep track of the Id of the root of the hierarchy. This could be a lookup field, or it could simply be a text field. I'll call this field Root_Account__c.

This will allow you to use a single query to grab all of the records that will need to be updated (and potentially several that don't).

The general structure of your Account trigger will look something like this:

  1. Loop through trigger.new, and build a Set<Id> containing the Ids of the hierarchy roots involved
  2. Map<Id, Account> accountsWithChildren = new Map<Id, Account>([SELECT Id, (SELECT Id, Direct_Count__c FROM Child_Accounts__r) FROM Account WHERE Id IN <set of root ids> OR Root_Account__c IN <set of root ids>]);
  3. Use some good, old-fashioned recursion to calculate the Indrect_Count__c for each level in a top-down manner (not using recursion, and working this bottom-up is possible, but a lot more work)

The recursive bit could look like this:

public Integer calculateIndirectTotals(Account startingAccount){
  Integer indirect = 0;

  for(Account child : accountsWithChildren.get(startingAccount.Id).Child_Accounts__r){
    indirect += calculateIndirectTotals(child);
  }

  // The map<Id, Account> below is declared outside of this method
  accountsToUpdate.put(startingAccount.Id, new Account(Id = startingAccount.Id, Indirect_Total__c = runningTotal));

  return runningTotal + startingAccount.Direct_Count__c;
}

Now, as is, this code would likely run into some issues (accountsToUpdate probably includes the account(s) that are part of the update trigger that kicked off this method, which would lead to a SELF_REFERENCE_FROM_TRIGGER exception), but the basic idea should be sound.

+edit:

After I had written my answer, it was revealed that there are some additional constraints:

  • A rollup summary field can't be used on Account
  • We can only touch the trigger for CustObjRecord

The constraint that only the trigger for CustObjRecord can be touched is the most disruptive. If Count__c needs to be kept up to date in near real-time, then the Declarative Lookup Rollup Summary tool is no longer a viable option (as it needs its own trigger on the child object to function in real-time mode, and order of trigger execution, when there are multiple triggers on an object, is not guaranteed).

If you can live with updating Count__c on a looser schedule

That is to say, not real-time (or near real-time). In this case, you're probably best served by the bottom-up approach using batchable (or queueable) apex as mentioned by crop1645 and Ratan.

The idea here is that you have a field on Account that keeps track of whether or not that Account needs to be updated. Initially, the trigger on CustObjRecord (for newly inserted, or updated, records) will aggregate the Accounts related to the CustObjRecords taking part in your trigger, and DML update those Accounts.

From there, you schedule a batch job. The batch Apex needs to end up running two queries

[SELECT Account__c, COUNT(Id) FROM CustObjRecord WHERE Account__r.Update_Account_Count__c = TRUE GROUP BY Account__c]

and

[SELECT Parent_Account__c, SUM(Total_Count__c) FROM Account WHERE Parent_Account__r.Update_Account_Count__c = TRUE GROUP BY Parent_Account__c]

The first query takes care of calculating Direct_Count__c, and the second query takes care of calculating Indirect_Count__c.

Finally, you update the Accounts taking part in the current batch job, setting their direct and indirect counts and, very importantly, unset Update_Account_Count__c. You'll also need to set Update_Account_Count__c for the parent accounts of the Accounts taking part in the current batch job. Then, you chain the next batch job in the finish() method of your batch apex class.

If everything goes right, the batch jobs will continue to chain until you reach the top of your hierarchy. Each new batch in the chain is in its own execution context, so you won't run into the 100 SOQL query limit by virtue of the batch apex alone.

If you need the counts updated as soon as possible

Then you'll need to expend a few more queries to get things set up.

CustObjRecord's trigger will need to do the work of updating the Direct_Count__c of the Accounts related to the records in your CustObjRecord trigger. That particular query is simple (gather affected accounts, use COUNT(Id) to query/count all the CustObjRecords related to those accounts, dml update).

The code to calculate Indirect_Count__c can easily be put into CustObjRecord's trigger instead of Account's trigger. The only difference is that you'll need to query for the Root_Account__c of the affected accounts instead of being able to grab that information from a trigger context variable.

+edit2:

That's all well and good, but how can I identify the Account at the top of the hierarchy?

In my original answer, I mentioned having a field Root_Account__c on every Account. This field isn't automatically populated, you'll need to have a plan to maintain the integrity of this field going forward, and you'll need to figure out a way to update your existing accounts so that they contain this information.

If Root_Account__c is a text field, a simple workflow rule set to run on creation every new Account with a field update to set Root_Account__c to Parent_Account__r.Root_Account__c (or to its own Id if it doesn't have a parent) would take care of adding new leaf nodes and roots. You still have other cases to worry about (deleting the root, deleting another non-leaf node, adding a new node above the current root, etc...), but handling those cases would be a separate question. This doesn't populate Root_Account__c on your existing accounts, however.

Without touching Account's trigger, your best bet to accomplish both tasks is probably a batch job that is called via another class that implements the Scheduleable interface (so that your batch job is being regularly run). You can schedule a batch job to run once via system.ScheduleBatch(), which I imagine can be done through executing anonymous apex in the developer console.

The batch class here would work top-down. The query for the batch could look something like this:

[SELECT Id, Parent_Account__r.Root_Account__c FROM Account WHERE (Parent_Account__c = null AND Root_Account__c = null) OR (Root_Account__c = null AND Parent_Account__r.Root_Account__c != null)]

This would pull the Accounts at the top of your hierarchies which haven't been set up to support the other group of Accounts that this query also pulls, the set of Accounts which don't have Root_Account__c set but have a parent that does.

Those null values in the query are problematic though. It will make the query slow, and it will outright fail from not being a selective query if you have enough Accounts (>= 100,000)

A better way to do this would be to do a little more prep work.

  • create one more field on Account, a checkbox field which I'll call Needs_Root_Populated__c, and defaulted to true.
  • With anonymous apex, query for all the root Accounts (those with no parent), set Root_Account__c to their own Ids, and unset Needs_Root_Populated__c

The query can then become

[SELECT Id, Parent_Account__r.Root_Account__c FROM Account WHERE Needs_Root_Populated__c = true AND Parent_Account__r.Needs_Root_Populated__c = false]

This won't pull new root accounts, but the query should be much more selective.

The last thing left to do is to use that query to update the Accounts that were pulled, and then to chain another batch so this process continues down to the last leaf node in your hierarchies.

This makes the Root_Account__c information available at all levels of your hierarchy. You simply need to query for this field on the Accounts whose Direct_Count__c is being updated in CustObjRecord's trigger.

| improve this answer | |
  • 3
    interesting, I had this problem too once and did it with a chain of batch jobs (could be queuable now) that (0) processed leaf nodes first, then (1) parents of leaf nodes next, then parents of nodes from (1), and so on until there was nothing left to do. Not as fast as triggers but requirement could be met by nightly job – cropredy Jan 12 '16 at 21:07
  • 2
    Can you explain how the answer I've provided doesn't solve the issue presented in your question? – Derek F Jan 14 '16 at 14:39
  • 2
    That's some very important information that should be included in your question. Please edit your question to include that. I'll work on editing my answer to reflect those constraints. – Derek F Jan 14 '16 at 15:03
  • 1
    You can always make rollups work. – Adrian Larson Jan 14 '16 at 16:42
  • 1
    @NileshMendhe Have you taken a look at my updated answer yet? – Derek F Jan 21 '16 at 15:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.