Now Reading: Automatically Move Dataverse Email Attachments to SharePoint Document Library Using Cloud Flow (Part 6 of 8)
-
01
Automatically Move Dataverse Email Attachments to SharePoint Document Library Using Cloud Flow (Part 6 of 8)
Automatically Move Dataverse Email Attachments to SharePoint Document Library Using Cloud Flow (Part 6 of 8)
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)
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.
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.
attachments will be deleted and that will reduce the size of the Attachment
table.
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.
library where the cost of the storage is x10 times cheaper and more features
(such as document collaboration, version history, etc.) are available.
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.
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.
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.
After that, add the Documents subgrid to the Email form.
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.
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)
as the one in
the Part 1 post).
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.
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('.','@','ß','²','³','µ','`','´','°','^','=','(',')','&','$','§', '~','#','%','*',':','<','>','?','/','|',' ', ' ','{','}','!','+','__','___')
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())
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(),'_')
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'], '-', ''))
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']
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')
library.
first(outputs('List_Document_Location_-_Parent_Document_Location_for_Email')?['body/value'])?['sharepointdocumentlocationid']
object from the previous List Rows action.
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'])
attachment in Microsoft Dataverse can be deleted.
- Create a separate Instant Flow to loop through all the emails and apply the actions outlined in Steps 2-5.
- 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
from Microsoft Dataverse to the SharePoint document library, file
capacity usage of the Dataverse can be reduced.
Original Post http://linnzawwin.blogspot.com/2022/02/automatically-move-dataverse-email.html
