Low-code & Pro-code working together: Hierarchical view in Canvas App

In the past I have had requirements about displaying information in Power Apps, but as a hierarchical view, so I think this requirement has become quite common, that’s why last year I created a PCF for Model Driven Apps that calls an API (is an http to the API you want in your project) to retrieve the response in a JSON format and display that information in a hierarchical view.

Pretty awesome, but I was wondering if there is another way to achieve this requirement and yes there are several ways, so for instance you can do the hierarchical view feature entirely in Canvas App/Custom Pages by using Power Fx and nested galleries.

There is another way to achieve this and that is by preparing the entire data structure (in a hierarchical way) in a SQL Server store procedure or a SQL Server view and then from a Canvas App retrieve from the SQL Server that information that is already prepared so you have to use much less Power Fx code to achieve the hierarchical view feature in the Canvas App.

And this is the method that inspired me to do the same, but instead of using SQL Server View I wanted to use a custom API to prepare the data structure.

So before continue, I want to give many thanks to Tiran Dagan for his awesome blogpost that inspired me to try cool stuff in Power Apps.

Without further introduction let’s start.

Explaining the data structure

For this example I will use the Accounts table in Dataverse which already has a parent-child relationship.

So I have created a few accounts related between them:

In the image above you can see that there are 3 main accounts (A,B and C) and each of them has child accounts, so to further appreciate the hierarchical structure in the Model Driven App you can click on the hierarchical tree icon and you can see this:

Ok the relationship structure is quite simple, just use the lookup column called parent account to relate the accounts to each other. Now the next step is to prepare the data in a hierarchical way on the server side by using a custom API.

Create the Custom API

I won’t explain how to create a Custom API because there are a lot of blogposts out there explaining how to do this, but I going to share the code I used

First you have to create a class called GetHierarchicalData.cs, here is the code:

using Microsoft.Xrm.Sdk;
using System;

namespace P365I.Main.CustomAPIs
{
public class GetHierarchicalData : IPlugin
{
public void Execute(IServiceProvider serviceProvider)
{
ITracingService tracingService = (ITracingService)serviceProvider.GetService(typeof(ITracingService));
IPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
IOrganizationServiceFactory factory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
IOrganizationService service = factory.CreateOrganizationService(context.UserId);

tracingService.Trace(“Start Custom API GetHierarchicalData”);
var hierarchicalDataHandler = new Core.Handlers.GetHierarchicalDataHandler(tracingService, service);
string result = hierarchicalDataHandler.Process();
context.OutputParameters[“p365i_GetHierarchicalDataResult”] = result;

tracingService.Trace(“End Custom API GetHierarchicalData”);
}
}
}

In the above code you can see I’m calling the Process class from my hierarchicalDataHandler class, this is just a way of delegate all the heavy lifting to the handler class.

Create the Handler class

So the enxt step is to create a class called hierarchicalDataHandler.cs, here is the code:

using Microsoft.Xrm.Sdk;
using System;
using System.Collections.Generic;
using Microsoft.Xrm.Sdk.Query;
using System.Linq;
using Newtonsoft.Json;

namespace P365I.Core.Handlers
{
public class GetHierarchicalDataHandler
{
private ITracingService _tracingService;
private IOrganizationService _service;

public GetHierarchicalDataHandler(ITracingService tracingService, IOrganizationService service)
{
_tracingService = tracingService;
_service = service;
}

public string Process()
{
_tracingService.Trace(“Start Process”);

List<Entity> entities = RetrieveHierarchicalData();
List<HierarchicalData> hierarchicalDataList = ProcessResults(entities);
string jsonResult = ConvertToJson(hierarchicalDataList);

_tracingService.Trace(“End Process”);

return jsonResult;
}

public List<Entity> RetrieveHierarchicalData()
{
string fetchXml = @”
<fetch distinct=’true’>
<entity name=’account’>
<attribute name=’accountid’ alias=’recordGuid’ />
<attribute name=’accountid’ rowaggregate=’CountChildren’ alias=’childrenCount’ />
<attribute name=’name’ alias=’TableName’ />
<link-entity name=’account’ from=’parentaccountid’ to=’accountid’ link-type=’outer’ alias=’children’>
<attribute name=’accountid’ />
</link-entity>
<attribute name=’parentaccountid’ alias=’ParentRecordGuid’ />
<order attribute=’name’ descending=’false’ />
</entity>
</fetch>”;

EntityCollection result = _service.RetrieveMultiple(new FetchExpression(fetchXml));

// Print retrieved entities for debugging
Console.WriteLine($”Retrieved {result.Entities.Count} entities”);
foreach (var entity in result.Entities)
{
Console.WriteLine($”Entity: {entity.GetAttributeValue<AliasedValue>(“recordGuid”)?.Value}, {entity.GetAttributeValue<AliasedValue>(“TableName”)?.Value}, {entity.GetAttributeValue<AliasedValue>(“childrenCount”)?.Value}”);
}

return result.Entities.ToList();
}

public static List<HierarchicalData> ProcessResults(List<Entity> entities)
{
Dictionary<Guid, List<Guid>> parentChildMap = new Dictionary<Guid, List<Guid>>();
Dictionary<Guid, Entity> entityMap = new Dictionary<Guid, Entity>();

foreach (var entity in entities)
{
// Extract the recordGuid correctly
var recordGuidAliasedValue = entity.GetAttributeValue<AliasedValue>(“recordGuid”);
Guid recordGuid = recordGuidAliasedValue != null ? (Guid)recordGuidAliasedValue.Value : Guid.Empty;
if (recordGuid == Guid.Empty)
{
continue; // Skip if recordGuid is not valid
}

entityMap[recordGuid] = entity;

// Extract the ParentRecordGuid correctly
var parentRecordGuidAliasedValue = entity.GetAttributeValue<AliasedValue>(“ParentRecordGuid”);
Guid? parentGuid = null;
if (parentRecordGuidAliasedValue != null && parentRecordGuidAliasedValue.Value is EntityReference)
{
parentGuid = ((EntityReference)parentRecordGuidAliasedValue.Value).Id;
}

if (parentGuid.HasValue)
{
if (!parentChildMap.ContainsKey(parentGuid.Value))
{
parentChildMap[parentGuid.Value] = new List<Guid>();
}
parentChildMap[parentGuid.Value].Add(recordGuid);
}

// Print parent-child mapping for debugging
Console.WriteLine($”Parent: {parentGuid}, Child: {recordGuid}”);
}

List<HierarchicalData> hierarchicalDataList = new List<HierarchicalData>();
ProcessEntity(null, 0, “”, parentChildMap, entityMap, hierarchicalDataList);

var distinctDataList = hierarchicalDataList
.GroupBy(data => data.RecordGuid)
.Select(group => group.First())
.ToList();

return distinctDataList;
}

private static void ProcessEntity(Guid? parentGuid, int level, string treePath, Dictionary<Guid, List<Guid>> parentChildMap, Dictionary<Guid, Entity> entityMap, List<HierarchicalData> hierarchicalDataList)
{
if (parentGuid.HasValue)
{
if (!entityMap.ContainsKey(parentGuid.Value))
{
Console.WriteLine($”Entity with GUID {parentGuid.Value} not found in entityMap.”);
return;
}

Entity entity = entityMap[parentGuid.Value];
var childrenCountAliasedValue = entity.GetAttributeValue<AliasedValue>(“childrenCount”);
int childrenCount = childrenCountAliasedValue != null ? (int)childrenCountAliasedValue.Value : 0;
bool hasChildren = childrenCount > 0;

var tableNameAliasedValue = entity.GetAttributeValue<AliasedValue>(“TableName”);
string tableName = tableNameAliasedValue != null ? tableNameAliasedValue.Value.ToString() : string.Empty;

var parentRecordGuidAliasedValue = entity.GetAttributeValue<AliasedValue>(“ParentRecordGuid”);
Guid? actualParentGuid = null;
if (parentRecordGuidAliasedValue != null && parentRecordGuidAliasedValue.Value is EntityReference)
{
actualParentGuid = ((EntityReference)parentRecordGuidAliasedValue.Value).Id;
}

string newTreePath = string.IsNullOrEmpty(treePath) ? tableName : treePath + ” -> “ + tableName;

hierarchicalDataList.Add(new HierarchicalData
{
ChildrenCount = childrenCount,
RecordGuid = parentGuid.Value,
TableName = tableName,
HasChildren = hasChildren,
Level = level,
ParentRecordGuid = actualParentGuid,
Treepath = newTreePath
});

// Print hierarchical data for debugging
Console.WriteLine($”Added: {parentGuid.Value}, {newTreePath}, ChildrenCount: {childrenCount}”);

if (hasChildren)
{
foreach (var childGuid in parentChildMap[parentGuid.Value])
{
ProcessEntity(childGuid, level + 1, newTreePath, parentChildMap, entityMap, hierarchicalDataList);
}
}
}
else
{
foreach (var rootEntityGuid in parentChildMap.Keys.Where(key => !entityMap[key].Contains(“ParentRecordGuid”)))
{
ProcessEntity(rootEntityGuid, 0, “”, parentChildMap, entityMap, hierarchicalDataList);
}
}
}

public string ConvertToJson(List<HierarchicalData> hierarchicalDataList)
{
_tracingService.Trace(“Start ConvertToJson”);

var jsonObject = new
{
data = hierarchicalDataList
};
return JsonConvert.SerializeObject(jsonObject, Formatting.Indented);
}

}

public class HierarchicalData
{
public int ChildrenCount { get; set; }
public Guid RecordGuid { get; set; }
public string TableName { get; set; }
public bool HasChildren { get; set; }
public int Level { get; set; }
public Guid? ParentRecordGuid { get; set; }
public string Treepath { get; set; }
}
}

With this code you only have to compile and upload your assembly to your Dataverse environment by suing the plugin registration tool, and only then you can create the Custom API in your solution:

Create the Canvas App / Custom Page

Here as you can see I’ve created a solution in my environment:

That solutions contains the Custom API, the response parameter from the Custom API and a Custom Page from which I will call the Custom API.

So first thing is to add the environment table as your data source:

Next, you have to upload 2 images that will make the user interface of the hierarchical view prettier, so that one image will be displayed when the parent node with children is collapsed and the other image will be displayed when the parent node with children is expanded:

Then you can add a button and in the OnSelect property you can add this code:

Set(varResultJSON, Environment.p365i_GetHierarchicalData());
Set(parsedResponse, ParseJSON(varResultJSON.p365i_GetHierarchicalDataResult));
Set(dataArray, Table(parsedResponse.data));

ClearCollect(flattenedDataArray,
ForAll(dataArray,
{
Treepath: Text(ThisRecord.Value.Treepath),
ChildrenCount: ThisRecord.Value.ChildrenCount,
RecordGuid: ThisRecord.Value.RecordGuid,
TableName: ThisRecord.Value.TableName,
HasChildren: Boolean(ThisRecord.Value.HasChildren),
Level: ThisRecord.Value.Level,
ParentRecordGuid: ThisRecord.Value.ParentRecordGuid
}
)
);

// Sort the flattened data and add new columns
ClearCollect(TreeItems,
AddColumns(
SortByColumns(
flattenedDataArray,
“Treepath”,
SortOrder.Ascending
),
Expanded, true,
Shown, true
)
);

The above code uses the Environment table to call the Custom API directly and not have to use a cloud flow. I then use the ParseJson function to parse the Custom API response and return an untyped object representing the JSON structure.

Then I access the data node, if you’re wondering why that node is there, it’s because it’s added by the Custom API at the end:

So in the canvas app I convert the data node into a table so I can create a temporary collection with all the specific columns, some of them I even have to cast them so that the value is retrieved correctly:

And finally I create the second collection that is sorted and with new columns:

In case you are wondering, yes you can combine both collections into one but for ease of code handling I did it in 2 separate collections.

Then you can add a vertical gallery with an image and a label components:

In the Items property of the gallery you can use this code:

The above formula will only show the nodes in the hierarchical view that are visible, because except for the parent nodes, all child nodes can be collapsed, so those nodes need not be visible.

Then in the component label you can show the column TableName which is the name of the Account:

Then in the image component you can use this formula:

That is to show one of the two images when the node is expanded or collapsed.

Testing

That is all so now you can test your Canvas App / Custom Page:

Conclusion

There are many ways to fulfill this requirement as I mentioned at the beginning, you can do it using only PowerFX and nested galleries or another way as I did it is to first prepare the data on the server side in a hierarchical way to return it to the canvas app, so the heavy work is on the server side and the canvas app only has to use small PowerFx formulas to display this data.

Once again a big shoutout to Tiran Dagan for his awesome blogpost that inspired me to try interesting things in Power Apps.