Today integration with SharePoint for Document Management with Dynamics 365 is supported out-of-the-box. When you go through the Document Management configuration for Dynamics 365 you define which tables you want to enable for the integration and it will also automatically create default Document Libraries for each table in your SharePoint Site.
When the configuration has been completed the end user experience will be as follows:
- End user views a record, such as Case.
- The end user uploads a file by navigating to Documents under the Related tab.
- A Dynamics 365 Document Location record (row) is created which represents the folder in SharePoint under the default Document Library of Case.
- The file is uploaded against the Document Location and from the Case record the file can be viewed, as well as browsed to in SharePoint.
An issue you don't know of until it hits...
One of the known SharePoint issues is that
A library (or list) cannot have greater than 50,000 unique security scopes
The Microsoft documentation implies that the recommendation is to have less than 5,000 unique scopes and one of the methods to apply is to split your SharePoint structure in a manner where documents can live under different Document Libraries.
What can I do to manage my Document Libraries?
Well there are many ways but in this WTF episode you'll learn how to manage your Document Libraries using a period structure to split the document libraries based on a date column's year value.
In the Case table I created a new column that represents when the Case was acknowledged. For example any files associated to a Case that was acknowledged/raised/received in the year 2020 will be stored in a Document Library that represents the year - Case 2020. This applies continuously,
- Case 2020
- Case 2019
- Case 2018
- etc...
The logic
Whenever a document is uploaded for a Case, check if a Document Library for the period exists based on the year of the date column's value.
If it does not exist create a new Document Library in SharePoint using the year value and naming convention of "Case - YYYY."
The same needs to be applied to Dynamics 365 where a Document Location record is created. A Dynamics 365 Document Location can represent both a Document Library and a Document Location (folder) for SharePoint.
The document would have initially been uploaded in a Document Location (folder) that lives under the default Document Library of "Case" in SharePoint. This folder needs to be moved to the Document Library that represents the period of the Case. This is done within SharePoint so Dynamics 365 also needs to be updated to point to the new Document Library after the folder has been moved.
The Dynamics 365 Document Location record needs to be updated to point to the Document Library that represents the period so that
- It's not under the default Document Library of "Case"
- The end user experience is not broken if they view the Documents tab from within the Case record and click on the button that directly opens the SharePoint document location in a new browser tab.
With regards to No.2, if the Dynamics 365 Document Location is not updated an error will be encountered by the end user because in SharePoint the folder has moved but the Document Location record in Dynamics 365 is still pointing to the default Document Library where the folder no longer lives.
If you're scratching your head, don't worry - keep reading + watch my vlog and it will make sense 😅
[BTW Ryan Maclean if you're inspecting my diagram and thinking about the lack of thoroughness, I'm sorry! Lol. I know you're into diagrams correctly reflecting a process.]
[BTW Ryan Maclean if you're inspecting my diagram and thinking about the lack of thoroughness, I'm sorry! Lol. I know you're into diagrams correctly reflecting a process.]
Let's Automate
This is what my cloud flow in Power Automate looks like.
Some details before we dive into the steps
- I configured the Dynamics 365 integration with SharePoint by linking it to a SharePoint site in the same tenant.
- The table I am using in my automation that has the Regarding lookup column is Document Location.
- The table of the Regarding object type that will be used in the trigger condition is Case.
- In the Case table I have a custom date column called "Date Acknowledge" which is used to identify the period the Case is associated with so that it is reflected in the Document Library structure. I chose not to use the Created On date field as customers I have worked with in the past can retrospectively enter in a past date when a Case is created in Dynamics 365.
- The naming convention I am using is "Case - YYYY"
The trigger
The Dataverse trigger is "When a row is added" where the table referenced is Document Locations.
Delay
In my vlog I explained the purpose of using the Delay action in the cloud flow. As soon as an end user clicks on the "Upload Document" button within the Case record, a Document Location record is automatically created which is by system design for the Dynamics 365 integration with SharePoint. This Document Location record is created before the document is actually uploaded. The automation in this cloud flow is only applicable when a document is uploaded so we need to give it time to identify a document is uploaded.
The delay I'm using is 2 minutes.
Note: As I write this I now realise that I should have additional logic to handle when a document is not uploaded (end user can choose to cancel the upload within the Dynamics 365 Case record) to terminate the cloud flow. That's something you should factor if you follow the steps in this WTF episode.
The delay I'm using is 2 minutes.
Note: As I write this I now realise that I should have additional logic to handle when a document is not uploaded (end user can choose to cancel the upload within the Dynamics 365 Case record) to terminate the cloud flow. That's something you should factor if you follow the steps in this WTF episode.
Get Case row by ID
This is the Dataverse Get row by ID action. In this action the Case is retrieved through the Regarding object value (remember this is the GUID of the row) and only returning my date column that represents the date the case was acknowledge. This is so that we can extract the year value from the date column to identify the period of the Case and be reflected in the Document Library structure.
Received Year value
This is a Compose action where only the year value is extracted from the Date Acknowledged column of the Case the Document Location is in regards to. The date field is referenced from the previous Dataverse action.
The expression is
formatDateTime(outputs('Get_Case_row_by_ID')?['body/ben_dateacknowledged'], 'yyyy')
List Document Location rows
In this step the Dataverse List rows action is used to retrieve a Dynamics 365 Document Location where the relativeurl column equals the period of the Case. The relativeurl is the Document Library.
Initialize Parent SiteLocation variable
An Initialize variable action is used so that we can set the document location based on
- the Document Location record that represents the Document Library for the period if it already exists. This is from the previous Dataverse List row action
- the Document Location record that represents the newly created Document Library for the period if it does not already exist.
Condition
The condition action is checking whether a record is returned in the Dataverse List Document Location rows action.
The expression used is
length(outputs('List_Document_Location_rows')?['body/value'])
And the operation used is "is equal to" where the value is "0"
If the value is equal to 0 then the cloud flow will proceed to the Yes path where
- A new Document Library is created in SharePoint
- A new Document Location is created in Dataverse
If the value is not equal to 0 then the cloud will proceed to the No path as it means a Document Location exists for the period which will be used downstream in the cloud flow to move the folder from the Default Document Library to the Document Library that represents the period.
If Yes - Send an HTTP request to SharePoint
I couldn't see an action to create a new Document Library in SharePoint using the SharePoint connector but I did find this blog post by Alan which I followed. Cheers Alan! 😃
In this action I'm providing a Description and the using my naming convention of "Case YYYY" for the SharePoint Document Library which references my year value that was extracted from the Date Acknowledged column from the Case table as per my earlier Received Year value Composed action. This is so that a new Document Library in SharePoint is created for the period the folder should live in instead of the default Document Library.
Add a new Document Location row
In the previous step we created the SharePoint Documentation and now the Dynamics 365 Document Location needs to be created so that it can be used for any documents uploaded against a Case. In this Dataverse Add a new row action I am creating a new Document Location record using the same naming convention as per the previous step where the year value from the Date Acknowledged column is used.
Three more fields that need to be configured for the Dataverse action is the
Three more fields that need to be configured for the Dataverse action is the
- Location Type
- Parent Site or Location field
- Now I have explicitly referenced the GUID of the SharePoint Site record in Dynamics 365, best practice is to use an environment variable but that's something I am not covering in this WTF episode. For purpose of learning I am explicitly reference the GUID.
- Relative URL
- The relative URL is set using the same naming convention. This is important as this points to the Document Library and is used whenever a new Document Location is created that needs to refer to the Document Library that represents the period. In other words, this will be what is used in the "No" path of the condition.
Set Parent SiteLocation variable
In this Set variable action the GUID of the newly created SharePoint Document Location is to define the Parent SiteLocation variable value in the earlier step of Initialize Parent SiteLocation variable.
If No - Set Parent SiteLocation variable 2
In this Set variable action the GUID of the SharePoint Document Location is retrieved from the earlier step that uses the Dataverse List rows action -List Document Location rows. A Document Location exists for the period and the GUID will be used to define the Parent SiteLocation variable value in the earlier step of Initialize Parent SiteLocation variable.
Move folder
In this SharePoint action we are moving the folder in SharePoint from the default library to the Document Library that represents the period of the Case. The relative url value from the trigger is referenced in the Folder to Move field by using the expression of
/incident/triggerOutputs()?['body/relativeurl']
The Destination Folder represents where the folder should be moved to which will be the Document Library that represents the period so again, the year value from the Date Acknowledged column in the Case table is referenced. The expression is
/Case outputs('Received_Year_value')
Upload Document Locations for Case
In this Dataverse action the Document Location that was originally created from when the end user clicked on the "Upload document" button in the ribbon within the Document tab in Dynamics 365 needs to be updated to point to the correct Document Library that represents the period.
This is where the Parent SiteLocation variable is referenced to set the GUID of the Document Library based on the Yes or No path taken.
Cloud flow in action
Time to give this automation a whirl. When a document is uploaded where the Regarding is Case and it is for a period that does not already exist as a Document Library in SharePoint, the Yes path will be processed.
When a document is uploaded where the Regarding is Case and it is for a period that does exist as a Document Library in SharePoint, the No path will be processed.
An alternative to Power Automate
I shared in my vlog that there is an ISV provider called Connecting Software that provides an add-on to one of their products to resolve the unique permissions issue I mentioned earlier. This product is called SharePoint Structure Creator, it will automatically manage your Document Libraries in SharePoint whenever a file is uploaded from Dynamics 365 into SharePoint.
I came to know of Connecting Software as one of the customers I work with uses their CB SharePoint Permissions Replicator product and they provide great support. I had a good support experience with them, the person was awesome.
It is important to note that the SharePoint Structure Creator is an add-on to the CB SharePoint Permissions Replicator.
I also want to make it clear this is not a sponsored blog post by Connecting Software, this is me simply sharing that this is an alternative as what I share in this WTF episode is limited to a single table whereas their add-on product can provide automation across all tables through simple configuration.
I also want to make it clear this is not a sponsored blog post by Connecting Software, this is me simply sharing that this is an alternative as what I share in this WTF episode is limited to a single table whereas their add-on product can provide automation across all tables through simple configuration.
Summary
Structuring Document Libraries for a table in Dynamics 365 can be achieved using a cloud flow in Power Automate. It's a matter of using a date column that can be used to determined the period the Case is associated to and checking if a Document Library in SharePoint exists for the period.
I hope you found this useful and learnt something from this WTF episode.
Shout out to...
My former work bestie Rex 🦖 for helping me out with my cloud flow in a couple of areas that needed to be refined. Miss you my friend.
Another SharePoint + Dynamics 365 + Power Automate blog post
If you want to learn other methods that can be applied to SharePoint integration with Dynamics 365, I recommend checking out this blog post by Ryan Maclean. Ryan shares how you can configure SharePoint differently to the default set up that is defined by Dynamics 365 using Power Automate.
No comments:
Post a Comment