.png)
Save 43 manual steps PER INVOICE -Automated landed cost freight invoice processing via attended RPA
- Chris Segurado
- Feb 13
- 16 min read
Agenda
1. Overview
The new Landed Cost module for Dynamics 365 Supply Chain Management helps businesses streamline inbound shipping operations by giving users complete financial and logistical control over imported freight, from the manufacturer to the warehouse. Auto costing is used to estimate freight, duty, local transportation and other charges for inbound transportation which is trued up later when the actual freight vendor invoice is recorded. In that context, I bridge the gap with a solution that leverages RPA and AI Builder to process via email received freight invoices in a streamlined and automated fashion. Processed information are stored in Dataverse. Via PowerApps and with use of PowerAutomate and PowerAutomate desktop, actual invoice information are lastly processed by a RPA bot and recorded in D365FO. This solution can save 43+ manual steps to be executed by an end user per invoice.
Environment/Infrastructure requirements:
- Dynamics 365 for Supply Chain Management Environment (I used my Tier 1 dev box)
- Powerplatform environment (I used a trial instance - Find details under https://docs.microsoft.com/en-us/power-platform/admin/trial-environments)
License requirements:
- Microsoft PowerAutomate Free
- PowerApps per app
- PowerAutomate per user with attended RPA plan
- Microsoft 365 E3
- AI Builder trial
- Dynamics 365 for Supply Chain Management
Components used:
- AI Builder (Form processing)
- PowerApps (with premium connectors)
- PowerAutomate (with premium connector)
- PowerAutomate Desktop (with onprem gateway)
- Dynamics 365 for Supply Chain Management (Landed cost module and custom entity development required)
2. Configure AI model for form processing and create PowerAutomate flow with AI model
I configure an AI form processing model that processes invoice information from an pdf document received via email. It sends out an confirmation email to the person in charge of invoices within your organization and it saves the processed data in a custom Dataverse table.
I navigate to https://us.flow.microsoft.com/en-us/ and create an AI Model for form processing.
After selecting form processing, I am prompted to specify a name for my AI model.
After creation, I added tags for all information on the invoice that I want to extract. Here, I make sure that I extract all information that I later need/want to process in Dynamics 365 for Supply Chain Management. (e.g. Invoice number, Duty Charges, Vendor name, Fuel Charges, etc.)
I manually created 5 invoice documents and saved them locally as pdf documents. AI models require at least 5 source documents to train the model. See below my example documents:
After confirming the tags, I added a new collection and uploaded the 5 documents mentioned above.
As stored locally, I select "upload from local storage".
You can see the upload is processing.
After the upload is completed, I select the "Analyze" button. This takes a view minutes. After that's done, I am able to map fields on the right side to information on the invoice pdf document - See below.
E.g. for invoice number field/tag:
After all tags/fields are mapped for all 5 uploaded documents (documents need to have the same layout), the model can be trained - Press Next.
In the next screen, I can see and review the model summary and if all looks fine I can progress with training the model. This take also a few minutes.
After the model is trained, I can review the model's performance and publish the model.
For validation purposes, I go to quick test under Models and upload a sample invoice to test the model for accuracy.
See the results are on point and in alignment with my mappings set up prior.
As I successfully tested the model, I am ready to publish the AI model under Models now.
After the model is published, I can use the model for my PowerAutomate flow.
I click "Use model "and "+ New flow in Power Automate".
I allow/grant access to Dataverse, Office 365 Users and Office 365 Outlook.
After doing so, a standard flow is automatically created so I do not need to start from scratch. I do not adjust the "Process and save information from forms" as well as the "Get my profile" actions. However, there are a few adjustments on other actions that I have to do. First, save and rename the flow - Here, I chose "LandedCostInvoice_FormProcessing".
The following adjustments were made to the standard flow automatically created:
- I deleted the welcome action.
- In the "When a new email arrives (V3)" action, I could potentially adjust the subject filter to "Invoice" instead of "AI Model" so all emails with subject "Invoice" would be processed by my automated flow. For now, I left "AI Builder" for simplicity.
- I adjust the condition in the "Apply to each" action to "Has attachment" equals "True".
Before continuing with what needs to be adjusted in the PowerAutomate flow now, I require a custom table in Dataverse as I reference fields of that custom table within the flow.
That said, I navigate to https://make.powerapps.com and under Data/Tables, I create a new custom Dataverse Table. I create this new custom Dataverse Table so I can save the information processed in table tailored for our needs.
See below the Dataverse/Tables form:
I create a new table by clicking "+ New table" and add information required.
After confirming, the columns form appears. Some standard fields have already been created at time of table creation by default. However, I still require our custom fields to be created. To do so, we leverage the "+ add columns" option.
I added the following fields/columns:
- Invoice number - Data type: Whole number
- Transportation charges - Data type: Decimal number
- Duty charges - Data type: Decimal number
- Fuel charges - Data type: Decimal number
- Insurance charges - Data type: Decimal number
- Freight vendor - Data type: Text
- Invoice date - Data type: Date only
- VoyageID - Data type: Text (This field does not come from the processed invoice as the freight vendor does not know the D365FO specific voyage number sequence. We later use a PowerApp to patch the correct D365FO voyage ID from D365FO to the processed invoice in Dataverse)
- Total - Data type: Decimal number
- Weight - Data type: Decimal number
- Dimensions - Data type: Text
See an example for Invoice number below:
Lastly, save the table and see the final result:
After doing so, I continue editing my PowerAutomate flow. I edited the "send an email (V2)" action and its subject and body. This email will be sent to the employee in charge of invoices.
Subject:
"Document processed"
Body:
"Hello <get my profile - given name variable>,
I hope you and your family are doing well.
A freight invoice has been processed and information have been extracted to Dataverse. Please match external freight invoice number to voyage ID from Microsoft Dynamics 365 for Supply Chain Management's Landed cost module and kick off the automated freight invoice recording process via PowerApp.
Link to the PowerApp: <we later paste the PowerApps URL here for the PowerApp that we are creating>
Key invoice information extracted:
Total: <Output - Total value > (Confidence score: <Output - Total confidence score>)
Invoice number: <Output - Invoice number value > (Confidence score: <Output - Invoice number confidence score>)
Vendor name: <Output - Vendor name value > (Confidence score: <Output - Vendor name confidence score>)
Thank you so much!
Best Wishes,
Chris Segurado"
After doing so, I added a "new row 2" action. Here, I can now reference the created Dataverse table called "LandedCostInvoices" and its columns/fields - short I can now specify columns/fields where we want to save/store the processed data,.
The unique LandedCostInvoice identifier/primary key (should not be invoice number as there is a chance that different freight vendors may send you an invoice with the same invoice number) is specified with UtcNow what saves the timestamp. I chose this design as let's say you have 2 different freight vendors, there is a chance that invoice numbers may fall on the same number. As we require a unique identifier the timestamp should do. For Invoice number, Fuel charges, Duty charges, Insurance charges, Invoice number, Total, Transportation charges and Weight outputs, I added a function that transforms outputs to string values to be passed on to the custom Dataverse table. (In my scenario, this is required as it otherwise errors when trying to create a record in Dataverse)
After doing the adjustments, I save and close.
To sum it up and as a quick recap on what we are trying to accomplish here: I trained an AI model that will process an via email incoming invoice pdf document. The PowerAutomate flow will be triggered for all incoming emails to the specified mailbox with subject "AI builder" as well as where the attachments equals true action is true. An confirmation email with invoice key information such as vendor name, total and invoice number is sent to the employee that is responsible for invoice posting/review. This confirmation email also contains a link to the PowerApp where the employee continues invoice processing later by kicking off the RPA bot. The processed information are stored in a custom Dataverse table. So far, so good.
As a next step, I am setting up Dynamics 365 for Supply Chain Management - Landed Cost Module.
3. Landed cost configuration for auto costing
Please follow my prior posted blog for details on how to set up auto costing and the landed cost module: https://www.dynamicsduoacademy.com/post/d365fo-new-landed-cost-module-release-10-0-18-auto-costing
4. Custom Development in D365F0
As voyage is not a standard entity, I need to create a custom entity herefore.
I found the correct table name in Dynamics 365 for Supply Chain Management under Landed cost/All voyages. Under right click/form information, I can see that voyages are housed in the ITMTable.
Under options/Record info and show all fields on the Actionpane, I can also identify the tableID.
To create a custom entity, I navigate to LCS under https://lcs.dynamics.com/ and navigate to my LCS project and cloud-hosted environment. I select my dev machine that I will use for this demo. Under Full details, I have now the possibility to RDP into my VM. Click on the VM name (blue hyperlink marked yellow below) to open a Remote Desktop session.
I am prompted with an RDP sign-in. Navigate to more options and use a different account. Now, I can sign in with Admin credentials shown above in LCS next to the VM name.
Note: RDP is only possible in cloud hosted Tier 1 machines. MS managed machines do not allow it.
After my RDP session to my VM is established, I open Visual studio 2017 as admin via right click/run as admin.
I sign in with my visual studio account and navigate to view/application explorer.
Under application model, I navigate to Data model/tables and search for ITM Table.
I right click the table and open the table in tablebrowser to see data currently maintained in the ITMTable in case I want to validate that I capture the right table.
Lets take a look at the entity - As you can see there is no entity available herefore yet. So, I need to create a custom entity.
I created a new model.
I created a new project and chose FO project and a name accordingly.
As a next step, I need to update the model parameters.
I reference the following packages:
- ApplicationFoundation
- ApplicationSuite
- InboundTransportationmanagement
I confirm and finish. After doing so, I right click on the model on the left hand side and add a new item. Under Data model, I select data entity.
In the Data Entity Wizard, all of the properties will be defaulted except Primary datasource. I select ITMTable as the primary datasource and document as entity category "master".
I confirm with Next and select all fields to be included - for my scenario, I select all fields. After confirming, the entity is completed and should be good to go. However, I still need to set some properties and in the end build.
In Solution Explorer, I right-click my project and then select Properties. I change the value of the Synchronize database on build property to True, and then click OK. This property must be set only one time per project.
As a last step prior building, I adjust the label.
Now, I am ready to build what was developed and I am doing so under Build solution in the build menu on the Visual Studio Toolbar. The Build starts...
After a few minutes, the build completes.
Just to make sure, I verify that the build doesn't contain any errors.
To see if the entity made it through, I open SQL server management studio on my dev machine and query our newly created entity via : "Select * FROM MZRLANDEDCOSTVOYAGEENTITY" - All looks good.
In case any additional change needs to be done at this point, make sure to click on rebuild to rebuild and synchronize the project. In my case, all looks good and we are ready to go to take a look into D365FO.
I log into my D365FO environment and navigate to data management workspace/Framework parameters.
Under entity settings I click refresh entity list - This will take a few to refresh the entity list.
As soon as it is completed, you will get a notification that is was completed.
The entity list is refreshed and I can see my custom entity being available in D365FO. I can run a test and a run an export project in DMF for that entity if I want to. However, I am comfortable that all looks fine so I can skip this here.
As a next step, I create the PowerAutomate desktop flow (for attended RPA).
5. Set up and configure PowerAutomate desktop for attended RPA
I set up/configure the PowerAutomate desktop flow and record all steps to be taken in D365FO to process an invoice.
Installation of Power Automate Desktop:
I downloaded the Power Automate Desktop installer and opened the Setup.Microsoft.PowerAutomateDesktop.exe file.
Note: Make sure you have local admin rights to your machine, otherwise installation may fail.
I followed the instructions in the Power Automate Desktop setup installer to complete the installation.
After installation is completed, I sign in.
I create a new flow.
I specify a name for the desktop flow.
I select web recorder and record steps to take. (Here: Steps for landed cost invoice processing; that means: vendor invoice journal and voyage cost allocation)
I specified the browser used for the recording/replay. (Here: Edge)
The web recorder started. Each step I am executing is now recorded.
Now, I record steps in D365FO (make sure you are logged out of D365FO prior).
Here:
- Navigate to D365FO
- Log in into D365FO
- Create Vendor Invoice journal
- Add line with total invoice cost
- Click voyage in Actionpane
- Distribute/Allocate actual costs per specific charge
- Confirm
After the recording is completed, I click finish and save - See the result below:
As the steps recorded above contain hard coded values (e.g. Invoice number entered during recording is 191881), the bot would fill in exactly those values when running. However, I want to pass on variable values - to be exact values from Dataverse depending on invoice selected on the PowerApp - to the bot. Because of that, I need to create placeholder variables that I can reference afterwards in my PowerAutomate flow.
That said, I added the following variables:
- Invoice number - Data type: Text
- Transportation charges - Data type: Text
- Duty charges - Data type: Text
- Fuel charges - Data type: Text
- Insurance charges - Data type: Text
- Freight vendor - Data type: Text
- Invoice date - Data type: Text
- VoyageID - Data type: Text
- Total - Data type: Text
- Weight - Data type: Text
- Dimensions - Data type: Text
All variables are declared:
After all variables are declared, I exchanged the static data entries with the variables. (E.g. see below for freight vendor)
Note: Make sure you turn off emulated writing for variables - inputs will fail otherwise later when running the RPA bot.
Lastly, I added some more settings under "on error" in the screenshot above. In case an action fails, I want to make sure the RPA bot retries the same action after 7 seconds. As I am running this in my Tier 1 dev box (what is not the fastest), I want to make sure if loading the page takes too long that the RPA bot does not stop/fail. I ran into that issue a few times while going through testing exercises here.
As a next step, I configure the instant flow that I reference in my PowerApp later and that will trigger the above set up PowerAutomate Desktop agent to run and passes values from Dataverse on to the Desktop agent.
6. Configure PowerAutomate instant flow for RPA processing
I configure an PowerAutomate instant cloud flow that will be triggered by a PowerApp. The flow should be able to get information from Dataverse and pass them on to the PowerAutomate desktop agent and kick of the RPA bot.
I navigate to https://us.flow.microsoft.com/en-us/ and create an instant cloud flow.
In the next screen, I select instant cloud flow and select PowerApps as trigger as we connect this PowerAutomate flow later to a PowerApp button.
I add a new step and select "run a flow built with Power Automate Desktop". This is a premium action as you can see.
As a next step, I add a new gateway for on prem connection - it downloads a file that you need to install on the machine where you want to run the RPA bot from. (here: my local machine)
I install the on-prem data gateway by following the wizard.
I sign in after installation and follow the wizard.
I register a new gateway on my PC.
I specify name and recovery key.
I confirm with "Configure" and the on-prem data gateway is ready to be used in PowerAutomate.
That said, I navigate back to PowerAutomate and you can see now that the gateway that I just created can be selected.
I select the "LandedCostGateway" in the gateway name field. As a next step, I identify the domain name of my local machine.
I open the command panel and type in "whoamI" and my domain will be shown. I enter that domain and my password and lastly confirm.
After doing so, the connection is established.
As a next step, I select the PowerAutomate desktop flow that I created/recorded earlier. (Here: "LandedCostInvoiceProcessing")
I specify "attended" as run mode. I still need to pass on the correct values from Dataverse for a specific invoice. To do so, I add a "Get a row" action prior to the "Run a flow with Power Automate Desktop" action to make sure we pass on the values from a selected record in the gallery of a PowerApp.
As a next step, I reference the specific variables/fields (also named columns) from Dataverse to be passed on to the PowerAutomate Desktop agent.
As a last step, I configure my PowerApp.
7. Configure PowerApp for freight invoice processing
I navigate to https://make.powerapps.com/ to create and configure my PowerApp. The PowerApp should be able to filter invoice records from Dataverse and show results in a gallery. It should also be able to patch the D365FO voyage number to the selected Dataverse invoice record (this is required as the RPA bot needs to know the D365FO voyage number to process received freight invoice information in D365FO). Lastly, the PowerApp should be able to kick off the PowerAutomate flow that kicks off the RPA bot (PowerAutomate desktop).
I create a blank canvas app.
I added the following data sources:
- MZRLandedCostVoyage custom entity via premimum connector
- RPATrigger - Landed cost PowerAutomate flow
- LandedCostInvoices custom Dataverse table
- Collection
I added the following gallery:
I referenced the following fields within the gallery with LandedCostInvoices custom Dataverse table as data source:
- Invoice number
- Freight vendor
- Total invoice balance
- Dynamics 365 Voyage ID (the voyage ID will be empty originally when the actual invoice is processed as D365FO specific number sequence for voyage number is not present on the actual invoice. We use the "Dynamics 365 Voyage ID" dropdown that has MZRLandedCostVoyage custom entity via premimum connector as data source to display all available voyage numbers in D365FO. Via Patch function on Save button the selected voyage number from dropdown will be saved to the in the gallery selected Dataverse invoice record.)
That said, I added a dropdown for voyages in D365FO with MZRLandedCostVoyage custom entity via premium connector as data source.
I added 2 filters that can be used to find the correct invoice to be processed via invoice number and freight vendor criteria within Dataverse LandedCostInvoices table.
I added the save button that patched the selected D365FO voyage number sequence to the voyageID field in Dataverse for the selected gallery invoice record.
I added a button to kick off the PowerAutomate flow (created prior - see above) that is passing on values to PowerAutomate Desktop based on the selected row in the gallery and kicks of the RPA bot.
I saved and published the PowerApp.
Note: Make sure your userID that you are using is set up with the correct Default LE in D365FO so Powerapp connector can fetch the correct data. (Here: Voyage records/data of the correct LE)
8. End to end process - Demonstration
1. In D365FO:
Purchase order for item 1000 and vendor 1002 is created and confirmed.
Voyage is created for our 3rd party freight vendor AirCarrier. Auto number sequence 0000012 is assigned.
In the next form, I specify the above PO number in the purchase order filter.
I check the lines and add them to the staging list under lines to select.
The lines are added to the staging list - I navigate now to view staging list under lines to select. Here, I click add to new shipping container and specify details.
Now, I wait for the product as well as the actual invoice to come in. Estimated costs are recognized - see under all voyages form and cost inquiry on the Actionpane under general.
2. Vendor sends us an invoice via email - The invoice is received in email post box
Here, I staged the scenario and sent the invoice from my private email to the central email box pretending being the vendor.
Here is the email sent: (AI Builder is required in the subject as we specified it in the automated flow)
Here is the attachment sent:
Taking a look at the flow - I see all was processed successfully. A confirmation email was sent to my work email stating that an invoice has been processed automatically and that such is ready for further processing in the PowerApp. (link to the PowerApps is embedded in the email - see below)
My run was successful.
Taking a look at Dataverse and my custom landedcostinvvoice table, I can see the data from the above processed invoice is there.
I can validate/see that a record for my scanned and processed invoice was created.
3. PowerApps
I click on the PowerApps link in the above received confirmation email.
The PowerApp opens up. I filter by the invoice number stated in the confirmation email (Here: 997755) and the vendor stated in the confirmation email (Here: AirCarrier). You can see above the D365FO voyage number is still not showing any value as not assigned yet. The voyage number is not present on the freight invoice by nature and is thus not saved in/processed to Dataverse as this is a D365FO specific number sequence.
The dropdown shows all D365FO voyage numbers within my LE. I select the correct D365FO voyage number applicable to the invoice and match my D365FO voyage number to the actual invoice number by clicking "Save" manually that way.
Short, I select the correct voyage number and save.
Now, the voyage number is assigned to voyageID field in custom LandedCostInvoices Dataverse Table.
After that is done, I can kick the RPA bot off via "Process Invoice" button. I sit back and watch - The bot is running currently as an attended RPA bot but of course I could also set it up to run unattended. (In that case, I would require another license however) All steps recorded will be executed and data from Dataverse is passed on via on-prem data gateway to PowerAutomate Desktop Agent. See in the below video recording:
I can validate and see that my RPA Trigger - Landed Cost instant flow ran succesfully.
Lastly, I want to have a last review look prior posting.
Note: I could have also recorded the posting process and let the bot post the invoice.
Posting went successful.
Let's review trued up estimates and actual costs recorded. On voyages form and on Actionpane under general/cost inquiry, I can see that actuals have been recorded.
Comments