How to lodge travel expenses in Dynamics 365 with Power Automate
AlfaPeople |
Dec 04, 2023

How to lodge travel expenses in Dynamics 365 with Power Automate

As a consultant implementing CRM and ERP systems you’re often required to travel to customer sites, conferences and venues, for which the company covers the expenses in their travel expense solution.

I have to mention that there are still companies that require physical receipts, but more and more companies are relying on digital copies of these expenses. What’s more, on 99.99% of the remote work we do, we are with our faithful companion, a smartphone running either Android or IOS (I really liked the simplicity of windows phones).

With this in mind, Microsoft Power Automate acts as a powerful engine to combine the multiple web services we use on a day to day basis to document our expenses.

In this blog, my intention is to showcase the capabilities of these different services in a simple solution, which I use to collect all the expense receipts I gather during my work trips. We use Dynamics 365 CE to record this information.

In short, you get an expense, pay for it, and receive a receipt, either electronically or printed. If it is an electronic receipt, you will have it available in your work or personal email. Otherwise, you will get a good old retail POS receipt (that we also implement if you are going with Dynamics 365 Finance and Operations-Retail). In this case you’ll have to take a picture of that physical receipt. Then you can “forget” about it. This small documentation task is our process trigger, so that once we receive that physical receipt, we kickstart our expense report entry, and can be assured that all will be tracked.

How to implement the Electronic Reporting process

The process is simple in its conception: take a picture, store it, and create a record in your expense management system with the image you took as an attachment.

The tool I have been using to scan documents is Microsoft Office Lens. This solution is available for IOS, Android, and even Windows to capture documents. You can quickly crop and edit images to save it as an image or as a different file format like PDF. In this link you can see the functionality for Android.

Office Lens allows you to connect to Microsoft OneDrive, the Microsoft Cloud storage solution. Office Lens can be set up to connect with multiple Microsoft accounts (personal or work) and will store the selected file formats in an Office Lens folder in your OneDrive. (PS: I have not found yet the way to change the folder where images are stored).

While learning about Microsoft Automate and all its connectors, I found that OneDrive, SharePoint, Dynamics 365 CE (CRM) and Dynamics 365 Finance were accessible. Then it hit me: I already have the image I took in Microsoft’s ecosystem, I just need to get it to the last mile.

Microsoft Power Automate can be summarized as a workflow engine (for all those old Dynamics AX consultants), which begins with a trigger, and then takes the information and can create a process flow through multiple steps. In this case, I did not need an approval process or any notification, just a simple documentation tool.

So, in short, the flow process ended up triggering when a file was created in one folder in OneDrive, taking that information and creating a record in our expense management system. We use a third-party solution in Dynamics 365 CE called PSAsuite link that allow us to track expenses in a Dynamics 365 CE entity.

Finally, to something that caught my attention on Power Automate. The connectors feed from the metadata of the solution, providing access to non-Microsoft entities, in this case, a third-party provider.

Of course, this all connects, and I can see all the information because my Azure Active Directory Account – Microsoft work account, has access to all these systems. This is the connection in Microsoft Power Automate, which is the credential storage so that the different connectors can work.

Getting back to business, as mentioned 9 paragraphs before, we take a picture and create an expense report entry. In more detail, you can follow the process in the image below.

how to lodge travel expenses in dynamics 365 with power automate 1 1
Image 1: Expense report process to implement with Microsoft Power Automate Flow

As you can see in the process diagram, this Microsoft Power Automate Flow also allows us to process Electronic documents. You just need to get them, rename them, and drop them in the same folder as Microsoft Office Lens does.

Following a clear process, we can go deep into creating our Flow.

how to lodge travel expenses in dynamics 365 with power automate 2
Image 2: Initial iteration of the Expend Report Entry Flow

Of the possible types of triggers, (image 3) we have in Microsoft Power Automate Flow, we picked the automated version because we do not want to manually trigger the process, but simply just creating the File should be enough.

how to lodge travel expenses in dynamics 365 with power automate 3
Image 3: Triggers as of October 2019 – Microsoft could add more

Here is where the knowledge of the Data Structure in the Dynamics solution comes into play. We can retrieve and create records, but how does Dynamics 365 handle attachments?

In Dynamics 365 Finance, we have the document management functionality, but in Dynamics 365 CE we have the “Notes” entity that can be related to any of the other entities in this product.

Taking this into account, we need to connect to Dynamics 365 CE and create a record in a main entity, so we can then create a “Notes” entity record where we will hold the attachment.

Creating the Flow

With the “when a file is created” OneDrive for business trigger created, set the folder to be the “Office Lens” folder.

how to lodge travel expenses in dynamics 365 with power automate 4
Image 4: Trigger – When a file is created

Then, having the file available, we proceed to initialize our variables. In this case, we are going to initialize our “expense” entity record in Dynamics 365 CE, and for this, we are calling a specific record in our Project Items Entity. In Dynamics 365 CE, a guide is available to uniquely identify a record.

how to lodge travel expenses in dynamics 365 with power automate 5
Image 5: Get Record – Dynamics 365 Record with information relevant to create our information

We now proceed to create a new record in our “expenses” entity.

Note: remember that you may not have an “expense” entity in your system, and so you will need to select the proper entity you want to create a record for.

When creating this record, we will use the file name from the initial trigger, the “Project” and “Project Item” value from our template record that we got from our -Get Record- step, and a date.

how to lodge travel expenses in dynamics 365 with power automate 6
Image 6: Create a new Record in the “Expenses” entity

As you can see, the date is not a direct reference to a creation date, but it is a function. This is where Microsoft Power Automate Flow begins to level up the game as we will need to use functions to process our data. We are using here a utcnow() function that gives us the day in which we are executing the process (the date that we are filing the receipt for), and a specific date format.

how to lodge travel expenses in dynamics 365 with power automate 7
Image 7: Detail on the Date Format to use for a date field

Once this record is created, we can proceed to create the “Note” entity. In this step, there are specific details to keep in mind. The “Document” and “Is Document” field are important to be filled out so the Note can hold an attachment. Notice the $content on the document Field. When creating this record, there was no way to reference the file content, and so I tried multiple functions decoding coding64, etc. until a review of a test run showed the structure of the outputs from our trigger event as shown in the image. It was funny because you could see the file content but not the content. This was the breakthrough to get it working.

how to lodge travel expenses in dynamics 365 with power automate 8
Image 8: Create a new record 2 – creating the Note entity record
how to lodge travel expenses in dynamics 365 with power automate 9
Image 9: Execution run with detail of data outputs

A simple process flow for reference

how to lodge travel expenses in dynamics 365 with power automate 10 1
Image 10: Simple Process Implemented

Execution: Simple point and shoot. And Microsoft Power Automate Flow does the magic.

Of course, for organization purpose I edit the file name of the scan, so I can identify it later, and taking into account that this could be used as a marker to control the flow of documents, I have implemented a new version of this flow as seen on Image 11. I added a condition control element to evaluate the first 4 letters of the file name, so that it will treat it as an expense to be recorded in our expense management solution, or if not to move the file to a new folder. There are other things besides receipts that I need to scan.

For details, you can always contact us at AlfaPeople to help you implement your system.

how to lodge travel expenses in dynamics 365 with power automate 11
Image 11: New Iteration of the Expense Report Entry Flow – using Office Lens for more things

Conclusion

Microsoft Power Automate allows us to create seamless interactions between different systems, making integration efforts look really simple. There are options to develop and created more intricated solutions but based on the Microsoft stack and piggybacking on Microsoft’s security and Microsoft’s Azure Cloud, really helpful solutions can be implemented.

This process solution does not limit to the expense report entry recollection, but can also be implemented for field services, to recollect information for cases.

This can be implemented with the expense management solution for Microsoft Dynamics 365 Finance and Operations or other systems that have a Microsoft Power Automate-Flow enabled connector.

Takeaways from creating this solution

The dashboard gives you all you need to troubleshoot. It will help you monitor your execution and check what happens. I implemented some Variable steps to figure out why my condition was not met, and as seen in Image 13, I was able to use the outputs to learn that the substring method in Flow does not work as the MID() method in Excel.

how to lodge travel expenses in dynamics 365 with power automate 12
Image 12
how to lodge travel expenses in dynamics 365 with power automate 13
Image 13: Execution result tool