Declaratively Create an ‘Ultimate Parent’ Lookup Field with Process Builder and Flow

5 min readOct 26, 2020

There’s no way around it — Account hierarchies are frustrating in Salesforce. Do you need to report on all the relationships each account has in the full hierarchy? Good luck!

Imagine Company A has 3 children, and each of those 3 children have 3 accounts. Every account has relationships to another object, and you need to report on that full hierarchy’s relationships. Not an easy feat with out of the box reporting — — enter the ULTIMATE PARENT LOOKUP FIELD!

I’m going to show you how to create an ‘Ultimate Parent’ lookup field which will link to the highest level entity in a full hierarchy. By doing that, you’ll be able to do some pretty fancy stuff in SOQL and Salesforce reporting.

Overview

Here’s what we’ll be creating.

Note that this will only work for hierarchies that are ~10 layers deep — you should consider Apex alternatives if you need to go deeper than that.

Custom Fields

Ultimate Parent Lookup — Type (Lookup)
Ultimate Parent Account ID Formula — Formula Field — I’m so sorry that Medium doesnt have a better way to show this, but:

IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.ParentId)), Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Id,
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.ParentId)), Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Id,
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.ParentId)), Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Id,
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Parent.Parent.ParentId)), Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Id,
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Parent.ParentId)), Parent.Parent.Parent.Parent.Parent.Parent.Parent.Id,
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.ParentId)), Parent.Parent.Parent.Parent.Parent.Parent.Id,
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.ParentId)), Parent.Parent.Parent.Parent.Parent.Id,
IF(NOT(ISBLANK(Parent.Parent.Parent.ParentId)), Parent.Parent.Parent.Parent.Id,
IF(NOT(ISBLANK(Parent.Parent.ParentId)), Parent.Parent.Parent.Id,
IF(NOT(ISBLANK(Parent.ParentId)), Parent.Parent.Id,
IF(NOT(ISBLANK(ParentId)),Parent.Id,
Id)))))))))))

Process Builder

This will kick off the Flow and pass in the PRIORVALUE of the Ultimate Parent Account ID

Flow

This will do the actual updating of the Ultimate Parent field AND all the companies in the old hierarchy

Flow

At a high level we are trigger this flow from process builder whenever there is a new record with a non-empty ParentId, or if ParentId changes.

Updating the Ultimate Parent Lookup field with the value of the formula field
Grabbing all of the companies in the old hierarchy so that we can update those as well after the change
Looping over those found companies to update their Ultimate Parent lookup fields with the value of their formula fields
Updating those records

Inputs (from Process Builder)

recordId
textOldUltimateParentID
textAccountsUltimateParentId

Step 1

We first update the Ultimate Parent Lookup field to the value of the formula field on the record. Formula fields returning text are unreliable to use as an Id and don’t allow for record traversal, hence why I recommend using a Lookup

One improvement here, if you’re tight on SOQL queries on the object’s automations, is to change this step to an Assignment variable and add it to the collection of updated accounts at the end of the flow. If you’re curious about that I am happy to add an alternate step here!

Step 2

We then retrieve all of the Accounts in the old account hierarchy so that we can update them with the new hierarchy info after the parent change.

Here’s a description for the step: Gets a list of all companies in the hierarchy based on the highest company in the hierarchy and using the previous value of the ultimate parent ID field on the company that just had a parent change.

We also exclude the parent from the query since we don’t want to update that lookup to itself which would cause a circular reference error.

Step 3

We then do a null check on the previous get — no need to do anything else if we don’t find records.

Step 4

We now loop over the results from the previous Get and set their Ultimate Parent lookup fields with the value of their formula fields to do a recalculation for the old hierarchy.

Current Item from {!Loop_over_companies_in_old_hierarchy.Ultimate_Parent_Account__c} Equals Current Item from {!Loop_over_companies_in_old_hierarchy.Ultimate_Parent_Account_Formula__c}

Step 5

We then use the collection we built in the loop to update all of the accounts in the old hierarchy.

Process Builder

Let’s trigger this thing! Because After-Save flows don’t support grabbing Priorvalue (easily) we will be using Process Builder.

Our condition for firing this will be a formula:

OR(
AND(ISNEW(), NOT(ISBLANK([Account].ParentId))),
AND(ISCHANGED([Account].ParentId))
)

Make sure your flow we just built is Active, then pass in the following:

TextOldUltimateParentAccountId = PRIORVALUE([Account].Ultimate_Parent_Account ID_Formula__c)

TextAccountsUltimateParentID = [Account].Ultimate_Parent_Account__c)

Activate, then give it a whirl with some Accounts!

Dataloader

You’ll want to update all of the existing accounts out there. My recommendation would be to use workbench or (my preference) Salesforce Inspector to grab the accounts with a non-blank ParentId field. You can of course use out of the box reports!

Export the results — you should have three columns — AccountId, Ultimate Parent Account, and Ultimate Parent Account Formula. The Ult. Parent lookup field should be blank, so just copy over the formula column into the lookup column and do your data load.

You’re ready to rock \m/!

Reporting in Action

Now that you have this all set up, let’s look at the reporting implications.

Let’s take my previous blog post’s Gas Station -> Account model as an example to show off the reporting capabilities of this new Ultimate Parent Lookup field.

Say we want to report on every Gas Station that is related to the whole account hierarchy tree. To do that, you could create a basic report where the ‘Owning Account’ = ‘accountID’ OR the Owning Account’s Ultimate Parent = ‘accountID’.

In SOQL, you’d do something like:

SELECT id,Owning_Account__c, Owning_Account__r.Name

FROM Gas_Station__c

WHERE Owning_Account__c = ‘0011I000002sJeQQAU’ or Owning_Account__r.Ultimate_Parent_Account__c = ‘0011I000002sJeQQAU’

If you wanted to simplify reporting and the query a bit, you could create a formula field called ‘Ultimate Parent Reporting Field’ which will return the Account Id if Parent ID is blank (I.e. it’s the top level), and returns the Ultimate Parent if the Ultimate Parent field isnt blank.

SELECT id,Owning_Account__c, Owning_Account__r.Name

FROM Gas_Station__c

WHERE Owning_Account__r.Ultimate_Parent_Reporting_ID__c = ‘0011I000002sJeQQAU’

Voila!