Ask Search:
Steve MolisSteve Molis 

Tips & Tricks: Formula to create "Ultimate Parent Account" for Opportunity Pipleline Reports

Here's a Formula I built to create an "Ultimate Parent Account" field that you can use to create Opportunity Pipeline reports that roll up all Opportunities under the top Account in the hierarchy. 

*** in this example I'm testing up to a 5 Tier Account Hierarchy (Compiled size: 342 characters) ***

Datatype: Formula 
Result: TEXT 
Formula: 
 
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Parent.Name,
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Name,
IF(NOT(ISBLANK(Parent.Parent.Parent.Name)), Parent.Parent.Parent.Name,
IF(NOT(ISBLANK(Parent.Parent.Name)), Parent.Parent.Name,
IF(NOT(ISBLANK(Parent.Name)),Parent.Name,
Name)))))

 
Best Answer chosen by Miglena (Salesforce.com) 
Steve MolisSteve Molis
An #AWESOME bonus tip from @Jeremiah Dohn to turn the Ultimate Parent into a Hyperlink to that Account

Datatype: Formula 
Result: TEXT 
Formula: 
 
HYPERLINK('/' + 
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Parent.Id, 
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Id, 
IF(NOT(ISBLANK(Parent.Parent.Parent.Name)), Parent.Parent.Parent.Id, 
IF(NOT(ISBLANK(Parent.Parent.Name)), Parent.Parent.Id, 
IF(NOT(ISBLANK(Parent.Name)),Parent.Id, 
Id))))), 
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Parent.Name, 
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Name, 
IF(NOT(ISBLANK(Parent.Parent.Parent.Name)), Parent.Parent.Parent.Name, 
IF(NOT(ISBLANK(Parent.Parent.Name)), Parent.Parent.Name, 
IF(NOT(ISBLANK(Parent.Name)),Parent.Name, 
Name))))))

 

All Answers

Steve MolisSteve Molis
An #AWESOME bonus tip from @Jeremiah Dohn to turn the Ultimate Parent into a Hyperlink to that Account

Datatype: Formula 
Result: TEXT 
Formula: 
 
HYPERLINK('/' + 
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Parent.Id, 
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Id, 
IF(NOT(ISBLANK(Parent.Parent.Parent.Name)), Parent.Parent.Parent.Id, 
IF(NOT(ISBLANK(Parent.Parent.Name)), Parent.Parent.Id, 
IF(NOT(ISBLANK(Parent.Name)),Parent.Id, 
Id))))), 
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Parent.Name, 
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Name, 
IF(NOT(ISBLANK(Parent.Parent.Parent.Name)), Parent.Parent.Parent.Name, 
IF(NOT(ISBLANK(Parent.Parent.Name)), Parent.Parent.Name, 
IF(NOT(ISBLANK(Parent.Name)),Parent.Name, 
Name))))))

 
This was selected as the best answer
Dustin KunzDustin Kunz
I have an idea for this, but I'm hoping you can solve it without me hacking away at it.

Let's say I'd like to expose the names of each Parent.Name along the way.

So I essentially want:
"GreatGrandParent Name > GrandParent Name > Parent Name"

Would I need to add individual lines that to the end of each "Parent.Name" with the correct number of Parent.'s along the way, or can I summarize that in one at the end that just disregards if there's not a GreatGrandParent?
Steve MolisSteve Molis
My Formula is a Nested IF, so it's mutually exclusive.  For what you want you'd need something like a Closed IF Array
Dustin KunzDustin Kunz
In the meantime, here's an answer that's a little bit hacky:

You can use either formula above to run a report for an entire hierarchy if you include the very top name in the hierarchy, but let's say you just want from the Grandparent on down, and you don't have any other filter (region, owner, etc). Break the connection btwn the GrandParent and GreatGP, run your report using the "Ultimate Parent FormulaField equa​ls Grandparent", and afterwards just edit the GrandParent so it reverts back to having the GreatGrandParent. 

Two things to keep in mind:
1.   Another user wont be able to re-run your report b/c what you did is technically witchcraft
2.   If you have anything else filtering up or down through relationships, make sure they update (or don't, as the case may be) and that you get your original data back.
Steve MolisSteve Molis
Hi Dustin,
You kinda lost me on that one, I'm not even sure what you're trying to do there.   I just built a simple Formula to display the name of the Top Parent Account for Summary Reports, I wasn't trying bring about World Peace (I'm still working on that one)
Diana VazquezDiana Vazquez
Hey Steve
I am looking to create a Ultimate Parent ID
do you have that formula build somewhere already? 
 
Dustin KunzDustin Kunz
@Diana Vasquez,

You could use Steve's original formula and replace "Name" with ID, or you can use this to create the Hyperlink version:

HYPERLINK('/' + 
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Parent.Id, 
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Id, 
IF(NOT(ISBLANK(Parent.Parent.Parent.Name)), Parent.Parent.Parent.Id, 
IF(NOT(ISBLANK(Parent.Parent.Name)), Parent.Parent.Id, 
IF(NOT(ISBLANK(Parent.Name)),Parent.Id, 
Id))))), 
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Parent.Id, 
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Id, 
IF(NOT(ISBLANK(Parent.Parent.Parent.Name)), Parent.Parent.Parent.Id, 
IF(NOT(ISBLANK(Parent.Parent.Name)), Parent.Parent.Id, 
IF(NOT(ISBLANK(Parent.Name)),Parent.Id, 
Id))))))
Steve MolisSteve Molis
Yeah, that's exactly how you'd do it. Sent from mobile, excuse the brevity + typos
Diana VazquezDiana Vazquez
Thank you Diana Vazquez Director, Strategic Platform •Hours : Monday to Friday [7:00 AM PST - 4:00 PM PST] Direct Line: 714-250-8187 Mobile: 727.804.4479 Email: DLVazquez@firstam.com
Susan PriceSusan Price
Hey guys,
Good post @Steve Molis.  Here's another variation on the theme!  Had a similar requirement a while ago, but needed to group by each of the levels in the hierarchy as well so you could see the full lineage of the accounts (good for complex organisation structures)

Same basic approach except with additional "tier" fields so you can see each of the parents 

Here is what I wrote up at the time:

1. Decide how many layers deep your accounts go (I know this works for 6 levels, not sure how many until you start to bump up against formula compile size limits)
2. Create a New field on Account called Tier Level.  This will indicate where in the hierarchy the account sits (top of the tree is 1 and so on to 6)  with the following code:

Tier Level

IF ( ISBLANK(ParentId),1, 
IF(ISBLANK(Parent.ParentId),2, 
IF(ISBLANK(Parent.Parent.ParentId),3, 
IF(ISBLANK(Parent.Parent.Parent.ParentId),4, 
IF(ISBLANK(Parent.Parent.Parent.Parent.ParentId),5, 
IF(ISBLANK(Parent.Parent.Parent.Parent.Parent.ParentId),6, 
NULL))))))

3. Create 6 additional fields, one for each Tier i,e Tier 1, Tier 2 etc  This will give the direct parentage of the record.  For each tier you need to add a "" to the top level like so:

Tier 1

CASE( Tier_Level__c, 
1, Name , 
2, Parent.Name, 
3, Parent.Parent.Name, 
4, Parent.Parent.Parent.Name, 
5, Parent.Parent.Parent.Parent.Name, 
6, Parent.Parent.Parent.Parent.Parent.Name, 
"")

Tier 2

CASE( Tier_Level__c, 
1,"", 
2, Name , 
3, Parent.Name, 
4, Parent.Parent.Name, 
5, Parent.Parent.Parent.Name, 
6, Parent.Parent.Parent.Parent.Name, 
"")

etc...

4. When reporting, I suggest grouping by the Tier 1 field, then by Tier level.  You will get the hierarchy of each account grouped by its ultimate parent
Steve MolisSteve Molis
*** UPDATE *** 

Here's a new and improved version using the BLANKVALUE function (simpler, cleaner, smaller Compile Size)
BLANKVALUE(Parent.Parent.Parent.Parent.Parent.Name, 
BLANKVALUE(Parent.Parent.Parent.Parent.Name, 
BLANKVALUE(Parent.Parent.Parent.Name, 
BLANKVALUE(Parent.Parent.Name, 
BLANKVALUE(Parent.Name, 
Name)))))

 
Roman EvseevRoman Evseev
@Dustin Kunz

Hi Dustin,

You wrote: 
So I essentially want:
"GreatGrandParent Name > GrandParent Name > Parent Name"


So, I needed that formula by myself, and here's something that I came up with: (works only up to a 5 parent records)
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Parent.Name & " > " & Parent.Parent.Parent.Parent.Name & " > " & Parent.Parent.Parent.Name & " > " & Parent.Parent.Name & " > " & Parent.Name & " > " & Name,

IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Name & " > " & Parent.Parent.Parent.Name & " > " & Parent.Parent.Name & " > " & Parent.Name & " > " & Name,

IF(NOT(ISBLANK(Parent.Parent.Parent.Name)), Parent.Parent.Parent.Name & " > " & Parent.Parent.Name & " > " & Parent.Name & " > " & Name,

IF(NOT(ISBLANK(Parent.Parent.Name)), Parent.Parent.Name & " > " & Parent.Name & " > " & Name,

IF(NOT(ISBLANK(Parent.Name)), Parent.Name & " > " & Name,
Name)))))
In case it might be helpful to someone.
 
Marcie GiovannoniMarcie Giovannoni
Hey All!
@Steve Molis - I'm having a little bit of trouble figuring out how to take your Parent Account field for only one tier - as in there's only one parent, but a bunch of children.

What would the Ultimate Parent Hyperlink formula look like for just 1 parent?
(I'm WAY rusty on this)
Steve MolisSteve Molis
Hi Marcie,
In that case why don't you'd just use the standard Parent Account field? 
Marcie GiovannoniMarcie Giovannoni
Hi Steve,
Thanks for the quick reply!

Because I'm putting this field on Opportunities, and there isn't a standard Parent Account Field.

Here's what I have:
HYPERLINK('/' +
IF(NOT(ISBLANK(Parent.Name)),Parent.Id,
Id,
IF(NOT(ISBLANK(Parent.Name)),Parent.Name,
Name))


Does that look right?
Steve MolisSteve Molis
Okay let's back up the truck... Why do you need to "put this field on Opportunities" in the first place?  
What is driving that requirement?
Marcie GiovannoniMarcie Giovannoni
My sales reps need to be able to total up opportunities that are under a parent account where there's multiple child accounts under one parent in an easy and replicable fashion- which is where this field comes into play. I got here via this Success Cmmty Chat: https://success.salesforce.com/answers?id=90630000000Csf1AAC
Marcie GiovannoniMarcie Giovannoni
GOOD NEWS!

I figured it out.

Here's the formula for just ONE parent account:

HYPERLINK('/' +
IF(NOT(ISBLANK(Account.Parent.Name)), Account.ParentId,
Id),
IF(NOT(ISBLANK(Account.Parent.Name)), Account.Parent.Name, Account.Parent.Name))
Steve MolisSteve Molis

Sorry Marcie,

I'm having a hard time trying to wrap my head around the Business need/requirement...  They need to be able access a link to the Parent Account record while they are on the Opportunity record???  

Marcie GiovannoniMarcie Giovannoni
They need to be able to reference the Parent Account within Opportunity Reports to be able to see the total amount of revenue from one parent account attached to multiple child accounts. Does that make sense?
Steve MolisSteve Molis
You don't even need a Formula Field, just add the standard Parent Account field to the Account Mini-Page Layout and they can access it simply by mousing-over the Account name on the Opportunity (zero code)
User-added image
Marcie GiovannoniMarcie Giovannoni
It doesn't work that way under the Reports Tab. I need it as a field to group by within the opportunities report.
Steve MolisSteve Molis
But if you're using an Opportunity Report you should have access to all standard AND custom Account object fields, I don't know why you'd need to create another custom field on the Opportunity object.  But I'm not familiar with your company's Sales Processes, Methodologies, etc, as long as it works for you.  
Shirley LafuenteShirley Lafuente
In your formula, you are reference Parent.Parent.Parent.Name throughout....
There isn't a Parent field ID.  There is a Parent ID or Parent Name.  I think I understand that concept but just not sure what if the Parent's referenced should be Parent.ID or Parent.Name. 
Steve MolisSteve Molis
Hi Shirly
That is the "path" to the Ultimate Parent Account Name that you get by navigating through the Parent Account> Lookup field using the Formula Editor (this is a Cross-Object Formula) 
User-added image
Mara LealMara Leal
Hi All!

Used the ultimate parent ID, as stated above -

HYPERLINK('/' + 
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Parent.Id, 
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Id, 
IF(NOT(ISBLANK(Parent.Parent.Parent.Name)), Parent.Parent.Parent.Id, 
IF(NOT(ISBLANK(Parent.Parent.Name)), Parent.Parent.Id, 
IF(NOT(ISBLANK(Parent.Name)),Parent.Id, 
Id))))), 
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Parent.Id, 
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Id, 
IF(NOT(ISBLANK(Parent.Parent.Parent.Name)), Parent.Parent.Parent.Id, 
IF(NOT(ISBLANK(Parent.Parent.Name)), Parent.Parent.Id, 
IF(NOT(ISBLANK(Parent.Name)),Parent.Id, 
Id))))))

However, I dont need the hyperlink, so I deleted along with the last argument in the fomula.  What I do need is to produce is the 18-character id. 

So I added the CASESAFEID...

CASESAFEID(
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Parent.Id, 
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Id, 
IF(NOT(ISBLANK(Parent.Parent.Parent.Name)), Parent.Parent.Parent.Id, 
IF(NOT(ISBLANK(Parent.Parent.Name)), Parent.Parent.Id, 
IF(NOT(ISBLANK(Parent.Name)),Parent.Id, 
Id)))))

Yeah that didn't go so well and returned an error "compiled Formula is too big to execute"...

Any ideas other than having to create another field (TEXT) to pass the info and a workflow to update the field with the 18-char id? 

Your help and ideas are much appreciated.
Steve MolisSteve Molis
I would try Process Builder, excuse the brevity I'm on my mobile phone
Leah HallLeah Hall
I used this on my account records. I was wondering if I could run a report for Activities and group it by the ultimate parent account?
Steve MolisSteve Molis
Hi Leah,

You should be able to if you use either an "Activities with Accounts" or an "Activities with Contacts" or any Activity report that has access to Custom Account Fields.  

Are you running into any trouble?
Leah HallLeah Hall
 I am not seeing it as an option in a report for tasks and events. maybe a a different report type?
Steve MolisSteve Molis
Hi Leah, an Activities (Tasks and Events) report does not have any access to Account custom fields.  Like I said before

"You should be able to if you use either an "Activities with Accounts" or an "Activities with Contacts" or any Activity report that has access to Custom Account Fields"
Steve MolisSteve Molis
Andrew, can you elaborate on what you're doing with Closed/Won Accounts?  That sounds like something custom (typically Opportunities are Closed/Won, not Accounts)
Steve MolisSteve Molis
Okay that's a bit different from what I built.  My custom Ultimate Parent Account simply returns the name of the Top Account in the Account Hierarchy in any Account within the Hierarchy for Reports and List Views.  

You'd need to amend the Formula at each Account Tier to evaluate your custom Closed/Won field, or whatever it's called
Sally MendoncaSally Mendonca
Thank you Steve Molis...we have been racking our heads to figure this out for over a year.  Salesforce Support finally pointed me to this post, I created the formula and it works! 

THANK YOU!!!!
Steve MolisSteve Molis
No problem SallyM, glad it helped you out
Anna ForsellAnna Forsell
Hi Steve! 
I have a issue with this ultimate parent. At our company we call it ;

IJ_Master_CRM__r.IJ_CRM_Id__c 

this is the "mother" and I want to build a field that is like the others have talked about in this tread but with this name.
I dont know what the formula will be with our parentname?
Can you assist??
Thanks //Anna
Anna ForsellAnna Forsell
The mother.....
Anna ForsellAnna Forsell
Update in my issue!
I was able to use this formula successfully!!! 

BLANKVALUE(MasterCRMid.Id,
CRMid.Id))))

The field provides a value from the CRM Master ID if there is, if it is empty, it gives a value from CRM id.

We will use this for buildning reports and dashboards to have a companystructure. 
 
Steve MolisSteve Molis
Hi Anna, glad you got it working (sorry I'm on Boston time)  

Did you want the Account ID or the Account Name in your Reports?
Anna ForsellAnna Forsell
Thanks for respons Steve! Yes I have the CRM id right now but maybe I want a field that generates the Account name. Do I use the same principle? //A
Steve MolisSteve Molis
Yes, drill into the the Parent object throught the Lookup> field and grab the Parent.Name field instead of the Parent.Id 
Steve MolisSteve Molis
Here's a new one I wrote to get the Number of the Account Tier within the Hierarchy 
https://success.salesforce.com/_ui/core/userprofile/UserProfilePage?u=0053000000235ByAAI&fId=0D53A000034TCbDSAW&tab=sfdc.ProfilePlatformFeed  

Create a new custom Field on the Account Object and select 
Datatype = Formula
Result = Number
Formula =
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Name)), 6, 
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Name)), 5, 
IF(NOT(ISBLANK(Parent.Parent.Parent.Name)), 4, 
IF(NOT(ISBLANK(Parent.Parent.Name)), 3, 
IF(NOT(ISBLANK(Parent.Name)),2, 
1)))))

 
Stefan OHalloranStefan OHalloran
Thanks Steve, this helped as a starting point, i then used your simplified BLAKVALUE formula and extended it to the hyperlink function, also using service cloud console so add the fucntion to keep in the same window.

HYPERLINK('/' + 
BLANKVALUE(Parent.Parent.Parent.Parent.Parent.Parent.Id, 
BLANKVALUE(Parent.Parent.Parent.Parent.Parent.Id, 
BLANKVALUE(Parent.Parent.Parent.Parent.Id, 
BLANKVALUE(Parent.Parent.Parent.Id, 
BLANKVALUE(Parent.Parent.Id, 
BLANKVALUE(Parent.Id, 
Id))))))&"?isdtp=vw", 
BLANKVALUE(Parent.Parent.Parent.Parent.Parent.Parent.Name, 
BLANKVALUE(Parent.Parent.Parent.Parent.Parent.Name, 
BLANKVALUE(Parent.Parent.Parent.Parent.Name, 
BLANKVALUE(Parent.Parent.Parent.Name, 
BLANKVALUE(Parent.Parent.Name, 
BLANKVALUE(Parent.Name, 
"")))))),"_self")

After all that, then realised that i actually need to show the Top level account owner on all Hierarchy accounts.  
Stefan OHalloranStefan OHalloran
Here is what i ended up with for Account Owner; 

IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Parent.Owner.FirstName&" " & Parent.Parent.Parent.Parent.Parent.Owner.LastName, 
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Owner.FirstName&" " & Parent.Parent.Parent.Parent.Owner.LastName, 
IF(NOT(ISBLANK(Parent.Parent.Parent.Name)), Parent.Parent.Parent.Owner.FirstName&" " & Parent.Parent.Parent.Owner.LastName, 
IF(NOT(ISBLANK(Parent.Parent.Name)), Parent.Parent.Owner.FirstName&" " & Parent.Parent.Owner.LastName, 
IF(NOT(ISBLANK(Parent.Name)),Parent.Owner.FirstName&" " & Parent.Owner.LastName, 
Owner.FirstName&" " & Owner.LastName)))))

It ended up a little messy with the 'Parent.Owner.FirstName....'. I was going to use a custom field that we already had that did 'Owner.FirstName&" " & Parent.Owner.LastName' but when i syntax checked it kept being unable to find the field.  Also couldn't use the BLANKVALUE operator as the Account Owner field is always populated.
Kate PorterKate Porter
Thank you for this formula! One thing to note though, the option using the hyperlink display doesn't allow alphanumeric sorting in reports...
Chrissy ThompsonChrissy Thompson
Hi All,

Thank you all for this formula! Super helpful. Just a heads up, for anyone who runs into a syntax error. I had to add "Account." to all of mine and this worked for me. See some of the bolds below as an example. 

HYPERLINK('/' + 
IF(NOT(ISBLANK(Account.Parent.Parent.Parent.Parent.Parent.Name)), Account.Parent.Parent.Parent.Parent.Parent.Id, 
IF(NOT(ISBLANK(Account.Parent.Parent.Parent.Parent.Name)), Account.Parent.Parent.Parent.Parent.Id, 
IF(NOT(ISBLANK(Account.Parent.Parent.Parent.Name)), Account.Parent.Parent.Parent.Id, 
IF(NOT(ISBLANK(Account.Parent.Parent.Name)), Account.Parent.Parent.Id, 
IF(NOT(ISBLANK(Account.Parent.Name)),Account.Parent.Id, 
Id))))), 
IF(NOT(ISBLANK(Account.Parent.Parent.Parent.Parent.Parent.Name)), Account.Parent.Parent.Parent.Parent.Parent.Name, 
IF(NOT(ISBLANK(Account.Parent.Parent.Parent.Parent.Name)), Account.Parent.Parent.Parent.Parent.Name, 
IF(NOT(ISBLANK(Account.Parent.Parent.Parent.Name)), Account.Parent.Parent.Parent.Name, 
IF(NOT(ISBLANK(Account.Parent.Parent.Name)), Account.Parent.Parent.Name, 
IF(NOT(ISBLANK(Account.Parent.Name)),Account.Parent.Name, 
Name))))))
Zakaria TAGAIZINEZakaria TAGAIZINE
Hello!

Thank you @Steve Molis. I create a formula field called "Ultimate Parent Account" on the Accounts object as advised. Worked really well. This one right here:
HYPERLINK('/' + IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Parent.Id, IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Id, IF(NOT(ISBLANK(Parent.Parent.Parent.Name)), Parent.Parent.Parent.Id, IF(NOT(ISBLANK(Parent.Parent.Name)), Parent.Parent.Id, IF(NOT(ISBLANK(Parent.Name)),Parent.Id, Id))))), IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Parent.Name, IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Name, IF(NOT(ISBLANK(Parent.Parent.Parent.Name)), Parent.Parent.Parent.Name, IF(NOT(ISBLANK(Parent.Parent.Name)), Parent.Parent.Name, IF(NOT(ISBLANK(Parent.Name)),Parent.Name, Name))))))

However I still would love to be able to create a Hyperlink that would direct me to the report. I haven't seen anyone show us how to do that? I created an Opportunity Report which I can filter by Ultimate Parent Account, however I would love to be able to just click on a link that would direct me to a generated report filtered by the Ultimate Parent Account.

Any help would be appreciated.

Zak
Brad CurrierBrad Currier
I am using this formula but instead of 15 Char ID i want the 18 Char ID.  So I took:
 
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Id,
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Id,
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Id,
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Id,
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Parent.Parent.Parent.Id,
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Parent.Parent.Id,
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Parent.Id,
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Id,
IF(NOT(ISBLANK(Parent.Parent.Parent.Name)), Parent.Parent.Parent.Id,
IF(NOT(ISBLANK(Parent.Parent.Name)), Parent.Parent.Id,
IF(NOT(ISBLANK(Parent.Name)),Parent.Id,
Id)))))))))))

and converted it to
 
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name)), CASESAFEID(Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Id),
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name)), CASESAFEID(Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Id),
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name)), CASESAFEID(Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Id),
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name)), CASESAFEID(Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Id),
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name)), CASESAFEID(Parent.Parent.Parent.Parent.Parent.Parent.Parent.Id),
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Parent.Name)), CASESAFEID(Parent.Parent.Parent.Parent.Parent.Parent.Id),
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Name)), CASESAFEID(Parent.Parent.Parent.Parent.Parent.Id),
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Name)), CASESAFEID(Parent.Parent.Parent.Parent.Id),
IF(NOT(ISBLANK(Parent.Parent.Parent.Name)), CASESAFEID(Parent.Parent.Parent.Id),
IF(NOT(ISBLANK(Parent.Parent.Name)), CASESAFEID(Parent.Parent.Id),
IF(NOT(ISBLANK(Parent.Name)),CASESAFEID(Parent.Id),
CASESAFEID(Id))))))))))))

When I do this I get an error of ​"Error: Compiled formula is too big to execute (28,875 characters). Maximum size is 5,000 characters"

Any other way to do this?
Steve MolisSteve Molis
Ouch!  that's gonna leave a mark... 

Just a single CASESAFEID Function has a Compiled size of 2,351 characters 

Since the Record ID never changes, you might be better off creating a custom Text field that is updated via a Workfklow Rule or the Process Builder.  Using an Immediate Action (Field Update) that uses the Formula
CASESAFEID(Id)
To update the Text Field.  Then reference your Text field in your Ultimate parent Account ID Formula
   
 
Brad CurrierBrad Currier
Thanks!.  The logic on that is sound, I will try it out that way.  
Marcel HobizalMarcel Hobizal
Thanks @Steve for the Ulitmate Parent Idea!  We have a business requirement to analyze XYZ Corp's offices and see how our sales reps might be stepping on each others toes and/or not being affected in certain locations.  I think this might be helpful with those type of reports.  Has anyone does this before and have any suggestions? 
Philipp MathisPhilipp Mathis
Cool thanks a lot Steve, I'm having the same request as Marcie. To have the direct link to the ulimate parent alos on the oppty level. Her formula (see below) works perfectly fine for 2levels. But as soon as you have such an account hierachy;
  • Company Group
    • Division A
    • Divison B
      • subsidiary B1
        • subsidiary B1a
    • Division C
    • .....
the formula doesn't capture the ultimate parent (company group) but only until the divison level. So my question is how can I "enlarge" below formula on 2 more levels?

Thanks,

Philipp

......................................
GOOD NEWS!

I figured it out.

Here's the formula for just ONE parent account:

HYPERLINK('/' +
IF(NOT(ISBLANK(Account.Parent.Name)), Account.ParentId,
Id),
IF(NOT(ISBLANK(Account.Parent.Name)), Account.Parent.Name, Account.Parent.Name))

....................................
Philipp MathisPhilipp Mathis
also I've realized that the ultimate parent field with the hyperlink function is not working when using it in a lightning chart component. Strangely on the report section it displayed itself perfectly fine but in the chart it is showing html code and not text form, see screenshot;

User-added image
any idea how to solve that?
Steve MolisSteve Molis
Yeeesh....  why are you using the Hyperlink Filed in a Chart?
Philipp MathisPhilipp Mathis
Because I'd like to have it displayed as hyperlink on the account page and not only as a normal text field.
It's just the one same field.
Or would I need to create two fields for that?
Steve MolisSteve Molis
Yeah, that's a known behavior with Hyperlink Formula Fields and Charts.  Create another Text Field and use that in the Chart
Philipp MathisPhilipp Mathis
Ok thanks.

What about the same formula for on an opportunity level (see two post above, basically same reason as Marcie)

HYPERLINK('/' +
IF(NOT(ISBLANK(Account.Parent.Name)), Account.ParentId,
Id),
IF(NOT(ISBLANK(Account.Parent.Name)), Account.Parent.Name, Account.Parent.Name))

but enlarge on 5 hiarchy levels (not only 2)
Steve MolisSteve Molis
Did you try this?
 
HYPERLINK('/' + 
IF(NOT(ISBLANK(Account.Parent.Parent.Parent.Parent.Parent.Name)), Account.Parent.Parent.Parent.Parent.Parent.Id, 
IF(NOT(ISBLANK(Account.Parent.Parent.Parent.Parent.Name)), Account.Parent.Parent.Parent.Parent.Id, 
IF(NOT(ISBLANK(Account.Parent.Parent.Parent.Name)), Account.Parent.Parent.Parent.Id, 
IF(NOT(ISBLANK(Account.Parent.Parent.Name)), Account.Parent.Parent.Id, 
IF(NOT(ISBLANK(Account.Parent.Name)),Account.Parent.Id, 
Id))))), 
IF(NOT(ISBLANK(Account.Parent.Parent.Parent.Parent.Parent.Name)), Account.Parent.Parent.Parent.Parent.Parent.Name, 
IF(NOT(ISBLANK(Account.Parent.Parent.Parent.Parent.Name)), Account.Parent.Parent.Parent.Parent.Name, 
IF(NOT(ISBLANK(Account.Parent.Parent.Parent.Name)), Account.Parent.Parent.Parent.Name, 
IF(NOT(ISBLANK(Account.Parent.Parent.Name)), Account.Parent.Parent.Name, 
IF(NOT(ISBLANK(Account.Parent.Name)),Account.Parent.Name, 
Name))))))

 
Philipp MathisPhilipp Mathis
thanks Steve. But strangely if there are opportunities linked to the ultimate parent account (so this account doesn't have anything filled in under the parent field, cause it does not have a parent, then the ultimate parent field on the opportunity is displaying as the very same opportunity name.

see here this is the account (ultimate parent account);
User-added image

and opportunities that are attached to an ultimate parent account have the following entered on their "ultimate parent account" field on the opportunity level:
User-added image


 
Philipp MathisPhilipp Mathis
it should be test account instead the very opportunity name gets listed as ultimate parent account....
Justin EpistolaJustin Epistola
Is it possible to use this formula in a process builder to populate the Ultimate parent name in a look up field? 

This will then be use in Ultimate roll up app to roll up information? 

Thanks!
Katie ZinmanKatie Zinman
I'm using the following formula to update the "Ultimate Parent Account Id" field. I then have a separate process builder that updates "Ultimate Parent Account" (a Lookup field on the Account) whenever the "Ultimate Parent Account Id" is changed.

BLANKVALUE(Parent.Parent.Parent.Parent.Parent.Id, 
BLANKVALUE(Parent.Parent.Parent.Parent.Id, 
BLANKVALUE(Parent.Parent.Parent.Id, 
BLANKVALUE(Parent.Parent.Id, 
BLANKVALUE(Parent.Id, 
Id )))))

When I go to edit the "Ultimate Parent Account" (the highest Account in the hierarchy - or the "Great great great grandparent"), I get the following error: "You cannot set a hierarchy field Ultimate Parent Account to point to itself or a child record. Either edit one of the child records to not refer to this record, or do not set the field on this record." 

Has anyone encountered this before?
Donovan EtchisonDonovan Etchison
Great thread! I'm using the formulas mentioned up and down this thread to populate two fields: Ultimate Parent ID and Ultimate Parent Name. They're working like a charm and updating as hierarchies are adjusted.

But like Justin and Katie I ran into trouble updating an ultimate parent in a lookup field. I tried process builder but that wasn't working for me since PBs don't kickoff when values in formula fields are adjusted (from what I've read). Katie do you want to share the Process Builder you set up? Maybe we could help each other out.

At the end of the day the functionality I am losing by not populating the ultimate parent lookup is related to reporting. I need to be able to create a report that shows if an organization has signed up for a program (which we denote with a simple checkbox) or if its ultimate parent organization has signed up (which I was planning to do with a formula field).
Donovan EtchisonDonovan Etchison
Katie, I just thought of this: Did you set up the first criteria in your Process Builder to be a check that the org you are trying to update is NOT the ultimate parent? If the org is the highest Account in the hierarchy, you don't want to kick off the process for two reasons. I think that would result in the error message about the Ultimate Parent Account pointing to itself. Just an idea.
Niels LønbergNiels Lønberg
@Katie Zinman @Donovan Etchison

I'm trying to solve the same problem for reporting purposes. However, as far as I can see, it is not possible to populate an Ultimate Parent Account lookup field with the Ultimate Parent itself due to hierarchy constraints. 

The formula option seems to be the only way forward but please let me know if you find a workaround. 
Sarah OrensSarah Orens
@Steve Molis - Thanks so much! Next challenge: 

How to not populate the Ultimate Parent field on the Ultimate Parent record.


Also - for anyone else using the hyperlink version, exporting to excel gets messy. 
Steve MolisSteve Molis
You could try using NULL as your final result
Steve MolisSteve Molis
PS but that kinda defeats the purpose of of being able to group and summarize everything
Paul LegrasPaul Legras
Hello,

I am using a Ultimate Parent field, with the formula mentioned at the top of this thread, it works very well, except for one thing, the report charts.

I have a report that shows all revenue per Ultimate Parent account, and the Utimate Parent acccount names don't appear correcly, as per below:

I've removed certain data for confidentiality


Would anyone have an idea on why it does that and how to fix it?

Thank you very much in advance!

Paul
Sarah OrensSarah Orens
Good Call Steve, I wasn't thinking. Makes perfect sense. 

Paul, I ran into this, too, when I used the hyperlink formula. This didn't work for us and we had to settle for plain text in order to export to excel and do reporting. 
Steve MolisSteve Molis
Hi Paul That’s because you’re referencing the URL Link version of the Formula. You definitely don’t want to use that in a Report or Dashboard Chart. If you’re displaying it in a Chart you’ll want to use the Text version of the Formula (without the Hyperlink). Typically people create 2 versions of the field. The “User Friendly” hyperlink, and the “Report Friendly” Text
Paul LegrasPaul Legras
Thank you guys for your prompt help!