In a previous post I showed how you can use a custom page to improve the user experience significantly when using the dataverse model-driven or customer engagement (Dynamics 365) app to SharePoint ‘integration’. This is something which I truly expected the integration to do out of the box, but it doesn’t. It doesn’t cause a problem at the time but later down the track it has bitten me and many other too I’m sure! SO here goes another ‘Make the SharePoint to Dynamics 365 integration a bit more bearable’ post.

An Account record is created with the name Amey Holden Pty Ltd - the SharePoint Folder and Document Location record are created also called ‘Amey Holden Pty Ltd’ - all good (for now)

Three years later the company changes it’s name to Spadey Mc Spade Face Pty Ltd - the user updates the Account name in Dataverse, but the SharePoint foler and Document Location are still called ‘Amey Holden Pty Ltd’. Dataverse is confused, the user is confused, and I am severely triggered by this data disarray. With a little bit of Power Automate magic we can automatically synchronise any record name changes with the SharePoint folder and Document Location, because that’s what it SHOULD do!

Synchronise row name updates to SharePoint folder names

How to make the magic happen? First I need you to pretend that every time you see the word ‘Vendor’ it means ‘Account’.

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.

The row name has been updated

Anytime the name of the row changes, we want to reflect those changes in SharePoint. Ensure you use Select Columns so the flow only runs on the name change and nothing else.

Find the Document Location

First we need to find the Document Location, as this gives us the key to the SharePoint folder (and we will also need to update this later too.
**Action**: List rows
**Name:** GetDocLocForVendor
**Table**: Document Location
**Filter** **rows**: _regardingobjectid_value eq *RecordID*
**Row** **count**: 1

Did you find the Document Location?

We need to check if the action above (GetDocLocForVendor) has found anything. If the Document Location was not found - you have got bigger problems, do not pass go, go create it - this blog might help you.

empty(body('GetDocLocForVendor')?['value'])

Clean up your forbidden characters

That sounds like a name for a new Netflix series doesn’t it?
Anyway SharePoint is a delicate little flower and doesn’t like some special characters being used in its folder name so these need to be stripped out of the new record name like so.

replace(replace(replace(replace(replace(concat(triggerOutputs()?['body/name'],' - ',triggerOutputs()?['body/tc_id']),':',''),'''',''),'/',''),'&',''),'.','')

Get the SharePoint folder

So we are going to use the ‘Send a HTTP Request to SharePoint’ action which scares me silly, but trust me it doesn’t bite.

Site Address: e.g. https://amey.sharepoint.com/sites/D365
Method: GET
URI: _api/web/GetFolderByServerRelativeUrl('/sites/D365/account/Amey Holden Pty Ltd')/ListItemAllFields
Headers:
Accept - application/json;odata=verbose
Content-Type - application/json;odata=verbose

In the URI we need to dynamically set Amey Holden Pty Ltd and D365 as these will change per record/environment

D365 - this is the text after the last ‘/’ of your SharePoint URL
last(split(parameters('SharepointSiteAddress (tc_SharepointSiteAddress)'), '/'))

Amey Holden Pty Ltd - this is the relative URL which you can get from the GetDocLocForVendor action earlier (but using first() so you don’t get stuck in a loopy loop)
first(outputs('GetDocLocForVendor')?['body/value'])?['relativeurl']

Prepare some handy items for later

You could set these up directly in the next HTTP request action but it can be a bit temperamental and helps with tracing any failures in the flows later. Parallel action here is optional, it probably saves 0.00004 milliseconds but it looks pretty smart too.

ETag: outputs('GetSharePointFolder')?['body/d/__metadata/etag']
Type: outputs('GetSharePointFolder')?['body/d/__metadata/type']

Update the SharePoint Folder Name

Another HTTP Request, the areas highlighted below are reused from above (Site Address, URI and the First two Headers)

Site Address: same as previous HTTP Request action
Method: POST
URI: same as previous HTTP Request action
Headers: same as previous HTTP Request action +
If-Match - ETag
X-HTTP-Method - MERGE
Body:{"__metadata": {"type": "TYPE"},"FileLeafRef": "NEWFOLDERNAME"}

NEWFOLDERNAME in this case would be the accounts new name - ‘Spadey Mc Spade Face Pty Ltd’

Update the Document Location Relative URL

Now we need to update the Document Location relative URL to match the new SharePoint folder name 9This is what ties the SharePoint folder and the Account record together). We know which Document Location to update because we found it earlier on in the list rows ‘GetDocLocForVendor’ action. Again using first() to avoid loopy loops.
first(outputs('GetDocLocForVendor')?['body/value'])?['sharepointdocumentlocationid']

Don’t forget to update the SharePoint link (optional)

In case you have added the SharePoint folder link anywhere, make sure you go and update it accordingly too!

And the data dilemma crisis was averted for another day

Credits to Pieter Veenstra for this blog on how to rename SharePoint folders, it helped me a lot to make this magic happen! Comments, questions or anything else always welcome, more rants about tidying up the SharePoint to Dynamics 365 integration coming soon…

Synchronise SharePoint folder name with row name in Dataverse or Dynamics 365