Document Generation Instant Cloud Flow

Power Apps + Power Automate + Word Template

Composed a Power Automate flow contained in a broader solution I developed for a specialized Foster Care unit tasked with identifying family connections for children in care. The process required an Instant Cloud Flow users can trigger on select of a button in a Canvas app. Power Apps passes the primary key value of the parent record to Power Automate which then uses an OData filter expression to identify all related records. Next, Power Automate fills a Word Template using repeating section controls with said related data, creates a file, and emails the file link to the individual that triggered the flow for them to upload to the final document repository. The Power Automate flow then deletes the file from a temporary SharePoint document library after eight hours. The final outcome is a snapshot of the entire case containing all related records used for case documentation.

Power Automate Flow Overview

My Role

I served as the sole designer and developer for this project, responsible for the full lifecycle from requirements analysis to deployment. I also designed, developed, deployed, and managed the application in which this instant cloud flow is contained.

Process

Users click a button in an app that triggers a Power Automate flow.

The Power Automate flow identifies the selected record and composes all related records

Gets the selected case number in the ASK app.

Lists the corresponding Dataverse row to the selected case

Lists rows for a related table that contains the parent information of the case (i.e. the parents of the children referred to the program).

Related records are composed into an Array object for later import into a repeating control in the Word Template.

{
    "inputs": {
        "from": "@outputs('List_rows_from_selected_environment_(Get_Child_Records_from_<redacted>_Table)')?['body/value']",
        "select": {
            "<redacted> Name": "@item()?['<redacted>name']",
            "<redacted> Relationship": "@item()?['<redacted>relationship']",
            "<redacted> DOB": "@item()?['<redacted>dateofbirth']",
            "<redacted> SSN": "@item()?['<redacted>ssn']",
            "<redacted> Notes": "@item()?['<redacted>notes']",
            "<redacted> ID": "@item()?['<redacted>id']"
        }
    },
    "metadata": {
        "operationMetadataId": "bc5efa72-0643-4fbe-bd63-bc873242ab59"
    }
}

This is the code view of the Compose Array step.

This pattern is repeated for all child tables:

List Rows from the related table based on an Odata Filter that compares the lookup value of the related table to the primary key value of the parent record.
Compose an Array Object based on the outputs from the prior step that will be used to populate a repeating control in the Word template.

Power Automate populates the parent and child records (via composed Array objects) into a Word template

Word template using repeating controls for related records

A link to a Word file stored in a Teams SharePoint site is emailed to the user who downloads it and uploads to the appropriate repository (OnBase).
Power Automate deletes the file from SharePoint eight hours after it’s triggered.

Challenges

One challenge is what to do when there are two referrals associated with the same overall case record. This scenario was commonplace because there could be multiple referrals on the same case.

SOLUTION: Use an OData expression to filter for the correct case and ensure the case did NOT have a closure date at the ‘Get Dataverse Rows’ step. This filtered the data source with the case actively being worked on.

<Overall Case ID> eq '@{triggerBody()['text']}'  and  <closuredate> eq null

The other problem I encountered was handling connection issues. Sometimes, flows would fail silently (about 1 percent of the time). I believe this connection issue is related to OAuth token expirations caused by password or MFA updates which are controlled at the Organizational level.
The solution was to have users update their connections by following these steps: Users must sign into Power Automate > Data > Connections and refresh their connections after account changes that impact OAuth tokens. Therefore, the flow has a 99% success rate with the 1% being a result of transient connection issues.

Flow Success Rate in the Past 28 days: