Use an Instant Flow to create a multi-table lookup

Recently I created a multi-table (polymorphic) lookup for the first time, and then created a custom page with a gallery in which a label references the polymorphic lookup so needed to understand how to display data from it. Read on to understand how I created the lookup using a flow.

The Problem

In our SmartCivil product, we have a couple of custom tables where staff record the work they do throughout the day. We have automation that pulls data from various places to automatically generate the timesheet for an employee based on other data in the system (dockets and leave requests). We wanted to store a reference to the source data within the timesheet entry, and rather than creating three different lookups we wanted a polymorphic lookup that could link to three different types of entities.

Using Power Automate to create the lookup

Currently there is no UI for creating the polymorphic lookup from the maker studio, to create them you can use an XRMToolBox plugin, a web API request or write some C# code. I have a number of "utility" flows that I use across different environments for interacting with the dataverse web API, for example to delete an appaction, and thought this would be a good opportunity to add to my list of utility flows. If the XRMToolBox is so familiar to you that it feels like an extension of your own body then you'd probably decide to use that. I don't spend a lot of time in it and already had the Azure App registration set up, so for me I thought I'd try a flow.

Building the flow

Prerequisites - you'll need to have an Azure App Registration for your dataverse environment, and have created the corresponding app user - you’ll also need the token endpoint from the app registration if you use token authentication like we have here. We already had these set up so the process was fast for us - if you haven’t done it before try these instructions - or these instructions which use Active Directory OAuth so only need one HTTP call.

This is a one-off flow so we didn’t bother with our usual scope blocks to catch errors. Its in a solution with environment variables for the token endpoint, resource (dataverse org URL) and client id, with client secret stored in Azure key vault.

Once you get the bearer token then just one HTTP call to the dataverse web API is needed. The complex part is writing the JSON with the lookup definition - use the example in the MS docs page and adjust with your entity and field names.

Note that I used version 9.0 of the API as that's the version referenced in the docs, does anyone know if I could have used a later version, eg v9.2?

Fortunately for me this worked, but if something goes wrong with your flow you may need to look at how to delete the lookup - there's details in the MS docs.

Accessing lookup data in custom page

Now that we’ve created the lookup, if you need to reference the lookup data in a canvas app or custom page, there is a little bit of extra work involved - you’ll need to get familiar with the AsType and IsType PowerFx functions. This is how we did it:

MS Docs reference:

https://learn.microsoft.com/en-us/power-apps/developer/data-platform/webapi/multitable-lookup

https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/working-with-references

https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-astype-istype