Additional menu

Tachytelic.net

Microsoft Power Automate (Microsoft Flow)

All the content that I have written related to Microsoft Power Automate (aka Microsoft Flow). Please click here to learn more about what Power Automate is, and how it can help your business.

I hope you find something that is useful, if you need further assistance please don't hesitate to get in touch!

Power Automate: Get a single record from CDS List Records action

by 5 Comments

You can easily retrieve the unique identifier of a record in CDS within flow using the List Records action. But this approach will create an “Apply to each” loop, even if there is only one possible result.

Image showing unwanted "Apply to Each" loop when querying a single record from Common Data Service.

I needed to get the accountid from the Account entity to create a new record in a related table. My list records action looks like this:

Image showing "List Records" action in Microsoft Power Automate.

Note that “Top Count” is set to 1.

The next step of the flow where the new record is created. You can stop the Apply to Each loop being created by using the “first” function. Example code:

first(body('List_records')?['value'])?['accountid']
Image showing how to create a new record in Common Data Service using only a single result from a "List Records" action in Microsoft Power Automate.
Image of Microsoft Flow Expression and the use of the "First" function which can give you the first result from a record set.

Then you can continue to your next action without having to deal with a for each loop. It makes the flow neater and easier to comprehend.

Create PDF Invoice using Microsoft Power Automate with JSON

by 2 Comments

This post explains how you can create a PDF document using Microsoft Power Automate and a Microsoft Word Template. In this example the flow is being triggered by a HTTP request containing a JSON payload to generate an invoice. The five step flow follows this sequence:

  1. Receive data from a HTTP request containing a JSON payload.
  2. Populate a Microsoft Word Invoice Template.
  3. Create File (Word Document).
  4. Convert previously created Word document to PDF.
  5. Save the PDF File.
Image of a Microsoft Flow that generates a PDF Invoice from JSON data.

Here is the PDF produced by Power Automate (click here to see full PDF):

Image preview of a PDF produced by Microsoft Flow (Power Automate) from a JSON file

You can view the full JSON payload on Pastebin.

Create a Word Template for the PDF Invoice

The first thing you need to do, is build your word template. I used the Simple Sales Invoice in the Word template gallery as my starting point and modified the appearance to my liking.

Next, add plain text control controls to the document for each of the fields in the JSON file that will appear only once (i.e. everything that is not part of an Invoice line). Do the following:

  • Enable the Developer Tab in Word.
  • Put the cursor into a table cell where your data needs to go.
  • Enter some sample data into that cell.
  • Highlight the sample data and click on “Plain Text Context Control” in the developer toolbar.
  • Click on Properties in the control toolbar ribbon.
  • Give the content control a meaningful name(preferably match it to your JSON field names).
Animated Gif showing how to add plain text content controls to a Microsoft Word Template.

Add Content Controls for repeating data

For the repeating data, follow the same procedure as above for each column of data in your table, but this time make sure the field names match the JSON field names. Then specify the row as repeating content like this:

Animated Gif showing how to add repeating data content to a Word Table.
  1. Select the entire Invoice line item row. I find it is easiest to go to the Layout tab and click “Select” and then choose “Select Row”.
  2. Go back to the developer tab and click on the repeating section content control.
  3. Name the repeating section with a sensible name.

In this example, the JSON for a single invoice line is like this:

{
	"PartNo": "813/00419",
	"Description": "GASKET-EXHAUST FLANGE",
	"ToInvoice": "1",
	"SellingPrice": "0.50",
	"DiscountPercentage": "0.00",
	"NetLineValue": "0.50"
}

Set Repeating Column Headers

My example data has quite a lot of invoice lines which do not all fit on a single page. So it is important to make sure that “Repeat Header Rows” is enabled. This will ensure the subsequent pages have column headings.

Select the top row of your Invoice lines table and go to the Layout tab and select “Repeat Header Rows”.

Image of "Repeat Header Rows" button in Microsoft Word.

Now that you have finished editing the Word document. Save it somewhere you will be able to access it from Flow, such as OneDrive or SharePoint.

Create the Flow

The flow is really easy to build and in this example there are only five steps:

  • Step 1 – Create a new flow
    • Choose “Instant – from blank”
    • Trigger the flow “When a HTTP request is received”
    • When the flow editor appears click the “Generate from Sample” button within the HTTP Request flow part and paste in a sample of your JSON data and then flow will automatically generate the JSON schema which you will be able to use in the rest of the flow.
  • Step 2 – Add the “Populate a Microsoft Word Template” action.
    • Select the previously saved template file.
    • Flow will read the document and present a list of fields to be added to the document.
    • Go through each field and add them to the document, apart from the repeating fields.
    • When you reach the line items block it will first appear like this:
    • Click on the little box highlighted in red which switches the input to accept an entire array. Then you can select the array from your JSON file.
  • Step 3 – Save the File
    • Now your Word document has been populated you can save it somewhere that is appropriate for your environment (I used “Create file – OneDrive for Business).
    • For the Filename I used “INV_” + Invoice Number from JSON File + “.docx”:
  • Step 4 – Convert the Word Invoice to PDF using the path from the previous step.
  • Step 5 – Save the new PDF. I named the file as per the above step but changed the extension to .PDF

Executing the Flow

This flow is triggered via a HTTP request with a JSON payload. In this example it was fired using curl from Linux like this:

#!/bin/bash
host="https://prod-84.westeurope.logic.azure.com:443/workflows/..."
curl -v -d @INV_00102707.json -H 'Content-Type: application/json' $host

But it could just as easily be executed from PowerShell like this:

$FlowUri = "https://prod-84.westeurope.logic.azure.com:443/workflows/..."
$PostBody = Get-Content ".\INV_00102707.json"
Invoke-RestMethod -Method Post -Uri $FlowUri -Body $PostBody

Conclusion

Microsoft Power Automate can provide a good solution for automatically generating invoices or other business documents. In it’s live implementation this flow also:

  • Saves the document to a custom SharePoint list that holds metadata about the document and makes it easy to find documents again.
  • Uses a Sendgrid dynamic template to email the invoice to the recipient and track delivery.

This flow was put into production use and is actually used with a legacy Kerridge system to generate PDF output, functionality that it did not have previously.