6

There is a field on Account called Account.Ultimate_Parent__c. I want to update(through trigger) this field with correct value whenever Account.Parent field is Changed.

Although, Ultimate Parent could be at any level up in the hierarchy which seems not possible to find using SOQL in a loop otherwise it will reach apex governor limit. Also accessing field in a query like parent.parent.parent will limit no of levels we can go up to find the Ultimate Parent.

Is there any way somehow we can pull all the records in the Map and then find out Ultimate Parent? Because after updating account with its Ultimate Parent, we have to update all of its child accounts down in the hierarchy to have a correct Ultimate Parent.

As shown in the following diagram that all child accounts have the same Ultimate Parent i.e 'A1'

enter image description here

1
  • I suspect this can be decomposed into two problems: one, which is quite a bit simpler, is writing a trigger that works for account inserts and updates when the Ultimate Parent field is already populated on existing data; and another, which is a bit trickier, is populating the Ultimate Parent field in a current data. set. Is using an ETL solution for the initial population an option?
    – David Reed
    Jan 8 '18 at 0:21
3

Your solution will really depend on how many levels you need to be able to support.

For instance, if you can rely on only going 10 levels deep then you can just use a formula field as per Adrian's answer to Need a custom field Grand parent Account which has highest level of account hierarchy.


Another alternative is to use a text field to maintain the hierarchy. It would essentially store the path from the current node to the ultimate parent.

E.g. Using your identifiers. In reality you would use the 18 character record IDs and a suitable delimiter.

  • For B4 it would be A1|B1|B2
  • For B3 A1|B1

Then if there is ever a modification to a non-leaf nodes parent you can perform a SOQL search to find all the affected nodes that need to be updated. Potentially doing so via a batch process to correct their parent relationship.

1
  • thanks for sharing the idea to traverse through the nodes. Second solution works for me and I am going to implement it because my concern was not to make too many soqls and efficiently update all child accounts in the hierarchy.
    – Mahwish
    Jan 8 '18 at 23:23
0

I recently wrote an apex library called hierarchical-search that can help set these fields and keep them in sync in realtime (or near realtime, via @future).

It currently provides two features:

  1. Finds all "Children" of a record
  2. Finds the "Ultimate" parent (root) of a record

It supports unlimited levels and is optimized to use as few queries as possible.

For example, here's a Schedulable Batch class to populate a lookup called Ultimate_Parent__c on the Account:

/**
 * Author: Charlie@callaway.cloud
 * Batch to set Ultimate_Parent__c
 */
global class UltimateParentBatch implements Database.Batchable<sObject>, Schedulable {
    global Database.QueryLocator start(Database.BatchableContext BC) {
        // all accounts which do not have parents
        return Database.getQueryLocator([SELECT Id FROM Account WHERE ParentId = null]);
    }

    global void execute(Database.BatchableContext BC, List<sObject> scope) {
        Map<Id, Account> parentAccounts = new Map<Id, Account>((List<Account>) scope);

        DescendantSearch ds = new DescendantSearch(Account.getSObjectType(), Account.ParentId);
        Map<Id, Id[]> children = ds.search(parentAccounts.keySet());
        Account[] childrenToUpdate = new List<Account>{};
        for (Id parentId : children.keySet()) {
            for (Id childId : children.get(parentId)) {
                childrenToUpdate.add(new Account(Id = childId, Ultimate_Parent__c = parentId));
            }
        }
        update childrenToUpdate;
    }

    global void finish(Database.BatchableContext BC) {
    }

    /* Scheduling Methods */

    global void execute(SchedulableContext SC) {
        Database.executeBatch(this);
    }
}

Writing a trigger is similar.

Plan on adding examples to the repo or even making an unlocked package for this at some point.

0

If Trigger is OK then,

In below code Ultimate_Parent_Account__c is a Lookup(Account) field.

trigger UPA_Trigger on Account (before insert,before update,after update, before delete) {
if(Trigger.isBefore){
    if(Trigger.isInsert||Trigger.isUpdate){//before insert, before update
        List<Id> parents=new List<Id>();
        for(Account a: Trigger.New){              
                parents.add(a.ParentId);
        }         
        Map<Id,Account> parMaps=new Map<Id,Account>([Select Id, ParentId, Ultimate_Parent_Account__c From Account where Id in:parents]);
        for(Account a:Trigger.New){
            a.Ultimate_Parent_Account__c=a.ParentId==null
                ?null
                :parMaps.get(a.ParentId).Ultimate_Parent_Account__c==null
                    ?parMaps.get(a.ParentId).Id
                    :parMaps.get(a.ParentId).Ultimate_Parent_Account__c;
        }   
    }else{//before delete
        List<Account> acc=[Select Id, ParentId from Account Where ParentId in :Trigger.oldMap.keySet()];
        for(Account a:acc){
            a.ParentId=null;
        }
        update acc;
    }
}else{//after update
    List<Account> childAccs=[Select Id,ParentId,Ultimate_Parent_Account__C from Account Where ParentId in:Trigger.oldMap.keySet()];
    Map<Id,Account> parMap=Trigger.oldMap;//considering updated accounts are parent itself
    for(Account a:childAccs){
        a.Ultimate_Parent_Account__c=a.ParentId==null
            ?null
            :parMap.get(a.ParentId).Ultimate_Parent_Account__c==null
                ?parMap.get(a.ParentId).Id
                :parMap.get(a.ParentId).Ultimate_Parent_Account__c;
    }
    update childAccs;
}
}

or

if your UPA's level is less then the Apex Soql limit then

public String GetTopLevleElement( String objId ){
    Boolean topLevelParent = false;
    while ( !topLevelParent ) {
        Account a = [ Select Id, ParentId From Account where Id =: objId limit 1 ];
        if ( a.ParentID != null ) {
            objId = a.ParentID;
        }
        else {
            topLevelParent = true;
        }
    }
    return objId ;
}

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.