Invoice Automation using the Power Platform

stpuceli · ‎03-29-2021

Invoice Automation using the Power Platform

Invoice processing is something that every company does, but many are still doing it manually and is very labor intensive. Not anymore! I'll show you how to use the new "Process and save information from invoices" flow in Power Automate to automatically capture invoice information, save it to a SharePoint document library. We’ll then use Cognitive Services to extract the invoice line-item information and save it to a SharePoint List. Finally, Power Apps will allow you to review and complete the processing so it can be integrated with the accounting LOB application.

Process Overview

Invoices come in several different ways. Ideally, they would be electronic, if not most scanners/copiers have integration into SharePoint that makes the capture process easy to accomplish. Here is a typical process overview for most organizations.

SharePoint Schema

To get things started, we need to configure out content types and SharePoint information architecture to accommodate capturing the invoice “header” information, typically located at the top that has non-repeating items such as vendor name, invoice number, date due etc.

We also need a “line items” list to hold each itemized item that has been purchased. A typical schema should resemble this:

A few things to keep in mind:

There should be a content type created for each list, we’ll need this later when configuring the search experience. It’s best to do this in the SharePoint Admin Center -> Content Services -> Content Type Gallery and then deploy it to the hub the library/list is in.
The relationship between Invoices and Line Items is a 1:many relationship where the InvoiceID column is the SharePoint ID in the Invoices list.

Automate metadata extraction using Power Automate

Now that we have the plumbing in place to receive the information, time to configure the tools for the metadata extraction.

Power Automate has a new activity, “Process and save information from invoices” that we’ll use to automatically extract the invoice header information. This couldn’t be much easier! I’ve set a few variables that we’ll use later in the flow but simply pass the file contents and it will scan and return values that can be used to update the Invoices metadata.

Once OCRed, the results are automatically available to be mapped to the document library.

Line-Item Extraction using Cognitive Services

Getting the line-item information requires a little more work but Azure Cognitive Services does the hard part for us.

Configure Form Recognizer in Azure

The ability to OCR a document and have AI determine if there is a table in the document is accomplished using the Form Recognizer service in Azure. You’ll need to configure this so we can use it in Power Automate.

Create a new or use an existing resource group in Azure. (portal.azure.com)
Using this resource group add the Form Recognizer service

3. Once created there are a few things you’ll need to use the service:

a. Get the Endpoint – we’ll use this in Power Automate to pass our invoice to the service.

b. Get at least 1 of the Keys. We’ll also need this to call the service.

Send the invoice to Cognitive Services

Extracting the line-item information (table) from the document requires 2 calls to the Forms Recognition service:

The first call will take the document and perform an analysis of the contents. Within Power Automate, make an HTTP call to the endpoint we created in Azure. This will return a Request ID that we’ll need for the 2nd call.

NOTE: The HTTP header must contain the Ocp-Apim-Subscription-Key key and the value is the key from the Forms Recognizer service we created in Azure. The URI is the Endpoint that was created plus the method to analyze the document.

I put a delay activity of 10 seconds to give Forms Recognition time to process the document, but the 2nd HTTP call is like the first, except it will return a JSON response of everything it OCRed.

NOTE: The key needs to be in the HTTP Header again. The URI is like the 1st call but contains the URI is decorated with the RequestID output from the 1st call.

Parse the JSON body

Using Visual Studio Code and a JSON plugin, you can analyze the JSON output. Key areas to focus on are:

The “tables” node. This will include all the rows and columns the Forms Recognizer service found.
Rows and Columns in this table node have indexers that tell you where in the table the information is located.
Pixel coordinates come in handy to know exactly where in the document the information is.
The “text” node is what were after. This is the text that was OCRed and returned.

Row 1, Column 1 (0 based array) in the table…IE invoice lines.

Here is a tool that you can use to test your documents and see the resulting JSON file contains. You can then use your favorite parsing tool to get at any information on the document.
To parse the JSON results I opted to write an Azure function to do all the heavy lifting. The function iterates through all the rows and columns and produces another JSON file with just the column header and value. The solution is available on GitHub.

Updating the Line-Item list

Taking the JSON response from the Azure function we can now iterate through the rows updating the SharePoint list with the results. Notice a couple things:

The Forms Recognizer will return all table rows, even if they are blank. Make sure to do a check for blank values.
If you are using non-text fields, IE number or currency, then make sure to cast it correctly in the JSON response. I kept things simple and everything is a string.

PowerApps for Accounting Review

Phew…the hard part is done! The user interface that accounting will use is done in PowerApps.

Form features include:

A list of the invoices that mee the Invoice Status value is displayed on the left.
When selected the invoice is displayed in an image control.
The invoice properties are displayed on the right so they can be validated.
If an approver has not been selected, it can be and then routed for approval.

Line items can be viewed for the associated invoice.
If accounting requires a GL code, this can be input and then saved to a collection.
Once all line items have been processed, a batch can be created for the LOB system and payment.

Searching for invoices

Users can review the status of any invoice input into SharePoint using the search.

Highly recommended to use the PnP Modern Search web part
Create a new result source that points to the content type used to create the Invoice library.

A nice feature of the PnP Modern Search web parts is the ability to add columns (Managed columns in search).
You can also configure handlebars that allow you to inject code. Specifically, I wanted to open the invoice in a new tab for easier viewing. Make sure to include data-interception=”off” value in the link reference. Otherwise, it will still open in the same tab.