My client was using a CMS to store data for managing offerings and demands for specific real estate items. I rebuild the system within Dynamics 365 using the Opportunities to store the offerings and demands and made sure that the website was now using D365 as a source instead of the CMS. There was one thing, the CMS system offered a super handy tool to upload a file with specific information for the real estate around NH3 guidlines for the specific area/real estate item. The Excel sheet is processed and the information was shown on the website.
Of course there are many ways to import or update data within Dynamics 365. But in this case we needed a super simple solution for the end users, so they could import the data themselves with a few clicks.
The new file type within Dynamics would offer the user to upload a file in a user friendly way.
After uploading the File the File will be processed by Power Automate, you can see the main steps below.
If you take a look at the main steps you will notice some strange things:
- You must use a tempory file. For this purpose the step Create File has been added. This step is needed as the Excel Online (Business) connector can only connect to an existing file on Onedrive / SharePoint. So I create a temporary file on Onedrive with the name consisting of the combination of the GUID of the opportunity and Timestamp. The File Content is the body of the step Get file or image content.
- Use the Excel Online (Business) connector and use the File ID as input. You cannot use the Filename in a variable; if you try to do so you will end up with an error like ‘The parameter fiel has an invalid value’…
- Use a Delay before Deleting the temporary file, otherwise an error will be shown that the file is locked. I used an delay for 10 minutes.
I still have one small issue and that is that the step Get file or image content fails when I use the function to remove a file within D365. The Flow triggers at that specific moment, as the Flow has been configured on the update of the opportunity and specific for the File field. So when removing the File the Flow also triggers, and fails on the Get file step.