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:

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!