Hi, Readers.
In the previous weeks we discussed how to implement attachment file transfer between Business Central and SharePoint through Graph API.
- Dynamics 365 Business Central: How to import/read files from SharePoint (document library) to Business Central via AL (Graph API)
- Dynamics 365 Business Central: How to upload files from Business Central to SharePoint (document library) via AL (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 type | Additional storage (for each license of this type) |
---|---|
Premium | 3 GB |
Essential | 2 GB |
Device | 1 GB |
More details: Announcing: Dynamics 365 Business Central database capacity changes as of July 1, 2021 and Managing capacity

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 type | Least privileged permissions | Higher privileged permissions |
---|---|---|
Delegated (work or school account) | Files.ReadWrite | Files.ReadWrite.All, Sites.ReadWrite.All |
Delegated (personal Microsoft account) | Files.ReadWrite | Files.ReadWrite.All |
Application | Files.ReadWrite.All | Sites.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 URL: https://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
コメント