Upload SharePoint Documents for Dynamics 365/Microsoft Dataverse Records Using Flow

In data migration from the legacy system to Dynamics 365, one of the common requirements that I have seen is the migration of the documents. Previously, we either have to develop our own .NET console application or use 3rd party tools. But now, we can leverage the Microsoft Power Platform to build the same mechanism using Power Automate instant flow.

In this blog post, you will learn how to achieve the following using the flow from Power Automate.

• create SharePoint Document Locations creation in Dynamics 365 or Common Data Service
• migrate documents to SharePoint folder of D365/CDS record
• use SharePoint connector actions such as List folder, Create new folder, Copy file

The Use Case: The requirements for each document migration will be different but in this blog post, I will demonstrate the solution based on the following requirements.

The files from the legacy system are placed into a shared network folder in which there are multiple subfolders

Each subfolder contains the files for each Account record and the folder name is the exact match of Account Number

The Account records are already there in the Dynamics 365 system and the SharePoint document location for the records may or may not be existing

First of all, the files are in the local network folder and now on the Microsoft cloud. Technically, we can use the File System connector and install On-premises Gateway to retrieve the files but let's just upload the files to SharePoint first for the sake of simplicity in this data migration. (if the requirement is for on-going document integration, File System connector and On-premises Gateway would require if the files are not on the cloud)

You can use this solution and tweak a little bit to trigger the automated flow when a file is placed into the shared network folder or uploaded into a staging SharePoint library.

Depending on the size/number of files, there are a couple of approaches to upload those local files to the SharePoint folder. For a few hundred to a couple of thousand of files, you may just open up the staging SharePoint folder and select all the files to drag & drop upload from the browser. But if there are a lot of records, you may sync the staging SharePoint folder with the OneDrive for Business sync client and move the local files into the synced OneDrive folder.

Once the files are there in the staging SharePoint folder, we can just create a flow for the document migration. This is the summary of the solution and the detailed explanation of the flow steps can be found below.

1. Manually trigger a flow
2. Initialize Source Site Address variable
3. Initialize Source Folder variable
4. Initialize Destination Site Address variable
5. Initialize Destination Folder variable
6. List Account parent Document Location
7. List staging Document folder
8. List Account records with Account Number
9. List existing Document Location of Account
10. Set Destination Folder variable with existing Document Location
11. Set Destination Folder variable with new SharePoint folder
12. Create new SharePoint folder for Account
13. Create Document Location for new SharePoint folder
14. List files from staging document folder
15. Copy file into Account Document folder

1. Manually trigger a flow

This step is self-explanatory. Since we are going to do one-time data migration, the flow will be manually triggered.

2. Initialize Source Site Address variable

The source SharePoint site address is stored in the variable to allow referencing in various places of the flow.

3. Initialize Source Folder variable

The source SharePoint staging folder where the original documents are uploaded is stored in the variable to allow referencing in various places of the flow. As the initial testing to load only a few Account records, a new folder can be created to copy a few documents folders for the testing purpose.

4. Initialize Destination Site Address variable

The destination SharePoint site address is stored in the variable to allow referencing in various places of the flow. e.g. If we want to test the migration, we can point it to the SharePoint site integrated with the UAT environment.

5. Initialize Destination Folder variable

The destination folder variable is initialised to get the SharePoint folder of the record either from the Document Location record (for records with existing SharePoint folder) or from the folder creation step later in the flow.

6. List Account parent Document Location

This step retrieves parent Document Location record for Account. It is required for "14. Create Document Location for new SharePoint folder" to set the parent document location for new Document Location record.

7. List staging Document folder

After initialising all necessary variables and retrieving required information, the first step is to retrieve all items (subfolders) in the staging SharePoint folder using Source Site Address and Source Folder variables.

8. List Account records with Account Number

For each document subfolder in the staging SharePoint folder, retrieve the Name and GUID of Account record where the Account Number = subfolder name. Assumption: The Account Number is unique for all Account records in the system and the Account record will be referenced as first(outputs('List_Account_records_with_Account_Number')?['body/value']) in the expressions from next step onwards.

9. List existing Document Location of Account

Next step is to retrieve Document Location records with the AccountID and check if the accountid record has an existing Document Location record created.

10. Set Destination Folder variable with existing Document Location

If there is an existing Document Location, set the Destination Folder variable with the relativeurl value from the existing Document Location record.

11. Set Destination Folder variable with new SharePoint folder

If there is no existing Document Location folder for the Account record, set the  Destination Folder  variable in a format that SharePoint integration creates a folder. <<Account Name>>_<<Account GUID without '-'>> (e.g. "Contoso Corporation_89CF8AE3F4A3453BAC5CCB05A76BFA40")

12. Create new SharePoint folder for Account

Use the value from the  Destination Folder  variable and Destination Site variable to create a new SharePoint folder under Account Library. The library name is hardcoded to Account but depending on the display name of the entity, it may change to Organisation or any value that may have been set.

13. Create Document Location for new SharePoint folder

Then, create the Document Location record to map the newly created SharePoint folder with the Account record. The value from step 6. List Account parent Document Location will be used in  Parent Site  field, accountid  in Regarding field and Destination Folder variable in Relative URL field.

14. List files from staging document folder

Now, we are sure that there is a SharePoint folder for the Account record. It is time to retrieve all the files from the subfolder from the staging SharePoint folder using Source Site Address  variable, Source Folder variable and the subfolder name.

15. Copy file into Account Document folder

The last step is to copy each and every file from the staging subfolder into the Destination Site Address , account Library, Destination Folder .

Summary

By using the SharePoint connector and Common Data Service (current environment) connector, we can build a flow which uploads files into the SharePoint folder related to the Dynamics 365/CDS record.

You can download the above sample flow from my GitHub repository via this link.