20 July 2021

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

Dropped in Power Platform

Jon
Formulated by Jon

We were recently asked whether it was possible to move attachments added to Notes into SharePoint and whether additional functionality needed to be implemented before doing so. After a bit of research, it was obvious that this question had been flagged multiple times before, but every article I read seemed to leave out one key point… What if you wanted to move the files to the SharePoint location referred to in the Document link? So, I thought I’d cover it here today for you.

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

 

Get in touch with one of the UK's best Microsoft Dynamics partner

Start your journey towards excellence with Microsoft Dynamics 365

Let us know your details and we'll get in touch with you to learn how we can transform your business processes and operations with Microsoft Dynamics 365 Business Central & Dynamics 365 Customer Engagement:

*

*

*

*

*

*

Protected by GDPR. We'll never share your personal data.

0
Years established
0
Happy customers
0
Projects
0
People supported
Brandauer
Pipers Crisps Co
Aden + Anais
Costa Coffee
Charlton & Jenrick
World Animal Protection

Get in touch

Wolverhampton

St Mark's Church
St Mark's Road
Wolverhampton WV3 0QH

Newcastle

19 Kingsway House
Kingsway
Team Valley
Gateshead NE11 0HW

Call: 01902 578 300
Skype: Call now

Co. Reg. 03100541
VAT No. GB559725692

Follow us

If you want even more, you can stalk us on any of our social media platforms: