Using Power Automate to Generate Dynamic Tabular Data Within Adaptive Cards

Both the advantage and disadvantage of working in the Microsoft Business Applications space is the massive variety of different, available tools at our disposal that we can leverage to solve common business issues. However, unless you’ve spent some time researching or getting shown new subjects from someone “in the know”, you often avoid stepping outside of your comfort zone. It was great, therefore, to recently join a session hosted by the excellent people over at the Virtual Power Group all about Adaptive Cards, a modern tool for allowing open exchanges of information between a variety of different systems. And, as luck would have it, I’ve been involved in a project recently where there was an excellent opportunity to put them into action. So I rolled up my sleeve, took them for a test drive and wanted to share how I overcame what turned out to be less than straightforward requirement…

The overall need for the solution was to display a list of variable data generated from a system, within a table, that a user could then consume from within Outlook. The underlying data was essentially a key/value pair of documents, and their appropriate URL’s derived from SharePoint Online. This information needed to be sent out via a Power Automate flow and resemble the format indicated below:

Sounds easy, right? 😅 Well, it is sort of, but not as simple as I might have hoped when starting. To begin with, we need to initialise a couple of array variables, which are used to store the relevant JSON snippets to store the data:

Next, because of how ColumnSets work in Adaptive Cards, we need to populate the first row in each of our column with the header values for our dataset. In this case, as well, we want to make it extra clear that this is the header row, by ensuring that the column values we supply render in bold. Therefore, we need to call two Append to Variable Array tasks to get this set up correctly:

Here’s a copy/pastable version of this snippet below - change the text value to suit your specific scenario:

{
  "type": "TextBlock",
  "text": "Document Type",
  "weight": "bolder"
}

Next, we need to actually to get the data that we want to display. In this situation, the data is being pulled from Microsoft Dataverse (AKA the Common Data Service), and the Document Type value is an Option Set. We also want to make sure that the URL’s are embedded as hyperlinks so that people can easily navigate to the document in question. So this will get interesting. 😁 Once our data has been retrieved, we then need to implement an Apply to each and, to begin with, the two compose steps we need that will extract the correct values from each row returned:

Again, here’s the raw JSON snippets. We are using a bit of Markdown magic to ensure the hyperlink generates correctly; you can modify the text in the square brackets to change the text of the resulting link:

{
  "type": "TextBlock",
  "text": "@{items('Apply_to_each')?['[email protected]']}"
}

{
  "type": "TextBlock",
  "text": "[@{items('Apply_to_each')?['jjg_url']}](@{items('Apply_to_each')?['jjg_url']})"
}

Then we do another append step again, which will get each row from our dataset added on as we’d expect, by referencing the output of the previous two compose actions:

Now we can bring it all together for a final compose action, which will inject in the dynamic values we’ve specified above. Here’s the final snippet to use to get this built out:

{
    "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
    "type": "AdaptiveCard",
    "version": "1.3",
    "body": [
        {
            "type": "Container",
            "items": [
                {
                    "type": "TextBlock",
                    "text": "Sample Adaptive Card",
                    "weight": "Bolder",
                    "size": "Medium"
                }
            ]
        },
        {
            "type": "Container",
            "items": [
                {
                    "type": "TextBlock",
                    "text": "This is an example adaptive card displaying dynamic tabular data.",
                    "wrap": true
                }
            ]
        },
        {
            "type": "ColumnSet",
            "columns": [
                {
                    "type": "Column",
                    "width": "200px",
                    "items": @{variables('DocumentTypeJSON')}
                },
                {
                    "type": "Column",
                    "width": "stretch",
                    "items": @{variables('URLJSON')}
                }
            ]
        }
    ],
    "actions": []
}

From there, you can then implement the appropriate step to distribute your Adaptive Card - whether through Microsoft Teams, Outlook or elsewhere.

Adaptive Cards provide some impressive capability that can sit neatly alongside the Power Platform, and I’ve been kicking myself for not taking a proper look at them until now. Although to be fair, you do need to identify a particular type of business requirement to leverage them to their fullest capability. I recommend you check out the recordings of all the Virtual Power Group’s online sessions on Adaptive Cards, so you can hopefully discover their benefits and how easy it is to get started with them. I’ve included the links below to all the sessions: