I have a requirement where i have the following need. If the account is a parent account, i need to loop and traverse through each child account and get a list of child accounts at any level deep. Once i get all the child accounts, I need to update all the child accounts with a custom field based on the parent. I am planning to create an account insert or update trigger to do the following. Here is a question. 1. Does any of one you have any sample utility class or method which would loop through all child accounts and return me a collection of child accounts? 2. How do we also make sure that i fire the trigger only once on the parent only and it does not fire on the child also? Buyan


On question 1), an alternative to the ultimate parent approach is a query per layer:

Id accountId = '...';
Account[] allChildren = new Account[] {};
Set<Id> parentIds = new Set<Id>{accountId};
Account[] children;
do {
    children = [select Id, Name from Account where ParentId in :parentIds];
    for (Account child : children) parentIds.add(child.Id);
} while (children.size() > 0);

On question 2), if you only want the trigger to process root Accounts, only process Accounts that have a ParentId field of null.

  • Good old do/while. You could probably use a SOQL For Loop to make your do block slightly less awkward. Hmm...
    – Adrian Larson
    Sep 6 '16 at 22:30
  • Definitely consumes significantly more of your governors, but that may be an acceptable tradeoff depending on use case.
    – Adrian Larson
    Sep 6 '16 at 22:31
  • @AdrianLarson Not sure if a for loop is possible as the parentIds need clearing immediately before the query... I've used this approach when creating a tree view because the relationships between the layers is explicit. But for collecting all the Accounts (assuming ~5 levels is enough), your ultimate parent approach is more elegant.
    – Keith C
    Sep 6 '16 at 22:39
  • Posted a similar answer that could be adapted for this here: salesforce.stackexchange.com/questions/28521/… or a different version here salesforce.stackexchange.com/questions/81603/users-hierarchy/… both of which do not use a formal field however
    – Eric
    Sep 6 '16 at 22:52

The key to any such functionality is an Ultimate_Parent__c function. I think it maxes out at ten levels deep, but here is a smaller example of how it should work:

        BLANKVALUE(ParentId, Id

Then, given any account, you can find its entire hierarchy using:

SELECT Id FROM Account WHERE Ultimate_Parent__c = :originalAccount.Ultimate_Parent__c

You could limit this result to records below the original by adding a hierarchy depth formula along the lines of:

IF(Parent.Parent.ParentId != null, 3,
    IF(Parent.ParentId != null, 2,
        IF(ParentId != null, 1, 0

And a query like:

WHERE Ultimate_Parent__c = :originalAccount.Ultimate_Parent__c
AND Hierarchy_Depth__c < :originalAccount.Hierarchy_Depth__c
  • Interesting solution. Could you show me an example of combing the formula with the hierarchy depth which you had shown above? Sep 7 '16 at 0:01

Not the answer you're looking for? Browse other questions tagged or ask your own question.