Exchange Rate Loader PowerApp for D365 F&O - Part 1

In this post I am going to detail how to create a Power App to load a excel sheet into D365 F&O. For this I will be using the exchange rates data entity, but this logic could be applied to any data entity in D365.

Firstly we have to create a Power App, I created an app with 2 separate screens:

Screen 1 : This is where we select a file and click Upload.

This screen contains a few different elements including various label fields, a link to the settings screen and an upload control

I wont go into detail around any of these as they are pretty straight forward apart from the upload control which requires you to hack the attachments control to allow you to save files, you can get details of how to do this here (372) PowerApps Attachment Control - Upload All Files not Images Only to Azure Blob Storage - YouTube

The one additional step I took was to modify the "onaddfile" like below, noting that this formula requires the parameters from the settings screen and therefore might be better to start by creating those before updating here:

Screen 2: This is where I store all the relevant parameters I need to pass over to power automate, it also contains a link back to the loader screen.

These settings include here are passed over to flow using the following:

1. Data Entity Name.

2. D365 Environment Name.

3. Legal Entity, you could move this to Upload screen, but I kept it here in this example as the Exchange Rate data entity is Global.

4. Blob Storage Account URL.

5. Blob Container.

6. File Name, I am working on the basis that the file name should alway be "Exchange Rates.xlsx", but it would be possible to make it so it could take dynamic names.

7. File Type, this is how it is defined in the Data Management.

8. Manifest, this was downloaded from D365 via a manually created data project.

9. Package Header, this was downloaded from D365 via a manually created data project.

The next element is to create a flow in Power Automate, you could use Logic Apps here as well if you wanted but would be a slightly different set-up see here Call logic apps from Power Automate and Power Apps - Azure Logic Apps | Microsoft Docs and basically would require you to trigger the Logic App from Power Automate so I stuck with Power Automate.

The flow that I build is below, its doesn't currently contain any logic for managing errors and only works for a single load at the moment, but I will refine it to add these at a later stage and will update here:

First is to trigger from the PowerApp:

Then I initialize the variables that I passed over from the PowerApp:

I then compose the Package Header and Manifest XML and load them to a blob storage container:

I then use an Azure Function to zip the three files together in a folder, there are plenty of azure functions to do this documented as its not a standard function in Power Automate.

I then get blob content for ZIP file, use the D365 Fin & Ops connector to get the blob container URL to upload the zip file to, parse the JSON response to get the URL and then use an HTTP call to upload the file to the blob container URL and finally use the D365 Fin & Ops connector again to trigger the package import.

The final stage here is to delete the various files which have been created in the blob container.

Like I said above, I want to refine this to:

1. Add some messaging back to user to let them know if there is any errors.

2. Put some kind of wait bar to give users idea of progress.

3. But some logic in to allow concurrent loads as currently only supports single person executing at the same time.

4. Allow for dynamic naming of files.

5. Provide a downloadable excel template for users so they can see the required format.

The final stage is to place the app in D365 or another location, I decided to place it on the  currency exchange rate screen,

When the flow has run you will see the data project created in D365 and the executed load:

Once I have completed this will put up a link to the complete solution.

That's all for now.