Dynamics Sharepoint Integration uses Wrong Folder

03/12/2020 Ryan Perry Comments 0 Comment

The Problem

When the Dynamics CRM Sharepoint integration is set to create document locations under a parent record (IE under the parent account), it stops creating them under the parent record, and instead creates them within a generic folder for the entity.

Reference Material

Set up model-driven apps in Dynamics 365 to use SharePoint Online
Manage your documents using SharePoint
Enable SharePoint document management for specific entities
Dynamics 365 SharePoint integration for custom entity – failing
Document folder location for multiple lookups

Microsoft indicates with the integration, documents are “seamlessly stored in sharepoint.” For the most part, this is true, and it can even be enabled for custom entities, but there are limitations. By default, an admin can set the integration to store documents either by record name, under a single folder for that entity type, or in a heirarchy, under a related record, such as an account, or contact. While any different, or custom folder structure (ie creating sub folders) requires a custom integration, for many businesses, this basic integration is fine – until suddenly, it breaks. They realize that documents on a custom entity are no longer being filed under the parent record.

The Helpful Bit

This behavior is caused due to ambiguity when the custom entity has multiple lookups pointing to the same parent entity. IE, when you create a second account lookup field, the integration no longer knows which relationship to use. Standard entities, such as the opportunity may not be affected as the relationship appears to be hard-coded in the integration, but for custom entities, when an additional lookup field is added, the integration reverts to filing documents under the generic entity folder, ignoring the original relationship.

Fixing the Integration

You have a few options:

Remove additional lookups: Removing additional lookups will resolve the issue. This may not always be feasible, depending on business requirements, but if it can be done, and quickly, do it.

Custom Sharepoint Integration via Power Automate: Not only can this resolve the issue, it will also enable you to:

Create a custom folder structure of your choosing.
Trigger sharepoint folder creation immediately, without requiring the user to manually trigger it by visiting the records document location. This is helpful if any other integrations need to access document locations very soon after record creation.

The other advatange of this is is that it requires very little or no code, but you should be aware that you will need to create both the document location records in dynamics and the folders in sharepoint.

Custom Sharepoint Integration via Plugin: This will require more effort. It is worth noting that attempting to trigger the underlying function used by the standard integration to create the document location didn’t pan out in my case. If you go this route, your developer must have knowledge of both Dynamics and Sharepoint.

What about the Data?

Once you have your integration fixed, either by eliminating the ambiguous lookup references, or by building a more powerful, custom integration, you may wish to move the existing data stored in the incorrect sharepoint folders. The problem is twofold: 1) moving the data to the correct folders and 2) fixing all of the Dynamics document location records pointing to them.

This can potentially be a bigger project than fixing the integration. There are 3rd party tools that can do it, and it may well be worth the investment, but if you are meticulous about the details, here is a high level overview of the steps you can take to do it manually:

Backup your system.
Create a view of all affected document location records. Export this Excel. Note, it is a heirarchy, including both branch and leaf locations.
Create new doclocs by either updating the exported doclocs in excel to match the intended structure and submitting as an import job, or using your new integration flow.
For each leaf docloc exported, generate a column showing the current docloc URL and the correct docloc URL. Then add a 3rd column to create create a PowerShell command to move the content.
Copy + Paste this column of comands into PowerShell to execute the migration. I recommend starting with just one to confirm it works as expected, before running the full list.
Confirm the documents are in the correct location and delete the old doclocs. Confirm the doclocs in Dynamics are still functioning correctly (remember, it’s a heirarchy).

Conclusion

The problem is caused by multiple lookups to the parent entity. If it is caught quickly, and the newly created additional lookup can be removed, it can be promptly addressed with minimal impact. If the additional lookups can’t be removed, or if many erroneous document locations have been created, a custom integration and data migration will take some work, but provide additional benefits, such as a more specfic folder structure and immediate document location creation.

I experienced this in 2017. Microsoft indicated they were aware of the issue and that the dev team had it on their patch backlog, but as of March, 2020, it appears unresolved. However, Microsoft has clarified this limitation: Document folder location for multiple lookups

Feel free to reach out with comments or questions.