cancel
Showing results for 
Search instead for 
Did you mean: 
stpuceli

Invoice Automation using the Power Platform

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.

 

Process.png

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:

Schema.png

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. 

MetadataExtraction.png

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.

FormsProcessing.png

FormsProcessing2.png

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.

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

FormsProcessingSetup.png

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.

 

FormsProcessingSetup2.png

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.

FormsRecognizer.png

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.

FormsRecognizer2.png

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.

FormsRecognizerJSONOutput.png

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.

FormsRecognizerPowerAutomate.png

 

PowerApps for Accounting Review

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

 

PowerApp.png

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.

PowerApp2.png

  • 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.

 

Search.png

  • 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.

SearchResultSource.png

  • 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.

SearchColumnFormatting.png

 

Meet Our Blog Authors
  • Experienced Consultant with a demonstrated history of working in the information technology and services industry. Skilled in Office 365, Azure, SharePoint Online, PowerShell, Nintex, K2, SharePoint Designer workflow automation, PowerApps, Microsoft Flow, PowerShell, Active Directory, Operating Systems, Networking, and JavaScript. Strong consulting professional with a Bachelor of Engineering (B.E.) focused in Information Technology from Mumbai University.
  • I am a Microsoft Business Applications MVP and a Senior Manager at EY. I am a technology enthusiast and problem solver. I work/speak/blog/Vlog on Microsoft technology, including Office 365, Power Apps, Power Automate, SharePoint, and Teams Etc. I am helping global clients on Power Platform adoption and empowering them with Power Platform possibilities, capabilities, and easiness. I am a leader of the Houston Power Platform User Group and Power Automate community superuser. I love traveling , exploring new places, and meeting people from different cultures.
  • MCT | SharePoint, Microsoft 365 and Power Platform Consultant | Contributor on SharePoint StackExchange, TechCommunity
  • Encodian Owner / Founder - Ex Microsoft Consulting Services - Architect / Developer - 20 years in SharePoint - PowerPlatform Fan
  • I am the Owner/Principal Architect at Don't Pa..Panic Consulting. I've been working in the information technology industry for over 30 years, and have played key roles in several enterprise SharePoint architectural design review, Intranet deployment, application development, and migration projects. I've been a Microsoft Most Valuable Professional (MVP) 12 consecutive years and am also a Microsoft Certified SharePoint Masters (MCSM) since 2013.
  • Big fan of Power Platform technologies and implemented many solutions.
  • Passionate #Programmer #SharePoint #SPFx #M365 #Power Platform| Microsoft MVP | SharePoint StackOverflow, Github, PnP contributor
  • Web site – https://kamdaryash.wordpress.com Youtube channel - https://www.youtube.com/channel/UCM149rFkLNgerSvgDVeYTZQ/