Thursday, December 5, 2013

Finding the Ultimate Parent in an Account Hierarchy with a Simple Formula

Traversing the account hierarchy via the Parent Account field can be challenging.

I was doing some research into including 'Ultimate Parent' in reports - so that you can see all Opportunities for any account in the hierarchy, for example.

I knew I had read something about it... and after spending a few hours searching, I discovered that not only had I read something, I had actually already implemented it. Ha.

I don't remember where I got this formula, so if it looks familiar to you, please let me know. It's a pretty awesome way to use standard functionality and can go through a hierarchy of up to 10 levels.

How to Do It:

  1. Create a text formula field named 'Ultimate Parent Name' on the Account object.
  2. Paste the following into the formula:
  3. 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"))))))))))

What it looks like:

I can now use this field in reports for grouping or filtering if I want to see all Opportunities belonging to any account in this particular hierarchy. Cool!

Note: We've already implemented the customizable inline account hierarchy (that's what you're seeing in the picture, with the formula field below it), which displays the hierarchy as a visualforce page on the Account page layout. Super handy!


  1. Trying to find out how to filter the Inline Account Hierarchy to only show accounts that are Customers. I'm getting prospects in the tree, and I want to filter them out. Any ideas?


    1. Andrew - I recommend that you contact the developer of the inline account hierarchy app on the AppExchange.

  2. Is there anyway to expose the Account ID of the Account in the Ultimate Parent Account field. I am trying to run a dynamic report via a link on an Account and I want it to return results of all Opportunities where the Ultimate Parent Account is the Account the link is clicked from. Unfortunately, since this field is a formula field, I cannot create a report filter for "Ultimate Parent Account ID equals Account ID". Any thoughts?

    1. Aaron, you should be able to either replace or create a new formula field and pass id instead of name into it. So, use 'Parent.Parent.Id' instead of 'Parent.Parent.Name'.