Automatically Move Dataverse Email Attachments to SharePoint Document Library Using Cloud Flow (Part 6 of 6)
Linn Zaw Win
3,405
Like
Share
Report This is the sixth post in a series of Automating SharePoint Integration with Dataverse using Power Automate. You can check out the other posts via these links (Part 1, Part 2, Part 3, Part 4, Part 5)
With server-side synchronisation, you can synchronise your email system with Microsoft Dataverse and create corresponding email activities in Dataverse. But if the option to automatically track incoming Outlook email in the personal options is not set properly, a lot of unnecessary emails can be tracked into Dataverse and that can take a lot of storage capacity.
If the file capacity usage of your Dynamics 365/Dataverse environment looks something like this and the file storage is over capacity because of the Attachment table, you have a few options to clean up the Attachment other than purchasing a file storage capacity.
(1) Deleting the Old Emails using Bulk Deletion
If deleting old emails in the system is an option, you can use Bulk Record Deletion under Advanced Settings. Once the emails are deleted, the related attachments will be deleted and that will reduce the size of the Attachment table.
(2) Using Tools to Export Attachments
By using the tools such as Attachment Downloader and Bulk Attachment Manager in XrmToolBox, you can export the attachments files and archive those at some storage with a cheaper option. However, you will still need to delete the downloaded files using another tool or build your own solution for deletion.
(3) Move Attachments to SharePoint
You can automatically move the email attachments to SharePoint document library where the cost of the storage is x10 times cheaper and more features (such as document collaboration, version history, etc.) are available.
Note
Before applying this solution, it is important to review this approach from the security perspective because the security roles of the users in Dataverse does not apply to the permissions of the SharePoint folders. If there are confidential emails in Dataverse which should only be seen by the users with specific role and the attachments are stored in SharePoint, the files will be exposed to any SharePoint user who has access to the folder. In such scenario, third party solutions like CB Replicator are recommended to restrict the access of the SharePoint folder.
Before applying this solution, it is important to review this approach from the security perspective because the security roles of the users in Dataverse does not apply to the permissions of the SharePoint folders. If there are confidential emails in Dataverse which should only be seen by the users with specific role and the attachments are stored in SharePoint, the files will be exposed to any SharePoint user who has access to the folder. In such scenario, third party solutions like CB Replicator are recommended to restrict the access of the SharePoint folder.
In this post, you will learn about how to automatically move attachment files of the emails in Microsoft Dataverse to the SharePoint document library using a cloud flow. In this solution, the files will be just stored in the SharePoint document folder related to the email and the files will be viewed under Documents subgrids instead of the Attachment subgrid.
Tip
If you are looking for a solution to move the email attachments to the SharePoint document folder of the Regarding row (record), check out this blog post by Amey Holden. If you are looking for a solution to move the notes attachments to SharePoint and delete the notes attachments using a cloud flow, Priyesh Wagh got those covered in his blog posts. If you are looking for a solution to move an attachment from file column of Dataverse to SharePoint, check out my previous blog post here.
If you are looking for a solution to move the email attachments to the SharePoint document folder of the Regarding row (record), check out this blog post by Amey Holden. If you are looking for a solution to move the notes attachments to SharePoint and delete the notes attachments using a cloud flow, Priyesh Wagh got those covered in his blog posts. If you are looking for a solution to move an attachment from file column of Dataverse to SharePoint, check out my previous blog post here.
First of all, enable Document Management for the Email table.
The rest of the solution is to build a cloud flow to move the email attachments to SharePoint document library and these are all the steps included in the cloud flow for this solution. The flow will be triggered when the Email is created and the value of the Status Reason column is changed to Completed, Sent, Received or Cancelled.
(statuscode eq 2 or statuscode eq 3 or statuscode eq 4 or statuscode eq 5)] The next step is the List rows action to get the SharePoint site URL (same as the one in the Part 1 post).
The first variable is the array of special characters to be removed from the email subject. The second Email Subject variable is to hold the updated email subject without special characters and the third variable is to hold the value temporarily because Power Automate does not support self-reference of the variable.
createArray('.','@','ß','²','³','µ','`','´','°','^','=','(',')','&','$','§', '~','#','%','*',':','<','>','?','/','|',' ', ' ','{','}','!','+','__','___')] The first loop is to go through each special character in the array and the second loop will run as long as that specific special character is found in the Email Subject variable (i.e. the indexOf the special character in the email subject is not -1)
indexOf(variables('Email Subject'), item()) Once the special character is found in the Email Subject variable, replace the special character with '_' character and set it to temp variable (to avoid self-reference). Then, set the value of temp variable back to Email Subject variable.
replace(variables('Email Subject'),item(),'_') 
toUpper(replace(triggerOutputs()?['body/activityid'], '-', '')) In the action to create the SharePoint folder, the site address is the value from step 1, the library name is custom text 'email' and the folder path is the output of the Compose step.
To move the attachments, retrieve the attachments related to the email and only the following three columns are required (activitymimeattachmentid, filename, body). For each attachment file, create a file in the SharePoint folder under the site address value from step 1, email library and folder path from the output of the Compose step. The file name is from the List Rows query and the attachment body needs to be converted from Base64 to Binary.
first(outputs('List_SharePoint_Site_-_URL')?['body/value'])?['absoluteurl'] After creating a folder in SharePoint, a Document Location needs to be created and linked with the parent Document Location in Microsoft Dataverse to show the related documents in the subgrid on the email form. You can read more details about why and how on the part 1 blog post (3. Create Document Location step). Here are the expressions used.
relativeurl eq 'email' and startswith(parentsiteorlocation_sharepointsite/absoluteurl, 'http') Filter to retrieve the parent Document Location for the Email document library.
first(outputs('List_Document_Location_-_Parent_Document_Location_for_Email')?['body/value'])?['sharepointdocumentlocationid'] Getting GUID of the parent Document Location by getting the first object from the previous List Rows action.
base64ToBinary(items('Apply_to_each_Attachment')?['body']) After creating the file in the SharePoint folder, the original email attachment in Microsoft Dataverse can be deleted.
Summary
By building a cloud flow to automatically move the email attachments from Microsoft Dataverse to the SharePoint document library, file capacity usage of the Dataverse can be reduced.
This was originally posted here.
*This post is locked for comments