Automatically Create SharePoint Document Location for Dataverse/Dynamics 365 Using Cloud Flow (Part 1 of 8)

This is the first post in a series of Automating SharePoint Integration with Dataverse using Power Automate. You can check out the other posts via these links (Part 2, Part 3, Part 4, Part 5, Part 6, Part7, Part 8)

This post will explain how you can automatically create the SharePoint Document Location using the cloud flow in Power Automate when a new row is added to the Dataverse/Dynamics 365 table.

Microsoft Dataverse supports integration with SharePoint Online and with out-of-the-box functionality, the SharePoint folder and the related Document Location data is created on the fly when the user opens the Documents associated view under the Related tab.

That behaviour is good enough for normal scenarios but sometimes, the SharePoint folder needs to be automatically created as soon as a new row is added to the table. e.g. If there is any automation or integration which uploads the files into the SharePoint document library of the Contact, the SharePoint folder needs to exist. In that case, the out-of-the-box behaviour of SharePoint folder creation is reliable and the integration may attempt to upload the file before the user opens the Documents associated view for the very first time (which creates the SharePoint folder). Instead of adding the logic of SharePoint folder auto-creation to each automation/integration, we can create a cloud flow in Power Automate to automatically create the related SharePoint folder as soon as a new row is added to the table (to make sure that the folder is always ready for other automation/integrations).

Before we jump into creating the cloud flow for this solution, I would like to explain a little bit about how the data in Dataverse/Dynamics 365 is mapped to the URL of the related SharePoint folder. As an example, I will use the Contact table for the following ERD as well as the cloud flow below but it can be applied to any other table with document management enabled.

The SharePoint site URL is stored in the SharePoint Site table and linked to the Document Location table which stores the SharePoint document library name for each table (with logical name). The folder related to the Contact is also stored in the Document Location table and it is linked to the parent Document Location as well as the Contact (or any other table with document management enabled). To replicate the out-of-the-box SharePoint folder creation, we need to create the SharePoint folder under the document library of the table and the Document Location row related to the Contact.

These are all the steps included in the cloud flow for this solution.

1. Trigger and initialise variable
2. Create SharePoint Folder
3. Create Document Location

1. Trigger and initialise variable

The trigger is just on Create of the Contact. The folder name is initialised as a variable since it is being used in a couple of places (and in my actual flow, the value is set in multiple steps too). You can use Compose step as well as filling the same value + expression in multiplaces too (if you want to save API calls). The format of the folder name is «fullname»_«contactid in uppercase without '-'»

toUpper(replace(triggerOutputs()?['body/contactid'], '-', ''))

2. Create SharePoint Folder

The next step is to get the URL of the SharePoint site. This step is important so that the Site Address can be dynamically populated and the cloud flow will work when it is deployed to different environments. The library is the logical name of the table ("contact" for this instance).

🛈 Note

If the library is different for some reason (e.g. having too many folders and the library was split), the relativeurl of the latest Document Location needs to be retrieved and dynamically populated into Library parameter.

This is the expression to populate the SharePoint site URL to the site address. Since there will be only one SharePoint site row, first() can be used to prevent the step to get into the loop.

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

3. Create Document Location

The final step is to create the Document Location and link with the Contact. Before creating the Document Location, its parent Document Location (for the document library) needs to be retrieved. This is the Filter to retrieve the parent Document Location for the Contact document library.

relativeurl eq 'contact' and startswith(parentsiteorlocation_sharepointsite/absoluteurl, 'http')

The additional filter is to make sure to retrieve the Document Location which is linked to the SharePoint Site (in case if there are subfolders with name 'contact').

In the example below, there are multiple Document Locations with relativeurl eq 'contact'.

The first one is the root contact Document Location that we are looking for (which is linked to the SharePoint Site). There can be other Document Locations with relativeurl value 'contact' if the Document Management Settings is configured to create in a folder structure as mentioned in Part 3 of this series. The second condition startswith(parentsiteorlocation_sharepointsite/absoluteurl, 'http') is to filter the only record which is linked to the SharePoint Site (via lookup column parentsiteorlocation but since the lookup is polymorphic lookup, it is postfix with _ + logical name of the SharePoint site table which is _sharepointsite) and check the absoluteurl value of the related SharePoint Site starts with 'http' (as highlighted in the screenshot above). You can read more about filtering based on the value from the related table in this blog post.

🛈 Note

The relativeurl in the filter needs to be updated if the document library is different from the default value.

The last step is to create the Document Location by populating the name, lookup to the parent Document Location, lookup to the Contact and the folder name in the Relative URL. The following is the expression to get GUID of the parent Document Location.

first(outputs('List_Document_Location_-_Parent_Document_Location_for_Contact')?['body/value'])?['sharepointdocumentlocationid']

Summary

By using the Microsoft Dataverse connector and SharePoint connector in Power Automate, the SharePoint Document folder can be automatically created as soon as a row is added to any Dataverse table with document management enabled.