Improve the Dynamics to SharePoint Integration user experience a little bit more

The dataverse model-driven or customer engagement (Dynamics 365) app to SharePoint ‘integration’ has been a little theme of mine recently. Previous posts have explored adding metadata during document upload and synchronising Dataverse name changes with the related SharePoint folder.

But wait, there’s more! There are many small yet of significant positive impact customisations that I make for most of my customers using this the Dataverse to SharePoint Integration, without getting knee deep in custom pages:

1. Add a link to the form which opens the relevant SharePoint folder

2. Remove the GUID after the folder name

3. Synchronise any name changes with the SharePoint folder (as seen in my previous post)

Note: When it comes to anything SharePoint related with Power Automate I use an environment variable for the SharePoint Site address so its a clean move to another environment and therefore a different SharePoint site.

Add a link to the row (record) direct to the SharePoint folder

My client suggested it might help to add a link onto the form which goes directly to the relevant SharePoint folder. It sounds too simple, honestly I was sceptical but it turns out to be a) relatively simple to achieve and b) really handy for users and makers alike!

Hidden buttons and multiple clicks versus right there on the form and a single click - I know which one I prefer!

Here is how to create the SharePoint folder URL for each record. In this case I have set up a ‘Based on entity’ folder structure so we have Accounts, then inside the Account folder, there is another folder that contains opportunities for that Account only. SO getting the URL for each of these is a little different. but the trigger is always the same.

It all starts with a Document Location

Change type: Added or Modified

Table: Document Location

Select columns: relativeURL

Filter rows: (statecode eq 0 and locationtype eq 0 and servicetype eq 0)

What is the Document Location ‘Regarding’?

Depending on whether you are looking for an opportunity URL or an Account URL you will set the condition accordingly (or maybe use a switch statement). When identifying the table it is ‘Regarding’ you need to use the plural name for the table so in this case ‘accounts’ or ‘opportunities’.

How deep is your folder?

If you are going for the URL of a child fodler (such as Opportunity) then you will need an extra step here to get the Parent Document Location, and also expand to get the relative URL of it’s parent record too.

Table: Document Location

Row ID: Parent Site or Location from the trigger step

Expand Query: parentsiteorlocation_sharepointdocumentlocation($select=relativeurl)

Formulate the SharePoint URL

Now we have all the pieces of the puzzle we need, just need to get them in the right order!

Account Folder URL:
SharePoint SiteAddress
/account
/outputs('Get_Parent_Doc_Loc')?['body/parentsiteorlocation_sharepointdocumentlocation/relativeurl']

Opportunity Folder URL: The same as above, plus
/outputs('Get_Parent_Doc_Loc')?['body/relativeurl']
/triggerOutputs()?['body/relativeurl']

Now you have the URL you can write that back to a field on the relevant record where the user can easily access to open the SharePoint Folder

Note: I did ‘hard code’ the word ‘account’ here, I know my folders are always going to be at account level and below but we could also retrieve that by getting the parent of the parent using an embedded expand for extra credits.

Remove GUID from SharePoint folder name

This comes with a health warning which I must first preach: there is a valid reason why the SharePoint folders get created with the record name followed by the record GUID - to make sure the folder name is unique and you don’t end up with multiple records pointing into the same folder. I prefer to handle this in Dynamics with duplicate detection and good data management practices. Therefore we can safely remove the glaringly ugly and confusing GUIDs from the folder names.

Of course I never actually work things out myself, I’m just here with the inspirational images, so here is a blog from Shaun Wilkinson in 2014. Yes, it’s still the exact same process 8 years later. But the link to download the OrgDbOrgSettings has changed, you can find it here courtesy of the wonder Sean McNellis. Updating this OrgDB setting ‘CreateSPFoldersUsingNameandGuid‘ can be done via the OrgDbOrgSettings managed solution as shown in the blog, via code (don’t ask me how) or also via a lovely tool in XrmToolBox called ‘D365 Org DB Settings’ (thanks Carl Cookson!).

Small changes with significant results - your clients and users will thank you. Well, maybe they won’t, but they would if they knew.

Questions, comments and dad jokes always welcome —->