Subscribe via RSS Feed Connect on LinkedIn

Dynamics NAV 2013R2 Excel Buffer Part 3

18th October 2014 0 Comments

This post is the last in a series of three on using the Microsoft Dynamics NAV Excel Buffer, if you have not read part one you can Read it Here, if you have not read part two you can Read it Here, in this post I will use the Template Report I created in part two to bring data into the “Item Journal Line Table”, there is a “Download Button” at the foot of this post for the report in Text Format.

In the Code I use Variables for the Item Template and Item Journal, as a rule I try not to hard code values, I have seen Code like “Journal Template Name” := ‘ITEM’; this is fine for a lot of databases but with an International Product it is not much use in a German Database where there is no “Journal Template Name” called ‘ITEM’ or Batch Name called ‘DEFAULT’.

 

Using the Template Report

I created a Template Report in Part Two so I can create a report to import data into Dynamics NAV using the template, I start by saving Report 50000 “Import from Excel Template” as 50001 “Import Item Journal from Excel”, there are some more C/AL Globals to add, as I go through you will see how I use these New Variable.

C/AL Globals

Post 323

 

I Also created a new Function so that the user can select the Item Batch to use on Import.

Post 324

 

Request Page

On the Request Page I added three of the new Variables, these will allow selection or input of the “Item Journal Template Name”, “Item Journal Batch Name” and the “Document No.” if applicable.

Post 325

 

Starting with the Item Journal Template I change some Control Properties, adding a Table Relation to select the Item Journal Template Name.

Post 3265

 

With the Item Journal Batch Name I will use a Boolean Variable in Code to Set this Control to Editable.

Post 327

 

With the Document No. I will use a Boolean Variable in Code to Set this Control to Editable.

Post 328

 

New Function Code

Gets the Item Journal Batch when called from the Assist Edit Trigger, this Code presents a list of Valid Item Journal Templates to the user, and Sets or Clears the Variables.

Post 331

 

Request Page Code

I Add more Code to the Request Page Trigger to check if the user has entered all the values I require for the Report.

Post 330

 

The “Item Journal Template” Control is Coded to Check to make sure the Item Journal Template Exists and is a Valid Template Name.

Post 332

 

The “Item Journal Batch” has two Code blocks On Validate to check to make sure the Item Journal Batch Name exists and is a Valid Name and On Assist Edit that uses the Get Journal Batch Name Function.

Post 333

 

Here is the Request Page when the report is run.

Post 329

Analyze Data Function Code

I add new code to the Function before the Loop, the Option Page will Store the values from a previous run therefore I get the Journal Template and Journal Batch Records, two Local Variables are added, No Series Management and Line Quantity.

Code Filters the Journal Lines to Get the Last Line Number, if the Journal Batch has a “No. Series” Set then I Set the “Document No.” to Use in Code.

Post 334

 

Inside the Loop I add Code to populate the Item Journal Line, I only add the Item Journal Line if the User has added a Quantity to import, defaults are added from the Template and Batch records, the sign of the Quantity is used to set the Entry Type, the Line No. is updated and set.

Getting the Excel Buffer record for the Posting Date will error if the Cell is empty, this will make the Posting Date a Mandatory cell, I used VALIDATE on the Fields this then mirrors the Functions as if I were Entering the Values directly in the Item Journal, Validate runs the Field Trigger Code and populates other Fields and Tables like the Dimensions.

Post 335

 

Here are the other Cells, the Code Gets the Item and tests that the Item is not Blocked, I use a conditional IF statement for none mandatory Cells like the Bin Code.

Positive and Negative Adjustments are both entered with a Positive Quantity so I use the ABS Value for the Quantity and Modify the line to run the On Modify Trigger.

Post 336

 

Item Journal

When the Report is run the user will Select the Options and Import the Workbook, the Item Journal in the Screenshot is from the Sample File I created in Part One

Post 339

Post 340

[download id=”291″]

Please Share this Post:

Leave a Reply

Spam protection by WP Captcha-Free