Automatically Move Dataverse Email Attachments to SharePoint Document Library Using Cloud Flow (Part 6 of 8)

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, Part7, Part 8)

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.

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.

First of all, enable Document Management for the Email table.

After that, add the Documents subgrid to the Email form.

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.

1. Trigger and Get SharePoint Site URL
2. Remove Invalid Characters from Folder Name
3. Create SharePoint Folder for Email
4. Create Document Location
5. Move Attachments to SharePoint

🔗 1. Trigger and Get SharePoint Site URL

The flow will be triggered on create of an Email row (e.g. when the email is created with "Received" Status Reason) and when the Status Reason is updated (e.g. when the draft email is changed to "Pending Send" and then, "Sent"). The value in Filter rows means the flow will be triggered only when the value of the Status Reason column is 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).

🔗 2. Remove Invalid Characters from Folder Name

Normally the SharePoint folder name set by the out-of-the-box SharePoint integration is the combination of the primary name value of the row and the GUID of the row. For this case, the primary name value would be the email subject and it may contain invalid characters which are not accepted as the folder name in SharePoint. That is the reason why special characters need to be removed before setting the email subject as a SharePoint folder name. To remove the special characters, I used the approach mentioned in this blog post by Fredrik Engseth and modified it a bit.

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(),'_')

🔗 3. Create SharePoint Folder for Email

The SharePoint folder name for the email will be used in multiple places of the flow so that a Compose step is created to store the Folder Name. The folder name will be a combination of email subject without special characters and the GUID of the email row without '-' (removed by using the following expression).

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.

first(outputs('List_SharePoint_Site_-_URL')?['body/value'])?['absoluteurl']

🔗 4. Create Document Location

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.

🔗 5. Move Attachments to SharePoint

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.

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. 

Moving Attachment from Existing Emails

For historical emails, you can either

1. Create a separate Instant Flow to loop through all the emails and apply the actions outlined in Steps 2-5.
2. Add a custom flag to the emails, update your flow to trigger based on this new flag, and then use the Bulk Data Updater tool to set the flag, as described in the blog post below.
   https://linnzawwin.blogspot.com/2021/09/trigger-oncreate-cloud-flow-for.html

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.