Automatically Create SharePoint Document Location for Dataverse/Dynamics 365 Using Cloud Flow (Part 1 of 8)
Published Sunday, February 20, 2022
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.
These are all the steps included in the cloud flow for this solution.
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'], '-', ''))
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.
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']
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'.
🛈 Note
The relativeurl in the filter needs to be updated if the document library is different from the default value.
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.
