+ Start a Discussion
Jeff BloomerJeff Bloomer 

Count Number of Parent Accounts

I'm writing a trigger that updates two fields in a custom object leverages a Related Account lookup field to populate two other lookups for the Parent account and Ultimate Parent account.  The Related Account Id already exists in the data fed by survey information received through ClickTools, but the trigger's job is to populate Parent account and Ultimate Parent account.  The point is, I need to populate Parent account with the first parent it finds and  then the Ultimate parent at the top of the list.  It seems to me that the only way I can effectively do this is by counting the number of parent accounts up the hierarchy from the account receiving the data, so here is my issue:

 

I've seen a number of examples for counting child accounts, but what I'm wanting to do is count the number of accounts above an account all the way to the top of the tree.  Here's an example:

 

  • Account 1
    • Account 2
      • Account 3
        • Account 4
        • Account 5
          • Account 7
        • Account 6

I want to know that, for Account 7, there are three parents above it.  I also want to obtain the ID and Type from each Parent above it.

 

This being said, I want to be able to do this in a variable situation, whether it's 2 or 10 layers deep.  Any ideas?

 

 

Best Answer chosen by Admin (Salesforce Developers) 
Jeff BloomerJeff Bloomer

As I believe it is only fair to provide my full solution now that I have benefitted from input, here is the full code for the trigger I just completed.  Also, even though this post will be marked as the solution post, I will make sure that mcrosby gets the Kudos.  Thanks again!

 

/**
    TRG_SurveyFeedback_UpdateParentAccounts
    
    @description
    Adds/Updates the Parent and Ultimate Parent accounts for the 
    Related Account on the survey results.
    
    @author Jeff Bloomer
    jeff.bloomer@standardregister.com
    937-221-3513
    
    @version 9/24/2013
*/

trigger TRG_SurveyFeedback_UpdateParentAccounts on Survey_Feedback__c (before insert, before update) {

    Set<ID> setAcctId = new Set<ID>();				//Holds the Account Id from the Survey Feedback			
    List<Account> currentAccount = new List<Account>();		//Holds Related Account on Survey Feedback
    List<Account> parentAccount = new List<Account>();		//Holds list of Parent Accounts for Related Account
    String relAcctId = '';						//Related Account Id
    Integer topAccount = 0;						//Holds number of top-most account in parentAccount list
    Boolean boolParentAssnd = false;				//Is Parent Account Assigned yet?

    //Upon the addition or update of a new Survey Feedback
    //record, obtain the Id of the Related Account (Account__c).
    for (Survey_Feedback__c sfb: Trigger.new){
        setAcctId.add(sfb.Account__c);
    }
	
    //For the Related Account in setAcctId, obtain the Account Id and Name.
    currentAccount = [SELECT Id, Name FROM Account WHERE Id in :setAcctId];
    //Assign Account Id to a string variable to use in the getAccountHierarchy function below.
    relAcctId = currentAccount[0].Id; 
	
    //Get the list of Accounts in the hierarchy for relAcctId.
    parentAccount = getAccountHierarchy(relAcctId);

    /**
	getAccountHeirarchy(string)
	
	@description
	The getAccountHierarchy function, which requires an accountId as a paramenter,
	fetches the list of accounts appearing above the accountId in he hierarchy.
	It returns a list (accList) that contains the following information for the Account:
	
	Id, Name, ParentId, and Location_Type__c
		
	@author Michael Crosby
	michael.crosby@newedge.com
    */
	
    List<Account> getAccountHierarchy(String accountId){
		
	List<Account> accList = new List<Account>();		//Holds list of accounts above accountId
        Boolean isTop = false;					//Is this the top-most account?
        
        //Until the top of the hierarchy has been reached,
        //populate accList with the accounts above accountId.
        while (!isTop){
            //Get the Id, Name, ParentId, and Location_Type__c for accountId
            Account a = [SELECT Id, Name, ParentId, Location_Type__c FROM Account WHERE Id =: accountId limit 1];
            //As long as the ParentId field is not blank, assign it to accountId
            //for the next time this loop returnes to the query above.
            if (a.ParentId != null){
            	accountId = a.ParentId;
            }else{//Otherwise, the top of the hierarchy has been reached
            	isTop = true;
            }
            //Append the account information retrieved to accList.
            accList.add(a);
        }
        return accList;
    }
    //End of getAccountHeirarchy
	
    //Get the index of the top-most account in parentAccount
    //Note: because array indexes always start with zero, the
    //last index number will be one less than its size.
    topAccount = parentAccount.size() - 1;

    //Now that the hierarchy list has been fetched, populate
    //the Parent__c and Ultimate_Parent__c lookup fields with
    //the pertinent information.
    for(Survey_Feedback__c sfb: Trigger.new){

	//Step through parentAccount to find the first Parent
	//account and assign it to the Parent__c field.
	//Note: start with index 1, because index 0 is the Related Account.
	for(Integer i = 1; i < parentAccount.size(); i++){
	
            //As long as Location_Type__c is Parent and this field has
	    //not yet been assigned during this process, populate Location_Type__c.
	    //If Location_Type__c is assigned in this step, change boolParentAssnd to true.
	    if(parentAccount[i].Location_Type__c == 'Parent' && boolParentAssnd == false){
		sfb.Parent_Account__c = parentAccount[i].Id;
		boolParentAssnd = true;
	    }
	}
	//As long as the top-most account is an Ultimate Parent and not 
	//the Related Account itself, populate Ultimate_Parent_Account__c. 
	if(parentAccount[topAccount].Location_Type__c == 'Ultimate Parent' && parentAccount[topAccount].Id !=  relAcctId){
	    sfb.Ultimate_Parent_Account__c = parentAccount[topAccount].Id;
	}		
    }
}

 

All Answers

mcrosbymcrosby

Here's something that you could try:

 

List<Account> getAccountHierarchy(string accountId)
{
	List<Account> accList = new List<Account>();
        Boolean isTop = false;
        while (!isTop) 
        {
            Account a = [select acc.Id, acc.ParentId, acc.Type From Account acc where acc.Id =: accountId limit 1];
            
            if (a.ParentID != null) 
            {
                accountId = a.ParentID;
            }
            else 
            {
                isTop = true;
            }
            accList.add(a);
        }
        return accList;
}

 Using the returned list, you should be able to determine the count of the hierarchy and get the Id, Type for each.

 

Since you want to do this in a trigger, I'm not sure if you would run into any limits with this or not, so obviously some indepth testing would be needed. Worth a shot.

Jeff BloomerJeff Bloomer

My brain must have been fried yesterday.  It just goes to show you that asking someone else helps put a fresh perspective on things.

Anyway, the code you provided needed just a couple of tweaks to make it work.  ParentId was ParentID in the 'if' statement below on lines 7 and 8.

List<Account> getAccountHierarchy(String accountId){
	
	List<Account> accList = new List<Account>();
	Boolean isTop = false;
	while (!isTop){
		Account a = [SELECT Id, Name, ParentId, Location_Type__c FROM Account WHERE Id =: accountId limit 1];
		if (a.ParentId != null){
			accountId = a.ParentId;
		}else{
			isTop = true;
		}
		accList.add(a);
	}
	return accList;
}

 

 I will be posting the full code for the trigger as soon as I have it completely cleaned up for the benefit of other developers, hopefully, by the end of the day.

mcrosbymcrosby

Sorry about the typo as I was writing off the cuff and sometimes I get "shift" happy with my typing.  Glad to see you got it working though.

Jeff BloomerJeff Bloomer

Believe me, it happens to the best of us.  =)

Jeff BloomerJeff Bloomer

As I believe it is only fair to provide my full solution now that I have benefitted from input, here is the full code for the trigger I just completed.  Also, even though this post will be marked as the solution post, I will make sure that mcrosby gets the Kudos.  Thanks again!

 

/**
    TRG_SurveyFeedback_UpdateParentAccounts
    
    @description
    Adds/Updates the Parent and Ultimate Parent accounts for the 
    Related Account on the survey results.
    
    @author Jeff Bloomer
    jeff.bloomer@standardregister.com
    937-221-3513
    
    @version 9/24/2013
*/

trigger TRG_SurveyFeedback_UpdateParentAccounts on Survey_Feedback__c (before insert, before update) {

    Set<ID> setAcctId = new Set<ID>();				//Holds the Account Id from the Survey Feedback			
    List<Account> currentAccount = new List<Account>();		//Holds Related Account on Survey Feedback
    List<Account> parentAccount = new List<Account>();		//Holds list of Parent Accounts for Related Account
    String relAcctId = '';						//Related Account Id
    Integer topAccount = 0;						//Holds number of top-most account in parentAccount list
    Boolean boolParentAssnd = false;				//Is Parent Account Assigned yet?

    //Upon the addition or update of a new Survey Feedback
    //record, obtain the Id of the Related Account (Account__c).
    for (Survey_Feedback__c sfb: Trigger.new){
        setAcctId.add(sfb.Account__c);
    }
	
    //For the Related Account in setAcctId, obtain the Account Id and Name.
    currentAccount = [SELECT Id, Name FROM Account WHERE Id in :setAcctId];
    //Assign Account Id to a string variable to use in the getAccountHierarchy function below.
    relAcctId = currentAccount[0].Id; 
	
    //Get the list of Accounts in the hierarchy for relAcctId.
    parentAccount = getAccountHierarchy(relAcctId);

    /**
	getAccountHeirarchy(string)
	
	@description
	The getAccountHierarchy function, which requires an accountId as a paramenter,
	fetches the list of accounts appearing above the accountId in he hierarchy.
	It returns a list (accList) that contains the following information for the Account:
	
	Id, Name, ParentId, and Location_Type__c
		
	@author Michael Crosby
	michael.crosby@newedge.com
    */
	
    List<Account> getAccountHierarchy(String accountId){
		
	List<Account> accList = new List<Account>();		//Holds list of accounts above accountId
        Boolean isTop = false;					//Is this the top-most account?
        
        //Until the top of the hierarchy has been reached,
        //populate accList with the accounts above accountId.
        while (!isTop){
            //Get the Id, Name, ParentId, and Location_Type__c for accountId
            Account a = [SELECT Id, Name, ParentId, Location_Type__c FROM Account WHERE Id =: accountId limit 1];
            //As long as the ParentId field is not blank, assign it to accountId
            //for the next time this loop returnes to the query above.
            if (a.ParentId != null){
            	accountId = a.ParentId;
            }else{//Otherwise, the top of the hierarchy has been reached
            	isTop = true;
            }
            //Append the account information retrieved to accList.
            accList.add(a);
        }
        return accList;
    }
    //End of getAccountHeirarchy
	
    //Get the index of the top-most account in parentAccount
    //Note: because array indexes always start with zero, the
    //last index number will be one less than its size.
    topAccount = parentAccount.size() - 1;

    //Now that the hierarchy list has been fetched, populate
    //the Parent__c and Ultimate_Parent__c lookup fields with
    //the pertinent information.
    for(Survey_Feedback__c sfb: Trigger.new){

	//Step through parentAccount to find the first Parent
	//account and assign it to the Parent__c field.
	//Note: start with index 1, because index 0 is the Related Account.
	for(Integer i = 1; i < parentAccount.size(); i++){
	
            //As long as Location_Type__c is Parent and this field has
	    //not yet been assigned during this process, populate Location_Type__c.
	    //If Location_Type__c is assigned in this step, change boolParentAssnd to true.
	    if(parentAccount[i].Location_Type__c == 'Parent' && boolParentAssnd == false){
		sfb.Parent_Account__c = parentAccount[i].Id;
		boolParentAssnd = true;
	    }
	}
	//As long as the top-most account is an Ultimate Parent and not 
	//the Related Account itself, populate Ultimate_Parent_Account__c. 
	if(parentAccount[topAccount].Location_Type__c == 'Ultimate Parent' && parentAccount[topAccount].Id !=  relAcctId){
	    sfb.Ultimate_Parent_Account__c = parentAccount[topAccount].Id;
	}		
    }
}

 

This was selected as the best answer