Get full hierarchy path

[KoenJanssensPD]

How can I get the full path of a hierarchy structure?

For example:

ID	ParentId	Name	FullPath
1		Boss 1	Boss 1
2	1	Manager 1	Boss 1 - Manager 1
3	2	Employee 1	Boss 1 - Manager 1 - Employee 1
4	2	Employee 2	Boss 1 - Manager 1 - Employee 2

I tried with a calculated field: 

Concat(Parent.FullPath, " - ", Name)

but that gives me the error that it would create a circular reference...

[SeanHenderson]

Hi @KoenJanssensPD 

Are you trying to create a view with the full hierarchy? Views can only reference what the Entity has a lookup to, not what the entity looked up to references. The only ways around would be to create a subgrid with a quick view or to create a Flow that moves the data on create and change so that you can view it in the main entity.

---
Please hit the "Accept as Solution" button if my post answered your question! If my post was helpful please consider giving it a "Thumbs Up."

[KoenJanssensPD]

@SeanHenderson I don't really understand what you are saying.

All I want, is a auto-generated field for each entity instance, that shows the full path in the hierarchy.

For example, see hierarchy of Product Categories in attachment.

eg, for instance "SubSub Category C", I would like to have a field containing "Main Category - Sub Category B - SubSub Category C".

In pseudo code I would write:

FullPath = IF (ParentCategory IS NOT EMPTY) THEN (CONCAT(ParentCategory.FullPath, " - ", Name) ELSE (Name)

If I try this in a calculated field, it gives an error that this would cause a circular reference.

[GarethPrisk]

The app is correct. There is no supported way to do this without invoking (potentially) a circular reference, or hitting the limit of recursion for a plugin/workflow.

The app cannot guarantee that you wouldn't:

• Relate record A to B, using self-referential N:1 attribute
• Relate record B to A, using self-referential N:1 attribute (same as above)

That being said, we have done stuff like this for clients in the past. Typically, it would be written as a custom plugin, and takes into consideration if/when recursion is occurring. It is basically manually stepping through each relationship's record, at-a-time.

For an OOB approach, you can try using Power Automate but it may be limited to the same recursion checks.

• NOTE
  • In subsequent steps 'relationship' implies the N:1 self-referential hierarchy enabled relationship/field
• On Update of Record "Child"
• Get upper hierarchy names
  • If relationship is not null, Retrieve Parent record using relationship
    • Add Parent name to a variable
  • If Parent record relationship is not null, Retrieve Parent's Parent record
    • Add Parent's Parent name to same variable
    • (This is obviously all hard-coded, but could step up a certain logical limit of your expected max hierarchy structure)
• Initiate Flows on child records
  
  • If any Children of Child (Grandchildren) records exist
    • Then run this same Flow, to account for changes up/down/within the hierarchy

This isn't great, but it would work. In our case, we also determined where the record existed in the hierarchy and set a level numeric integer to help things out. Basically, any record with no parent was level 1. The other levels were calculated dynamically.

This is ultimately a big headache because the hieraechy can shift at any time. New level added, mid-tiers realigned, etc.

Good luck!

[dancohen]

combined a few things to get this working;
collections (table)
using ForAll() to "do something a number of times"
referencing the last table row's value to add another row to the collection/table

- Last(<collection>).<attribute_name>
and checking for errors... if using Office365Users.ManagerV2() has an error, don't add anything the the collection.
its not the most elegant, but hey, this is powerapps.
results in a collection of the User and their management hierarchy; and saves a comma separated list of all the email addresses.
The collection can be used as the data source for a vertical gallery, for example.

ClearCollect(Loop,
{Index:0},
{Index:1},
{Index:2},
{Index:3},
{Index:4},
{Index:5},
{Index:6},
{Index:7},
{Index:8},
{Index:9},
{Index:10}
);
ClearCollect(org_hier,
{email:Office365Users.UserProfileV2(User().Email).mail, name:Office365Users.UserProfileV2(User().Email).displayName});
ForAll(Filter(Loop,Index<=10),
If(!IsError(Office365Users.ManagerV2(Last(org_hier).email).mail),
Collect(org_hier,
{email:Office365Users.ManagerV2(Last(org_hier).email).mail, name:Office365Users.ManagerV2(Last(org_hier).email).displayName})
)
);
Set(org_string,Concat(org_hier.email, email, ", "));

[FollowTheLion]

I have the exact same application.  If you found a simple solution, please pass it along.  I tried using two different calculated fields as a workaround (Path vs FullPath), so that one could be used to calculate the other, but it was still "smart" enough to flag it as a circular reference.  It's a little silly because we're already limited to the number of levels in our hierarchy, so why not allow a so-called "circular reference" as long as it's via a parent or child, rather than from the same record?  The recursion would be limited to the levels in your hierarchy, which maxes out at 100 by default.  I might put something together in Power Automate to update these periodically, but I was really hoping for a live calculation.

[KoenJanssensPD]

@FollowTheLion  recently, it came to my attention that the Product table (in D365 Sales) has a "Hierarchy Path" (single line of text) that is somehow populated with the full path... you might take a look at that field how it's being set...?