function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
ppiyushppiyush 

Ultimate parent for Accounts, Contacts, Projects

Hello!

 

We are in the professional services business, and we often work with organizations with multi-level hierarchies (3 to 4 levels is very average). I was wondering if anyone knows how one can create a script to automatically find out the ultimate parent of any account / contact / project, and store the result in a field on every entity.

 

this would allow us to create savvy rollups in our reporting and dashboarding. any help will be much appreciated.

 

thanks,

Pranav

Best Answer chosen by Admin (Salesforce Developers) 
Jeremy.NottinghJeremy.Nottingh

I could see doing this with an Apex Trigger, yes. However, a lookup field on a record cannot point to that record itself. So a whole hierarchy may have lookups to the one "top parent" Account, but that Account will not be included. In SF Reports, that Account will not be sorted or grouped with all of its children and grandchildren.

 

However, this may not be a problem for your use. In that case, I would build a Trigger to handle inserts and updates, and use some kind of batch process to catch up the existing Accounts.

 

Jeremy

All Answers

Jeremy.NottinghJeremy.Nottingh

We have a formula field something like that for our Account hierarchies.

 

if( CONTAINS( ParentId , "0"), 
  if( contains( Parent.ParentId , "0"), 
    Parent.Parent.Id, 
    Parent.Id) , 
  Id 
)

 This goes up to two levels up, but you should be able to extend it to 3 or 4. Of course, you can use Name instead of ID if you like. If you use this field to Group By in your reports, it will keep the parent with all of its children together. If you do that, using Name will probably be easier on the eyes. 

 

Jeremy

 

ppiyushppiyush

Hi Jeremy,

 

that was very helpful. By doing this, I was indeed able to get an ultimate parent field on the account field. 

 

I noticed that a formula field can only be of type number, text etc... Can't I create a LOOKUP field on Accounts? That way I would be able to refer to the ultimate parent in much easier ways - even in reporting - I dont have to group by - instead I can just say all accounts with Parent Account = certain account...

 

Any way of programming this in apex?

 

Best,

Pranav

Jeremy.NottinghJeremy.Nottingh

I could see doing this with an Apex Trigger, yes. However, a lookup field on a record cannot point to that record itself. So a whole hierarchy may have lookups to the one "top parent" Account, but that Account will not be included. In SF Reports, that Account will not be sorted or grouped with all of its children and grandchildren.

 

However, this may not be a problem for your use. In that case, I would build a Trigger to handle inserts and updates, and use some kind of batch process to catch up the existing Accounts.

 

Jeremy

This was selected as the best answer
ppiyushppiyush

thanks jeremy - thats actually exactly what i have been working on - and your reply confirms that this is the right way of doing it.

 

how do you do batch processes in salesforce?

Jeremy.NottinghJeremy.Nottingh

The Apex Developers Guide (included in the Help Contents with Eclipse IDE) is a good place to start. Search for "batch apex" in Eclipse Help.

 

Jeremy

ppiyushppiyush

Jeremy,

 

I have developed the following code, but for some reason, nothing inside the execute method is getting executed! :) any guidance would be great...

 

 

global class MassUpdateParents implements Database.Batchable<sObject>{
	
	global final string query;
	
	global MassUpdateParents ()
	{
	   query = 'SELECT id, Ultimate_Parent_Account__c, Ultimate_Parent_Id__c FROM Contact';
	}
	
	global Database.QueryLocator start(Database.BatchableContext BC){
	
	   return Database.getQueryLocator(query);
	}
	
	global void execute(Database.BatchableContext BC, List<Contact> scope){
	
	  for(Contact o : scope){
	  	o.Ultimate_Parent_Account__c = o.Ultimate_Parent_Id__c;
	  }
	}
	
	
	global void finish(Database.BatchableContext BC){
	  // Get the ID of the AsyncApexJob representing this batch job  
	  // from Database.BatchableContext.    
	  // Query the AsyncApexJob object to retrieve the current job's information.  
	
	 AsyncApexJob a = [Select Id, Status, NumberOfErrors, JobItemsProcessed,
	   TotalJobItems, CreatedBy.Email
	   from AsyncApexJob where Id =:BC.getJobId()];
	
	  // Send an email to the Apex job's submitter notifying of job completion.  
	  Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
	  String[] toAddresses = new String[] {a.CreatedBy.Email};
	  mail.setToAddresses(toAddresses);
	  mail.setSubject('Apex Sharing Recalculation ' + a.Status);
	  mail.setPlainTextBody('The batch Apex job processed ' + a.TotalJobItems +
	    ' batches with '+ a.NumberOfErrors + ' failures.');
	
	  Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });
	}	
}

 

 

Controller for my visual force page:

 

 

public class UpdateParentController {

    public void batchexe() {
		MassUpdateParents batchApex = new MassUpdateParents();
		ID batchprocessid = Database.executeBatch(batchApex);
    } 

}

 

visualforce page:

 

 

<apex:page controller="UpdateParentController">
<!-- Begin Default Content REMOVE THIS -->
<h1>Congratulations</h1>
This is your new Page
<!-- End Default Content REMOVE THIS -->
<apex:form >
<apex:commandButton action="{!batchexe}" value="Execute"/>
</apex:form>
</apex:page>

 

 

Any clue?

 

Jeremy.NottinghJeremy.Nottingh

I haven't worked with Batches a whole lot, but I wonder if you actually have any data to run through. If your query results are null, the execute method will not run.

 

Also, try replacing the variable "query" with the actual text of your query with [] square brackets around it, and see if that makes a difference.

 

Good luck,

 

Jeremy

forecast_is_cloudyforecast_is_cloudy

Pranav - you need to perform a DML update in your Execute method - otherwise no changed will be committed to the DB and it'll seem as if the Batch didn't run at all (even though it did).

Change your execute method code as such:

 

 

global void execute(Database.BatchableContext BC, List<Contact> scope){
	
	  for(Contact o : scope){
	  	o.Ultimate_Parent_Account__c = o.Ultimate_Parent_Id__c;
	  }
          update scope;
	}

 

 

ppiyushppiyush

thanks - that worked! :) silly me...

rpr2rpr2

Check out this formula that I found if you want to use clicks instead of code.  It accommodates a hierachy 10 levels deep.

 

IF( LEN(Parent.Name)<1 ,HYPERLINK("/"&Id, Name,"_parent"), IF( LEN( Parent.Parent.Name )<1 ,HYPERLINK("/"&Parent.Id,Parent.Name,"_parent"), IF( LEN( Parent.Parent.Parent.Name )<1 ,HYPERLINK("/"&Parent.Parent.Id,Parent.Parent.Name,"_parent"), IF( LEN( Parent.Parent.Parent.Parent.Name )<1 ,HYPERLINK("/"&Parent.Parent.Parent.Id,Parent.Parent.Parent.Name,"_parent"), IF( LEN( Parent.Parent.Parent.Parent.Parent.Name )<1 ,HYPERLINK("/"&Parent.Parent.Parent.Parent.Id,Parent.Parent.Parent.Parent.Name,"_parent"), IF( LEN( Parent.Parent.Parent.Parent.Parent.Parent.Name )<1 ,HYPERLINK("/"&Parent.Parent.Parent.Parent.Parent.Id,Parent.Parent.Parent.Parent.Parent.Name,"_parent"), IF( LEN( Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name )<1 ,HYPERLINK("/"&Parent.Parent.Parent.Parent.Parent.Parent.Id,Parent.Parent.Parent.Parent.Parent.Parent.Name,"_parent"), IF( LEN( Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name )<1 ,HYPERLINK("/"&Parent.Parent.Parent.Parent.Parent.Parent.Parent.Id,Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name,"_parent"), IF( LEN( Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name )<1 ,HYPERLINK("/"&Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Id,Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name,"_parent"), IF( LEN( Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name )<1 ,HYPERLINK("/"&Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Id,Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name,"_parent"), "Ultimate Parent Beyond 10 Levels"))))))))))

 

nbansal10nbansal10
solution by Rpr2 does not work anymore.. any other solution to this?