Power Apps – Making a selection based on hierarchy
When making Power Apps, different hierarchies often come into play. Organization, product, service, etc. The hierarchical information must be stored in a usable form. It’s usually easy. The challenges come from leveraging the hierarchy in the actual application. That is, how the hierarchy is presented so that the user can make selections based on it.
This time our topic is working with hierarchical information. We will use the service directory found on the websites of several cities as an example.
Let’s get started!
Storing hierarchical information
The services are saved in one table (Services Demo) with the following fields
- Service Name (Name)
- Service under which the service belongs (Parent Service)
- At what level of the service hierarchy is the service (Hierarchy level)
- Order number of the service, if the services are presented in one list (OrderId
- Title of the service, when the services are presented in one list (Title for dropdpown)
The first two fields describe the hierarchy. Others are auxiliary fields for the application.
In practice, the stored service hierarchy looks like this.
The relationships between the services are known, but unfortunately, it is not always enough for our application by itself.
Make a selection from the hierarchy using multiple drop-down menus
The user needs to select the service in Power Apps. We can easily implement an interface where the user first selects the main level of the service hierarchy, and then the next level from the next control, and so on.
You can find a more detailed description of this implementation method in my old blog post (timopertila.com).
The implementation is straightforward, but there are a few problems with it. First, the depth of the hierarchy has to be decided in advance. For example, in the picture above the depth is three. Second, the solution works if the user selects only one service. What if the user should be able to choose multiple services and even from different levels?
Making a choice freely from the service hierarchy
We would like to offer the user the freedom to choose n pieces of rows from different levels of the service hierarchy. That is multiple lines from a single list / drop-down menu.
The presentation style is familiar to everyone, but how can we get the services listed in the right order and intended correctly? Services are stored in a single table in a completely random order
We should rearrange the lines of the service hierarchy so that the services are in a logical order. This order can be built in several ways.
Organizing services in Power Apps
A new collection is created and then the levels of the service hierarchy are reviewed. After this, the levels are added to the collection in the correct order.
The generated collection can be utilized in different controls.
The list is always up to date, as the order is built in the application each time it is used. But the depth of the hierarchy is limited (3 levels in our example code).
Quite neat, but it would be even simpler if the services were in the right order automatically. In this case, they should have a sequence number.
Organizing services with Flow
Next, a flow is built that goes through the service hierarchy and stores the sequence number on each line so that the services can be listed in the correct order, e.g. in the drop-down menu in our application.
Create a variable (Order Id) for the sequence number and retrieve the main levels of the hierarchy, basically those services that do not have a Parent level defined.
For each service, its Order Id is updated, as well as the name to be displayed in the control (Title for dropdown). The Order Id number is then incremented.
For each row, the services below it are searched next (Level2). They are similarly reviewed and their Order Id is updated. This time, a “-” character is added to the beginning of the name displayed in the control.
Finally, the same trick is done to the third level of the hierarchy. In this case, “-” is added in front of the title to be displayed in the control.
Overall the Flow looks like this.
After performing the Flow, the table looks like this.
In Power Apps, we can use tables directly as a data set for controls. As long as the rows are sorted by the Order Id we created.
Items: Sort('Services Demos', OrderId, Ascending)For example, a combo box now looks like this.
The solution is easy to understand, but now it only works with a 3-level hierarchy. For each new level, a new internal loop should be added to the Flow. There can be a maximum of 8 internal loops in Flow.
Order Ids should also be created when the hierarchy changes.
Organizing services using recursive Flow
Finally, let’s create a flow that builds the correct Order Ids for the service hierarchy regardless of the depth of the service hierarchy. This is accomplished with a recursive Flow. A program that calls itself is called recursion, and it is perfect for a situation like this. At the same time, however, it is very difficult for a beginner to understand.
Let’s still try.
Let’s create a flow that starts with an HTTP trigger (Child flows would be easier to do, but they can’t call themselves).
The flow gets as parameters the following
- The identifier of the parent service, whose services we are currently reviewing (ParentGuid)
- First free order id (CurrentOrderId)
- The hierarchy level we are on (HierachyLevel)
Initialize the OrderId variable with the received order id number and retrieve all services under the received service.
The results are reviewed and an OrderId and hierarchy level are updated for the service.
The number of “-” characters corresponding to the hierarchy level is set in the field Title for dropdown, which is displayed in the control.
substring('-----------------------------',1,triggerBody()?['HierarchyLevel'])Let’s increment the sequence number by one.
Note that the loop (Apply to each) cannot be run in parallel, so the degree of parallelism is set to 1.
And then comes the wild part. Let’s call the same flow, but with the data of the service being processed. The flow returns the next free OrderId number that is set in the variable.
As a final step, the flow returns the next free OrderId number, as well as information on which hierarchy level we are currently at.
Here is the flow in its entirety.
It goes through one level of the hierarchy, and for each service calls itself to take care of the next level.
Ingenious and confusing. That is my definition of recursion.
We will need another flow to start the process. It goes through the first level of the service hierarchy and calls the previous flow we created for each service. Also here it should be noted, that the Apply to each loop is not run in parallel.
Although recursion allows us to go through an n-level hierarchy, the approach has its own limitations. The flow we made keeps a connection to their caller for 120 seconds. In practice, the flow has 120 seconds to process the hierarchy below each main level. After that, everything is ruined.
Summary
We went through three very different ways to prepare a list of services for display with a Power Apps control. Which of these would I use myself?
- If the hierarchy has frequent changes, I would probably build a collection in Power Apps (if there are less than 2000 levels in the hierarchy). I would implement a formula so that if there are a few more levels added to the hierarchy in the future, it would still work.
- If the hierarchy is fairly permanent, I would complete it with flow but without recursion. I would do a flow that is able to go through the allowed 8 levels. The flow would start manually or when changes are made to the hierarchy.
I would forget the recursive solution. While it is pretty neat, its operating logic may not be clear to the next citizen developer who will update the solution in the future.
Show me more apps running on Power Platform!
Are you interested in seeing examples of the kinds of apps we’ve built for our customers & ourselves by using Power Apps, Power Automate and Power BI? Check out the Forward Forever App Gallery for ideas and inspiration on what your organization could also achieve with low-code apps.