Export Business Central online entities to Azure storage blob container

As most probably know, it is not possible to access the file system while in Business Central cloud environment.

For example, in Dynamics NAV, we could have a job queue entry that, when run, creates a file and copies it in a network folder. We can still do that in an On-Premise environment, but not with cloud BC.

You could create the file and use DownloadFromStream, but that would only prompt you do download it locally, but would not copy it somewhere on a local or network folder.

If you try to use File.Create() you would get the warning: “The type or method ‘Create’ cannot be used for ‘Extension’ development”.

If your customer is happy to grab the file manually every time from the downloads folder then this should suffice:

But, if we want to automatize this process and run the extract on a regular basis, we need to find a cloud solution for storing the files.

Currently, there are 4 types of storage in Azure platform:

  • Containers/Blobs
  • File Shares
  • Queues
  • Tables

In my previous blog I dived into the Azure Storage of type Tables and tackled its API.

This blog is about interacting with the Azure storage blob containers:

  • manually, via Azure Portal
  • simulation, via VS Code extension “Rest Client”
  • Business Central extension
  • view blob container with Excel
  • get Azure Blobs locally

I found on Michael Megel’s blog a nice solution for exactly what I need. Awesome job on Blob Containers API, Michael! Thank you for sharing!

What I need:

  • Azure:
    • Set up a blob container to store Business Central exported files
    • Set up Storage Access Key
  • Simulation:
    • In VS Code, write requests with “Rest Client” extension, targeting Azure blob container API
  • Business Central:
    • A setup table in Business Central for Azure access stuff
    • Wrote an export interface that would allow users to run an action(“Write File in Azure”) that will send the extract to Azure container. The same code could be executed by a job queue.

Blob Container Setup

To set up a container, following Michael’s notes on above blog was enough for me.

For blob container accessibility I went on the path of shared access signature “SAS Token”.

Once created, you can start playing with the storage account container API.

I created the storage manually:

Drilling down into the storage account, I created a new container:

Simulation:

In VS Code, using Rest Client,

  1. I sent a request to get the list of containers:

Request:

GET https://svflorida.blob.core.windows.net/?comp=list&%5Bhere you insert your SAS token key]

content-type: application/json

Response:

HTTP/1.1 201 Created

Content-Length: 0

Content-MD5: 1B2M2Y8AsgTpgAmY7PhCfg==

Last-Modified: Wed, 18 Aug 2021 19:05:13 GMT

ETag: “0x8D9627B1BD88A0F”

Server: Windows-Azure-Blob/1.0 Microsoft-HTTPAPI/2.0

x-ms-request-id: 3f97555d-801e-006d-5263-94f646000000

x-ms-version: 2020-08-04

x-ms-content-crc64: AAAAAAAAAAA=

x-ms-request-server-encrypted: true

Date: Wed, 18 Aug 2021 19:05:13 GMT

Connection: close

2. I sent a PUT request to insert an empty file:

Request:

PUT https://svflorida.blob.core.windows.net/vendorlist/vl1111?%5Byour SAS token here]

x-ms-blob-type: BlockBlob

Content-Length: 0

Response:

HTTP/1.1 201 Created

Content-Length: 0

Content-MD5: 1B2M2Y8AsgTpgAmY7PhCfg==

Last-Modified: Wed, 18 Aug 2021 19:23:46 GMT

ETag: “0x8D9627DB340E9DD”

Server: Windows-Azure-Blob/1.0 Microsoft-HTTPAPI/2.0

x-ms-request-id: b77cbfb2-b01e-003b-2566-9407a9000000

x-ms-version: 2020-08-04

x-ms-content-crc64: AAAAAAAAAAA=

x-ms-request-server-encrypted: true Date: Wed, 18 Aug 2021 19:23:46 GMT

Connection: close

And this is the file in Azure portal:

Business Central extension:

This is how the new setup table “Azure Storage Setup” looks in BC:

This is how the new BC interface “Vendors Export Log” looks like:

“Write File In Azure” action on page 50251 “Vendor Export Log” does the following:

  • exports all BC vendors to a blob
  • the blob is then written to a PUT request content
  • the PUT request is sent to Azure Blob Storage API

Consult Blobs with Excel:

BC users can click on the URL link above and download locally the file or they, and other 3rd party users, can access the files via Excel, as I explained in my previous blog.

This time though, when creating the connection choose Data – > Get Data -> From Azure -> From Azure Blob Storage.

And finally displayed in the Excel book:

Get Azure Blobs locally

To help with getting the files locally, I wrote 2 blogs:

  • one about getting the files locally using Power Automate
  • one about Azure CLI to copy the files from azure blob storage locally

For more about storage accounts in Azure check this out.

You can find sample code repository here.

Azure Storage Table and its API

One way to make available data from Business Central to different parties and users is to leverage Azure Storage.

Customers will need to have an Azure Portal subscription.

There are 4 types of storage in Azure portal: Blob Containers, File Shares, Queues, and Tables.

Today’s blog is about Azure Storage Tables and its API.

More on its API here.

In this blog I covered:

  • create a storage account table using Azure Platform;
  • insert data in the storage account table via VS Code extension “Rest Client”;
  • an extension to send Business Central vendor data to the Azure Storage table;
  • using Excel to share Azure Storage table data with 3rd party users.

Create storage account and storage account table

I created through the Azure portal one storage account and a few tables stored inside that storage account.

The process is simple and you can find details about storage accounts here and an overview on storage account tables here.

You can insert manually data in these tables using Azure Storage Explorer.

Let’s see how we can interact with them first via Rest Client (extension for VS Code), and then via Business Central extensions.

Using Rest Client to interact with Azure Storage Table

1. we can query the Azure Tables via Rest Client in VS Code:

[in the picture insert your SAS token right after “?”]

2. Query Vendor table:

3. Insert new record in Vendor Table:

Let’s now verify in Azure portal the last action:

Use Azure Storage Table API from Business Central

With the API tested, let’s now move to Business Central and AL and try to insert records in the Azure tables.

The sample code I worked on will scan all vendors and send Vendor.”No.” and Vendor.Name to the Vendor table in Azure Storage.

When creating a new table in Azure Storage Table, each table comes by default with the 2 fields:

  • PartitionKey
  • RowKey

In my example, PartitionKey will be empty, but you could populate it with the company name.

RowKey will be populated with Vendor.SystemId.

“Azure Storage Setup” it’s an extension table to keep all that great Azure stuff:

To log the work done by the Post request I created table and a page and I am inserting records in this table with each successful Post request.

Use the Azure Storage Explorer to view records in the Azure Portal:

What about the 3rd party users?

How do we give them access to the data in the storage table?

The good old Excel is here to contribute.

Use of Excel to share Azure Storage Data with 3rd party users

-Open Excel

  • go to Data menu
  • Get Data -> From Azure Storage Table
  • when prompted enter for the “Account Name or URL” enter the storage URL:
  • For access key enter the access key from storage account

And this is what we get:

Click on Load, and then double click on Vendor Connection:

PowerQuery is opening and we can enable the other fields (by clicking on Content column and select the missing columns):

You can find the AL sample code here.

To use or not to use SelectLatestVersion()

When using web services or API exposed entities you might find useful to request the application service to grab the latest version of the underlying data.

The definition at Database.SelectLatestVersion Method – Business Central | Microsoft Docs states that with using SelectLatestVersion() function you make sure that “the data displayed is the most current data in the database”.

Why would we do that? Isn’t the browser page automatically refreshed?

Not always. Not when a 3rd party up updates the records.

Let’s do some tests in a SaaS environment.

I created a custom entity (testTable), with a list page and an API page. Will start with pushing 10 records to the table via a batch using Postman:

This is the result when executing “refresh” action:

And now let’s send another batch with 4 Delete request:

Next, I’m going to send another 10 records batch to BC.

Using a new action, “refresh-SelectLatestVersion” that does not contain SelectLatestVersion() gives us the following:

It appears that SelectLatestVersion does not make any difference in SaaS and that affecting records with a BC native API does not require SelectLatestVersion().

Let’s try something similar in an On-prem installation.

When records are updated by other apps, not through Business Central means (by the way, not a great idea), the page is not notified of changes in the underlying data and therefore is in a stale state.

How can we enforce the data to update?

Using SelectLatestVersion() we’re clearing up the client cache for the underlying table, initiating a new Read transaction for the affected table, thus affecting performance.

Let’s see how much is actually taking the server to grab the latest data.

I inserted via T-SQL 1,000,000 records:

and this is what I’ve got when I refreshed the page:

The I removed all records:

As you can see above, while my CurrPage.Update is before Message, the page still shows the records. I am guessing that the Message gets displayed before the page is re-rendered.

After clicking on OK the page get rendered again and shows 0 records.

It took 69 milliseconds but the table had only 2 fields. With more fields the result might take longer.

Sometimes customers will ask for an auto-refresh page. While there are technical means to satisfy the request, we need to recognize that this comes with a price, hurting performance. And when applying an auto-refresh to multiple pages the price consequently multiplies.

Things to consider:

  1. avoid when possible use of Selectlatestversion on prem.
  2. In SaaS no need for SelectLatestVersion, refreshing the page via an action or browser F5 displays the latest data.
  3. avoid auto-refreshing. Rather go with a manual refresh(action on page to refresh and call SelectLatestVersion) than auto-refresh (a timer controladdin)
  4. To decrease the number of SelectLatestVersion() calls and CurrPage.Update, log your refresh triggers (count and refresh datetime), and compare current count against last refresh count, get the maximum System Modified At among your records and compare it against your last log datetime …

Extension code for SaaS is located here.