5

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.

  • 1
    Can you show me your code..@Rahul Sharma – Pavan tej Apr 4 '16 at 12:24
  • I don't have much of the code to share over here. Currently its in design phase, existing logic is pretty bad with query inside a for loop. – Raul Apr 4 '16 at 15:24
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);
}
| improve this answer | |
  • Thanks for your reply Adrain. Unfortunately the problem with Ultimate Parent Id formula is we are hitting this limit - you have reached the maximum number of 15 object references [help.salesforce.com/apex/… – Raul Apr 4 '16 at 15:03
  • @Rahul I belIeve you can get it increased if you open a case with support. – Adrian Larson Apr 4 '16 at 15:08
  • As per the help document, it says max of 15 are possible. I will try to get rid of unneeded references. Also I am bit concerned in getting the updated values in the formula fields in before or after triggers. I think I have to try this, Also I really liked this solution. Thanks for your help! – Raul Apr 4 '16 at 15:23
  • Unfortunately we can't use this approach as we are near to number of field reference limit. :( – Raul Apr 5 '16 at 21:03
1

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

| improve this answer | |
  • we have already using 9 field references in formula, workflows, etc. We can't use the formula field approach. – Raul Apr 5 '16 at 21:04

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.