Leverage Power Automate with D365FO workflows (Vendor invoice journal workflow)

Recently I came across the following business requirement:

1. When a business approver rejects an invoice journal workflow for any reason the workflow originator gets notified. At this point the workflow originator has two options, either correct the invoice journal and re-submit again or simply delete the invoice journal.
2. The issue with the process is that the data of why the invoice was rejected is not easy to track. User must go into the workflow history or hunt through hundreds of emails to find the one’s that got rejected.
3. Worst case scenario if the user deleted the invoice journal then that workflow history will be deleted as well.

The solution I built is two fold:

1. The purpose is to extract the data of the rejection from the D365FO vendor invoice journal workflow and store it in an easier to query place
2. Have a history of vendor invoice rejections  for future audit purposes

I decided to solve this business requirement by leveraging Power Automate or MS flow as it was previously known.

Below is a snippet of the information stored in the D365FO Vendor invoice journal workflow

Vendor invoice journal rejected for %Ledger journal table.JournalNum%

Link to journal: %Workflow.Link to web%

InvoiceNumber:%Ledger journal table.Journal lines.Invoice% – VendorAccount:%Ledger journal table.Journal lines.LedgerDimension% – Amount:%Ledger journal table.Maximum voucher credit amount% – InvoiceDate:%Ledger journal table.Journal lines.DocumentDate% – Comments: %Workflow.Last note% – Lastuser:%Workflow.Last action user% – End

 

As you can see, I am collecting the invoice number, vendor account, invoice amount, invoice date, the comment of why it was rejected and the person that rejected the invoice journal.

Below is what the workflow originator will get on their email when an invoice is rejected

As you can see the information is stored in the users outlook email application

This is where Power Automate comes in:

1. The first step of the flow is to look for an email in the users inbox that originated from the Dynamics user email. In my case emails come from the following dynamicsnotifications@xxx.com
2. The subject of the email always contains the following subject “Approval returned”
3. The trigger of the flow is activated when a new email arrives from the user above and the subject line reads as above.
4. Then flow using a series of variables and parsing functions extract the data from the HTML body of the email and finally stores the data in an excel spreadsheet. (Ideally you would want to store the data in a better source such as a SharePoint list or a SQL database)

This is the trigger of my flow – When a new email arrives with the conditions mentioned above

Then I am storing the body of the email in a variable

then using a combination of the last and split functions, I am parsing the email into the different pieces of data needed.

For example this is what I used to extract the invoice number from the body of the email

last(split(variables(‘BodyofEmail’),’InvoiceNumber:’))

the split function is splitting the body of the email into two pieces, the first piece is the data before it finds the word “InvoiceNumber:” and the last piece is all the data after the word “InvoiceNumber:” Using the last function I am storing the last piece into this variable

This is the result of the step above

Using a second variable now I am going to grab only the invoice number “test1234” by using the following functions

first(split(variables(‘ParseInvoiceNumber’),’ – VendorAccount:’))

the split function is splitting the result from the Parse invoice variable above, the first piece is the data before it finds the word ” – VendorAccount:” and the last piece is all the data after the word ” – VendorAccount:” Using the first function I am storing the first piece into this variable. The result is just the invoice number data “test2234”

This is the result

 

I did the exact same two variable process for all the strings in the email. The end result is six variables that store the rejected invoice data.

Once I had all the data then the last step is to insert the data into the spreadsheet table. I am using an Excel file stored in a one drive folder. (As mentioned earlier, the best solution will be to store the data in a SharePoint list or Azure SQL database)

This is the end result of the solution, an excel table with the data. Now you can build a nice report to show your manager how many invoices get rejected and why.

Hope this adds value to you guys.

Until the next one 🙂

Juan Sebastian Grijalba, CPA