cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KoenJanssensPD
Helper V
Helper V

Get full hierarchy path

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

 

For example:

IDParentIdNameFullPath
1 Boss 1Boss 1
21Manager 1Boss 1 - Manager 1
32Employee 1Boss 1 - Manager 1 - Employee 1
42Employee 2Boss 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...

 

 

6 REPLIES 6
SeanHenderson
Power Participant
Power Participant

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."

@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.

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
New Member

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
Frequent Visitor

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
Helper V
Helper V

@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...?

Helpful resources

Announcements
Super User 2 - 2022 Congratulations 768x460.png

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

<
Users online (4,461)