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

closed as off-topic by Eric, Sebastian Kessel, Vamsi Krishna, Tushar Sharma, Ratan Paul Sep 7 '16 at 5:05

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions on problems in code you've written must describe the specific problem and include valid code to reproduce it. For help writing short, self-contained syntactically-valid examples, see: SSCCE.org" – Eric, Sebastian Kessel, Vamsi Krishna, Tushar Sharma, Ratan Paul
If this question can be reworded to fit the rules in the help center, please edit the question.


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? – buyan kumar Sep 7 '16 at 0:01

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