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