Use Power Automate to maintain Top Parent Account in account hierarchies

Using account hierarchies in Microsoft Dataverse (XRM, you know) is super easy with the out of the box Parent Account fishhook relationship.
This article will demonstrate how to maintain information about the Top Parent Account on all accounts, using a single Power Automate cloud flow.

---

Parent Account

The out of the box relationship allows to recursively define a Parent Account on each account by using the self-referencing (fishhook) relationship on the Account table.

The platform validates that no circular account references are created, so all hierarchies will always have one well defined great grand master, or a “Top Parent Account”. The tip of the corporate pyramid. You know, the one with the office for Satya, Elon, Bezos or Zuckerberg.

---

Introducing the Top Parent

In many scenarios you want to be able to see the Top Parent Account on each account in the hierarchy. This makes it easy to see which corporate group each subsidiary belongs to, regardless of its position in the enterprise structure.

To do this, you can simply add another fishhook relationship to the Account table:

Maintaining the Top Parent can be messy though… I have created a few plugins over the years to do this – and I have seen even more from others. Using workflows could take you part of the way, but in one way or another you would always need code of some kind to take it the last kilometer.

---

---

Main principles

• The Top Parent Account field on accounts is read-only, the flow is responsible for updating it.
• The flow shall handle both setting and clearing the Parent Account field on any account in the hierarchy.

Flow schematic

The flow chart for the flow below describes the logic on a high level.

To avoid confusion in the text below, the account that was updated (triggering the flow) will be referred to as Triggering Account.

---

Trigger

Defining the correct trigger is of course very important to prevent recursion and unnecessary API consumption.

I make sure to trigger only when the Parent Account is updated or added when creating an account.

---

Check update

The logic should check whether the Parent Account was set or cleared on the Triggering Account.

---

Parent Account is set

If Parent Account is set on the Triggering Account, the Triggering Account should get the same Top Parent as the new Parent Account has. If the Parent Account does not have a Top Parent, the Parent will also be the Top Parent for Triggering Account.

Finding the Top Parent

The actions below find the Top Parent Id and set it on the Triggering Account.

Formula for the Top Parent Id compose action:

@if(
  empty(
    outputs('Get_Parent_Account')?['body/_jr_topaccount_value']
  ),
  triggerOutputs()?['body/_parentaccountid_value'],
  outputs('Get_Parent_Account')?['body/_jr_topaccount_value']
)

The formula above basically says “if the parent account has a top parent, use that, otherwise use the parent”.

---

Parent Account is cleared

When the Parent Account is cleared from the Triggering Account, we need to clean up any possible previous hierarchy the Triggering Account used to be part of.

Triggering Account has Top Parent

If the Triggering Account has a Top Parent Account defined, we need to remove that reference.

Clear previous Top Parent

If the Triggering Account has a Top Parent, this needs to be cleared.

Clearing values from Lookup fields is however still not as straightforward as you might expect… You need to know the OData URL of the Triggering Account, and the only way to get that is to actually retrieve it. You can then use that URL in the Unrelate action.

Thanks Linn Saw Win and EY Kalman for your pointers in getting the Unrelate action to work for me!

---

Cascade Top Parent to children

We have now made sure the Triggering Account has correct information about Top Parent Account, based on the current Parent Account of the record.

What remains is to make sure all children, grandchildren and beyond are also updated with correct Top Parent.

Touching Parent Account on all child records

To do this, we can retrieve all direct children of the Triggering Account, and for each of those “touch” or “re-set” their Parent Account. To “touch” a value basically means to set it to the same value it already had. That might of course seem unnecessary, but it will trigger this same cloud flow for the children to set their Top Parent, and in turn cascade to the next level of children in the hierarchy.

---

Download complete flow

The flow described above can be downloaded from GitHub:
https://github.com/rappen/Sharing/tree/main/TopParent

---

The post Use Power Automate to maintain Top Parent Account in account hierarchies appeared first on The Power Platform Trenches.