Push fields down the Account hierarchy

Question

Have a requirement related to Account hierarchy:

1. Field on Account which should display the name of Ultimate parent at any child level.
2. Certain fields would be such that if at any level, user changes the fields at parent, all the child records under it should be updated. The field values would be based on the latest updated parent/superparent not Ultimate parent.

Existing Solution- This was an existing design implemented using triggers, but was hardcoded upto 5 levels due to following limitation:

SOQL- "When dealing with Child to Parent, you can traverse up to 5 levels."

Option 1: As it is a trigger, I can't query all the records to get all Accounts under the hierarchy, due to 50k query limit.

Option 2: Using ultimate parent formula as suggested by Mr. Adrain. Hitting maximum number of 15 object references limit.

It would be great to know if anyone has an idea on how to implement the functionality with better design.

Answer 1

The name part is simple. It should be obvious how to expand the below to arbitrary depth

BLANKVALUE(Parent.Parent.Parent.Name,
    BLANKVALUE(Parent.Parent.Name,
        BLANKVALUE(Parent.Name, Name)
))

You can follow a similar concept with Id:

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

In an after update trigger, you can get child records based on the second formula quite simply:

List<Account> children = [
    SELECT Ultimate_Parent_Id__c FROM Account
    WHERE Ultimate_Parent_Id__c IN :newRecords
    AND Id NOT IN :newRecords
];

You will probably want to group the results:

Map<Id, List<Account>> children = Map<Id, List<Account>>();
for (Account child : [/*query*/])
{
    if (!children.containsKey(child. Ultimate_Parent_Id__c))
        children.put(child. Ultimate_Parent_Id__c, new List<Account>());
    children.get(child. Ultimate_Parent_Id__c).add(child);
}

Answer 2

I can't think of any real life example of the Account to have parents over 15 levels deep. The formula below could have the same limitation, but is much simpler IMO: IF(ISNULL(ParentId),NULL,NULLVALUE(Parent.Ultimate_Parent_Id__c, ParentId))