How to Build a General Journal Import in Business Central
Today’s post will explain how to build a simple General Journal import in Microsoft Dynamics 365 Business Central using Visual Studio Code, step-by-step.
What to Do Before You Begin Your General Journal Import
To get started, you will need to download the Visual Studio Code and the AL Language extensions from Microsoft. For help getting started with the AL language extension and the Visual Studio Code tenant, check out Microsoft’s setup guide.
For the purpose of this blog, we will be using an On-Premise version of Dynamics 365 Business Central Sprint Release. The import will work, however, on any SaaS version of Business Central.
How to Build a General Journal Import in Business Central
Here’s an overview of the tasks we’ll be completing as we build a simple General Journal import within Business Central:
- Build an XML Port Extension to import a file into the “Gen. Journal Line” table
- Build a Page Extension to “General Journal” page and add the “General Journal Import” we just built
- Deploy the extension to a Dynamics Business Central environment
The following standard format will be used for the import:
The file will be created in Excel, the header will be deleted and saved as .CSV file.
Set Up Visual Studio Code
Under the workspace in Visual Studio Code, we have created a new folder for my import files and copied the settings for the “.alpackages” and “.vscode” as well as the “app.json” file to the new folder.
If you have just set up Visual Studio Code, this should happen when you download the symbols for Dynamics Business Central and set up the program.
Create XMLPort Extension
For the first file, we will be creating a new file by right-clicking on the GeneralJournalImport and selecting New File. All extension files will need to be named with an .al extension.
Now that the file is created, we can start building the XMLPort.
Visual Studio Code IntelliSense has a great tool for building the framework of any of the extension files which we will be using. By pressing ‘t’ in the application code, Visual Studio Code IntelliSense should suggest a template for Table, Page, Xmlport or any other type of Extension. In our example, we will type “tx” which will give the option for “txmlport.”
This will give us the layout for the import using generic names and numbers which will need to then be replaced by the values you expect to use.
We’ll be replacing the “Id” with 50105 and “MyXmlport” with GenJournalImport. The Id needs to be within the range specified in the “app.json” file, which will default to 50100 – 50149. You should stay within the range 50000-59999 as a rule, as this is a standard range for custom objects.
We’ll also replace the “NodeName1” with the table “root.” For “NodeName2” we'll use GenJnlLine and the Gen. Journal Line table. Each field element will be added for each field in the journal import. Once the updates are complete, the .AL file will look similar to the following:
Notice that the “FieldValidate” is set on the AccountNo field, which will validate the value during the import. We will also be adding this validation to the Amount, Dimension 1, and Dimension 2 fields. The format will also need to be set to VariableText because the default value for XMLPorts is XML. If you want the file to be a CSV, you shouldn’t need to set the FieldDelimiter or FieldSeparator properties.
Now we will need to add a function to pass the “Journal Batch” and “Journal Template Name.” To do this, we will add a procedure to pass the values when we add the page extension on the General Journal. We’ll need to create public variables for JournalTemplate and JournalBatch as Code variables and a procedure to set the values. The procedure should look something like the following:
If you create the procedure using the Tprocedure, just make sure you remove the “Local” which VS Code will put in front, or you will be unable to call the procedure from the General journal Page.
Finally, we will be creating a global variable for LastLineNo adding triggers for OnPreXMLPort() to set the original value from the Journal Template/Batch and adding a trigger OnBeforeInsertRecord() to set the Journal Batch, Journal Template, and Line No. fields before the record is inserted.
It is very important to add the triggers within the brackets of where they are needed. For example, the “OnPreXMLPort” trigger is global to the entire XMLPort, so this trigger must not be added inside of one of the schema or element brackets. In my import, I added it just above the global variables. The “OnBeforeInsertRecord” trigger must be added inside the GenJnlLine element after the field elements.
Once the triggers have been added and all of the code errors have been resolved, the file should appear with white text to show that it should compile.
If there are any compilation issues, the file will be red and the “Problems” should list all of the possible compilation issues with the file.
It is generally best to fix the compilation issues from top to bottom, as a compile error at the top may cause many other errors.
Create the General Journal Page Extension
Now that we have an XMLPort extension, we will be adding it to the General Journal Page as a page extension. At a high level, we’ll just need to add an action to the page which will run the XMLPort and import the file.
To import the file into any Business Central SaaS version, we’ll need to use the UploadIntoStream function in order to open the file dialog to select the file. The function to run the XMLPort should look similar to the following:
Once the Gen. journal Page extension has been created, the extension will need to be deployed and installed onto the server.
Testing the General Journal Import
Once deployed and installed, the icon should show up on the General Journal page. Once run, it should open a file dialog box to import a file and import the said file using the XMLPort we created.
File format example:
Files loaded into General Journal: