Dynamics NAV 2013R2 Excel Buffer Part 3
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
I Also created a new Function so that the user can select the Item Batch to use on Import.
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.
Starting with the Item Journal Template I change some Control Properties, adding a Table Relation to select the Item Journal Template Name.
With the Item Journal Batch Name I will use a Boolean Variable in Code to Set this Control to Editable.
With the Document No. I will use a Boolean Variable in Code to Set this Control to Editable.
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.
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.
The “Item Journal Template” Control is Coded to Check to make sure the Item Journal Template Exists and is a Valid Template Name.
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.
Here is the Request Page when the report is run.
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.
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.
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.
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
