3

I need to create a custom field "Grand parent account" which has the highest level of account hierarchy. Maximum child records can be up to 500 as per salesforce. So,how can i get grand parent account name if it has till 500 of levels of account hierarchy. Currently i have till 10 levels of hierarchy formula but how can i cover till 500 levels of account hierarchy.Let me know how to achieve till 500 levels of account hierarchy.

My code till 10 levels:

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"))))))))))

My trigger is below:

trigger updateUltimateParentOnAccount on Account (after insert,after update) {

List<Account> account = [SELECT id,Name,Grand_parent__c,ParentId FROM Account where id IN :trigger.newMap.keySet()];
for(Account acc : account ){
    Boolean isAccNotNull = true;

String strParentRecordId = acc.ParentId;

while(isAccNotNull) {

    Account objParentAccount = [SELECT ParentId, Name FROM Account WHERE Id = :strParentRecordId];

    if(objParentAccount.ParentId == null) {

        acc.Grand_parent__c = objParentAccount.Name;
        isAccNotNull = false;
        continue;
    }

    strParentRecordId = objParentAccount.ParentId;
}

update acc;
}

}

1
  • I think you need to revisit either your design or requirements as this issue is indicative of a potential flawed implementation. Also check this code out: salesforce.stackexchange.com/questions/28521/… - BUT regardless of the route you go with this implementation you are limited in depth but he number of SOQL queries allowed
    – Eric
    Oct 13 '15 at 13:02
4

If you try to pursue this to a more limited depth, you can greatly reduce your compile size. If you ever get to a hierarchy depth of 500 records, anything you implement will probably crash and burn, this approach included. If you expect any hierarchy to hit such depth, you should seriously reconsider your architecture.

Ultimate_Parent__c

BLANKVALUE(Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.ParentId,
    BLANKVALUE(Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.ParentId,
        BLANKVALUE(Parent.Parent.Parent.Parent.Parent.Parent.Parent.ParentId,
            BLANKVALUE(Parent.Parent.Parent.Parent.Parent.Parent.ParentId,
                BLANKVALUE(Parent.Parent.Parent.Parent.Parent.ParentId,
                    BLANKVALUE(Parent.Parent.Parent.Parent.ParentId,
                        BLANKVALUE(Parent.Parent.Parent.ParentId,
                            BLANKVALUE(Parent.Parent.ParentId,
                                BLANKVALUE(Parent.ParentId,
                                    BLANKVALUE(ParentId, Id)
)))))))))

Ultimate_Parent_Name__c

BLANKVALUE(Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name,
    BLANKVALUE(Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name,
        BLANKVALUE(Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name,
            BLANKVALUE(Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name,
                BLANKVALUE(Parent.Parent.Parent.Parent.Parent.Parent.Name,
                    BLANKVALUE(Parent.Parent.Parent.Parent.Parent.Name,
                        BLANKVALUE(Parent.Parent.Parent.Parent.Name,
                            BLANKVALUE(Parent.Parent.Parent.Name,
                                BLANKVALUE(Parent.Parent.Name,
                                    BLANKVALUE(Parent.Name, Name)
)))))))))
2
  • 3
    +1 for a spectacular use of indentation to get the point across. I never would have thought of this. As for the OP use case, it is hard to imagine humans maintaining a hierarchy of this depth to any data quality unless the application is some B2B equivalent of Ancestry.com :-)
    – cropredy
    Mar 10 '16 at 2:47
  • 1
    Another +1 for use of indentation. I've seen this code in lots of answers, but nothing indented like this to really show what's going on. Jun 27 '16 at 19:56
2

Eric is right, you will hit the governor limit in whatever way you try to achieve through Apex.

However there is a Workaround for this. If your Goal is just to get the Grand Parent, then do this:

  • Create a new custom field called GrandParent__c on Account object.

  • Through trigger(or anything else) update this field by the Name of the Account in lookup.

  • If the Lookup Account's GrandParent__c is not null, then it is a child of something. So in this case, update the GrandParent__c of THE account with the Lookup Account's GrandParent__c.
  • Similarly for the next child account and so on. The Grand parent value is passed to all the child account.

Get the grand parent with SOQL query.

Select Grandparent__c from Account;

Source: I was a biology student, and this is how the Genes are passed to the offspring.

2
  • 1
    +1 for the biology reference - perhaps a first for SFSE; that said, if relationships between ancestors change, will your solution propagate downwards into the descendants? (in biology, one's grandparents and their parents, and so on are fixed ...not true for SFDC accounts and their ancestors/descendents that can change through DML or user action)
    – cropredy
    Oct 13 '15 at 16:25
  • @crop1645: Good catch about ancestor change in SFDC Oct 13 '15 at 16:46
0

I wrote an apex library called hierarchical-search with this use case in mind.

Unit Test Demonstrating how it works:

  private static void deepRootSearch() {
    //creates a chain of X parented accounts
    List<Account> accounts = createAccountChain(200);

    RootSearch r = new RootSearch(Account.getSObjectType(), Account.ParentId);
    // find parent of 200th account
    Map<Id, Id> roots = r.search(new Set<Id>{ accounts[199].Id });
    System.assertEquals(accounts[0].Id, roots.get(accounts[199].Id));
  }

It's written to be as performant as possible and can traverse N levels deep in CEIL(N\6) SOQL queries. So in your case of 500, it should take 84 SOQL queries (I haven't actually tested it to this depth)

See this related answer or the git repo for more usage examples.

-1

You can achieve it via apex code below:

private void updateUltimateParentOnAccount(Account objAcc) {

    Boolean isAccNotNull = true;

    String strParentRecordId = objAcc.ParentId;

    while(isAccNotNull) {

        Account objParentAccount = [SELECT ParentId, Name FROM Account WHERE Id = :strParentRecordId];

        if(objParentAccount.ParentId == null) {

            objAcc.Ultimate_Parent__c = objParentAccount.Name;
            isAccNotNull = false;
            continue;
        }

        strParentRecordId = objParentAccount.ParentId;
    }

    update objAcc;
}
3
  • I am using Trigger with after insert and after update.Do i need to use before insert and before update. ?But i am getting below error: Error: updateUltimateParentOnAccount: execution of AfterUpdate caused by: System.DmlException: Update failed. First exception on row 0 with id 00128000006bm2dAAA; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, updateUltimateParentOnAccount: maximum trigger depth exceeded : Trigger.updateUltimateParentOnAccount: line 23, column 1
    – Jack vardy
    Oct 13 '15 at 11:05
  • I tried trigger with before insert and before update but still it throws error but different error.
    – Jack vardy
    Oct 13 '15 at 11:17
  • You will not be able to do it with this either as you will hit SOWL governor limits
    – Eric
    Oct 13 '15 at 12:57

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.