Need a Help to Convert Workflow Formula field into Apex

Question

We have a existing workflow rule on Account that fires whenever record is created or updated. It does have a condition but it executes every time. Below is the Workflow criteria:

Current User: Bypass Workflow EQUALS False

and it updates below field on Account object by calculating formula:

/* Global Parent */ 
IF(ISBLANK(Parent.Id), Name, 

/* 1st Tier */ 
IF(ISBLANK(Parent.Parent.Id), Parent.Name, 

/* 2nd Tier */ 
IF(ISBLANK(Parent.Parent.Parent.Id), Parent.Parent.Name, 

/* 3rd Tier */ 
IF(ISBLANK(Parent.Parent.Parent.Parent.Id), Parent.Parent.Parent.Name, 

/* 4th Tier */ 
IF(ISBLANK(Parent.Parent.Parent.Parent.Parent.Id), Parent.Parent.Parent.Parent.Name,

/* 5th Tier */ 
IF(ISBLANK(Parent.Parent.Parent.Parent.Parent.Parent.Id), Parent.Parent.Parent.Parent.Parent.Name, 
"Account has too many tiers"))))))

the above workflow rule works as expected, but the reason that i want to convert this workflow to apex is because, when I have a large account hierarchy, multiple times update operation is performed. As I am updating value on workflow, it repeatedly calls before and afterUpdate operation that exceeds the CPU time limit. If I can convert this workflow to apex, it will follow unique order of execution will save CPU Time somehow.

I have written below apex code

public static void PopulateTopLevelAccountText(Account acc,Account oldAcc)
{
    system.debug('Account acc:'+acc);
    Id TopID;
    String Name;
    User u=[Select Id,BypassWorkflow__c from User where Id=:UserInfo.getUserId()];

    if(u.BypassWorkflow__c==false)
    {
        system.debug('UltimateParentAccountId__c'+acc.UltimateParentAccountId__c);
        TopID = acc.UltimateParentAccountId__c;
        system.debug('Top Levele Acc Id:'+TopID);       
    }

    Name = [Select Name from Account where Id=:TopId].Name;
    system.debug('Acc Name:'+Name);
    Account ac = [Select id,TopLevelAccountText__c,Name from Account where Id=:acc.Id];
    system.debug('Ac Name'+ac);
    ac.TopLevelAccountText__c = Name;
    update ac;
}

Field UltimateParentAccountId__c that I am using is another formula field present on Account object. It contains below formula:

/* Global Parent */ 
IF(ISBLANK(Parent.Id), Id, 

/* 1st Tier */ 
IF(ISBLANK(Parent.Parent.Id), Parent.Id, 

/* 2nd Tier */ 
IF(ISBLANK(Parent.Parent.Parent.Id), Parent.Parent.Id, 

/* 3rd Tier */ 
IF(ISBLANK(Parent.Parent.Parent.Parent.Id), Parent.Parent.Parent.Id, 

/* 4th Tier */ 
IF(ISBLANK(Parent.Parent.Parent.Parent.Parent.Id), Parent.Parent.Parent.Parent.Id, 

/* 5th Tier */ 
IF(ISBLANK(Parent.Parent.Parent.Parent.Parent.Parent.Id), Parent.Parent.Parent.Parent.Parent.Id, 


NULL))))))

If you compare both formula fields, there is only difference is in workflow field update. I am calculating Name, and in this formula field I am calculating ID.

and I am calling this method on AfterInsert and AfterUpdate trigger.

Let's say If I have a Top Level Account 'ABC' contains 200 child accounts. If want to make ABC as child account of newly created parent account 'DEF' then on 'ABC' account it does not update any value on TopLevelAccountText__c field that I am updating via apex code. With workflow rule, it updates perfectly fine for child and their grandchildren accounts. If 'ABC' is Top level Account, then 'DEF' text value should be updated on 'ABC' and all their 200 Child accounts.

Can someone help me what is wrong in the apex logic?

DEF(Top level Account)
  ---ABC(Child of DEF)
    --ABC1
    ---ABC2 till 200 accounts (Child of ABC)

I also want to call this method on Delete operation. Let's say if I delete Top Level Account 'DEF' then TopLevelAccountText__c field should get updated with name as'ABC' in 'ABC' account as well as all their 200 child accounts.

Updated Code:

Below is the query that I have designed:

public static List<Account> getAllChildrenIncludingParent(Account acc) {
        List<Account> result = [
            select Id,Name,Parent.Id,Inactive__c,UltimateParentAccountId__c from Account where Id=:acc.Id
            or Parent.Id = :acc.Id
            or Parent.Parent.Id = :acc.Id
            or Parent.Parent.Parent.Id = :acc.Id
            or Parent.Parent.Parent.Parent.Id = :acc.Id
            or Parent.Parent.Parent.Parent.Parent.Id = :acc.Id
        ];
        return result;
    }

Below is the modified apex method that updates Top level Account Name on their children.

public static void PopulateTopLevelAccountText(Account acc,Account oldAcc)
{
List<Account> accs=new List<Account>();
List<Id> aId=new List<Id>();
List<Account> aId1=new List<Account>();

accs=AccountServices.getAllChildrenIncludingParent(acc);
String Name;
User u=[Select Id,BypassWorkflow__c from User where Id=:UserInfo.getUserId()];

if(u.BypassWorkflow__c==false)
{
if(accs.size()>0){
for(Account a:accs){
TopID=a.UltimateParentAccountId__c;
aId.add(a.Id);     
}
}
}
//Get the Name of Top Level Account
Name=[Select Name from Account where Id=:TopId].Name;
for(Account ac:[Select id,TopLevelAccountText__c,Name,UltimateParentAccountId__c from Account where Id IN:aId]){
ac.TopLevelAccountText__c=Name;
aId1.add(ac);
}
update aId1;
}

--Comments from Derek:

As for further recommendations, your new query will only grab 5 levels of your hierarchy (which may not be your entire hierarchy). If you've studied the use cases of this code thoroughly, and have determined that you will only need to support up to 5 levels of hierarchy, then this will work ok. In most situations though, I'd say that the far better query would be one that doesn't rely on parentId = :value OR parent.parentId = :value ...

Answer 1

As you've found out, having object hierarchies that are more than a few levels deep can be problematic. I've worked through a similar problem myself (tracking my company's IPv4 address space, which results in a hierarchy that's up to 24 levels deep).

In my case, there are three keys to success

1. Ensure that information about the record(s) at the top of the hierarchy is distributed to all the descendants
2. When there is a change to the hierarchy, rely on the hierarchy as little as possible
3. Don't use SOQL to traverse the hierarchy. Query once to get information from all levels of the hierarchy, and do the rest in Apex

It sounds like you already have #1 taken care of for your existing records (if not, there is likely no way around using batch apex or repeatedly executing anonymous apex to distribute this information to all child records).

Key #2 is mostly about using information that's as close in the hierarchy, to the record that you're currently working on, as possible. If you're inserting a new child into the hierarchy, you can get the information about which Account is at the top of the hierarchy by only looking at the new Account's parent.

I believe that you're taking the wrong approach with your Apex. Your workflow looks like it can only handle inserting new records, you'll have data skew if the Account at the top of the hierarchy changes at all, and it only works for up to 5 levels of relationships (a limitation of formulae and SOQL). Trying to translate this workflow into Apex will likely leave you disappointed. This is where key to success #2 comes into play. The code that you end up writing should traverse the hierarchy as little as possible.

Before I get into some example code, it'll be helpful to list the use cases that you'll need to handle.

I'd say your use cases look something like this:

• When adding a new record into the hierarchy
  • If the insert is going to be the new root/top-level of the hierarchy, distribute the new root information to all levels of the existing hierarchy
  • If the insert is going to be within the hierarchy (anywhere but the root), grab the information about the root of the hierarchy from as close as possible (e.g. from this new record's parent)
• When updating a record in the hierarchy
  • If the root of the existing hierarchy is being changed, distribute the new root information to all levels of the hierarchy
  • If part of the hierarchy is being split off into a new hierarchy, distribute the root of the new hierarchy to all levels of the new hierarchy
• When deleting a record in the hierarchy
  • If the root of the hierarchy is being deleted, find the direct children of the old root.
    • If there is only one direct child, simply distribute the new root information to all levels of the hierarchy
    • If there is more than one direct child, each child becomes the root of a new hierarchy. Distribute the relevant new root information to each hierarchy

In my case, the hierarchy roots are well-known, and extremely unlikely to change. I can also only add records to the bottom of the hierarchy. This allows me to make some simplifications that I don't think you'll be able to take advantage of.

In your case, to handle all of the possibilities (especially splitting one hierarchy into many), we need to get into a bit of Computer Science theory. I believe we'll need to create a data structure called a "tree". At its most basic, a tree is comprised of nodes (Accounts). A node can have any number of child nodes, and has one parent. A tree is simply a node with no parent, and all of the nodes below it.

For solving your problem, I'll use the following class:

// This kinda strains the strict definition of a tree...
// It could possibly be useful to have a separate class to define
//   the tree itself, but this should be all we need.
public class Node{
    public Node root {get;set;}
    public Node parent {get;set}
    public Account data {get;set}
    public List<Node> children {get; set;}

    public Node(Account inputData, Node inputParent, Node inputRoot){
        data = inputData;
        children = new List<Node>();
        root = inputRoot;

        if(inputParent!= null){
            inputParent.children.add(this);
        }
    }
}

Using a tree structure like this has several advantages:

• Every node is technically a tree of its own (i.e. a subtree)
• Merging one tree (hierarchy) into another is simple. Set the parent, grab the new root from the parent, update the root, add yourself to the list of children for your new parent. Then, update the root of all the child nodes of the old tree
• Splitting one tree into two (or more) is also simple. Take the node you want to split, remove it from the list of child nodes for its parent, update the root node of the node being split to point to itself. Then, update the root of all the child nodes of the new tree.

As a consequence of those 3 things, if we have a simple list of Accounts in a hierarchy, we can build the tree in any order that we want. Since each node is its own tree, we just merge these 'child subtrees' into their parents over and over. No matter the order that we build the tree in, it will end up being properly connected.

The code to build the tree from a list of Accounts might look like this (this code is untested, and may contain bugs):

// Storing nodes we create in a map, keyed on Account Id, allows us to create nodes
//   in any order.
Map<Id, Node> acctNodeMap = new Map<Id, Node>();

// First things first, it's a good idea to create the root node before we loop.
// As long as you have the Id of the top level account, we'll be good.
// No extra query required

Node root = new Node(new Account(Id = targetTopLevelAccountId, TopLevelAccount__c = targetTopLevelAccountId), null, null);

// Since we can't set the root node's root property when we create the root node, set it now.
root.root = root;

Node tempNode;
Node parentNode;
for(Account acct :[SELECT Id, TopLevelAccount__c, ParentId FROM Account WHERE TopLevelAccount__c = :targetTopLevelAccountId){
    // First, we should check if there is already a parent node.
    if(!acctNodeMap.containsKey(acct.ParentId)){
        acctNodeMap.put(acct.ParentId, new Node(new Account(Id = acct.ParentId), null, root));
    }

    // At this point, we know that the parent node exists in the map
    parentNode = acctNodeMap.get(acct.ParentId);

    // Next, we should check to see if we need to create a new node for this account, or
    //   if a node already exists.
    if(!acctNodeMap.containsKey(acct.Id)){
        acctNodeMap.put(acct.Id, new Node(acct, parentNode, root));        
    } else {
        // If we get into this else block, that means that our current Account is
        //   a parent to some node in the hierarchy that we've already made a node for.
        // Because we didn't have all of the Account data when we first created this
        //   node, we need to update it.
        tempNode = acctNodeMap.get(acct.Id);
        tempNode.data = acct;

        // Also, in this particular situation, this node hasn't yet been added
        //   as a child to _its_ parent.
        // Take care of that now.
        parentNode.children.add(tempNode);
    }
}

After the above code has been run, you have an in-memory representation of your hierarchy that you can start working with.

Adding a new node as a child to an existing parent:

// Assuming acct is defined elsewhere as a variable of type Account, 
//   and that the fields topLevelAccount__c and ParentId are available 
Node parent = acctNodeMap.get(acct.ParentId);
Node newChild =  new Node(acct, parent, parent.root);

// don't forget to add the new child to the acctNodeMap
acctNodeMap.put(acct.Id, newChild);

Inserting a new node between an existing parent and its children:

Node parent = acctNodeMap.get(acct.ParentId);
Node newChild = new Node(acct, parent, parent.root);
newChild.children = parent.children;
parent.children = new List<Node>{newChild};

// don't forget to add the new child to the acctNodeMap
acctNodeMap.put(acct.Id, newChild);

Deleting an existing node (non-root):

Node oldNode = acctNodeMap.get(oldAcct.Id);
Node parentNode = acctNodeMap.get(oldAcct.Parent);

// Remove the old node from its parent's children
// This could be done without using sets, but sets are easier for me
Set<Node> parentChildrenSet = new Set<Node>(parentNode.children);
parentChildrenSet.remove(oldNode);
parentNode.children = new List<Node>(parentChildrenSet);

// Unless you want to remove the entire subtree starting at oldNode, you'll
//   need to add the oldNode's children to the parent node's children.
parentNode.children.addAll(oldNode.children);

// Last step is to remove oldNode from the node map
acctNodeMap.remove(oldAcct.Id);

Making changes to the root node of the hierarchy means that we'll need to push the new root information down to the rest of your hierarchy. We can do this with a recursive method (which would be part of the Node class)

public void propagateRoot(){
    // This is what's known as a tree-traversal.
    // From our starting point, we will eventually visit all of our child nodes,
    //   all of our children's child nodes, all of their child nodes, etc...
    //   until we run out of children.
    // Start this from a root node, and all children in that sub-tree will be 
    //   updated with the correct, new root.
    for(Node child :children){
        // Set the child's root to it's parent's root
        child.root = root;

        // Update the top level account id on the child node's account so that
        //   when you go to DML update the account, it contains the new information
        //   (which _is_ why we're going through all of this work, after all)
        data.topLevelAccountId = root.data.Id;

        // Then repeat this process for this particular child
        child.propagateRoot();
    }
}

Using that, adding a new top-level account would look like this:

Node newRoot = new Node(acct, null, null);
// again, we can't set the root of a new node to itself when creating the new node.
newRoot.root = newRoot;
newRoot.children.add(oldRoot);

// Propagate to all children
newRoot.propagateRoot();

I'll leave you with one last example, deleting a root:

// This should only be done if oldRoot is indeed a root node
for(Node child :oldRoot.children){
    // Each child of the previous root is now its own root.
    // Update the child to reflect this, then propagate down the subtree
    child.root = child;
    child.propagateRoot();
}

I hope you'll be able to take things from here. Remember that this data only exists in memory until you do a DML update. You can create a method in the Node class to do a similar traversal, but end up returning a List<Account> that contains all of the accounts (which you could then perform DML on).