Generating invoice attachments with Power Automate

Microsoft Word Online (Business) connector is the hidden gem in the Power Platform. It makes possible to generate nice-looking documents with dynamic content.

Let’s go through how to utilize this cool connector in practise.

Example

We like to automatically generate invoice attachments for our customers. Once a month and using our own heavily branded invoice template. The attachment includes information about the hours we have spent with customer’s projects.

Data model

All the data is stored on Common Data Service (with hour tracking Power App) and entities related to invoicing looks like this (simplified version).

• Every Account has 0-n WorkOrder having 0-n HourEntries
• Each HourEntry is done by User

But first we have to build template for the invoice.

Word template

We start with plain Word document having company header and footer in place. The actual content will be

• The customer name in the header
• The total amount of hours shown before hour report
• Invoiced hours are shown in the table (work order, working day, working hours, description and employee)

Next we change into design mode in Word and add placeholders for all the dynamic content we will populate from the Flow.

Our final invoice template looks like this.

Generating attachments with Flow

Our Flow is started manually. First we build path for storing attachments. The path is in year/month format

Then we get all active Accounts.

For each account we list all work orders they are related to.

But how we get the sum of all the hours related to single work order (without counting them inside the Flow by ourselves)?

With aggregation transformation feature.

Next we get actual hour entry records related to work order.

We like to include employee display name on the attachment. Unfortunately hour entry record contains only guid for the related user.

Do we need to make new query just to get user display name?

No. We can expand the query so that includes display name of related user.

Format hour entry records for Word template

Our Word template needs hour entries in the following format.

[
  {
    "Hours": "1",
    "Description": "Documentation",
    "Day": "1.1.2020",
    "Work order": "Work order 1",
    "User": "Timo Pertilä"
  },
  {
    "Hours": "3",
    "Description": "Implementation work",
    "Day": "1.1.2020",
    "Work order": "Work order 1",
    "User": "Timo Pertilä"
  }
]

Just use Select action for formatting hour entries into proper format.

Populate word template

Finally we have everything to populate our Word template.

After populating the template we have to save the document somewhere. A SharePoint document library is always a good choice.

We like to have attachments also in PDF format. Word Online has action also for that.

Outcome

After running the flow we have all invoice attachment nicely in new folder.

And sample attachment looks like this.

Flow with all the actions looks like this.

This is a short version of the post originally published in my personal blog (in Finnish). You can find a little bit more details from there if needed.

Show me more apps running on Power Platform!

Are you interested in seeing examples of the kinds of apps we’ve built for our customers & ourselves by using Power Apps, Power Automate and Power BI? Check out the Forward Forever App Gallery for ideas and inspiration on what your organization could also achieve with low-code apps.

Browse gallery