How to Build a General Journal Import in Business Central

by | Updated January 7, 2021 | Dynamics 365 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:

  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

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Upcoming Events

september

01sep10:00 am10:30 amEnsuring Quality and Compliance for Batch Manufacturers in Life Sciences

01sep12:00 pm12:30 pmIs it Worth It to Move to the Cloud? A Look at Considerations for Current Agribusinesses Using Dynamics GP

01sep2:00 pm2:45 pmWhat’s New in Dynamics 365 Finance and Supply Chain

09sep11:00 am12:00 pmConfab Live with Stoneridge – Data Strategy and Reporting – Mining Decision Making Insights

15sep10:00 am11:00 amSolving the Biggest Challenges in Agribusiness Through Innovation and Technology

22sep12:00 am12:30 pmSimplifying Payroll and HR Management with ADP Workforce Now

22sep10:00 am10:30 amStreamlining Batch Manufacturing with Technology

22sep2:00 pm2:30 pmProcess Automation for Microsoft Dynamics D365 for Business Central, Finance and Operations and GP

23sep11:00 am12:00 pmConfab LIVE with Stoneridge - Riding the Wave 2 Release – Key Features Coming to Dynamics 365 this October

29sep10:00 am11:00 amTop Five Reasons Why NOW is the Right Time to Move from Salesforce to Dynamics 365 Customer Engagement

29sep12:00 pm12:45 pmUnderstanding Job Costing and Tax Management in Business Central

29sep2:00 pm3:00 pmDigitalizing Horticulture & Agriculture - How to Sell Plants Online and Simplify Business Management

30sep12:00 pm4:00 pmSecurity and Permissions Training for D365 Business Central or Dynamics NAV

october

06oct10:00 am10:30 amPreview of D365 Business Central Fall Release Features and Functionality

06oct12:00 pm12:30 pmInsider's Guide to New Features Available in the Fall Release of D365 Finance and Supply Chain

07oct11:00 am12:00 pmConfab LIVE with Stoneridge - Dataverse and Dynamics in Review – Let’s Get Technical

13oct12:00 pm12:30 pmWave 2 Release – What’s Coming for Dynamics 365 Sales and Customer Service

21oct11:00 am12:00 pmConfab LIVE with Stoneridge - Dataverse and Dynamics in Review – Let’s Get Functional

26oct(oct 26)9:00 am28(oct 28)5:00 pmStoneridge Connect Leadership and Community Conference

About Stoneridge
Stoneridge Software is a unique Microsoft Gold Partner, with emphasis on partner. With specialties in Microsoft Dynamics 365, Microsoft Dynamics AX, Microsoft Dynamics NAV, Microsoft Dynamics GP and Microsoft Dynamics CRM, we focus on attracting the most knowledgeable experts in the field to our team, and prioritize delivering stellar solutions with maximum impact for your business. At Stoneridge, we are deeply committed to your results. Each engagement is met with a dedicated team, ready to provide thorough, tailored, and expert service. Based in Minnesota, we intentionally “step into your shoes,” wherever you are. We focus on what you care about, and develop trusting, long-term relationships with our clients.

Subscribe To Our Blog

Sign up to get periodic updates on the latest posts.

Thank you for subscribing!

X