How to move Note attachments in Microsoft Dynamics 365 CRM into SharePoint

We had a request to implement some functionality to move attachments added to Notes into SharePoint. On the customer’s system, a lot of Notes (with attachments) had been added over the years and it was really hitting the storage costs hard. The per gigabytes per month cost of storing data in CRM compared to SharePoint is significantly higher which, depending on when you read this, could be anything around 10 to 20 times more expensive. Anyway, after a bit of research, it turns out there were quite a few blogs about this… so here’s another one!

Getting started – how to move files from Notes in Microsoft Dynamics 365 CRM into SharePoint

So this should be everything we need; we have the file (attached to the Note), along with its filename, and we know where we want to put it. Sounds simple.

First of all, we need to pick a tool to do this and, without question, it will be Microsoft Power Automate from the Microsoft Power Platform. Let’s get started.

When we’re in Power Automate, we need a trigger, and it’s got to be the creation of a Note.

The next thing that I would suggest it needs is a break clause to stop this running if there’s not actually a file attached. We have a few options here (if the filename is blank, if the Is Document field is No, if the file size doesn’t contain data), but I picked the file size option for this example.

So there’s nothing in the ‘Yes’ option, and the Flow will continue, but the Flow will stop and do nothing else on the' No' side.

On the Note, we’ve got the file itself along with the filename; we need a location to move it to. This location is made up of the SharePoint site address and the folder structure. The site address is selected, so it’s just a case of getting the folder sorted. This will be stored in the RelativeURL field in the Document Location table. To get the record we need (and there should only be one), we have to look for the Regarding Object field to match the Regarding Object of the Note.

To get this information, we need to use the List Rows feature and filter the results by the Regarding Object.

As with all List operations, we then use the ‘Apply to each’ to go through these records, although there should only be one returned.

So the folder is made up of the table’s name and the data in the RelativeURL field. The table’s name can come from the Object Type Code attached to the Note. This brought up another issue – what if the Note is not linked to a record. Well, for our use, there shouldn’t have been too many records like this, so we added another condition to perform a stop check (similar to the one that checks the file size) that checks if there’s data in the Regarding field.

We then get to create the file. The site address is selected from the drop-down – very simple. As you can see below, I used a variable to put the folder structure together. The Filename is taken directly from the Note’s filename. Lastly, the file contents were the DocumentBody field of the Note, but to move them to SharePoint, they need to be converted using the ‘base64ToBinary’ expression.

Finally, we need to remove the file from the Note. This is done with a simple Update step to set Is Document to No and put a null in the DocumentBody and Filename fields – so that’s the file is gone from the Note. I also added some tests to the Description to say ‘File moved to SharePoint’ to make it clear that this has happened.

This was tested, and, hurrah, it works… up to a point. The first couple of times it ran was against custom entities, and I saw the contents of the Note update, and the file appears in the SharePoint location.

However, when I tested it against a Case and an Opportunity, I saw the Flow run and the file disappear from the Note, but nothing appeared in the SharePoint file location. After a bit of investigation, I found that the SharePoint location for these tables isn’t following the same rules as the custom entities, as the SharePoint structure in Dynamics is set to be based on entity. This means it’s creating more folders for it to be put in, So for the custom entities, the structure is:

<Site Address>/<Custom Table name>/<Unique Folder>/<File Name>

However, if we pick the Opportunity table, it’s using the following structure:

<Site Address>/<Account>/<Unique Account Folder>/<Opportunity>/<Unique Opportunity Folder>/<File Name>

To solve this, we just did another List Rows, which gets the two folders for the linked Account and used these along with the other List Rows to get the full folder structure. This did work, but we had to add more conditions based on the table linked to the Note, so it started to get a bit complicated. The other option was not to select the ‘Based on entity’ option when setting up SharePoint.

So that was about it for getting this sorted, but there was one more gotcha.

Dynamics does not create the SharePoint folder by default; it only does it when a user clicks in the Related section and then on the Documents link. To get around this, there’s a couple of options: either get a process to create this folder and corresponding Document Location record (which is quite involved) or add a subgrid to the form which accesses the SharePoint documents (which is considerably easier).

In conclusion, this functionality, which seemed fairly straightforward at first, got ever more complicated as we went through, but we have managed to solve this. However, when you consider that storage in Dynamics 365, on a per GB per month basis, is over 10x more expensive for Dynamics than it is for SharePoint, it is worth doing in the long run to save these storage costs.
For more information or guidance around Microsoft Power Automate or any other tools in the Power Platform, please get in touch with your Account Manager. Or, why not sign up to our free and virtual workshop, which will explore Power Automate in more detail: https://www.tecman.co.uk/events-and-training/workshops