About the Client
Our client is a European leader specializing in technical consultancy, systems, and services in the area of applied engineering for filtration, separation, and purification of fluids and treatment of water. The client owns a large fleet of vehicles, which operate over long distances.
The Challenge
Our client’s challenge was to eliminate hours of manual work organizing and cross-referencing purchase invoice data from their suppliers to their ERP - Navision. More specifically, every month, their fuel supplier sends our client one email with the main invoice, followed by a second email with tens or hundreds of data lines detailing every car refuel. Now, our client has many cars in their fleet, and each car matches a specific cost-center department. Therefore, each month, an employee had to manually sort and organize each car refuel expense and add it to its specific cost center. This was done manually and would take one full day of work.
The Solution
We utilized Power Automate as the main automation Platform.
The client receives two emails with the invoice lines that need to be put in Navision. These emails are automatically forwarded to the automation inbox and this initiates the automation process.
Converting and processing Excel file
First, the e-mail with the invoice lines arrives, which has an attachment of XLS type, which is the old Excel format. This is then updated with Cloudconvert to XLSX so it can be processed through automation. It takes each line and then creates a Line in Navision with the correct category and associated cost center, defined by the client.
Processing zipped PDF attachments from mail for OCR
There are additional bonus lines, which come in a second email as a PDF. These are processed separately since they arrive in a separate email in a ZIP. The automation extracts the compressed archive and then gets the correct PDF file out of it. This is then sent to an AI OCR processing - which we have trained with 5 previous examples - and correctly identifies the lines that need to be scanned and put into Navision.
All of these processes are managed through Microsoft Power Automate. If any error arrives and manual input is needed, then those invoice lines are sent to the administrator so she can manually process them.
One of the main challenges was that the Navision version was an on-premises version, to which we had to create a custom API connector in Power Automate. This was done successfully so new automation can be easily done into Navision.
Apps Used
To enable this automation we have utilized:
- Power Automate - Integrates all different modules and applications. Processes the data and manages the stages of the execution
- Cloudconvert - Convert old outdated XLS format into XLSX format so we can process the data from it.
- AI Builder OCR (Microsoft) - Does advanced OCR processing by training on 5 invoice examples. This provides a highly accurate OCR solution
- Sharepoint was utilized for tracking the different stages of the process and initiating the process stages
- Navision - ERP (Enterprise Resource Planning) tool that the client utilizes for the main system.
All of these are really well integrated into the Microsoft ecosystem and have standard modules that can be utilized for automation.
Issues during development
The e-mail came in XLS format instead of XLSX, so we had to utilize Cloudconvert for the data.
Power Automate is really cumbersome and hard to utilize for complex automation. The scenario building took 3 times as much as it would've had in Make.com.
The Navision server is an on-premises server that is managed by a Third-Party Company (Arggo). They had to create the endpoints for us to utilize and were hard to move.
Because it is an invoice it had to be 100% the same value, which was hard because of decimal rounding.
Results:
Apart from eliminating error risks that come with manual labor, our automation solution helped our client decrease project time from one full day to under 5 minutes. Even more so, these 5 minutes are only for data validation, which in the future, can be dropped down to 0, since the whole process can be automised.
In Conclusion
This project is a prime example of how automation can revolutionize corporate operations, even the tiniest and most intricate ones. In this case, we effectively automated the laborious and prone-to-error work of processing and classifying fuel bills for a sizable fleet of cars by integrating a range of Microsoft products, including Power Automate, Cloudconvert, AI Builder OCR, and SharePoint, with the client's current ERP system, Navision.
This drastically decreased the possibility of manual errors in addition to cutting down the time needed for this task from a whole day to only five minutes. Notwithstanding difficulties like adjusting to an on-premises Navision server and guaranteeing accurate decimal handling in the processing of invoices, the knowledge of our team and the adaptability of the Microsoft environment allowed for a smooth and effective solution.
This solution can be expanded to other purchase-supply operations, in order to complement the whole expense management process.
Are you ready to transform your organizational processes with smart automation?
