We will use Flow to generate some JSON for the organizational hierarchy, which can then be used in various org charts around the business. There is a specific D3.js library, which will be used to display it all, but for now we going to cover the overall structure to generate it all properly.
For transparency, using Flow is probably not the best way to do this, just given the number of workarounds to achieve what should be basic programming techniques. This org chart approach is pushing the limits of what we can do with Flow just to see how far we can get with its functionality. Its huge advantage is that it is available to anyone using Microsoft 365, so even though it may not be an elegant solution at times, it is hugely powerful by putting this kind of automation in the hands of every user.
Microsoft Flow Performance
Each HTTP call has a performance hit, which in a recursive traversal will add up. This is not the most efficient way to traverse a very large organization hierarchy in Microsoft 365. So, a department of about 30 individuals will take roughly 50 seconds to traverse and a larger group of 60 individuals can approach the 120 seconds maximum. These timings can fluctuate, most likely due to the load and capacity of the underlying compute that Microsoft puts at the disposal of Flow.
There are two Flows, the “Get Manager Org JSON” is currently the main entry point, which pulls the manager details, and then calls the “Get Direct Reports JSON” Flow to get the reports, and that has the logic to call itself multiple times to get all the levels of reports below.
We could look at some form of caching. If the Flow is traversing an organizational hierarchy and along the way encounters a manager has already been mapped out, we could just reuse that. So, if we start with lower level managers, save their results to say a SharePoint library, then assemble that existing JSON into any calls above that manager we can avoid doing the traversal from scratch.
Saving to SharePoint
We start by adding a step (1) to our “Get Manager Org JSON” Flow, which will save the output of the JSON to a SharePoint site. We can use the manager name as the filename to be able to reference that same JSON later.
Then in the “Get Direct Reports JSON” Flow we add the corresponding conditional within the loop that if the direct report is a manager and that manager already has the JSON saved to the SharePoint site (2), simply read that JSON rather than traverse. Otherwise if no saved JSON, then traverse.
The larger group which took ~120 seconds to traverse, is now completed in under 10 seconds when assembling 5 pre-populated org charts for the direct reports. This is saves time tremendously with a simple tweak to save results to a SharePoint site. More importantly, this shows how several of the Microsoft 365 features can be joined up to solve an interesting programming challenge.