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