DEV Community: Matthew Collinge

Cloning Power Automate Flows using a Power Automate Flow

Matthew Collinge — Wed, 15 Apr 2026 09:25:54 +0000

Introduction

When you build a document trigger on top of SharePoint, you quickly run into a fundamental constraint: a SharePoint trigger Flow is hardwired to a single Site and Library. There is no native way to point one Flow at a dynamic or configurable location — the Site URL and Library GUID are baked into the trigger definition at creation time.

For a one-off deployment that's fine. But when you're setting up a standardised *Processes & Procedures * programme across an organisation — where every Team or Business unit gets its own SharePoint Site — you need an approval trigger Flow for each one. Creating and maintaining these by hand rapidly becomes a maintenance problem: if you fix a bug in one, you have to fix it in all of them.

This post walks through the solution we built to address that: a Site-specific Provisioning Flow that clones a template approval trigger into a new, Site-specific Flow automatically, using nothing more than Dataverse, Power Automate, and a clean separation between configuration data and Flow logic.

The SPO P&P System at a Glance

Before diving into the provisioning Flow, it's worth establishing the wider architecture of the solution.

Each Processes and & Procedures Library is in a SharePoint Site. Authors upload documents to the Library. When a document is submitted for approval (its approval status set to Pending Approval), a Power Automate Flow fires, routes the approval through a standard and centralised process via Microsoft Teams, and on Approval publishes the document to its major version and updates the approval metadata. This process can be maintained and changed whenever required and is all done in one place.

That last Flow, the one watching the Library for changes, is the one we need to clone. It is specific to a Site and Library. The goal of this project was to make provisioning a new P&P Site a self-service operation: an admin fills in a record, runs a Flow from the Model Driven app, and everything wires itself up automatically 😀.

The Three Flows

The solution contains three Power Automate Flows that work together:

Flow	Role
SPO PP - Provision Site Flow	Orchestrator — clones the template and provisions a new Site-specific Flow
SPO PP - Approval Trigger - TEMPLATE	Template — the Flow definition that gets cloned for each new Site
Child: PP Approval Standard Process	Worker — handles the actual approval logic, called by every cloned trigger Flow

The template and child Flows are permanent, shared fixtures. The cloned approval trigger Flows are ephemeral — one per Site, generated at provisioning time. We can have as many as we want of these, but point them all to the same child Power Automate Flow so we can control the centralised process in one place.

The Site Registry Table

The hub of the whole system is a custom Dataverse table: Site_Registry . It stores one row per P&P Site and acts as the source of truth for both provisioning and ongoing operations.

Custom Columns

Display Name	Logical Name	Type	Required	Purpose
Site Name	`Sitename`	Text	Yes	Human-readable name; also used as the Primary Name field and in the provisioned Flow's display name
Site URL	`Siteurl`	Text	Yes	Full URL of the SharePoint Site (e.g. `https://companySite.sharepoint.com/Sites/Finance`)
Library GUID	`Libraryguid`	Text	Yes	GUID of the Document Library within the Site — required by the SharePoint trigger
Flow ID	`Flowid`	Text	No	Written back by the provision Flow after cloning; links this registry entry to its live approval trigger Flow
Site Registry ID	`Site_Registryid`	GUID (Primary Key)	System	Auto-generated unique identifier for the row

The Flowid column starts empty. It is the only field the provisioning process writes — everything else is entered by the admin before triggering provisioning.

Why Library GUID, Not Library Name?

The SharePoint When a file is created or modified (properties only) trigger requires a list/Library GUID rather than its display name. GUIDs are stable across renames and don't break if someone tidies up the Library name. Recording the GUID in the Site Registry means provisioning is reliable regardless of what the Library is called. For our template, we can just use a development SharePoint site and a placeholder document Library to keep things simple and standard. I had tried inserting placeholderName and Library-guid-name as placeholders, but the Flow struggled to save.
As a possible improvement in the Model Driven App, we could make selection easier by showing them a list of possible document libraries for the selected sites, so the user does not have to go and find the GUID.

The Approval Trigger Template

The template Flow (SPOPP-ApprovalTrigger-TEMPLATE) is the blueprint for every Site-specific approval trigger. It lives in the solution and is never activated — it exists solely to be cloned.

Its trigger is a SharePoint When a file is created or modified (properties only) action polling every 1 minute, pointed at two placeholders:

Site URL placeholder: https://companysite.sharepoint.com/Sites/ITDevelopment
Library GUID placeholder: a3f7c291-84e6-4d1a-b509-2e763dc18f45

A trigger condition filters the events down to only relevant ones:

ApprovalStatus = 3 (Pending Approval)
AND Editor is NOT the service account

This prevents the Flow from firing on system-generated updates and ensures it only acts when a real user has submitted a document for review.

When the trigger fires, the Flow calls a single action inside a Try scope: Run a Child Flow — PP Approval Standard Process. It passes the item's ID, Site URL, Library GUID, author claims, full path, approval status, and editor claims to the child Flow, then waits for a response.

The Catch scope sends an error notification email to the support desk if anything fails.

The `clientdata` Column — the Key to Cloning

When Power Automate stores a Flow in Dataverse, it uses the workFlows ("Processes") table. The Flow's entire definition — triggers, actions, connections, expressions — is serialised as JSON and stored in a column called clientdata.

This is the column we read from the template and write to the new Flow. The string-replace approach works because the placeholders (the hardcoded Site URL and Library GUID) are literal strings embedded in that JSON. Swapping them out produces a valid, Site-specific Flow definition ready to run.

The Provision Site Flow — Step by Step

The SPO PP - Provision Site Flow is triggered from the Model Driven App by an admin selecting a Site Registry row and running the Flow from the command bar.

Trigger: Record Selected on Site_registries

The trigger uses splitOn, meaning if multiple rows are selected the Flow runs once per row — each Site is provisioned independently.

Step 1 — Initialise Variable (`varClientData`)

Before entering the Try scope, a string variable varClientData is initialised empty. This will hold the cloned Flow definition once the placeholders have been replaced.

This is declared outside the Try scope deliberately: it needs to be initialised before any branching logic, and the scoped Try/Catch pattern requires variables to be declared at the root level to be accessible in both branches.

Step 2 — Get Site Registry Row (`Get a row by ID — Site Registry from Trigger`)

The trigger provides the Site_Registryid from the selected record, but only the ID and Site name are included in the trigger payload. We need the full row — specifically the Siteurl and Libraryguid — so the first action inside the Try scope fetches it.

Entity: Site_registries
Record ID: triggerBody()?['entity']?['Site_Registryid']

The output of this action is referenced throughout the rest of the Flow.

Step 3 — Retrieve the Template Flow Definition (`List rows — Processes`)

Next, the Flow queries the Dataverse workFlows ("Processes") table to retrieve the template's clientdata:

Entity: workFlows
$select: clientdata, workFlowid
$filter: workFlowid eq '05a1a909-5b33-f111-88b3-6045bddd6b33'
$top: 1

The template's workFlow ID is hardcoded here — it is a known, stable fixture in the solution. Using $top: 1 ensures a single result is returned efficiently.

The clientdata that comes back is the full JSON definition of the template Flow, including the hardcoded Site URL and Library GUID placeholders.

Step 4 — Inject Real Values (`Set variable — varClientData`)

This is the core of the cloning mechanism. A double-nested replace() expression swaps out both placeholders in a single operation:

replace(
  replace(
    first(outputs('List_rows_-_Processes')?['body/value'])?['clientdata'],
    'https://companySite.sharepoint.com/Sites/ITDevelopment',
    outputs('Get_a_row_by_ID_-_Site_Registry_from_Trigger')?['body/Siteurl']
  ),
  'a3f7c291-84e6-4d1a-b509-2e763dc18f45',
  outputs('Get_a_row_by_ID_-_Site_Registry_from_Trigger')?['body/Libraryguid']
)

The inner replace substitutes the Site URL. The outer replace then takes that result and substitutes the Library GUID. The outcome is stored in varClientData: a complete, valid Flow definition targeting the new Site's Library.

No parsing, no schema manipulation — just string replacement in JSON.

Step 5 — Create the New Flow (`Add a new row — Processes`)

The modified definition is now written back to the workFlows ("Processes") table as a new row, creating the cloned Flow:

Entity: workFlows
item/name:     "SPO PP - Approval Trigger - {Sitename}"
item/category: 5        (Modern Flow)
item/type:     1        (Definition)
item/primaryentity: "none"
item/xaml:     " "     (required field, but not used for modern Flows)
item/clientdata: @variables('varClientData')

The category: 5 value identifies this as a cloud Flow. The xaml field is a legacy artefact required by the API but irrelevant for modern Flows apparently — a single space " " keeps it happy.

The response from this action includes the new Flow's workFlowid, which is carried forward to the next two steps.

The new Flow is created in a disabled/draft state. It will not run until it is manually opened, saved (to authenticate connections), and turned on. This is by design — connections must be authorised by a human with the appropriate permissions, not ideal for automations, but we can live with it.

Step 6 — Add to Solution (`Perform an unbound action — Add to Solution`)

With the Flow created, it exists in Dataverse but is not yet part of any solution. This step calls the AddSolutionComponent unbound action to register it under the SPOPPSiteConfig solution:

ComponentId:         (new Flow's workFlowid)
ComponentType:       29    (Cloud Flow)
SolutionUniqueName:  SPOPPSiteConfig
AddRequiredComponents: false
DoNotIncludeSubcomponents: false

Component type 29 maps to cloud Flows in the Dataverse component type enumeration. By adding it to the solution, the provisioned Flow becomes visible in the main solution and is logically grouped with the rest of the P&P components - making management cleaner.

Step 7 — Write the Flow ID Back to Site Registry (`Update a row — Site Registry`)

The final Dataverse write closes the loop: the new Flow's ID is written back to the Site Registry record that triggered this whole process. This is for record keeping maintenance mainly. As an added extra you could concatenate the full URL for the Flow if you wanted to.

Entity: Site_registries
Record ID: triggerBody()?['entity']['Site_Registryid']
Flowid: outputs('Add_a_new_row_-_Processes')?['body/workFlowid']

After this step, the Site Registry row has a Flow ID value. This serves as:

A human-readable audit trail (the admin can see which Flow was provisioned for which Site)
A navigation link back to the provisioned Flow
A signal that provisioning has completed for this Site

Step 8 — Notify via Teams Adaptive Card (`Post card — Turn Flow On`)

The final action sends an Adaptive Card to the user who triggered the Flow via Teams (the triggerOutputs()?['headers']['x-ms-user-email'] header on the trigger response identifies them):

![Screenshot: Adaptive Card in Teams — SPO P&P Flow Provisioning notification]

As mentioned earlier, I tried to use some Power Automate actions to Turn On the Flow, But couldn't find any that worked, so this is our only workaround and a quick step for the end user, although not ideal. If you know a way to easily do this, please leave a comment.

The card surfaces:

Completed automatically: a green checklist of everything the Flow did
Manual activation required: a warning block explaining the three steps the admin must take
Open Flow button: a direct link to the new Flow in the Power Automate portal

The three manual steps are:

Open the Flow via the link in the card
Click Turn on

This manual step is a conscious design decision. Power Automate does not permit programmatic activation of a Flow that has connection references requiring user consent — the connections must be authorised interactively. The card makes this as frictionless as possible.

Error Handling — Scope Catch

If any action inside the Try scope fails, times out, or is skipped, the Catch scope runs. It:

Filters result('Scope_-_Try') to find the actions that failed, timed out, or were skipped
Posts a plain Teams message to the triggering user with a direct link to the failed Flow run

❌ Provision Site Flow Failed
Site: {Sitename}
View Flow Run: [link]
Once resolved, re-trigger provisioning from the SPO P&P Administration app.

The admin can inspect the failed run, resolve the root cause, and re-trigger provisioning from the app. Because the Site Registry record is only updated in Step 7 (after everything else succeeds), a partial failure leaves the record in its original state — Flow ID will be blank, signalling that provisioning is incomplete and can safely be re-run.

The Child Approval Flow

This is the central Flow that the cloned trigger Flows hand off to. The team can iterate on this particular Flow / Process as much as is needed without having to worry about duplicate actions, etc., as the Process can be amended when needed.

The Child: PP Approval Standard Process is a manually-triggered child Flow that accepts seven parameters from every approval trigger:

Parameter	Description
`ItemID`	SharePoint list item ID
`SiteURL`	URL of the Site (passed through, not hardcoded)
`ListGUID`	Library GUID (passed through)
`AuthorClaims`	Claims string of the document author
`FullPath`	Server-relative path of the document
`ApprovalStatus`	Numeric approval status value from SharePoint
`Editor`	Claims string of the last editor

On receipt, it:

Checks the item isn't already approved (terminates gracefully if it is)
Builds the server-relative URL via a Compose expression
Calls the SharePoint REST API to publish the document (_api/web/GetFileByServerRelativeUrl(...)/Publish)
Reads the updated file properties back from SharePoint
Calls the SharePoint REST API to set the approval status to Approved (OData__ModerationStatus: 0) and records the approval date and updates any other column metadata required.
Posts a Teams message to the document author confirming approval and the new version number

Because the Site URL and Library GUID are passed in as parameters (not hardcoded), this single child Flow serves every cloned approval trigger across the entire organisation.

How It All Fits Together

Here's the end-to-end lifecycle for adding a new P&P Site to the system:

1. Admin creates a SharePoint Online Site and Document Library
2. Admin notes the Library GUID (from the SharePoint URL or list settings)
3. Admin opens the SPO P&P Administration app
4. Admin creates a new Site Registry row:
      Site Name:    "Finance"
      Site URL:     https://companysite.sharepoint.com/Sites/Finance
      Library GUID: [GUID from SharePoint]
5. Admin selects the row and runs "SPO PP - Provision Site Flow" from the command bar
6. Flow runs (~10 seconds):
      - Reads Site Registry row
      - Fetches template clientdata from Dataverse
      - Replaces Site URL and Library GUID placeholders
      - Creates new Flow: "SPO PP - Approval Trigger - Finance"
      - Adds Flow to SPOPPSiteConfig solution
      - Writes Flow ID back to Site Registry
      - Sends Adaptive Card to The user that initiated the process via Teams
7. Admin clicks "Open Flow" in the Teams card
8. Admin saves and turns on the Flow
9. Finance P&P Site is live — approval triggers are active

From step 4 to a live Flow takes under a minute of admin time, with no Flow editing or cloning required.

Key Design Decisions

Why string replacement instead of a proper API?

Power Automate does not expose a public API for cloning Flows. The clientdata approach is the only programmatic path available without leaving the Power Platform. It is undocumented but stable — Microsoft's own import/export tooling uses the same workFlows ("Processes")("Processes") table and clientdata column.

Why store Library GUID and not Library Name?

GUIDs are immutable. Library names can be changed by Site owners without affecting the trigger. Storing the GUID avoids a class of hard-to-diagnose breakages.

Why manual activation?

Connection references in Power Automate require user consent. There is no API to activate a Flow with unauthenticated connection references. The Adaptive Card makes the manual step as lightweight as possible.

Why add the Flow to the solution?

It keeps all provisioned Flows grouped under SPOPPSiteConfig in the environment. This makes bulk management, export, and environment promotion practical — rather than having dozens of orphaned Flows scattered across the default solution.

Environment Constraint: Unmanaged Required
⚠️ One big constraint worth flagging: the Environment where the provisioned flows are created must be unmanaged.
The cloning mechanism writes new rows directly to the workflows table at runtime. Those flows are unmanaged artefacts — they exist outside the solution package and outside the ALM pipeline. That is intentional; they are site-specific, runtime-generated, and self-service by design.
But it means a managed-solution environment is incompatible with this pattern. The provisioning Flow will run fine as a managed component, but the governance model of a managed environment conflicts with generating unmanaged Flow records inside it.

The default environment is the most common candidate simply because it is typically unmanaged — but the constraint is unmanaged vs. managed, not default vs. non-default. Any unmanaged environment works.

Summary

The project is delivered a self-service Site provisioning capability built on a single mechanism: reading a Flow's clientdata from Dataverse, replacing two placeholder strings, and writing the result back as a new workFlow row. The Site Registry table is the operational hub — it drives provisioning and records the outcome. The template Flow is the blueprint — stable, never activated, just there to be cloned.

The result is a system where adding a new P&P Site is a data entry task, not a development task.

Generate a Dataverse ERD for Azure Dev Ops Wiki

Matthew Collinge — Sun, 16 Nov 2025 09:44:04 +0000

Nobody likes manually writing and updating Documentation. ERDs (Entity Relationship Diagrams) provide a really helpful overview of what your database relationships look like.

Context: We are using Azure DevOps (ADO) to manage our CI/CD Pipelines using the Power Platform Tools and therefore the Solutions are unpacked and stored in the Repository (there may be multiple Solutions inside of a Project).

The idea: working with Power Platform solutions stored in ADO, it would be great to visualise complex entity relationships right inside ADO. Visualizing these relationships can make solution design and troubleshooting much easier, it certainly helps me.

In this quick post, we'll use a short prompt using Chat in VS Code to get an AI Agent to generate a Mermaid ERD from our Dataverse solution files and then we can copy it and publish it to our ADO Wiki.

✅ Why This Matters

Better Documentation: ERDs help us understand entity relationships at a glance.
Native Rendering: Mermaid diagrams render natively in ADO Wiki.
Automation Ready: This process could be adapted for CI/CD pipelines later. For this we can implement Ian Tweedie - Upload Markdown to Wiki

Step 1: Clone the Repository in VS Code

In Azure DevOps, go to Repos → Clone → IDE. ---
Select Clone in VS Code. ---
Choose a local folder where the repo will be stored.

Step 2: Locate a Relationships File

In VS Code, navigate to the Power Platform solution folder inside the repo.
Go to: Other → Relationships.xml --- (if there is more than one Relationships.xml file, you may need to repeat these steps)
Open Relationships.xml in VS Code.

Step 3: Use AI to Generate the ERD

Open Chat in VS Code with Relationships.xml in file context.
Paste this prompt:

Please scan this file and identify all Dataverse-related entities and their relationships. Focus on extracting entity names, attributes, and how they relate to each other (e.g. one-to-many, many-to-one, many-to-many). Then, generate a Mermaid entity-relationship diagram (ERD) that includes cardinality for each relationship.

 Format the output as a Markdown file containing the Mermaid ERD code block. Ensure the diagram is readable and reflects the structure of the Dataverse schema as accurately as possible.

If any assumptions are made due to missing or ambiguous information, please list them at the end of the Markdown file.

Step 4: Copy the Mermaid Diagram

From the AI response, copy everything starting from:

erDiagram

erDiagram

    CUSTOMER ||--o{ ORDER : "1:N"
    CUSTOMER ||--o{ ADDRESS : "1:N"

    ORDER ||--o{ ORDER_LINE : "1:N"
    ORDER_LINE }o--|| PRODUCT : "N:1"

    PRODUCT ||--o{ PRODUCT_CATEGORY : "N:1"

    PRODUCT ||--o{ INVENTORY : "1:N"
    INVENTORY ||--|| WAREHOUSE : "1:1"

    PAYMENT ||--|| ORDER : "1:1"
    SHIPMENT ||--o{ ORDER : "1:N"

Example Mermaid

Step 5: Publish to ADO Wiki

In Azure DevOps, go to Overview → Wiki.
Click New Page.
Paste the Markdown (including the Mermaid block) into the left-hand pane.
Give the page a title.
Click Save.

✅ Pro Tip

Mermaid diagrams render natively in ADO Wiki, so your ERD will be interactive and easy to read 😜.

Possible Next Steps

Automate this process using a pipeline - this is going to require me to learn some fancy PowerShell Scripts to parse the XML 😵‍💫.
This could allow Automatic Documentation as part of a Release, keeping the ERD up to date.
Add entity attributes for deeper insights.

Extract table data from Documents using Azure AI Document Intelligence

Matthew Collinge — Wed, 16 Oct 2024 15:30:29 +0000

Extract table data from Documents Using Azure AI Document Intelligence Layout Model with Power Automate

Azure AI Document Intelligence is a cloud-based service that allows you to build intelligent document processing solutions. It uses machine learning to extract text, tables, and key-value pairs from documents, providing structured data for easy use.

In this post, I’m going to go through how to send a PDF document using an Azure AI Document Intelligence REST API request and return the text and table information using the Layout model. I’ll go through some of my processes for transforming the received JSON output into an array that can be used to create rows in a Dataverse table, all within Power Automate.

Power Automate isn’t Power Query, so I will use some techniques to get it to try and replicate some of the filtering, cleansing, and sorting capabilities of Power Query. Some of the filtering can be quite specific to the documents you have, so I will start quite general and then cover some techniques to cleanse the data by type alongside.

Choosing a Model

I would highly recommend that you use the Azure AI Document Intelligence Studio to find the most suitable model for your documents. You can try out models in the studio with your own documents. I did this and was confused about how you can actually use the model. The studio provides code examples, but you are kind of left to it from then on. When I’d found the REST API Documentation I certainly felt more comfortable, as I can use this with Power Automate!
Test multiple documents as you may discover inconsistencies in the results. To get around these, you can adjust some of the actions and expressions in your Flow to deal with them.

My Example

I have created a simple document that includes one table and I like sausages, but in the real world, I have dealt with documents of all shapes and sizes. For example, where the tables start on a different page (we can append &pages= to the URI to extract a specific page 🙂).

Passing the Document to Azure AI Document Intelligence

I have to start by mentioning that I could not have done this first part without the help of this YouTube video from the excellent Damien Bird on Power Automate Invoice Processing Tutorial AI Builder and Azure. Here Damien explores the AI Builder option and compares the costs, introduces the Document Intelligence Service on Azure, polling the services, reviewing the Response, and selecting data from the response.

We’re basically going to do the same, but select the Layout Model API Version 2023-07-31 from Azure AI Document Intelligence.

Azure Services

Before we start building our Flow, we will need some Azure Services set up:

Resource Group
Azure AI Services > Document Intelligence (Select the F0 Pricing tier for up to 500 documents per month for free, much better than AI Builder).
Azure Key Vault Secret

Make sure to copy the Endpoint, as we will need this in our Flow. To keep things secure keep your key inside Azure Key Vault. I can recommend following this video Power Automate +Azure Key Vault - get key and secret and pass to Flow from Sean Astrakhan on how to do this.

Flow Structure

Ok, let’s get on with the Flow!

Manual Trigger

Start with a manual trigger with a File Content Input. This will be used to bring the PDF Document into the Flow.

and then set up some variables....

Initialize variable – Status - We will need to use this String Variable to set the outcome of the second HTTP request status.

Initialize variable – Metadata - We will use this Object Variable to contextualise and reference the Document that we have extracted from.

Initialize variable - ParsedCellsArray - We will use this Array Variable to loop through the table cells.

Azure Key Vault Get secret

Enter the name of your Azure Key Vault secret to return its value. In settings, enable ‘Secure inputs’ and ‘Secure outputs’ to ensure the secret is hidden.

HTTP Request

This is where the fun starts. Add an HTTP Action.

URL: {Your Azure AI Service Endpoint}/formrecognizer/documentModels/prebuilt-layout:analyze?api-version=2023-07-31

There’s some more information here on versions and models. Here I have chosen the Model: prebuilt-layout and the api-version of 2023-07-31 – which is GA at the time of writing. It’s worth noting that I struggled to use the newer preview models due to my Azure AI services location being UK South (we have to wait for the new toys!).

Method: POST
Headers: Ocp-Apim-Subscription-Key: {Azure Key Vault Secret Value}
Body:
{"base64Source": triggerBody()?['file']?'contentBytes']}

For the body, we are passing the '‘File content contentBytes’' value from the Manual Trigger. This converts the file into base64 format so that it can be uploaded as part of the request.

Now we have a way to send the Document, we then need a way to wait until it has been processed.

Do Until

Create a Do Until polling loop using Loop Until so that we can wait for the service to process the document and send back a response.

Variable Status is equal to “succeeded”, with a Count of 5 to limit the number of retries, with a 1 hour timeout.

Within the Do Until we need:

Delay: 10 Second Delay

HTTP_1: This HTTP action uses the ‘Operation-Location’ Header from the first HTTP action to check if the service has completed.

URL: outputs('HTTP')?['headers/Operation-Location']
Method: GET
Headers: Ocp-Apim-Subscription-Key: {Azure Key Vault Secret Value}

Set the Status Variable:

Value: body('HTTP_1')?['status']

After the Do Until:

body('HTTP_1')?['analyzeResult']
Now we have a response from the Azure AI Document Intelligence Model, we can start to deal with the JSON that we get back. As Damien Bird mentions, it is helpful to visualise this output using https://codebeautify.org/jsonviewer to give yourself an idea of the output structure and how to reference and extract the data in your Flow.

Extract the Document Information

I have decided that it is important to extract some of the headers from the document as well as the table data in order to give context to the information.

Parse JSON – Paragraphs

As the Layout model gives us the text in the body('HTTP_1')?['analyzeResult/paragraphs'] we can just select that.

Content: body('HTTP_1')?['analyzeResult/paragraphs']
Schema:

{
  "type": "array",
  "items": {
    "type": "object",
    "properties": {
      "content": {
        "type": "string"
      }
    },
    "required": ["content"]
  }
}

As we only need the Content properties, I have shortened the Schema so that only the ['content'] is returned in the output.

Set variable - Metadata Object

Name: Metadata
Value:

{
  "Report_Name": "@{body('Parse_JSON_-_Paragraphs')['content']}",
  "Issue_No": "@{first(split(\r\nbody('Parse_JSON_-_Paragraphs')['content'],'\n'))}",
  "Issue_Date": "@{last(split(\r\nbody('Parse_JSON_-_Paragraphs')['content'],'\n'))}"
}

Here we use the index of the ['content'] property to extract the values that we want. This is where it is helpful to preview the output using https://codebeautify.org/jsonviewer so that you can work out the index beforehand. In my example, the Report Name is the 2nd value [1] and then I have used a Split() expression on the "|" to allow me to capture the first() and last() values either side of the “|” giving us the Issue_No and Issue_Date.

This is just an example, you will want to adjust the structure of your output to meet your own needs. We have captured this as an object here so that we can then align it alongside the table rows later.

Extracting the table rows

Parse JSON

We can now use a Parse JSON to extract all the Table data by selecting the child property /tables.

Content: body('HTTP_1')?['analyzeResult/tables']
Schema:

{
  "type": "array",
  "items": {
    "type": "object",
    "properties": {
      "columnCount": {
        "type": "integer"
      },
      "cells": {
        "type": "array",
        "items": {
          "type": "object",
          "properties": {
            "rowIndex": {
              "type": "integer"
            },
            "columnIndex": {
              "type": "integer"
            },
            "content": {
              "type": "string"
            }
          },
          "required": ["rowIndex", "columnIndex", "content"]
        }
      }
    },
    "required": ["columnCount", "cells"]
  }
}

This is the full schema as we do need a few of the properties.

For each – Table

Input: body('Parse_JSON')
If you have multiple tables to go through, this is useful; if not, you can skip this action.

Set variable ParsedCellsArray

Set the ParsedCellsArray variable with 'cells' - this is the tables cells.
Name: ParsedCellsArray
Value: items('For_each-Table')['cells']

Select

So, if we were to just use....

From: "@variables('ParsedCellsArray')",

Map:

{
 "content" : @item()?['content'],
 "rowIndex" : @item()?['rowIndex'],
 "columnIndex" : @item()?['columnIndex']
}

Then we would get each cell on top of each other in separate objects,

which we would have to somehow ZigZag across or remap, as we want every object in our array to contain a Name and a Price.

To do this we need to integrate a Range into the Select action using:

range(0, length(variables('ParsedCellsArray')))

to generate an index to iterate over the array.

From

In order to get the value of the adjacent column in the Select action, we can use range(startIndex: integer, count: integer) and use length() to use the length of ParsedCellsArray. This allows the Select action to iterate over each element in **ParsedCellsArray **and generate a sequence of numbers.

Map

This number sequence is then used in the Map for the last Property to get the next element “nextContent”.

nextContent:

@{if(less(item(), sub(length(variables('ParsedCellsArray')), 1)), variables('ParsedCellsArray')[add(item(), 1)]?['content'], null)}

This expression uses the If() function to check if the current index item() is less than the last index of the array sub(length(variables('ParsedCellsArray')), 1)).

If true, it retrieves the content of the next element variables('ParsedCellsArray')[add(item(), 1)]?['content']).
If false (i.e., the current element is the last one), it returns null.

So to put this all together we get:

From: range(0, length(variables('ParsedCellsArray')))
Map:

{
  "content": "@{variables('ParsedCellsArray')[item()]?['content']}",
  "rowIndex": "@{variables('ParsedCellsArray')[item()]?['rowIndex']}",
  "columnIndex": "@{variables('ParsedCellsArray')[item()]?['columnIndex']}",
  "isColumnHeader": "@{equals(variables('ParsedCellsArray')[item()]?['kind'], 'columnHeader')}",
  "Index": "@{item()}",
  "nextContent": "@{if(less(item(), sub(length(variables('ParsedCellsArray')), 1)), variables('ParsedCellsArray')[add(item(), 1)]?['content'], null)}"
}

The outcome of this starts to line up for us, but still needs Filtering... For example we only need the first Column and we can ignore the column headers.

Filter Array

We can use Filter Array to filter the returned data. This is especially useful if the tables contain some data that we don’t want, such as comments or notes. Rather than using a single, basic filter, we need to switch to advanced mode 🤓 to combine the multiple conditions we want.
You may want to use other conditions inside the and() expression to fine-tune your data.

From: body('Select')
Advanced Filter:

@and(
    equals(item()?['columnIndex'],0), //Only use the first column
    equals(item()?['isColumnHeader'], false), //Ignores the Column Header
    not(equals(item()?['content'], '')), //No Blanks
    not(equals(item()?['nextContent'], '')), //No Blanks
)

Select – Filtered Table

We can merge in some of the metadata extracted from the Document, which we can now align in the context of each record in the array. The first three in my example are from the Metadata Object created earlier. You can also use this opportunity to further cleanse the values returned using string expressions if you need.

From: body('Filter_Array')
Map:

{
  "Report_Name": @{variables('Metadata')?['Report_Name']},
  "Issue_No": @{variables('Metadata')?['Issue_No']},
  "Issue_Date": @{variables('Metadata')?['Issue_Date']},
  "Product": item()?['content'],
  "Price": item()?['nextContent']
}

Set variable - Filtered TransformedArray

Value: body('Select_-_Filtered_Table')
We will capture the table data into an array so that we can use it elsewhere. Here you can do whatever you want with your array. If you want to use an Add a new row Dataverse action, you can map each array property in the action for example.

Conclusion

Thank you for reading this post. Hopefully, this is useful to anyone who needs to use this particular Azure AI Document Intelligence Model to process their Documents and place the data into a more organised format. Please leave feedback in the Comments if you found this useful, or if there are any improvements you could recommend. Please experiment with it too, I'd love to hear some of the outcomes.

Using Mermaid JS to generate a diagram from Power Automate

Matthew Collinge — Tue, 20 Feb 2024 19:52:20 +0000

Introduction

This is the "bonus part" of my blog series on
creating a Conversational Approval Process using Power Automate and Microsoft Teams.

I needed a way to produce an easy to read depiction of the steps of a process to users or even a way to debug what happened. After seeing Jon Russell and Mike Gowland's amazing demo of
JustAskIt on a Microsoft 365 & Power Platform Development community call I have been keen to try out Mermaid JS in Power Automate.

What is Mermaid JS?

Mermaid JS is a tool that lets you create diagrams and charts such as flowcharts, sequence diagrams, Gantt charts using simple text commands. It works by converting your text commands into a graphical representation that you can customize and share.
https://mermaid.live/

Example

Using Mermaid JS we easily can convert Markdown like this:



flowchart TD
    A[Christmas] -->|Get money| B(Go shopping)
    B --> C{Let me think}
    C -->|One| D[Laptop]
    C -->|Two| E[iPhone]
    C -->|Three| F[fa:fa-car Car]

into an image like this that lists the steps in an easy to read graphical format:

Building the Flow

Manual Trigger

We start off our Flow with the manual trigger. The first text input is varSteps, this is the steps that we are going to either create or append to our existing Flowchart.
The other text input is the Flowchart, so this is where we could insert an existing Flowchart to our Power Automate Flow.

Alphabets Variable

The first variable to initialise is Alphabets, here we will have a string value of the 26 character alphabet. We will use this to pick out the next alphabetical character if we need to append to an existing Flowchart.

Flowchart Variable

The next variable to initialise is Flowchart, which is a string variable that will capture the Flowchart text input from the manual trigger.

varSteps Variable

Then the next variable to initialise is the varSteps, which is an object variable. It will capture the text input for varSteps from the manual input, but we will wrap this inside JSON() so that it is converted to JSON from a string value.

Condition

We will start the actions with a Condition action. The condition will check whether variable('Flowchart') is equal to Null. If it is Null, then we'll create on from scratch. If it's not, then we can append to it with further Steps.

Creating a new FlowChart

I will first go through the steps that are required in the Yes or True side. In here we are just creating a brand new Flowchart that is created in the format ready for Mermaid JS. Set the variable FlowChart and in the value box we will build our markdown so that it can be read inside the Mermaid JS. We will reference the Object properties from the varSteps to build that.

Define the type using "flowchart TD"
Starting with “A” we will enter variables('varSteps')?['Trigger'] .
Then for “B” we will add the variables('varSteps')?['Condition']
Then ”B-->” for variables('varSteps')?['Outcome']
Lastly we will insert the “C” variables('varSteps')?['Action'].

That should give us all we need to create a new Flowchart.

Appending to an Existing Flowchart

On the No / False side of the Condition Action we will be appending to an existing Flowchart. This is useful if there are multiple child flows that make up part of the full process that you are trying to build into a Flowchart. In my Conversational Approval process, there may only be one run of the Approver's Adaptive card, or there may be a need to the Requestor to respond to an Adaptive Card also. Having the Append option allows us to keep building on top of the initial FlowChart that gets created.

Compose - Last Step

We will start by extracting the alphabetical character for the last step that exists in the Flowchart. For example; if the existing Flowchart is:



flowchart TD
A[RM Request] --> B{Matthew Collinge - receives Approval Card}
B -->|Pending| C[Send back to Matthew Collinge]
Then we want the letter **“C”**

Using a Compose action we can use.


 sub(lastIndexOf(variables('flowchart'), '['), 1), 1)

We will need to use this value to increment to the next letter in the alphabet.

Compose Letter No

We now get the next letter's number by using the next compose action. Referencing the alphabet variable we get the next number (D would be 4).


 outputs('Compose_-_Last_Step'),1)

Compose Approval

The next compose step is actually composing the approval Markdown text, starting with the original Flowchart and then appending the next steps. It will start with the starting letter that we have derived from the previous few actions.



@{variables('FlowChart')}
@{outputs('Compose_-_Last_Step')} --> @{substring(variables('Alphabets'), add(outputs('Compose_-_Letter_No'),2),1)
}@{variables('varSteps')?['Condition']}
@{substring(variables('Alphabets'), add(outputs('Compose_-_Letter_No'),2),1)
} -->@{variables('varSteps')?['Outcome']} @{substring(variables('Alphabets'), add(outputs('Compose_-_Letter_No'),3),1)
}@{variables('varSteps')?['Action']}

The last step on this append side is setting the Flowchart variable with the output from the previous compose action, so that we have a variable with the whole Flowchart ready to convert to Mermaid JS.

Putting it all together

Compose - URL

After the Condition Action we have a single compose action.
This will concatenate a string starting with 'https://mermaid.ink/img/', and here is where we convert the Flowchart variable to base 64 giving us the correct encoding to create a URL.



concat('https://mermaid.ink/img/',base64(variables('FlowChart')))

Respond to App or Flow

All we need to do now, is respond to the App or Flow that triggered this Flow. We will use Text input of Flowchart using the Flowchart variable and a text input of Mermaid URL which includes the URL we have just generated.

Inserting this Child Flow into another Flow to capture the steps

Using the "[child] - Approver Card" as an example from my previous posts. In our Parent Flow we can construct a JSON Object inside of a Compose action.

Compose - Outcome

Capture the outcome of the Adaptive Card



outputs('Post_adaptive_card_and_wait_for_a_response')?['body/submitActionId']

Compose - Action

Convert this outcome using "If(equals" to what the follow on Action will be:



if(equals(outputs('Post_adaptive_card_and_wait_for_a_response')?['body/submitActionId'], 'approve'),'Complete Approval',
if(equals(outputs('Post_adaptive_card_and_wait_for_a_response')?['body/submitActionId'], 'respond'), 'Send back to Requestor', 
if(equals(outputs('Post_adaptive_card_and_wait_for_a_response')?['body/submitActionId'], 'reject'),'Inform Requestor',
if(equals(outputs('Post_adaptive_card_and_wait_for_a_response')?['body/submitActionId'], 'acAssignCard'), 'Assign to other person'))))

Compose - varSteps

The JSON Object will be written in a format that could capture various outcomes and statuses that happen within the flow in . For Example:



{
  "Trigger": "[@{variables('varObject')?['Request_Type']} Request]",
  "Condition": "{@{variables('varObject')?['Approver_Name']} - receives Approval Card}",
  "Outcome": "|@{outputs('Compose_-_Status')}|",
  "Action": "[@{outputs('Compose_-_Outcome')}]"
}

Then we can trigger the Child Flow using 'Compose - varSteps' as a String.



string(outputs('Compose_-_varSteps'))

and then FlowChart. If we're carrying over a previous one held inside our varObject or Null if we're creating a new one.



if(empty(variables('varObject')['FlowChart']), 'null', variables('varObject')['FlowChart'])

Capture the Response

To then capture from our child flow and add it to our varObject.

Conclusion

Thank you for reading this post. Please leave feedback in the Comments if you found this useful, or if there are any improvements you could recommend. Please experiment with it too, I'd love to hear some of the outcomes.

For such a simple Flow, I found it quite difficult to write and explain, so I hope it all makes sense!

Conversational Approval process using Adaptive Cards and JSON objects

Matthew Collinge — Mon, 29 Jan 2024 20:49:16 +0000

Introduction

This is Part 3 of a series of posts where I try to explain how to send Form answers from a Power App into Power Automate and then send Adaptive Cards in Microsoft Teams to allow the Requestor and Approver make incremental changes to the Form answers.

Part 1

Part 2

We're going to start Part 3 by building a second Flow. In this Form Approval Process this Flow will actually be the one that sends an Adaptive Card to our Approver.

Here is an overview of what we're going to do here.

Why don't I use the standard Approvals Actions?

What I'm trying to achieve is a more conversational process for the Approval, where the Requestor and Approver can both edit the data inside Message Cards in Teams until the Approver decides what to do with it.. We'll do this by passing our Object again, plus a few other useful Expressions.

We're going to trigger this from our "[proxy] - Object Flow" we made in an earlier Post: Passing Objects from Power Apps to Power Automate.

The Approver's Flow

As this will be a Child Flow. We'll need a Manual trigger and I'll call this Flow "[child] - Approver Card". We just need one text Input. varObject as this can contain everything we need.

Then, we'll Initialize a Variable that will convert the Text Input to JSON.



json(triggerBody()['text'])

Adaptive Card

Let's add another action - Post adaptive card and wait for a response. We'll need to construct an Adaptive card for this. Again I would recommend you build your full Adaptive Card by previewing it with the Help of The Adaptive Card Designer or the VSCode Microsoft Adaptive Card Previewer

Text Inputs
I will start with the Text value we're getting for the "Name" field in the Form.

We need a type, this will be Input.Text
We can specify the Label above the field as "Name"
For the Value, we can reference the property inside of varObject by inserting "@{variables('varObject')?['Name']}"
Finally, each field in the Adaptive Card needs an id, which we can keep exactly the same as the Object Property name to make it easier to reference in the future.

In my scenario, we already have an input value, so 'Placeholder Text' isn't really needed.



    {
      "type": "Input.Text",
      "label": "Name",
      "value": "@{variables('varObject')?['Name']}",
      "id": "Name"
    },

You can use the example above to build any other Text Inputs.

Toggle
The 'Mortgaged' field is a Boolean field in our Power Apps Form, so we can replicate this with Input.Toggle, similar to the Text Input we need to give an id, label and value.



    {
      "type": "Input.Toggle",
      "title": "Mortgaged",
      "id": "Mortgaged",
      "label": "Mortgaged",
      "value": "@{variables('varObject')?['Mortgaged']}"
    },

Choices
I have previously described how we can have a Choices Dropdown in this Blog Post:
Passing a Choices List from Power Apps to an Adaptive Card - in this example, we can add a Label: "Country"



    {
    "type": "Input.ChoiceSet",
    "label": "Country",
    "choices": @{variables('varObject')?['Countries']},
    "id": "Country",
    "value": "@{variables('varObject')?['Country']}"
    }

For the Rating Field, we know there's only five options, so we might as well enter these manually, giving us this:



{
      "type": "Input.ChoiceSet",
      "choices": [
        {
          "title": "1",
          "value": "1"
        },
        {
          "title": "2",
          "value": "2"
        },
        {
          "title": "3",
          "value": "3"
        },
        {
          "title": "4",
          "value": "4"
        },
                {
          "title": "5",
          "value": "5"
        }
      ],
      "label": "Rating",
      "id": "Rating",
      "value": "@{variables('varObject')?['Rating']}"
    }

Actions
That's the fields done, now we need some actions to respond with. These can be placed at the foot of the Adaptive Card like this and will be where the Approver makes their decision.



  "actions": [
        {
            "type": "Action.Submit",
            "title": "Approve",
            "style": "positive",
            "id": "approve"
        },
        {
            "type": "Action.Submit",
            "title": "Respond",
            "id": "respond"
        },
        {
            "type": "Action.Submit",
            "title": "Reject",
            "style": "destructive",
            "id": "reject"
        },
        {
        "type": "Action.ShowCard",
        "title": "ReAssign",
        "card": {
            "type": "AdaptiveCard",
            "body": [
                {
                "type": "Input.Text",
                "label": "ReAssign to",
                "style": "Email",
                "id": "ReAssign_To",
                "isRequired": true,
                "errorMessage": "Please enter an email address"
                }
            ],
            "actions": [
                {
                "type": "Action.Submit",
                "title": "ReAssign"
                }
            ]
        },
        "id": "acAssignCard"
        }
    ]

Feel free to elaborate on the options you provide, such as comments, or even an option to reassign. We'll use these soon in a Switch Action to decide on the next step. IF you want to use this you'll see that there's an expandable section (https://adaptivecards.io/explorer/Action.ShowCard.html)[Action.ShowCard] when ReAssign is selected, this is to ensure an email address is entered.

The JSON for the Approval Adaptive Card is here.

Union

Now we have an Adaptive card that we can send to an Approver and Wait for a response.

We can now capture any changes that the Approver may have made back into our original varObject.

Start by adding a Compose Action, and in the Compose Action we can enter this Expression



union(variables('varObject'),outputs('Post_adaptive_card_and_wait_for_a_response').body.data)

What the Union expression does is combine the properties from the varObject with the data obtained from the response of the action Post_adaptive_card_and_wait_for_a_response. This is done to update or synchronize the values in the varObject with the field values changed in the Adaptive Card.

Then we just need to feed the output of our Compose expression into Set the varObject.

Switch

We'll add a switch Condition to allow us to decide the path of the Flow from this point using some Switch Cases, which match the the ID of our Actions.



outputs('Post_adaptive_card_and_wait_for_a_response')?['body/submitActionId']

We have the options: approve, respond, reject, ReAssign_To and we can decide what we we want to happen with each of these paths. I'll come to respond in a moment, but I will first give some examples of how you can use the other Switch Cases.

Approve
This would be where you would define what you want to happen if the Adaptive Card is approved; Send an Email, update a SharePoint List Item etc...

Reject
Here you could send Post a Standard Adaptive card or send an email to the Requestor.

ReAssign
A good example for using the setProperty() expression would be if you had a defined Approver_Email in your varObject. You could use a Compose Action with:



setProperty(variables('varObject'), 'Approver_Email', outputs('Approver_adaptive_card')?['body/data/ReAssign_To'])

Then, Set Variable - varObject again with the output of the Compose Action.

Respond
So for the Respond Case, we're going to create another Child Flow, it will be a copy of this Flow where the Requestor can make changes to re-submit to the Approver.

Respond to a PowerApp or flow

To make sure this Child Card let's the Parent Flow it has run, place in a Respond to a PowerApp or flow as the last Action.

[proxy] - Object Flow

Before we move on to the Requestor Card, we need to trigger this Child Flow from the [proxy] - Object Flow by making this the 3rd action in the Flow.

Requestor Card

Now - back to the [child] - Approver Card] Flow, so we can copy it....

We'll call this: [child] - Requestor Card and add it to the same solution as our App and other Flows.

Let's first make a couple of small adjustments to the Post adaptive card and wait for a response Message in this one by changing the first TextBlock with text Property from:

To:



    {
      "type": "TextBlock",
      "size": "Medium",
      "weight": "Bolder",
      "text": "Requestor Request"
    },

Then we just need to swap out the "actions": [] section at the bottom of the Card. The Requestor won't have the same options as the Approver, so we can swap out the whole section for this:



  "actions": [
        {   
        "type": "Action.Submit",
        "title": "Re-Submit 🔁",
        "id": "acSubmit",
        "style": "positive"
      },
      {
        "type": "Action.Submit",
        "title": "Cancel Request ❌",
        "id": "acCancelRequest",
        "style": "destructive"
      }
    ]

And of course, change the Receipient, so that the message goes to our Receipient. The Union action is still here to update any changes which then update our varObject.

Switch

As we've changed the Actions, we'll now need to change the cases to "acSubmit" and "acCancelRequest".

acCancelRequest
Again, I'll leave it up to you what to do with this path; you may want to imform the Approver with another standard Adaptive Card message.

acSubmit
So here's the key feature that allows us to perform this conversation loop between the Approver and Requestor. - We're now going to re-trigger [child] - Approver Card and with that, pass back the varObject as a String again.

Create a 'Run a Child Flow' action and select our [child] - Approver Card Flow we created earlier. For the varObject Text Input enter: string(variables('varObject')) - this converts the varObject into a String again to pass to Approver Flow.

Respond to a PowerApp or flow

Again, to make sure this Child Card let's the Parent Flow it has run, place in a Respond to a PowerApp or flow as the last Action.

JSON for the Requestor Adaptive Card is here

Back to [child] - Approver Card to finish off

All we need to do now is the mirror opposite of this in the [child] - Approver Card Flow. Go back to that flow and underneath the Case - respond create a 'Run a Child Flow' action and select our [child] - Requestor Card

Testing and fine tuning

To prevent any confusing repeat runs of these Flows, I would recommend changing the Retry Policy
on each Run a Child Flow Action to None

So, here are some examples that we should see in Microsoft Teams.

Power App Form

Approver's 1st Message

Requestor's 1st Message

Approver's 2nd Message

Conclusion

Hopefully, that was a helpful guide. I have one final Post to add a little bit extra to this type of Approval process. Please leave any questions or comments below.

Passing a Choices List from Power Apps to an Adaptive Card

Matthew Collinge — Sat, 13 Jan 2024 18:53:31 +0000

Introduction

In a follow-on from my previous post Passing Objects from Power Apps to Power Automate I am now going to cover a method I have found to 'render' our JSON Object in an Adaptive Card that is part of an Approval Process.

Recap

After we have got our Object into Power Automate I mentioned that we can then reference a property by first referencing the name of the Object (varObject) and then the property name variables('varObject')?['____']. These can be used inside of an Adaptive Card to show single text values, but what if we want to have a dynamically generated dropdown (choice) list inside of the Adaptive Card?

ChoiceSet

Adaptive Card - ChoiceSet
The Adaptive Card Input.ChoiceSet needs a Title and a Value for each choice. Title is the user friendly name and Value is the value passed to your app our service.



{
            "type": "Input.ChoiceSet",
            "choices": [
                {
                    "title": "Choice 1",
                    "value": "Choice 1"
                },
                {
                    "title": "Choice 2",
                    "value": "Choice 2"
                }
            ],
            "placeholder": "Placeholder text"
        }

So, we need to prepare our data in that format.

Choice Collection

The method I have been using to do this is done back in Power Apps...
This is where I am already using a Collection inside my OnVisible property of the Form Screen.

This is a very basic collection, but in practice your use case may involve generating this from a Datasource Such as Dataverse or SharePoint Create A Collection (from a datasource) - Matthew Devaney.



Collect(
    colCountry,
    "England",
    "Northern Ireland",
    "Scotland",
    "Wales"
)

This Collection works well for the Country Combobox in our Form.

But, this Collection isn't quite right for a ChoiceSet yet as it only has a "Value" column. So, to sort this out I have written this, which can run before we create the varObject.



ClearCollect(
    colCurrentCountries,//Array of Current countries for passing to Adaptive Card
    ForAll(
        ShowColumns(
            SortByColumns(//Sort Alphabetically
                colCountry,
                "Value",
                SortOrder.Ascending
            ),
            "Value"//We only want this column
        ),
        {
            value: Value,
            title: Value
        }
    )
)

I will break down what is happening here:

First we create a collection called "colCurrentCountries" ClearCollect(colCurrentCountries,
Then we're going to loop through each country in the existing "colCountry" Collection using ForAll() to do the looping. But, we only need the "Value" column (this isn't strictly necessary in my example). Neither is 'SortByColumns()', but it keeps things tidy 🙂.

ForAll(
ShowColumns(
SortByColumns(//Sort Alphabetically
colCountry,
"Value",
SortOrder.Ascending
),
"Value"//We only want this column
),{% raw %}``

The last part to construct is the schema of each record inside this Collection. To do this I'm just repeating the "Value" column for both the Title and Value as I've no need to for these to be different in the Adaptive Card. ` { value: Value, title: Value } `

Adding the Collection to the Object

To add the Collection or Array to the Object we just need to add the line 'Countries: colCurrentCountries' like so:
` Set( varObject, { Name: txt_Name.Text, Address_1: txt_Address_1.Text, Address_2: txt_Address_2.Text, City: txt_City.Text, Country: cbx_Country.Selected.Value, Mortgaged: tgl_Mortgaged.Value, Rating: rtg_Rating.Value, **Countries: colCurrentCountries** } `
So now with everything put together in Power Apps we have this in the OnSelect Property that runs the Flow:
` ClearCollect( colCurrentCountries,//Array of Current countries for passing to Adaptive Card ForAll( ShowColumns( SortByColumns(//Sort Alphabetically colCountry, "Value", SortOrder.Ascending ), "Value"//We only want this column ), { value: Value, title: Value } ) ); Set( varObject, { Name: txt_Name.Text, Address_1: txt_Address_1.Text, Address_2: txt_Address_2.Text, City: txt_City.Text, Country: cbx_Country.Selected.Value, Mortgaged: tgl_Mortgaged.Value, Rating: rtg_Rating.Value, Countries: colCurrentCountries } ); '[proxy]-ObjectFlow'.Run( JSON( varObject ) ) `

Power Automate

To see this in an Adaptive Card in Power Automate for now we need to add an Action: "Post card in a chat or channel" Posting As: Flow Bot and Post In: Chat with Flow Bot. For testing add your email address for our "Approver" and then we can construct the Message.
Yes, this screenshot is from the V2 designer 😫

Adaptive Card

I would recommend you build your full Adaptive Card by previewing it with the Help of (https://adaptivecards.io/designer/)[The Adaptive Card Designer] or the (https://marketplace.visualstudio.com/items?itemName=TeamsDevApp.vscode-adaptive-cards#:~:text=The%20Microsoft%20Adaptive%20Card%20Previewer,%2C%20and%20high%2Dcontrast%20themes.)[VSCode Microsoft Adaptive Card Previewer]

To help us out a little, here is a basic Adaptive Card with a Single ChoiceSet Input, with it waiting for you to type in some choices:
` { "type": "AdaptiveCard", "body": [ { "type": "Input.ChoiceSet", "choices": [ { "title": "Choice 1", "value": "Choice 1" }, { "title": "Choice 2", "value": "Choice 2" } ], "placeholder": "Placeholder text" } ], "$schema": "http://adaptivecards.io/schemas/adaptive-card.json", "version": "1.5" } `
Now to make this work with the Data passed over from our varObject Variable in Power Automate.

First Remove the "choices" array - the [ to the ] and replace this with a reference to our Countries list in varObject, like this: ` "choices": @{variables('varObject')?['Countries']}, `
Then we can add the Country value that was selected in the Power App Form, by adding this (The ? operator is only needed for handling null values.): ` "value": "@{variables('varObject')?['Country']}" `
Then we need to add an ID for the field. ` "id": "Country", `
And for reasons I don't understand we need to change the version from 1.5 to 1.4 to work with Teams version 1.7?!? "version": "1.4" } `

Power Automate

We then have an Adaptive Card that we can paste in to the Message in Power Automate.
` { "type": "AdaptiveCard", "body": [ { "type": "Input.ChoiceSet", "choices": @{variables('varObject')?['Countries']}, "placeholder": "Placeholder text", "id": "Country", "value": "@{variables('varObject')?['Country']}" } ], "$schema": "http://adaptivecards.io/schemas/adaptive-card.json", "version": "1.4" } `

Save the Flow and Refresh the Flow in the Power App too. If you get
"[proxy]-ObjectFlow.Run failed: { "code":
"InvokerConnectionOverrideFailed", "message": "Failed to parse invoker connections from trigger 'manual' outputs. Exception: Could not find any valid connection for connection reference name
'shared_teams' in APIM header." }" Just Save and Re-Open the Power App 🙂.

Teams

Submit the Form in the Power Apps and Teams should now send you a message with the Selected Country and when you select the Choices, give you a selection of other Countries.

Conclusion

Hopefully this is a helpful tip and I will elaborate on how we can get more in to Adaptive Cards soon.
Please leave any questions or comments below.

Passing Objects from Power Apps to Power Automate

Matthew Collinge — Wed, 10 Jan 2024 16:14:33 +0000

Introduction

This is the first part, in what I hope will be a series of posts describing how to trigger an advanced approval process. Starting with Power Apps capturing data in a Form and then passing that as a JSON Object or "Record".

We are passing a JavaScript object as a JSON string from Power Automate to Power Automate. Power Automate has many actions and expressions available to allow us to reference and manipulate the Object once it has been received. More on that later.

I have chosen to implement a JSON Object as it is a common method to store and exchange data. The benefits are that:

Power Apps code is simpler with all the fields being referenced the once when creating the Object.
Power Automate is kept more efficient than initiating a Variable for each field value passed

The Power App

Firstly we need a Power App where we will capture the values in a Form filled in by a user. The Form I have created is just a bunch of Text Input controls. We are going to capture these as a Record into an Object Variable using the Button Control OnSelect property.

To begin this Function we need to start with this in in the Button's OnSelect Property:



Set(
    varObject,
    {

Then we can start building the object inside the record (curly brackets). each Field needs a unique name with no spaces. Make sure to make sure to reference the '.Text' Property of the Text Input control, it is quite strict on that.



Set(
    varObject,
    {
        Name: txt_Name.Text,
        Address_1: txt_Address_1.Text,
        Address_2: txt_Address_2.Text,
        City: txt_City.Text,
        Country: cbx_Country.Selected.Value,
        Mortgaged: tgl_Mortgaged.Value,
        Rating: rtg_Rating.Value
    }
);

Power Automate

We can now create a Flow ready to receive this Object. We can do this from inside Power Apps or Externally, just make sure that they are both inside the same Solution.

We'll call this [proxy] - Object Flow and add a Text Input to the Trigger. As there's no JSON Input option available, we will have to convert the record variable into a JSON text string inside the Power App to allow us to capture it here.

The only action we need so far is Initialize variable this is where varObject can be converted back into a JSON Object like this by placing it inside 'json()'.



json(triggerBody()?['text'])

Power App

Ok, so back to the Power App. To make sure to Refresh the Flow from the Power Automate section of Power Apps, this will just ensure that the Flow is up to date and ready to receive the Text Input.

Back in the Button's OnSelect Property and underneath where we set the Variable varObject, we can now send it to the Flow, but inside a 'JSON()'.



'[proxy]-ObjectFlow'.Run(
    JSON(
        varObject
    )
)

Testing

Now enter some test values in the Form and click on the Button, which should now trigger the Flow and pass the Object over from the App for us.

Now we have an Object ready inside our Flow that we can use to extract data from.

Each time we need a value we can reference it by using ```

variables('varObject')?['____']

The ? operator is only needed for handling null values.

For example:

variables('varObject')?['Name']


![Power Automate - Name Property](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dgplwz8ylh9vye0zggux.jpg)

## Summary
Now we have a way to pass a record between Power Apps and Power Automate there are a few useful tricks that I will add soon. We will cover how we can bring over the alternative values for next....

Send Multiple files as email attachments from a SharePoint Document Library Folder using Power Automate 📂➡️📨

Matthew Collinge — Sat, 12 Aug 2023 14:02:47 +0000

Have you ever needed to email multiple specific files as attachments from a SharePoint Document Library? There is documentation on how to send ALL the files in a given folder (https://learn.microsoft.com/en-us/microsoft-365/community/power-automate-send-sharepoint-files-as-attachments), but here we'd like to have some control over which files we want to email as attachments without having download the files beforehand.

User interaction

The previously mentioned documentation provides us with a good Power Automate solution to build from, but how do we include the interaction that allows the user to see the available files to pick from and to select just the files they'd like to email? Well, that's where Adaptive Cards for Teams comes in. Adaptive Cards let us build an interactive and dynamic structure for a user interface for the actions of a Power Automate Flow.

NOTE: I'd love to develop a way to open up a new email tab with Outlook for the Web to do this, or even a PWA that's opens up in the SharePoint Sidebar... maybe one day.

The Power Automate process in summary

Flow is triggered by the "For a selected file" trigger.
Flow will locate the parent folder
Flow will collect all the files
Flow will add the files to an attachment names string
An Adaptive Card will be sent to the user that Triggered the Flow
The Adaptive Card will replicate the layout of an email, with Sent To, Subject, Body and a pick list using the attachments array
The user fills out the fields, selects the files and clicks send.
For each of the selected files the Flow will retrieve the file content and Append to an Array.
Email is sent with selected Attachments

How the flow works

The user can trigger the Flow by selecting a single file in a SharePoint Document Library and clicking on Automate > Send Attachments

They are then then asked to \"Run Flow\"
An Adaptive Card is sent to them via the Microsoft Teams Flow Bot where the user can input the Receipient Email (with Regex Validation), Subject, Body and a Dynamic Picklist of all the other files in the chosen folder. 😃
Once the user clicks \"Send\" the flow will verify that they actually selected any attachments and if so, send the email with the selected attachments via email from the user's mailbox.

How it is Built

For a selected item

To start building the Flow for a given SharePoint Document Library, you need to start from that Library and then select Integrate > Power Automate > Create a Flow then we can select a Template "Request approval in Microsoft Teams when a SharePoint item is created". We're going to remove all the actions from this apart from the trigger.
Doing this creates the relationship between the Document Library and the 'For a Selected Item' Flow Trigger. For some reason, if you don't create the Flow this way, the Document Library refuses to show the Flow under "Automate", which is very frustrating. 😩

Variables to Initialise

varUserEmail
varFolderPath
varAttachmentNames
varAttachmentNamesArray
varSelectedArray
BodyAsString
varCardObject

varSiteLibrary is an Object where I have set up the SharePoint Site and Document Library

{
    "Site": "<SharePoint Site>",
    "Library": "<Document Library>"
}

We can retreive these in our various actions just by referencing variables('varSiteLibrary')['Site'] or variables('varSiteLibrary')['Site']

Get parent - the folder's path

Here's the expression used to return the Folder Path without the '/' at the end, so we can use it in the next action.



substring(
    outputs('Get_file_properties_-_Folder')?['body/{Path}']
    ,0
    ,sub(
        length(outputs('Get_file_properties_-_Folder')?['body/{Path}']))
    ,1
)

Get an Array of all the available files in this folder

Using \"Get files (properties only)\" and the Filter Query: FSObjType eq 0 to return just the files and not folders.

Now we use \"Apply to Each\" for each value (file) found.

Each value is run through the "Get File Content" action to then append it's File name with extension and a unique Identifier.
Adaptive cards need to have a unique ID for each property, so I have used the Identifier property.

Append to String varAttachmentNames schema:



{
    "type": "Input.Toggle",
    "title": "@{items('Apply_to_each')?['{FilenameWithExtension}']}",
    "id": "Item_@{items('Apply_to_each')?['{Identifier}']}",
},

In Parallel Append to String varAttachmentNamesArray schema (we will need this later to get the File Names):



{
  "FileName": "@{items('Apply_to_each')?['{FilenameWithExtension}']}",
  "Identifier": "@{items('Apply_to_each')?['{Identifier}']}"
}

Post adaptive card and wait for a response

The full build of the Adaptive card is here, but the key bit here is to include the varAttachments as a dynamic repeatable item within the Card. To do this we can place varAttachments inside of "items": [ ] , so that each filename is shown with it's own Input Toggle.

We replace this with our varAttachmentNames String



{
    "type": "Container",
    "items": [
               @{variables('varAttachmentNames')}
            ]
}

Parse the Adaptive Card response

Now the fun part, doing something with all the data we've gathered.
To do this we need to Parse the JSON from the \"Data\" Property of the Adaptive Card Body.

The Card body used here is in the form of this expression. We will set this as varCardObject



body('Post_adaptive_card_and_wait_for_a_response')['data']

We will set this as \"varCardObject\" and use is in the Content for the Parse JSON action.

JSON Schema used



{
    "type": "object",
    "properties": {
        "RecEmail": {
            "type": "string"
        },
        "Subject": {
            "type": "string"
        },
        "EmailBody": {
            "type": "string"
        },
        "Item_": {
            "type": "string"
        }
    }
}

Parsing this Data Object will allow us to extract the RecEmail, Subject and EmailBody for use in the Send Email Action later.

Extracting the selected files

A challenging part of this process is extracting the list of just the Selected Files from the Adaptive Card. We only want to get the ones that have the Toggle set to \"True\".

To do this we'll need to convert the varCardObject in to a String so that we can split it using an expression.

Set the \"BodyAsString\" variable the the \"Parse JSON Body\".

Then bring that into a Compose action to use a Split expression.

Compose - Split

We will split the string at each _item



Split(variables('BodyAsString'),'Item_')

Apply to Each Split Item

In order to return just the selected (True) files we need to do an Apply to Each on the Output of Compose - Split

Condition

CurrentItem Contains:



":"True"

Append to array variable - varSelectedArray

With each Current Item that matches the Condition we will Append it to our varSelectedArray and apply an expression to just give us just the File Identifier that we need. To do that we will use the expression:



replace(
    replace(
        items('Apply_to_each_-_Split_Item'),
        '":"True","',''
        )
        ,'":"True"}',''
    )

Failover - No Attachments have been selected

In case the user forgets to actually select any attachments we can add a failover that checks the number of attachments



length(variables('varSelectedArray'))

is or greater than 1. If the Condition is not matched, then we will send the User a Teams message \"No attachments were selected, please try sending this again\". Then the Flow will be Terminated

Get the File Content and prepare attachments for each of the files

Apply to Each - Selected File

To give us an Array of Attachments that Outlook can send in an email we need to find two things for each selected file: The File Name with Extension and the ContentBytes.

To acheive this we can run two actions in Parallel for each item from the varSelectedArray which has a list of file identifiers.

Filter array - for Identifier

From: varAttachmentNamesArray
item().Identifier is equal to Current Item

Advanced mode:



@equals(item().Identifier, items('Apply_to_each_-_Selected_File'))

Using the expression below this can return us the matching FileName for the File Identifier



first(body('Filter_array_-_for_Identifier'))?['FileName']

Get file content - Each File

Search the Site Address for the Current Item (file identifier) to return the File Content.



@{body('Get_file_content_-_Each_File')}

Append to array variable - AttachmentsArray

So, to join these two together we Append each of these to the AttachmentsArray variable like this:



 json

{

  "Name": @{first(body('Filter_array_-for_Identifier'))?['FileName']},

  "ContentBytes": @{body('Get_file_content-_Each_File')}

}

Send an email

We can now use the data we've gathered above to compose and send an email.

An important point to mention is that to add the AttchmentsArray we need to select the \"Switch to input entire array\" toggle so that we can insert the combined Name and Content that we put together in AttachmentsArray

DEV Community: Matthew Collinge

Cloning Power Automate Flows using a Power Automate Flow

Introduction

The SPO P&P System at a Glance

The Three Flows

The Site Registry Table

Custom Columns

Why Library GUID, Not Library Name?

The Approval Trigger Template

The clientdata Column — the Key to Cloning

The Provision Site Flow — Step by Step

Step 1 — Initialise Variable (varClientData)

Step 2 — Get Site Registry Row (Get a row by ID — Site Registry from Trigger)

Step 3 — Retrieve the Template Flow Definition (List rows — Processes)

Step 4 — Inject Real Values (Set variable — varClientData)

Step 5 — Create the New Flow (Add a new row — Processes)

Step 6 — Add to Solution (Perform an unbound action — Add to Solution)

Step 7 — Write the Flow ID Back to Site Registry (Update a row — Site Registry)

Step 8 — Notify via Teams Adaptive Card (Post card — Turn Flow On)

Error Handling — Scope Catch

The Child Approval Flow

How It All Fits Together

Key Design Decisions

Summary

Generate a Dataverse ERD for Azure Dev Ops Wiki

✅ Why This Matters

Step 1: Clone the Repository in VS Code

Step 2: Locate a Relationships File

Step 3: Use AI to Generate the ERD

Step 4: Copy the Mermaid Diagram

Step 5: Publish to ADO Wiki

✅ Pro Tip

Possible Next Steps

Extract table data from Documents using Azure AI Document Intelligence

Extract table data from Documents Using Azure AI Document Intelligence Layout Model with Power Automate

Choosing a Model

My Example

Passing the Document to Azure AI Document Intelligence

Azure Services

Flow Structure

Manual Trigger

Azure Key Vault Get secret

HTTP Request

Do Until

Variable Status is equal to “succeeded”, with a Count of 5 to limit the number of retries, with a 1 hour timeout.

Delay: 10 Second Delay

Set the Status Variable:

After the Do Until:

Extract the Document Information

Parse JSON – Paragraphs

Set variable - Metadata Object

Extracting the table rows

Parse JSON

For each – Table

Set variable ParsedCellsArray

Select

From: "@variables('ParsedCellsArray')",

Map:

From

Map

Filter Array

Select – Filtered Table

Set variable - Filtered TransformedArray

Conclusion

Using Mermaid JS to generate a diagram from Power Automate

Introduction

What is Mermaid JS?

Example

Building the Flow

Manual Trigger

Alphabets Variable

Flowchart Variable

varSteps Variable

Condition

Creating a new FlowChart

Appending to an Existing Flowchart

Compose - Last Step

Compose Letter No

Compose Approval

Putting it all together

The `clientdata` Column — the Key to Cloning

Step 1 — Initialise Variable (`varClientData`)

Step 2 — Get Site Registry Row (`Get a row by ID — Site Registry from Trigger`)

Step 3 — Retrieve the Template Flow Definition (`List rows — Processes`)

Step 4 — Inject Real Values (`Set variable — varClientData`)

Step 5 — Create the New Flow (`Add a new row — Processes`)

Step 6 — Add to Solution (`Perform an unbound action — Add to Solution`)

Step 7 — Write the Flow ID Back to Site Registry (`Update a row — Site Registry`)

Step 8 — Notify via Teams Adaptive Card (`Post card — Turn Flow On`)