Identify Ultimate Parent in an Account Hierarchy

To build Account hierarchy Salesforce has native "Parent Account" lookup field in Account object. Unfortunately Salesfroce does not go further to provide native solution to automatically calculate "Ultimate Parent" or "Top Level Account" if you have more than two levels in Account hierarchy. As per our experience organizations have up to 5 (sometimes more)  levels of Account hierarchy.

Having such "Ultimate Parent" field will allow SFDC Users/Administrators to do a lot of advanced data exercises using grouping, summation or aggregation by the entire account tree.
For instance:

• total sales to multi-level hierarchies
• list of Contacts in entire account hierarchy
• views, reports and dashboards for account families

There are a few workarounds to create such useful field. Please find below the most popular workarounds with analysis about their PROS and CONS.

#1 Use formula field to calculate Ultimate Parent Name or ID

Most obvious and popular solution. Using formula field you can determine the ultimate parent in the hierarchy. Formula below returns Ultimate Parent as a text formatted to hyperlink to use it in Account layout. Click to the link will open Ultimate Account page. This formula supports up to 10 levels of Accounts hierarchy.

IF(LEN(Parent.Name) < 1, HYPERLINK("/"&Id, Name,"_parent"), 
IF(LEN(Parent.Parent.Name) <1, HYPERLINK("/"&Parent.Id,Parent.Name,"_parent"), 
IF(LEN(Parent.Parent.Parent.Name) < 1, HYPERLINK("/"&Parent.Parent.Id,Parent.Parent.Name,"_parent"), 
IF(LEN(Parent.Parent.Parent.Parent.Name) < 1, HYPERLINK("/"&Parent.Parent.Parent.Id,Parent.Parent.Parent.Name,"_parent"),
IF(LEN(Parent.Parent.Parent.Parent.Parent.Name) < 1, HYPERLINK("/"&Parent.Parent.Parent.Parent.Id,Parent.Parent.Parent.Parent.Name,"_parent"), 
IF(LEN(Parent.Parent.Parent.Parent.Parent.Parent.Name) < 1 ,HYPERLINK("/"&Parent.Parent.Parent.Parent.Parent.Id,Parent.Parent.Parent.Parent.Parent.Name,"_parent"), 
IF(LEN(Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name) < 1 ,HYPERLINK("/"&Parent.Parent.Parent.Parent.Parent.Parent.Id,Parent.Parent.Parent.Parent.Parent.Parent.Name,"_parent"), 
IF(LEN(Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name) < 1 ,HYPERLINK("/"&Parent.Parent.Parent.Parent.Parent.Parent.Parent.Id,Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name,"_parent"), 
IF(LEN(Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name) < 1 ,HYPERLINK("/"&Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Id,Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name,"_parent"),
IF(LEN(Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name) < 1 ,HYPERLINK("/"&Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Id,Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name,"_parent"), "Ultimate Parent Beyond 10 Levels"))))))))))

If needed formula could be updated to return text instead of hyperlink.

PROS: Easy to implement
CONS: Covers very limited number of use cases because the result of the formula is text. In move advanced cases you need Ultimate Account as a lookup field to use in reports, APEX code or in Roll-up formulas.

#2 Singe synchronous APEX trigger to calculate the Ultimate Parent when Account created or updated.

The trigger requires and has to calculate/re-calculate custom "Ultimate Parent" lookup field on Account object (read-only for users).

Let's consider few scenarios using following Accounts hierarchy tree as example:

Accounts 1 and 8 are "Ultimate or Top Accounts" (i.e. do not have parent Accounts)

Case 1: New Account created as a child of Account 3. The trigger has to calculate Account 1 as Ultimate Parent for new Account and update this Account with calculated value for "Ultimate Parent" field. This is easy recursive update.

Case 2: Account 3 updated to be the child of Account 1 instead of Account 2 (i.e. Account 3 moved up along the Account 1 hierarchy tree branch).
In this case Account 1 still Ultimate Parent for Account 3, 4 and trigger no need to update these two Accounts.

Case 3: Account 2 updated to be on the top of  the hierarchy tree  (i.e. Account 2 will become Ultimate Account for all related child Accounts).
In this case the trigger has to calculate Account 2 as Ultimate Parent for Accounts 3-7 and update these five Accounts with new Ultimate Parent. This is quite complicated recursive update. To find all impacted child Accounts the trigger will have to traverse down through Account 3 and Account 5 branches (layers) to identify all related to these branches Accounts and update them with new Ultimate Parent value.

Case 4: Account 2 updated to be the child of Account 8 instead of Account 1 (i.e. Account 2 with all related child Accounts moved to another [Account 8] hierarchy tree branch).
In this case trigger has to calculate Account 8 as Ultimate Parent for Accounts 2-7 and update these six Accounts with new Ultimate Parent value. This is also complicated recursive update which involve multiple parent to child account layers.

The most complicated part here is to traverse parent-child relationships from account to account. Unfortunately SFDC does not support such traverse so you can not do this in a single SOQL query. What you can - is to include parent.parent.parent.parent.parent.id in your SOQL select. Than loop through the result and if none of them are null - launch another SOQL query until you find a parent without parent. And then assign it as Ultimate Parent for involved Accounts.

Sounds complicated even in theory. But after number of experiments and brainstorming we finally built such trigger and even used it for a while. Unfortunately the trigger code was pretty ugly and solution (as appeared) was fragile. In particular cases - when hierarchy has a lot of parent to child account layers - trigger hits SFDC's governor limits. In cases when the trigger was able to handle governor limits it took a lot of time to finish - up to 5 seconds - this was not acceptable from performance point of view.

That's why we DO NOT recommend such approach and do not sharing here the trigger's code.

PROS: Works in not complicated SFDC implementations where accounts hierarchy does not have a lot of parent to child account layers.
CONS: Covers only limited use cases. Ugly and fragile code which apparently hits SFDC governor limits. Performance issues.

#3 Two components solution: synchronous APEX trigger and APEX batch

In this solution the trigger has to handle only inserts and updates along the single Account hierarchy tree branch (see Cases 1 and 2 above) and use overnight APEX batch to catch and calculate more complicated cases.

APEX batch class has more wider governor limits, could be launched overnight and does not have strict performance requirements.

The final solution consists of following components:

• custom "Ultimate Parent" lookup field on Account object (read-only for users).
• an APEX Trigger that populates "Ultimate Parent" field for new created or updated Accounts
• scheduled APEX batch class to recalculate all Accounts to catch cases not covered by trigger (see Cases 3 and 4 above).

PROS: The solution covers all possible use cases. Stable code which does not hit SFDC governor limits. No performance issues.
CONS: In cases when Account moved to another branch (family) Ultimate Parent will be re-calculated only next night. But such scenarios happens quite rare.

APEX Trigger: SetUltimateParent

trigger SetUltimateParent on Account (before insert, before update) 
/*
Version        : 2.0
Company        : Websolo Inc. 
Description    : The trigger calculates Ultimate Parent ID ONLY for the updated/inserted Account with Hierarchy of parent Accounts to 5 levels up.

Scheduled APEX job will recalculate Ultimate Parent ID for all Account overnight to make sure they calculated properly in case if for example Account moved to another Account
*/ 
{
    list<Account> ListAcc = new list<Account>();
    set<id> idAcc = new set<id>();
    for(Account a: Trigger.new)
    {
        if(a.ParentId != null)
        {
            ListAcc.add(a);
            idAcc.add(a.id);            
        }
    }
    
    List<Account> AccUltP = [select id, Name,
                                     ParentId,
                                     Parent.ParentId,
                                     Parent.Parent.ParentId,
                                     Parent.Parent.Parent.ParentId,
                                     Parent.Parent.Parent.Parent.ParentId                                     
                                     from 
                                        Account
                                     where 
                                        ParentId != null
                                        and
                                        id IN: idAcc];   
   if(AccUltP.size() > 0)
   {                                                                        
       for(Account a: ListAcc)
       {
        for(Account b: AccUltP)
        {           
            if(a.id == b.id)
            {
                if(b.Parent.Parent.Parent.Parent.ParentId != null)
                {
                    a.Ultimate_Parent__c = b.Parent.Parent.Parent.Parent.ParentId;
                }           
                else
                {
                    if(b.Parent.Parent.Parent.ParentId != null)
                    {
                        a.Ultimate_Parent__c = b.Parent.Parent.Parent.ParentId;
                    }       
                    else
                    {
                        if(b.Parent.Parent.ParentId != null)
                        {
                            a.Ultimate_Parent__c = b.Parent.Parent.ParentId;
                        }       
                        else
                        {
                            if(b.Parent.ParentId != null)
                            {
                                a.Ultimate_Parent__c = b.Parent.ParentId;
                            }       
                            else
                            {
                                if(b.ParentId != null)
                                {
                                    a.Ultimate_Parent__c = b.ParentId;
                                }
                                else
                                {
                                    a.Ultimate_Parent__c = b.id;
                                }                               
                            }                   
                        }               
                    }           
                }                   
            }
         }
      }
   }                                    
}

APEX Batchable Class: UltimateParentUpdateBatchable

/*
Version        : 2.0
Company        : Websolo inc. 
Date           : 11/2013
Description    : This batchable class calculates Ultimate Parent for Account with Hierarchy of parent Accounts to 5 levels up.

*/ 
global class UltimateParentUpdateBatchable implements Database.Batchable<SObject>, Database.Stateful 
{
    global Database.QueryLocator start(Database.BatchableContext bc) 
    {
        return  Database.getQueryLocator([select id, Name, ParentId, Parent.ParentId, Parent.Parent.ParentId, Parent.Parent.Parent.ParentId, Parent.Parent.Parent.Parent.ParentId from Account]);
    }
    global void execute(Database.BatchableContext bc, List<SObject> batch)      
    {               
        list<Account> ListAcc = new list<Account>();
        set<id> idAcc = new set<id>();  
        for (Account a : (List<Account>) batch) 
        {
           if(a.ParentId != null)
           {
              ListAcc.add(a);
              idAcc.add(a.id);            
           }                
        }
        List<Account> AccUltP = new List<Account>();
        if(Test.isRunningTest())
        {
            Account Ac1 = new Account();
            Ac1.id = ListAcc[0].id;
            Ac1.Name = 'Test1';
            AccUltP.add(Ac1);
        }
        else
        {
                            AccUltP = [select id, Name,
                                             ParentId,
                                             Parent.ParentId,
                                             Parent.Parent.ParentId,
                                             Parent.Parent.Parent.ParentId,
                                             Parent.Parent.Parent.Parent.ParentId                                     
                                             from 
                                                Account
                                             where 
                                                ParentId != null
                                                and
                                                id IN: idAcc]; 
        }  
       if(AccUltP.size() > 0)
       {                                                                        
           for(Account a: ListAcc)
           {
            for(Account b: AccUltP)
            {           
                if(a.id == b.id)
                {
                    if(b.Parent.Parent.Parent.Parent.ParentId != null)
                    {
                        a.Ultimate_Parent__c = b.Parent.Parent.Parent.Parent.ParentId;
                    }           
                    else
                    {
                        if(b.Parent.Parent.Parent.ParentId != null)
                        {
                            a.Ultimate_Parent__c = b.Parent.Parent.Parent.ParentId;
                        }       
                        else
                        {
                            if(b.Parent.Parent.ParentId != null)
                            {
                                a.Ultimate_Parent__c = b.Parent.Parent.ParentId;
                            }       
                            else
                            {
                                if(b.Parent.ParentId != null)
                                {
                                    a.Ultimate_Parent__c = b.Parent.ParentId;
                                }       
                                else
                                {
                                    if(b.ParentId != null)
                                    {
                                        a.Ultimate_Parent__c = b.ParentId;
                                    }
                                    else
                                    {
                                        a.Ultimate_Parent__c = b.id;
                                    }                               
                                }                   
                            }               
                        }           
                    }                   
                }
             }
          }
          update ListAcc;
       }                    
    }
    global void finish(Database.BatchableContext bc) 
    {
        
    }
    private static testMethod void test() 
    {
        Test.startTest();
        Account Acc1 = new Account();
        Acc1.Name = 'Test1';
        insert Acc1;
        Account Acc2 = new Account();
        Acc2.Name = 'Test2';        
        Acc2.ParentId = Acc1.id;
        insert Acc2;
        
        Test.stopTest();     
    }       
}

APEX Batchable Class: UltimateParentUpdateBatchable

global class UltimateParentUpdateSchedulable implements Schedulable
{
    global void execute(SchedulableContext ctx) 
    {
        Database.executeBatch(new UltimateParentUpdateBatchable(), 200);
    }
    private static testMethod void test() {
        Test.startTest();
        System.schedule('UltimateParentUpdateBatchable', '0 0 0 * * ?', new UltimateParentUpdateSchedulable());
        Test.stopTest();
    }    
}