How to Build a General Journal Import in Business Central

By Daniel Sukup | February 28, 2020

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:

  1. Build an XML Port Extension to import a file into the “Gen. Journal Line” table
  2. Build a Page Extension to “General Journal” page and add the “General Journal Import” we just built
  3. 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:

 

Related Posts

Recommended Reading:

Manage U.S. Use Tax on Purchase Orders in Dynamics 365 Finance and Operations

  Managing sales tax requirements on your business purchase can be complicated, but Dynamics 365 Finance and Operations can help […]

Read the Article
5.19.22 Dynamics CRM

How to Write a Great Support Ticket in the Stoneridge Support Portal

Submitting a support ticket through the Stoneridge Support Portal is a quick and effective way to get assistance for any […]

Read the Article

Managing Your Business Through Uncertain Times Using Dynamics 365 Finance and Operations

  Dynamics 365 Finance and Operations (F&O) can help you make informed decisions on how to move your business forward. […]

Read the Article
5.13.22 Power Platform

Using Power BI Object Level Security

  The following article will demonstrate how to use Power BI Object Level Security to disable column data based on […]

Read the Article
5.12.22 Dynamics CRM

How to Use the Stoneridge Support Portal

Stoneridge Software’s support portal is an intuitive and useful function that makes it easy for you to access resources to […]

Read the Article
5.6.22 Dynamics GP

Dynamics GP Transaction Removal: Purchase Orders

  Are you having performance issues with Purchase Orders?  Do you find that there are old Purchase Orders on your […]

Read the Article
5.5.22 Dynamics GP

The Real Story about the Long-Term Future of Dynamics GP Support

I’ve seen a number of people put forward comment that Dynamics GP is going away and you have to get […]

Read the Article

New Features in Dynamics 365 Business Central 2022 Wave 1 Release – Financial Enhancements

The Dynamics 365 Businses Central 2022 Wave 1 Release has a lot of new and exciting features to help your […]

Read the Article
4.29.22 Dynamics GP

Dynamics GP Transaction Removals: Bank Reconciliation

  This is part 2 of a 3 part series on Dynamics GP Transaction Removals. These quick tips will hopefully […]

Read the Article

Start the Conversation

It’s our mission to help clients win. We’d love to talk to you about the right business solutions to help you achieve your goals.

Subscribe To Our Blog

Sign up to get periodic updates on the latest posts.

Thank you for subscribing!

X