1

Update: Okay so this was marked as a duplicate, which I can appreciate, because it is similar to the question posed here: Get 10+ Level Hierarchy Relationship - however, I believe mine differs slightly in the sense that I am specifically looking for a way to do this without querying inside a loop/without recursive or constant SOQL Queries. I am trying to run this code on every account in our system simultaneously, roughly 19,000 records - so doing a SOQL query every time I want to get the next relationship / parent record in the chain doesn't seem ideal. I could do it I suppose, by building this into a batch class of some sort that only processes a few accounts at a time, but I was looking for a solution I could run against my entire database simultaneously. Is there no way to accomplish this without constantly querying SOQL for the new record every time? Unless I am missing something, the suggested answer in Get 10+ Level Hierarchy Relationship would only work up to ~100 times in a transaction before hitting the SOQL Query limit.

Issue: My company had a nasty prior habit of creating duplicate account records every time one of our customers cancelled and then returned to us, or every time they changed ownership. They would create a new account & contacts, relate the new account to the old account via a custom Account(Self) Lookup field, and then sell new opportunities/products to that new account. If that same customer cancelled or changed ownership again, they would repeat the whole process. Over, and over.

This has resulted in some situations where there may be a Parent-Child relationship of unknown depth of Account > Account > Account > Account > Account > etc.


I've since then created a new Customer Lifecycle tracking system that creates new related records on a single account to track a customers activation/cancellation journey with our company - now I am trying to deal with the cleanup of all of these old very fragmented account records.

Goal: Essentially what I want to do is, starting from the "Most Recent" account, which I determine via it having a Legacy_Account__c Parent object, and also having no child Accounts in that relationship, traverse up each Parent in this tree, stopping at each account along the way to collect some key data from the record and convert that data into a Customer Lifecycle record that's related to the Most Recent account before moving on to the next parent, and so on and so on, until I reach the "top" of the tree.


Problem: Every account could have a different amount of 'hops' between their most recent account and their first account, so I have no way of knowing how many levels deep to go, so I'm trying to come up with a way to do this dynamically using an Integer for loop or perhaps a While loop until a condition is met, but every time I try to tackle it I end up doing a bunch of SOQL inside loops which is not scalable.


I think I'm on the right track using a while loop, but the code I've written so far seems to only succeed in building one record in the list and store it in the map even when I know for a fact that there are account relationships that have more than one level to traverse through. It's late and I've been poking around at this code for a while now, so I'm hoping a fresh set of eyes might be able to point out some key flaws in my approach and help guide me to Valhalla.

Code:

        Map<Id,List<Account_Lifecycle__c>> AccIdToLifeCycleListMap = new Map<Id,List<Account_Lifecycle__c>>();
        Map<Id,Account> accMap = New Map<Id,Account>([SELECT Id,ParentId,Platform_Application__c,Anniversary_Date__c,AccountCancellationDate__c,Cancellation_Reason__c,Inadequate_Available_Features__c,Switched_to_Competitor__c,Legacy_Account__c,(Select Id,Name FROM Accounts2__r) FROM Account]);
        Id masterId;
        Id nextId;
        Account masterAccount;
        Account nextAccount;
        Boolean nextLevel;
        Integer level;

        for(Account a : accMap.values()){
            if(a.Legacy_Account__c != null && a.Accounts2__r.size() == 0){
                AccIdToLifeCycleListMap.put(a.Id,new List<Account_Lifecycle__c>());
            }
        }

        for(Id record : AccIdToLifeCycleListMap.keySet()){
            List<Account_Lifecycle__c> lcList = new List<Account_Lifecycle__c>();
            masterId = record;
            masterAccount = accMap.get(masterId);
            level = 0;
            nextLevel = true;
            While(nextLevel){
                if(level == 0){
                    nextId = masterAccount.ParentId;
                    level++;
                }else{
                    nextId = nextAccount.ParentId;
                    level++;
                }
                nextAccount = accMap.get(nextId);
                if(nextAccount != null){
                    lcList.add(new Account_Lifecycle__c(Account__c = masterId,
                                                        Platform_Application__c = nextAccount.Platform_Application__c,
                                                        Anniversary_Date__c = nextAccount.Anniversary_Date__c,
                                                        Cancellation_Date__c = nextAccount.AccountCancellationDate__c,
                                                        Cancellation_Reason__c = nextAccount.Cancellation_Reason__c,
                                                        Inadequate__c = nextAccount.Inadequate_Available_Features__c,
                                                        Switched_To_Competitor__c = nextAccount.Switched_to_Competitor__c));
                }else{
                    nextLevel = false;
                }
            }
            AccIdToLifeCycleListMap.put(masterId,lcList);
            System.debug('Records : ' + AccIdToLifeCycleListMap.get(masterId));
        }
  • I had implemented something similar to traverse the account hierarchy. See this post, if it helps you someway. – Jayant Das Jul 6 '18 at 4:01
0

So while the post was temporarily closed as a duplicate I kept playing with it, added some debug lines to it and ultimately figured out that its working great, but I accidentally was referencing ParentId instead of my custom relationship field.

Once I stepped through the code and realized my late night mistake, I've determined that this is a good baseline example of how to traverse through a chained relationship of unknown length without doing multiple in-loop or recursive SOQL Queries. I'll leave this code here for anyone who may come across it in the future:

public static void ConvertAccounts(){
    Map<Id,List<Account_Lifecycle__c>> AccIdToLifeCycleListMap = new Map<Id,List<Account_Lifecycle__c>>();
    Map<Id,Account> accMap = New Map<Id,Account>([SELECT Id,ParentId,Legacy_Account__r.Id,Platform_Application__c,Anniversary_Date__c,AccountCancellationDate__c,Cancellation_Reason__c,Inadequate_Available_Features__c,Switched_to_Competitor__c,Legacy_Account__c,(Select Id,Name FROM Accounts2__r) FROM Account]);
    List<Account_Lifecycle__c> lifeCyclesToInsert = new List<Account_Lifecycle__c>();
    Id masterId;
    Id nextId;
    Account masterAccount;
    Account nextAccount;
    Boolean nextLevel;
    Integer level;

    for(Account a : accMap.values()){
        if(a.Legacy_Account__c != null && a.Accounts2__r.size() == 0){
            AccIdToLifeCycleListMap.put(a.Id,new List<Account_Lifecycle__c>());
        }
    }

    for(Id record : AccIdToLifeCycleListMap.keySet()){
        List<Account_Lifecycle__c> lcList = new List<Account_Lifecycle__c>();
        masterId = record;
        masterAccount = accMap.get(masterId);
        System.debug('Master Account: ' + masterAccount + 'Master ID: ' + masterId);
        level = 0;
        nextLevel = true;
        While(nextLevel){
            System.debug('Status Check: Level = ' + level + ' while next = ' + nextLevel);
            if(level == 0){
                nextId = masterAccount.Legacy_Account__r.Id;
                System.debug('Level: ' + level + ' : Next Id Should be Parent of Master: ' + nextId);
                level++;
                System.debug('New Level: ' + level);
            }else{
                nextId = nextAccount.Legacy_Account__r.Id;
                System.debug('Level: ' + level + ' Next Id is: ' + nextId);
                level++;
                System.debug('New Level: ' + level);

            }
            nextAccount = accMap.get(nextId);
            System.debug('Next Account: ' + nextAccount);
            if(nextAccount != null){
            System.debug('Next Account Not Null');
                lcList.add(new Account_Lifecycle__c(Account__c = masterId,
                                                    Platform_Application__c = nextAccount.Platform_Application__c,
                                                    Anniversary_Date__c = nextAccount.Anniversary_Date__c,
                                                    Cancellation_Date__c = nextAccount.AccountCancellationDate__c,
                                                    Cancellation_Reason__c = nextAccount.Cancellation_Reason__c,
                                                    Inadequate__c = nextAccount.Inadequate_Available_Features__c,
                                                    Switched_To_Competitor__c = nextAccount.Switched_to_Competitor__c));
            }else{
                System.debug('No Next Level: ' + nextLevel);
                nextLevel = false;
            }
        }
        AccIdToLifeCycleListMap.put(masterId,lcList);
        //System.debug('Records : ' + AccIdToLifeCycleListMap.get(masterId));
    }
    for(Id record : AccIdToLifeCycleListMap.keySet()){
        List<Account_Lifecycle__c> lcList = AccIdToLifeCycleListMap.get(record);
        lifeCyclesToInsert.addAll(lcList);
        if(lcList.size() > 1){
            System.debug('Found Multiple Records: ' + lcList.size() + ' : ' + lcList);
        }
    }

    if(!lifeCyclesToInsert.isEmpty()){
        insert lifeCyclesToInsert;
    }
}

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.