Move Attachment from File Field of Microsoft Dataverse to SharePoint in Power Automate (Part 1 of 3)

This is the first post in a series of File field as input and SharePoint as storage. You can check out the other posts via these links (Part 2, Part 3)

What is a File field and how you can use it

Microsoft has added the File and Image datatypes to Microsoft Dataverse last year which was only available in canvas apps first, and then was later on made available in the model-driven app with 2020 Release Wave 1. If you want to know more details about adding the file datatype field into a custom entity, check out this 2 minute Tuesday video by Gus Gonzalez. In that video, Gus briefly explained the point of using the file field to store a document as opposed to storing the file using the Notes entity or the SharePoint document library related to the entity. Jukka Niiranen also wrote a blog post on why would you store images and files in CDS. If the file is uploaded as a Note attachment or SharePoint document, it will be a challenge for the system to verify if the necessary files exists unless the files are stored with the exact name as specified in the system. By storing the files as a field level, we can specify what type/category of file to expect for each file field and the system can validate whether a file is uploaded or not. This enforces the user to upload specific required files before proceeding further in the process. Here are a couple of sample scenarios where creating a file field would be a better fit.

Scenario 1: A Case Officer needs to upload the identity document in the Verification step of the Business Process Flow before the application can proceed to the Assessment stage.

Scenario 2: Once all documents specified in the checklist are uploaded against the case, the case should automatically be routed to the Manager for approval.

File field as input and SharePoint as storage

Even though the file field is a perfect fit for your requirements, there may be times where the files should be stored in the SharePoint document library for a few reasons – that could be because the capacity of your environment is not aligned with the volume of the files, or collaboration & document versioning is required for document processing. In those cases, you can have the best of both worlds by the following solution which involves a file field, a single line of text with URL format type (to store the SharePoint document URL) and one automated flow to move the file. This solution allows the documents to be uploaded using the field in the model-driven app and the system can validate the existence of the file based on the URL field. (You can find out how the whole solution works in the Part 2 blog post.)

In this blog post, I will focus on the automated flow which moves the attachment from the file field to the SharePoint document folder related to the record. Here is the summary of the steps:

1. Trigger When Contact is updated
2. Get Contact record for file information
3. Get file content of Contact
4. Initialize variables for SharePoint Site and SharePoint folder
5. Get Document Location of the Contact record (create new if there is none)
6. Create the file to Contact SharePoint Document folder
7. Update Contact to populate document file URL
8. Delete the file attached to file field

When doing the above steps, you will also learn how to achieve the following using the flow in Power Automate:

• get the content of the attachment in the file field
• get Document Location of the record and create new if there is none
• create a file in SharePoint with Base64 content
• clear the value of a file field of the record

1. Trigger When Contact is updated

The flow will trigger only when the file field ("lzw_identitydocumentfile" in my sample flow) is updated. The flow expression can be added to make sure the flow is only triggered when there is a value in the file field. Currently, there is a bug with Microsoft Dataverse trigger in my tenant and Filter Expression cannot be used (which shows an error "There's a problem with the flow's trigger"). In that case, the alternative solution would be checking the value of the file field using Condition control.

This step needs to be after Get a record action in step 2 because file field value is not returned as the output in the body of the trigger. If there is no value in the file field, the flow can be terminated.

2. Get Contact record for file information

This step is required to get the GUID and the name of the file field because the output of the trigger step does not return the file field data. This step will return the information of the file but it will not return the actual content of the file.

3. Get file content of Contact

To get the actual content of the file attached to the file field, we need to use Get file or image content action with entity name, field name and the GUID of the record.

4. Initialize variables for SharePoint Site and SharePoint folder

This step is to get the absolute URL of the default SharePoint site configured in the SharePoint integration and store it in "SharePoint Site" variable for future use. Another variable "SharePoint Document Folder" is also initialised to populate with the document location of the record.

5. Get Document Location of the Contact record (create new if there is none)

The purpose for all these steps in the screenshot above is to get the Document Location of the Contact record and create a new one if there is no existing Document Location for the current record. You can read more details about it in my previous blog post about Upload SharePoint Documents for Dynamics 365/Microsoft Dataverse Rows Using Flow. In summary,

• get the existing Document Location records of the Contact record using List records action
• if there is any record, set the relative URL in the "SharePoint Document Folder" variable
• if there is no record found, set the "SharePoint Document Folder" variable with record name and GUID. If there is no "Enter custom value" option to use the variable for the Site Address, disable the Power Automate Experimental Features to revert back to the classic expression builder.
• create a new SharePoint folder in the site using the value of "SharePoint Document Folder" variable
• get the parent Document Location for Contact entity using List rows action (as in the step below). If you want to populate the parent Document Location for the different table (entity), replace 'contact' with the appropriate logical name in the "Filter rows" parameter.
• 
  
  
• create the Document Location for the record and populate with newly created SharePoint folder, parent Document Location from the step above and regarding lookup to the Contact record

6. Create the file to Contact SharePoint Document folder

Once we have the Document Location of the Contact record, create the file in the SharePoint folder with the file name from step 2 and the file content from step 3 using Create file action. If there is no "Enter custom value" option to use the variable for the Site Address, disable the Power Automate Experimental Features to revert back to the classic expression builder. In the File Content field, base64toBinary() function needs to be used because the field content from the Get file or image content action returns in Base64 format and the SharePoint connector expects in binary format.

7. Update Contact to populate document file URL

Once the file is created in SharePoint, update the URL field on the contact with "SharePoint Site" variable + Path (output from the Create file action).

8. Delete the file attached to file field

The File column of the Microsoft Dataverse row cannot be cleared by simply setting null in the Update a record action above. It needs to be done using DeleteFile action using Perform an unbound action.

Summary

Using the file data type field gives you more control on file validation in your solution and by using the Microsoft Dataverse connector and SharePoint connector in Power Automate, the file uploaded to the 'file' column of Microsoft Dataverse can be moved to SharePoint document library and removed from Microsoft Dataverse.

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