1

Problem

My org has many extremely complicated account hierarchies because of our client type. I have an ask: to stamp all accounts with sum of all of the accounts sales volumes in the hierarchy on each account.

So all accounts have two fields

  • location sales volume
  • Hierarchy (group sales volume)

Whenever Account Revenue changes on any account, or an account is added to the hierarchy, the Hierarchy Revenue needs to be update across the whole Hierarchy.

Current Solution

I used the foundation from this article to create an account hierarchy class. https://jdspaceit.wordpress.com/2016/01/13/traversing-through-an-accounts-hierarchy-in-apex/

Creating the following Apex class to assemble an account hierarchy when it is instantiated with an account.

public class AccountHierarchy {

public list<account> accountHierarchyList{get;private set;}
public map<id,integer> accountIdLevelMap{get;private set;}
public map<id,account> accountDetailsMap{get;private set;}
public map<id,account> AccountDetailsWithoutOrginalNode{get; private set;}
public map<id,list<account>> parentChildLinkedListMap{get;private set;}

public integer numberOfAccountRecords{get;Private set;}
public double grossSalesVolume{get;Private set;}
public integer NumberOfOpenLocations{get;Private set;}
public id topLevelAccount{get; private set;}

public AccountHierarchy(account a) {
    topLevelAccount = findTopLevelParent(a);
    getChildAccountsInHierarchy(topLevelAccount);//populates accountHierarchyList, accountIdLevelMap, accountDetailsMap, parentChildLinkedListMap
    numberOfAccountRecords = accountHierarchyList.size();
    NumberOfOpenLocations = calculateNumberOfOpenLocations();
    grossSalesVolume = calculateGrossSalesVolume();

    AccountDetailsWithoutOrginalNode = accountDetailsMap;
    AccountDetailsWithoutOrginalNode.remove(a.id);

}

//find highest parent account from a child
public id findTopLevelParent(account a) {
    Boolean isTopLevelAccount = false;
    account acct = new account();
    id currAcctId = a.id;

    while (!isTopLevelAccount) {
        system.debug('Finding the toplevel of:'+a);
        acct = [select Id, ParentId From Account where Id = :currAcctId limit 1];
        if (acct.ParentID != null) {
            currAcctId = acct.ParentID;
        } else {
            isTopLevelAccount = true;
        }
    }
    system.debug('currAcctId:'+ currAcctId);
    return currAcctId;   
}

//builds linked list of the hiearchy 
Private List<Account> getChildAccountsInHierarchy(Id parentAcctId) {

    Boolean endTraversing = false;
    Integer accountLevel = 0;
    String query = '';
    List<account> acctList = new list<account>();
    list<id> parentIdList = new list<id>();

    accountHierarchyList = new List<account>();
    accountIdLevelMap = new map<id,integer>();
    accountDetailsMap = new map<id,account>();
    parentChildLinkedListMap = new map<id,list<account>>();

    parentIdList.add(parentAcctId);

    while(!endTraversing) {
        // observe closely at the WHERE clause, that's the key
        if(accountLevel == 0) {
            acctList = [SELECT Id, Name, ParentId,Location_Sales_Volume__c, Type FROM Account WHERE Id IN :parentIdList];
        } else {
            acctList = [SELECT Id, Name, ParentId,Location_Sales_Volume__c, Type FROM Account WHERE ParentId IN :parentIdList];
        }

        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);
    return accountHierarchyList;
}

//helper method for getChildAccountsInHierarchy
Private 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
    // this step takes care of adding the accounts in the return list in order of
    // their appearance
    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;
}

private double calculateGrossSalesVolume() {
    double sum = 0.0;
    for(account a : accountHierarchyList) {

        if(a.Location_Sales_Volume__c != null) {
            sum += a.Location_Sales_Volume__c;
        }

    }
    return sum;
}

private integer calculateNumberOfOpenLocations() {
    integer sum = 0;
    for(account a : accountHierarchyList) {
        if(a.Type != 'Parent Account' && a.Type != 'Out of Business - Do Not Contact') {
            sum++;
        }
    }
    return sum;
}

public Static void updateGrossSalesVolumeInHierarchy(List<account> AccountHierarchyList, Double Value) {
    list<account> acctToUpdate = new list<account>();

    for(account a : AccountHierarchyList) {
        system.debug('INSIDE AH STATIC METHOD: account: ' + a);
        a.Group_Sales_Volume__c = value;
        acctToUpdate.add(a);
    }        
    try{
        Database.update(acctToUpdate,False); //allows partial sucess
    }
    catch(exception e) {
        system.debug('ERROR: Attempting to update this accounts hiearchy\'s Group Sales Volume Falied');
        System.Debug('ERROR:' + e);
    }        
}

This method works damn well... but Its slow. It slows down my unit testing because I am hooking it up to the account trigger and running it whenever an account is inserted with a parentid, parentid changes, or locations sales volume changes.

Has anyone else run into a similar problem and found a better solution. I am thinking about moving this to batch. Just to get it out of synchronous run rime.

Update:

I moved the class to batch and its working a little bit better. But still very open to anyone having improvements to syncing account hierarchies

  • 2
    The only other thing i thought of is having a custom object that simply has account and parent account, and level. Then on each Account Hierarchy (ParentId) update we will update the hierarchy in that custom object. And when we need to get the hierarchy (e.g. when the Sales Volume updates for you) you just query that flat object. Obviously there is redundant storage there, but that MAY be faster if your hierarchy does not update AS OFTEN as your Revenue – zaitsman Jun 17 at 13:26
0

So I came up with my own solution which I will post here. I'm not super confident in it, but it has been working effectively on a large scale.

First Attempt:

My first solution was to correct account hierarchies on the fly whenever an update was made to one of two fields on the account. This was done by hooking up the above account hierarchy class to my account trigger handler. Whenever a change was made on an account that required an update.

result: too heavy an operation to have running synchronously and would result in longer page load times.

Second Attempt:

I created a batch job to run every 15 min to do the dirty work. Whenever one of the two fields is updated a flag field is marked true. This flag field is called "Account_Hierarchy_Needs_Update__c" The batch job then checks for all accounts that have this flag marked true. Updates the hierarchy then unchecks the box.

global class AccountHierarchyUpdateBatch implements Database.Batchable<sObject>, Database.stateful{

global list<account> AccountsUpdated = new list<account>();
global Map<id,exception> exceptionMap = new Map<id,exception>();

global database.QueryLocator start (Database.BatchableContext bc) {

    return database.getQueryLocator('SELECT id FROM account WHERE Account_Hierarchy_Needs_Update__c = True');
}

global void execute(Database.BatchableContext bc, List<Account> scope) {
    list<account> acctToUpdate = new list<account>();

    system.debug('LIMIT: ' + Limits.getLimitQueries());

    for(Account a: Scope) {
        AccountHierarchy AH = new AccountHierarchy(a); 

        a.Group_Location_Count__c = AH.NumberOfOpenLocations;
        a.Group_Sales_Volume__c = AH.grossSalesVolume;
        a.Account_Hierarchy_Needs_Update__c = false;

        try{AccountHierarchy.updateNumberOfOpenLocationsInHierarchy(AH.AccountDetailsWithoutOrginalNode.values(), AH.NumberOfOpenLocations);}
        catch(exception e) { exceptionMap.put(a.id, e);}

        try{AccountHierarchy.updateGrossSalesVolumeInHierarchy(AH.AccountDetailsWithoutOrginalNode.values(), AH.grossSalesVolume);}
        catch(exception e) { exceptionMap.put(a.id, e);}

        acctToUpdate.add(a);
    }

    update acctToUpdate;
    AccountsUpdated.addAll(acctToUpdate);
}

global void finish(Database.BatchableContext bc) {

    //chains next job to run in 15 min (change here to make it run at a different time)
    if(!test.isRunningTest()) system.scheduleBatch(New AccountHierarchyUpdateBatch(),'AccountHierarchyUpdateBatch 15min Interval',15,1);
}

}

Basically for each account in the batch. Build an account hierarchy. Push the updates across the hierarchy. Then update the account itself. I designed it to run on multiple accounts per batch. but... batch size of 1 has been the only thing I can run it safely at without hitting the 200 soql querry limit.

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.