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.
- Manually trigger a flow
- Initialize Source Site Address variable
- Initialize Source Folder variable
- Initialize Destination Site Address variable
- Initialize Destination Folder variable
- List Account parent Document Location
- List staging Document folder
- List Account records with Account Number
- List existing Document Location of Account
- Set Destination Folder variable with existing Document Location
- Set Destination Folder variable with new SharePoint folder
- Create new SharePoint folder for Account
- Create Document Location for new SharePoint folder
- List files from staging document folder
- Copy file into Account Document folder
This step is self-explanatory. Since we are going to do one-time data migration, the flow will be manually triggered.
The source SharePoint site address is stored in the variable to allow referencing in various places of the flow.
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.
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.
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.
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.
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.
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.
Next step is to retrieve Document Location records with the AccountID and check if the accountid record has an existing Document Location record created.
If there is an existing Document Location, set the Destination Folder variable with the relativeurl value from the existing Document Location record.
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")
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.
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.
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.
The last step is to copy each and every file from the staging subfolder into the Destination Site Address , account Library, Destination Folder .
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.