Microsoft Business Central File Handling Part 1
I recently attended the Microsoft Directions Asia Conference for the first time. Eight employees from ArcherPoint participated in or attended nearly 80 sessions presented by Microsoft and ISVs. Team members went to different sessions and shared that knowledge. The experience was awesome.
The conference showcased for the first time the April ’19 Release of Dynamics 365 Business Central. The Microsoft team also announced the end of Windows Client starting with the October ’19 release and presented best practices for moving forward with Visual Studio Code and AL.
We also learned some practical applications, including best practices for handling files in Microsoft Dynamics Business Central online. This series of four blogs will explain how to:
- Export to Excel and download the file
- Upload a file and store it in a table
- Update an existing Excel file
- Email the exported file
This blog explains how to export to Excel and download the file.
Exporting to Excel and Downloading the File
With Business Central online, you don’t have access to the filesystem. However, it is a common scenario to upload or export files. The choice for Excel files is because this is by far the most used file type to export and import files. Other file types, like comma separated text or XML files can also be handled with XML ports. But Excel files are more complex to create. We will make use of the standard features of Business Central, like the Excel Buffer table. There is no need to install any other tool. The prerequisites are:
- Dynamics 365 Business Central Sandbox Environment
- Dynamics 365 Business Central should run from Azure Cloud
In this exercise, we are going to export customer records to Excel. Before you start, you need to create an empty AL workspace in VS Code. Remove the HelloWorld.al file from the workspace after it has been created. NOTE: This blog does not cover the steps to create an AL workspace.
Create A Codeunit to Loop Through the Customer Table
- Create a new codeunit 50100 “Export Customer 2 Excel”
- Create a function Export2Excel
- Call the function from the OnRun trigger
Fill the Excel Buffer Table
- Create a new function, FillExcelRow, with two parameters: TempExcelBuf and Customer.
- In the function FillExcelRow, create lines to fill the Excel row with the fields of the Customer record.
- Create a function, FillExcelBuffer, with parameter TempExcelBuffer.
- In the function FillExcelBuffer, create a local variable Customer.
- In the function FillExcelBuffer, loop through the Customer table and call the function FillExcelRow.
- In the function Export2Excel, create a local variable called TempExcelBuf.
- In the function Export2ExcelBuf, call the function FillExcelBuffer.
Export the Excel Buffer Table to an Excel File and Download It
- Create a function, OpenExcelFile, with parameter TempExcelBuf.
- In the function OpenExcelFile, write code to create the Excel file and download it to the client.
- In the function Export2ExcelBuf, call the function OpenExcelFile.
The whole codeunit should now like look this:
Figure 1 – Creating a Codeunit in Microsoft Dynamics Business Central online
Run the Codeunit
It makes sense to run the codeunit from the Customer List page, so let’s create a page extension and add a function to run the codeunit:
- Create a new page extension for the Customer List.
- Add an action to the page extension.
- Publish the extension and use the action on the Customer List page to test the Excel download.
Figure 2 – List page to test an Excel download in Microsoft Dynamics Business Central online
The next blog in this series discusses how to upload a file and store it in a table.
If you have any Dynamics NAV or Business Central questions for any version, contact ArcherPoint.
Read more “How To” blogs from ArcherPoint for practical advice on using Microsoft Dynamics Business Central or NAV.
If you are interested in more NAV/Business Central development, check out our collection of NAV/BC Development Blogs.