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 4Part 5, Part 6, Part7Part 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.

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.

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']


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']


💡 Tip

If you need to trigger this flow for for multiple tables to automatically create SharePoint document locations, you can refer to the child flow approach as Sarah Bennett commented below. You can create this as a child flow that can be called for different table types (as opposed to being triggered by the creation of one specific table). For the main flow, you can pass record type, record id, recordname, record's EntitySetName, library display name) as parameters and use the same flow for different table types. Then, you can use IF statements to set the Regarding field in the final step to add doc location record. If any of those tables are set to create the subfolder based on a specific Account/Contact, you need to handle the logic in your child flow to handle it as mentioned in this blog post.

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.

Comments

  1. I'm not getting a Regarding (Contacts) option in the last step but everything up to has been fine.

    ReplyDelete
    Replies
    1. Regarding (Contacts) is setting the Contact lookup column value of the Document Location just to specify that this particular Document Location with SharePoint URL is linked to this Contact. Only then, when the user opens the Documents associated view on the Contact, the system would show the documents from that SharePoint folder.

      Delete
    2. Sorry Linn, I meant that the Regarding (Contacts) isn't showing up as a field for in the flow. But I am seeing all the other Regarding options.

      Delete
    3. So sorry. Figured it out. I didn't have the Contact Folder made in Sharepoint. That seems like it would be kinda crucial to this whole thing LOL.

      Delete
    4. Cool, Nathan. I'm glad it all worked out finally.

      Delete
  2. Hello Linn, thank you very much for your post! Does this will also move Case email attachments do Contact sharepoint Document?

    ReplyDelete
    Replies
    1. Yes, it can also be done with the cloud flows and I will cover that scenario in my #6 post of this series.

      Delete
  3. I am having trouble understanding this statement:
    relativeurl eq 'contact' and startswith(parentsiteorlocation_sharepointsite/absoluteurl, 'http')

    I understand what startswith does after looking at this documentation:
    https://docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#startswith

    This is the part I don't get:
    parentsiteorlocation_sharepointsite/absoluteurl

    hope that makes sense.

    ReplyDelete
    Replies
    1. Hi Anthony

      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).


      I also updated that part of my blog post and added more explanation with a screenshot.
      https://linnzawwin.blogspot.com/2021/08/automatically-create-sharepoint.html#example

      Hope it makes sense. Feel free to let me know if you have any question.

      Delete
  4. Hi Linn,

    I am getting this error: "Resource not found for the segment..." for the last step in the regarding row. what can be the issue? I am inserting the 'logical table name'(GUID column)?

    ReplyDelete
    Replies
    1. Hi Yoav,
      It is supposed to be EntitySetName(GUID column) and not logical name. EntitySetName is usually the plural name of the logical name (e.g. contacts, new_applications, etc.)

      Read more details in my other blog post.
      https://linnzawwin.blogspot.com/2019/11/power-automate-how-to-set-lookup-field.html

      Delete
    2. Hi Linn,

      my plural name according to the powerapps is "cr288_hotmelt_units" but this too returns this error...

      Delete
    3. Hi Yoav,
      Please go through the blog post link above on how to find out the EntitySetName using the API or browser extension. Sometimes, it is not straight forward plural name of the table logical name.

      Delete
  5. Can this be leveraged when a Team is created through PowerAutomate?

    Example Use Case:
    1) User logs into Model Driven App
    2) User Creates item in Model Driven App.
    3) When item creates new row, a PowerAutomate runs that creates a Team based on that row. Now has a SharePoint Site. (I already have a Flow for these 3 steps that works on manual run.)
    4) The SharePoint URL needs to be added to the Row as a field. (Cannot find how to do this.)
    5) PowerAuomate then needs to send Custom Email Template as documented here, but the SharePoint URL needs to be the one that was just created. (Also, I cannot get the Custom Email Template to populate the field data properly. It only populates when I send manually through the timeline.)
    6) After the attachment(s) are moved to SharePoint, it needs to be deleted from the Dataverse.

    ReplyDelete
    Replies
    1. 3) If you triggers the step 3 using "When a row is added" trigger of the Dataverse connector (instead of manual run), you will have the SharePoint URL after creating in a SharePoint.
      4) After that, you can update the SharePoint URL back to the row as a field in Dataverse using "Update a row" action.

      5) For sending and email using an Email Template, check out this post. If you saved the data in the field in the step 4, you should be able to populate in the email template.
      https://benitezhere.blogspot.com/2020/03/how-to-send-email-using-email-template-with-power-automate.html

      6) To delete the Notes attachment from the Dataverse, you can following this blog post.
      https://d365demystified.com/2022/01/18/remove-attachments-from-dynamics-365-crm-notes-using-power-automate-dataverse/

      Delete
  6. Hi Linn

    How would this work if using the alternative SharePoint structure (where everything hangs under the account), and where the account Document Location may or may not exist?

    ReplyDelete
    Replies
    1. Please check out the part 3 post for creating folders for related records under account folder.
      https://linnzawwin.blogspot.com/2021/09/automatically-create-sharepoint-folder.html

      You'll also need to set up this automation for Account onCreate to make sure account Document Location exists.

      Delete
  7. Wow Linn... what a great series of articles. To be honest, due to time shortage, I only went through the 8 parts diagonally, but I am very interested because we implemented about 1,5 years ago a similar setup (New Case => cloud flow creating a SharePoint folder). Main reason was to 1.) 'Beautify' the folder names, 2.) use DocumentSets instead of Folders and 3.) but more importantly, push certain case fields to SharePoint Metadata (amongst with a deeplink to the case, allowing users to open the case form in Dynamics directly from the SharePoint Metadata).
    But one thing, up untill today, I was not able to solve: there is a slight delay between dynamics record creation and finalisation of the flow. If a user creates a case and immediatly opens the 'Documents' tab, the default flow kicks in and a default folder is generated. I'll solve this with some Javascript (to only show 'Documents' tab if there is at least one document location linked to the case) but I was wondering: Do you know if it is possible to desactivate the default SharePoint folder creation flow WITHOUT disabling the SharePoint integration entirely?
    Again, thanks for the great tutorials serie! Will most definitly check these out!

    ReplyDelete
    Replies
    1. As far as I know, there's no way to disable the default SharePoint folder creation. Other than the JavaScript solution that you mentioned, you may add a step in your folder auto creation flow to check if there's any existing folder. If there's any existing one, move the files inside that OOB folder into your custom folder and delete the OOB folder.

      Delete
    2. This is because cloud flows are asynchronous and don't run in real time - which means there is a delay between when the record gets created and when the cloud flow runs.

      To make this cloud flow run in real time when a record is created you could use an instant cloud flow with a http request trigger. You could then trigger the workflow using a webhook.

      Delete
  8. Hi Linn, thank you for this information, it is really helpful.

    I have created my Power Automate Flow with the steps to retrieve the parent document location, and to create the new document location, but I am getting this error for the 'create document location' step:

    URL was not parsed due to an ODataUnrecognizedPathException. Resource not found for the segment provided in the URL.

    This is the 'raw input' that shows after the Flow runs:

    "item/parentsiteorlocation_sharepointdocumentlocation@odata.bind": "sharepointdocumentlocations(dc6c3009-3009-ed11-82e6-002248ae4003)",

    I have tried many different syntaxes, such as using forward slashes, but I cannot get past this error.

    sharepointdocumentlocations(GUID)
    sharepointdocumentlocations/GUID
    sharepointdocumentlocations/(GUID)
    /sharepointdocumentlocations/(GUID)

    Any idea on what else I can try?

    Thank you!

    ReplyDelete
    Replies
    1. Well, as often happens, I figure out the solution to my problem after I submitted the issue :)

      I was using the correct syntax for the "Parent Site of Location" field "sharepointdocumentlocations(GUID)", but I also needed to specify the table plural name in the "Regarding" field:

      abc_mytables(GUID)

      Thanks again for all of your awesomely useful posts, Linn!

      Delete
  9. Thank you! Really good and working Information! Keep going!

    ReplyDelete
  10. Could you please help? I"m getting an error when trying to make FolderName a variable or a compose action. I am getting this error: Unable to process template language expressions in action 'Compose_-_FolderName' inputs at line '0' and column '0': 'The template language function 'replace' expects its first parameter 'string' to be a string. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#replace for usage details.'.

    ReplyDelete
    Replies
    1. In the expression of this blog post, I used contactid because my example is being triggered on create of the Contact row.
      triggerOutputs()?['body/contactid']

      If you are triggering the flow for your custom table (e.g. xyz_case), replace it with the GUID column of your table. e.g.
      triggerOutputs()?['body/xyz_caseid']

      Try to Compose the first parameter in your replace function and make sure the value is not null.

      Delete
    2. Hm ok, i was doing mine on Account table so i used triggerOutputs()?['body/accountid'] and tried to compose that to check if its null and it seems to be null... why is that

      Delete
    3. Oh I solved the null problem but now i'm getting this error: Root folder is not found.
      clientRequestId: 06c5871d-6fae-4afc-a51c-568059a8c990
      serviceRequestId: 06c5871d-6fae-4afc-a51c-568059a8c990

      Delete
    4. "Root folder is not found" error could happen when the parameter value in the "List or Library" is incorrect.
      Can you double check the input parameters of the "Create new folder" step that you have getting an error to see if any of the parameters looks different from the actual site/library path?

      Delete
    5. Hello, thanks for the reply. I fixed the root folder not found error but noticed there was a bit of a logic problem somewhere for me. I implemented almost exact but I did for Account table but noticed it keeps creating a new document location for the table each time. But I want it to only do once IF the default folder does not currently exist. However, my flow is doing it multiple times. I am struggling to do some sort of condition to have it check if one already exists and how to have it only have ONE document location which is the default one.

      Delete
    6. What is the trigger of the flow?
      Is the Change type = Added in the trigger as in the screenshot below?

      https://1.bp.blogspot.com/-WfCumVoJ_aE/YS4FQOMmeRI/AAAAAAACDdI/qQdB__x_KZULldZbnYeR83Bb7HpwORoPACLcBGAsYHQ/s618/Trigger%2Band%2Binitialise%2Bvariable.png

      Delete
  11. Hi Linn,

    Many thanks for the great post series - explains a lot, provides insight, and helps very practically! Keep doing what you do!

    I've got a question with regards of naming the document folder for the record. OOTB a combination of human readable content of "Primary Name Column" and the record ID is being used, like e.g. "Rogi_06c5871d6fae4afc-a51c568059a8c990" as folder name for a contact.

    If I am using your approach for various custom entites _and_ want to keep the OOTB behaviour, I need to know the content of primary name column for the variable FolderName in the above. But the "primary name column" may be named differently for each entity.

    Do you know a trick, how to find out the user given name of the "primary name column" in the flow?

    Many thanks,
    Rogi.

    ReplyDelete
    Replies
    1. Hi Rogi

      The flow to create the SharePoint Folder and Document Location is specific for each custom entity, right? In that case, you don't need to get the "primary name column" of that specific custom entity dynamically and you can hardcode it for each entity.

      If you ever need to get the Primary Name Attribute of the entity in flow, you can Invoke an HTTP request action to retrieve Entity Metadata.
      https://www.inogic.com/blog/2021/02/how-to-retrieve-dynamics-365-ce-entity-table-metadata-through-power-automate-flow/

      Delete
    2. Many thanks, Linn! That was a great hint.

      I do want to have a solution which is independent from the entity, hence the "automation" with using Metaadata.

      Finally, I used JS to read the metadata (instead of propsed flow) - essentially the same, but I find it sleaker.

      here is reference for future readers: https://learn.microsoft.com/en-us/power-apps/developer/model-driven-apps/clientapi/reference/xrm-utility/getentitymetadata

      Delete
    3. Thanks for posting back your solution. 😊

      Delete
  12. Hi Linn, I refer to this series of posts frequently. It has been a big help for me. Something I did recently was create this as a child flow that can be called for different entity types (as opposed to being triggered by new contact creation), in which case I pass the entity type, library name and entity set name as parameters (five parameters, record type, record id, recordname, record set name, library display name), so I can use the same flow for different entity types. then I use IF statements to set the Regarding field in the final step to add doc location record.

    ReplyDelete
    Replies
    1. Hi Sarah

      Thanks for sharing the solution with us. Extracting the common logic to the the parameterised child flow would be an excellent solution to trigger the flow for multiple tables.

      If you don't mind, I have added your solution to the post in case if anyone else come across to this post and interested to build something similar for multiple tables.

      Delete
  13. When I should open the Document tab...should be able to see first folder location ..how can I achieve this?

    ReplyDelete
    Replies
    1. What do you mean by first folder location? The folder location which was created earliest if there are multiple ones, is that correct?

      From memory, I understand that the folder location is opened alphabetically and you will have to rename those if you want to open the earliest one.

      Delete
  14. Hi Linn,
    Nice blog, I'm trying to implement this and in the last step (adding a row) I have this error:
    URL was not parsed due to an ODataUnrecognizedPathException. Resource not found for the segment provided in the URL.

    so we have only 3 fields
    Regardind
    site
    url
    what can be the cause.
    thank you

    3 fields Regarding

    ReplyDelete
  15. Digital Transformation in India: A Landscape of Growth
    India's business landscape is undergoing a rapid digital transformation. Companies are adopting digital transformation services to optimize operations, enhance customer experience and gain a competitive edge. These services encompass a wide range of solutions ranging from cloud migration and data analytics to mobile app development and automation.

    ReplyDelete

Post a Comment

Popular Posts