Dynamics 365 Business Central: Manage attachments in SharePoint (Save attachments externally and do not use BC capacity)

Dynamics 365 Business Central

Hi, Readers.
In the previous weeks we discussed how to implement attachment file transfer between Business Central and SharePoint through Graph API.

PS: SharePoint and OneDrive in Microsoft 365 are cloud-based services that help organizations share and manage content, knowledge, and applications to:

  • Empower teamwork
  • Quickly find information
  • Seamlessly collaborate across the organization

In this post, let’s discuss a specific solution, how to manage attachments in SharePoint (Save attachments externally and do not use BC capacity).

On most list pages, cards, and documents, you can attach files on the Attachments tab of the FactBox pane. You can attach any type of file, such as text, image, or video files, to a card, document, or a line on a document. This is very useful.

The attachments that users add to BC will increase the database capacity. This is a matter of course. Attachments are saved in the database using the Media data type.

PS: Using the Media or MediaSet data type provides better performance than using a BLOB data type and is more flexible in its design. With a BLOB data type, each time the media is rendered in the client, it’s retrieved from the SQL database server, which requires extra bandwidth and affects performance. With the Media and MediaSet data types, the client uses media ID to cache the media data, which in turn improves the response time for rendering the media in the user interface. More details: Working With Media on Records

But the capacity of BC is not infinite. By default, Business Central customers can use up to 80 GB of database storage capacity across all their environments (production and sandbox). This limit means that the sum of database capacity usage across all of their environments must not exceed 80 GB.

Apart from the default storage capacity, the customer is entitled to additional storage capacity based on the number of Business Central licenses they own:

License typeAdditional storage (for each license of this type)
Premium3 GB
Essential2 GB
Device1 GB

More details: Announcing: Dynamics 365 Business Central database capacity changes as of July 1, 2021 and Managing capacity

PS: Dynamics 365 Business Central: What happens when the capacity limit is exceeded (Exceeding capacity quota)

So some customers will consider saving attachments externally, and SharePoint is a good choice.

In this post, I will be adding a new external file management module for customers. I will do the following things. (Of course you can modify it in your actual case)

1. Create a new Factbox similar to Doc. Attachment List Factbox to manage SharePoint Documents.

Test video:

2. Create a new SharePoint Attached Documents page that displays details.

3. The pages contains Upload and Delete actions to upload and delete files.

  • When uploading, if the same customer contains files with the same name, an error message will be displayed.
  • Different customers can upload files with the same name, which will overwrite the original file on SharePoint (Therefore, only one file with the same name will be saved on SharePoint)
  • When deleting a file, you also need to check whether other customers are associated with this file. If so, only delete it in BC, and do not delete the file in SharePoint.

4. When inserting data in BC, save the file ID and Web URL in SharePoint (The file ID is the primary key of the file on SharePoint, and the Web URL can open the file directly)

Test video: As long as the file can be opened on the SharePoint Web client, it can be opened.

5. Allows users to modify the file name and the name will be returned to SharePoint (If multiple customers are associated with the same file, they will be modified simultaneously) -> This step also need to update the Web URL

Test video:

6. Since users can delete files on SharePoint side, add a Check action in BC so that if the file is not found in SharePoint, it can be deleted from BC side as well.

Test video:

Since there is a lot of code, I will not paste the code directly in the blog this time. Please go to Github for reference.
Source Code: GitHub (Please note that the source code is for reference only, you can improve it according to your own needs)

Next I will briefly explain how to achieve this. If you are interested, you can read it next.

First, create a new table to manage SharePoint attachments. Because I only added it to the Customer table, there are not many fields in the primary key. If you need to add it to documents such as Sales Order and Sales Order Line, it is recommended to refer to the standard Document Attachment (1173) table to add more primary key fields.

Then there are two new pages. One is the Factbox and the other is the detail page. This is the same as the standard.

Next is to add the logic, starting with authentication. More details: Dynamics 365 Business Central: How to use OAuth 2.0 in AL with SecretText (Using codeunit 501 OAuth2)

PS: It is recommended to create a settings page and add Client ID, Client Secret, Tenant ID, and Scope to it so that users can set.

Then let me briefly share the endpoints and logic of Upload, Delete, Update, and Check.

Upload (PUT):

Endpoint:

https://graph.microsoft.com/v1.0/sites/{site-id}/drives/{drive-id}/root:/{folder}/{filename}:/content

For example,

https://graph.microsoft.com/v1.0/sites/5b3b7cec-cbfe-4893-a638-c18a34c6a394/drives/b!7Hw7W_7Lk0imOMGKNMajlK0n-8Wdev9FmPdhx03j5o95rz4xvtmtTIUW5qUH7Jww/root:/Business Central/SharePointUploadTest.csv:/content

In my code:

https://graph.microsoft.com/v1.0/sites/5b3b7cec-cbfe-4893-a638-c18a34c6a394/drives/b!7Hw7W_7Lk0imOMGKNMajlK0n-8Wdev9FmPdhx03j5o95rz4xvtmtTIUW5qUH7Jww/root:/Business Central/' + FileName + ':/content'

If the upload is successful, insert the data and synchronize the name, id and Web URL into BC.

Delete, Update, and Check: Their endpoints are the same

Endpoint:

https://graph.microsoft.com/v1.0/sites/{site-id}/drive/items/{item-id}/

For example,

https://graph.microsoft.com/v1.0/sites/5b3b7cec-cbfe-4893-a638-c18a34c6a394/drive/items/01AA2EHNP46T7UJIJ6DZBYKJUOMSKBYWRZ/

Delete (DELETE):

Update (PATCH):

Check (GET):

Just like the authentication information above, you can also add endpoints to the settings page. I just hardcoded it for testing.

PS: I add some basic settings and tests, I hope it can also give you some tips

Microsoft Graph API: Microsoft Graph is a RESTful web API that enables you to access Microsoft Cloud service resources. After you register your app and get authentication tokens for a user or service, you can make requests to the Microsoft Graph API.

Registering your application establishes a trust relationship between your app and the Microsoft identity platform. This part of the setup is the same as in Using OAuth to connect Business Central APIs and Web Service in Postman. Just the API permissions are different.

To configure application permissions for the app in the app registrations experience on the Microsoft Entra admin center, follow these steps:

  • On the application’s API permissions page, choose Add a permission.
  • Select Microsoft Graph > select Application permissions.
  • In the Select Permissions dialog, choose the permissions to configure to the app.

This time I only added Microsoft Graph -> Application -> Files.ReadWrite.All.

PS: Upload or replace the contents of a driveItem permission

Permission typeLeast privileged permissionsHigher privileged permissions
Delegated (work or school account)Files.ReadWriteFiles.ReadWrite.All, Sites.ReadWrite.All
Delegated (personal Microsoft account)Files.ReadWriteFiles.ReadWrite.All
ApplicationFiles.ReadWrite.AllSites.ReadWrite.All

Then we can test in Postman to see if it is accessible.

Enter the following info in Postman. (You can use variables instead to keep sensitive data secure)

Access Token URLhttps://login.microsoftonline.com/d8f36038-1f93-4543-affc-5dc92b6ee871/oauth2/v2.0/token (Change it to your tenant ID)

Client ID: a80c03cf-6ffa-4b6e-b2c8-6005310d3d87

Client Secret: fME7Q~cAaSBhXMGZoHY3ei64nn1fxGpqF42mh

Scope: https://graph.microsoft.com/.default (Note that Scope is different from BC)

Cilent Authentication: Send client credentials in body

And my test SharePoint site:

https://2qcj3x.sharepoint.com/sites/BCShareFiles

Documents:

Business Central Folder:

Then we can get the GUID of the SharePoint Online site via adding the following _api/site/id at the end of the Site URL:

https://2qcj3x.sharepoint.com/sites/BCShareFiles/_api/site/id

SharePoint (site) id: 5b3b7cec-cbfe-4893-a638-c18a34c6a394

At this time we can get document libraries information.
Test Endpoint:

https://graph.microsoft.com/v1.0/sites/{site-id}/drives

For example,

https://graph.microsoft.com/v1.0/sites/5b3b7cec-cbfe-4893-a638-c18a34c6a394/drives

Documents (drive) id:

 b!7Hw7W_7Lk0imOMGKNMajlK0n-8Wdev9FmPdhx03j5o95rz4xvtmtTIUW5qUH7Jww

If we want to get the file information in the folder, we need to use the following endpoint.
Test Endpoint:

https://graph.microsoft.com/v1.0/drives/{driveId}/root:/{folderPath}:/children

For example,

https://graph.microsoft.com/v1.0/sites/5b3b7cec-cbfe-4893-a638-c18a34c6a394/drives/b!7Hw7W_7Lk0imOMGKNMajlK0n-8Wdev9FmPdhx03j5o95rz4xvtmtTIUW5qUH7Jww/root:/Business%20Central:/children

Here we can get the file (item) id.
File (item) id: 01AA2EHNP46T7UJIJ6DZBYKJUOMSKBYWRZ

Let’s read the contents of the file, 1988-S.jpg.

Test Endpoint:

https://graph.microsoft.com/v1.0/sites/{site-id}/drive/items/{item-id}/content

For example,

https://graph.microsoft.com/v1.0/sites/5b3b7cec-cbfe-4893-a638-c18a34c6a394/drive/items/01AA2EHNP46T7UJIJ6DZBYKJUOMSKBYWRZ/content

If you don’t need to display the content, but only need file information, use the following endpoint

Test Endpoint:

https://graph.microsoft.com/v1.0/sites/{site-id}/drive/items/{item-id}/

For example,

https://graph.microsoft.com/v1.0/sites/5b3b7cec-cbfe-4893-a638-c18a34c6a394/drive/items/01AA2EHNN7JYZKCPZS6NFKDFGKK2PGMJEE/

GET:

This endpoint can also be used for modification and deletion.

PATCH:

DELETE:

One more test this time, try uploading a file to SharePoint from Postman. Here is the endpoint I tested (Including content).

PUT

https://graph.microsoft.com/v1.0/sites/{site-id}/drives/{drive-id}/root:/{folder}/{filename}:/content

For example, I want to upload the SharePointUploadTest.csv file to Documents (root) -> Business Central folder.

Endpoint

https://graph.microsoft.com/v1.0/sites/5b3b7cec-cbfe-4893-a638-c18a34c6a394/drives/b!7Hw7W_7Lk0imOMGKNMajlK0n-8Wdev9FmPdhx03j5o95rz4xvtmtTIUW5qUH7Jww/root:/Business Central/SharePointUploadTest.csv:/content

Then the http action is not Get, but Put.
Headers -> Content-Type -> text/csv:

Body -> binary -> upload file:

Choose Send.
201 Created: A new resource was created successfully.

If the specified folder is not found, a new folder will be created automatically.

Test video:

More details: Working with SharePoint sites in Microsoft Graph and Upload or replace the contents of a driveItem

END

Hope this will help.

Thanks.

ZHU

コメント

Copied title and URL