Advertisements

How to attach additional 6 Shortcut Dimensions to Dimension Set when copy data to Journal from Excel

 

In my previous post I explained how we can copy data to-and-fro between Excel and Navision. Also we saw the limitation of additional 6 Shortcut Dimensions neither can be exported nor imported back.

Today we will see solution to this limitation, as it is required from several customers and we keep getting request for same. The same reader for whom I have posted my previous post have requested similar need.

If you missed my previous post you can find here : Copying data to-and-fro between Excel & Navision

Let us see how we can get this part working, yes it will require a small customization to get this working. Below I explain the steps we require to achieve this.

My example refers to Table 81 & Page 39. You can do same with other Journals too.

We will Add Custom Fields for these additional 6 Shortcut Dimensions.

 

ExcelToNavJnl-8

Add the Fields and write the above code to OnValidate trigger and set the Property accordingly for all of the 6 Shortcut Dimension Fields.

ExcelToNavJnl-9

Similarly add a piece of code to OnInsert trigger of the Table.

ExcelToNavJnl-10

Add our newly created Fields to Page too.

Now Let us check if it works as expected.

ExcelToNavJnl-11

 

Perform the operation as in previous post, this time add the newly created fields for Dimensions as shown in above screen.

If you missed my previous post you can find here : Copying data to-and-fro between Excel & Navision

Export and Import back by adding value to these additional Dimensions 1 &2 is Global 3-6 is my additional Shortcut Dimensions, 7-8 I have not included as not setup in my database.

After importing back check the Dimensions and you will find due to above customization my all the Shortcut Dimensions are attached to Dimension Set Entry.
ExcelToNavJnl-12

Yes we are done.

I will come up with more information in my upcoming posts. Till then keep exploring and Learning.

 

 

Advertisements

Copying data to-and-fro between Excel & Navision

ExcelToNavJnl

One of my reader has requested to show him how to export data from Nav Journal to Excel, perform correction and import back to Navision.

So let us see how can we perform this and what are the limitations.

Open the Journal in Navision.

Arrange and show all the Fields that you want to export to Excel on the page.

Fill some sample data. Say single line of Journal, way usually you do.

ExcelToNavJnl-2

Now Send to Excel using options shown in below screen.

ExcelToNavJnl-3

Data will get Exported to Excel.

Have you noticed something, with the data that got exported yo Excel?

Your 2 Additional Shortcut dimension was not Exported to Excel. Customer Group Code & Area Code, why?

Since these are not the actual fields in the table and it is calculated on Page level, so you will only be able to export Dim-1 & Dim-2 your Global Dimensions which is available as Field in the Table.

Make sure you enter Dimension Values in Capitals in Excel Columns.

ExcelToNavJnl-4

Now perform required changes to the Journal data.

Insert New Lines, Delete existing Lines or Edit existing Lines.

Make sure you don’t keep more than 30000 to 40000 lines, until this it works fine if more than this either performance is too slow or Navision gets hang while you copy back your data to Navision. Upto 40000 works fine have tested several time. Depending upon your system performance you can decide how much data will be ok for you.

ExcelToNavJnl-5

As we have seen above my 2 additional Dimensions is missing from the exported data. We need to match the columns what we have in our Excel and sequence. So we will hide/remove the additional columns from the Journal to match the sequence from Excel columns before we copy back our data from Excel to Navision.

ExcelToNavJnl-6

Select the Rows in excel containing you data excluding header columns and copy.

Return to your Journal and Paste as shown in above screen.

ExcelToNavJnl-7

Your modified data is imported back to the Journal in Navision.

Now perform the Journal action way you do normally.

 

Design Overview of Dimension handling in Navision 2016

Hold on,

Way of handling Dimension have changed from Navision 2009 not a new concept. Till now everyone is aware of this technical change, but still to keep handy reference today I decided to share the same with community, which can help for new comers in this industry.

Detailed technical insight into the concepts and principles that are used to redesign the dimension entry storing and posting feature in Microsoft Dynamics NAV 2016. Helpful when upgrading from earlier version to 2009.

Dimension Sets

A dimension set is a unique combination of dimension values. It is stored as dimension set entries in the database. Each dimension set entry represents a single dimension value.

The dimension set is identified by a common dimension set ID that is assigned to each dimension set entry that belongs to the dimension set.

Dimension Set Entries

Dimension sets are stored in the Dimension Set Entry table as dimension set entries with the same dimension set ID.

When you add, edit and close the Edit Dimension Set Entries window, a check is performed to see whether the combination of dimension values exists as a dimension set in the table.

If the combination occurs in the table, then the corresponding dimension set ID is assigned to the journal line, document header, or document line.

Otherwise, a new dimension set is added to the table, and the new dimension set ID is assigned to the journal line, document header, or document line.
Performance Improvement
By storing dimension sets once in the database, database space is preserved, and overall performance is improved.

Searching for Dimension Combinations

Building Search Tree
Table 481 Dimension Set Tree Node is used when Microsoft Dynamics NAV evaluates whether a set of dimensions already exists in table 480 Dimension Set Entry table.

The evaluation is performed by recursively traversing the search tree starting at the top level numbered 0.

The top level 0 represents a dimension set with no dimension set entries. The children of this dimension set represent dimension sets with only one dimension set entry.

The children of these dimension sets represent dimension sets with two children, and so on.

Finding Dimension Set ID

At a conceptual level, Parent ID, Dimension, and Dimension Value, in the search tree, are combined and used as the primary key because Microsoft Dynamics NAV traverses the tree in the same order as the dimension entries.

The GET function (record) is used to search for dimension set ID.

DimSet.”Parent ID” := 0;  // ‘root’

IF UserDim.FINDSET THEN

REPEAT

DimSet.GET(DimSet.”Parent ID”,UserDim.DimCode,UserDim.DimValueCode);

UNTIL UserDim.NEXT = 0;

EXIT(DimSet.ID);

However, to preserve the ability of Microsoft Dynamics NAV to rename a dimension and dimension value, table 348 Dimension Value is extended with an integer field of Dimension Value ID.

This table converts the field pair Dimension and Dimension Value to an integer value.

When you rename the dimension and dimension value, the integer value is not changed.

DimSet.”Parent ID” := 0;  // ‘root’IF UserDim.FINDSET THEN  REPEAT      DimSet.GET(DimSet.ParentID,UserDim.”Dimension Value ID”);  UNTIL UserDim.NEXT = 0;EXIT(DimSet.ID);

Table Structure

New Tables

Three new tables have been designed to manage dimension set entries.

Table 480 Dimension Set Entry
Table 480 Dimension Set Entry is a new table. You cannot change this table. After data has been written to the table, you cannot delete or edit it.

Deleting data requires that you check against all occurrences of the dimension set ID in the entire database, including partner solutions.

Field No. Field Name Data Type Comment
1 ID Integer >0.0 is reserved for the empty dimension set. References field 3 in table 481.
2 Dimension Code Code 20 Table relation to table 348.
3 Dimension Value Code Code 20 Table relation to table 349.
4 Dimension Value ID Integer References field 12 in table 349. It is the secondary key that is used when traversing table 481.
5 Dimension Name Text 30 CalcField. Lookup to table 348.
6 Dimension Value Name Text 30 CalcField. Lookup to table 349.

Table 481 Dimension Set Tree Node

Table 481 Dimension Set Tree Node is a new table. You cannot change this table.

It is used to search for a dimension set. If the dimension set is not found, a new set is created.

Field No. Field Name Data Type Comment
1 Parent Dimension Set ID Integer 0 for top level node.
2 Dimension Value ID Integer Table relation to field 12 in table 349.
3 Dimension Set ID Integer AutoIncrement. Used in field 1 in table 480.
4 In Use Boolean False if not in use.

Table 482 Reclas. Dimension Set Buffer
Table 482 Reclas. Dimension Set Buffer is a new table.

The table is used to edit a dimension set ID.

It is required when you edit a dimension value code and a new dimension value code, for example, in the Item Reclas. Journal table.

Field No. Field Name Data Type Comment
1 Dimension Code Code 20 Table relation to table 348.
2 Dimension Value Code Code 20 Table relation to table 349.
3 Dimension Value ID Integer References field 12 in table 349.
4 New Dimension Value Code Code 20 Table relation to table 349.
5 New Dimension Value ID Integer References field 12 in table 349.
6 Dimension Name Text 30 CalcField. Lookup to table 348.
7 Dimension Value Name Text 30 CalcField. Lookup to table 349.
8 New Dimension Value Name Text 30 CalcField. Lookup to table 349.

Modified Tables
All transaction and budget tables have been modified to manage dimension set entries.

Changes to Transaction and Budget Tables
A new field has been added to all transaction and budget tables.

Field No. Field Name Data Type Comment
480 Dimension Set ID Integer References field 1 in table 480.

Changes to Table 83 Item Journal Line
Two new fields have been added to table 83 Item Journal Line.

Field No. Field Name Data Type Comment
480 Dimension Set ID Integer References field 1 in table 480.
481 New Dimension Set ID Integer References field 1 in table 480.

Changes to Table 349 Dimension Value
A new field has been added to table 349 Dimension Value.

Field No. Field Name Data Type Comment
12 Dimension Value ID Integer AutoIncrement. Used for references in table 480 and table 481.

Tables That Get New Field 480 Dimension Set ID
A new field, 480 Dimension Set ID, has been added to the following tables.

For the tables that store posted data, the field only provides a non-editable display of dimensions, which is marked as Drill-down.

For the tables that store working documents, the field is editable. The buffer tables that are used internally do not need editable or non-editable capabilities.

The 480 field is non-editable in the following tables

Table No. Table Name
17 G/L Entry
21 Cust. Ledger Entry
25 Vendor Ledger Entry
32 Item Ledger Entry
110 Sales Shipment Header
111 Sales Shipment Line
112 Sales Invoice Header
113 Sales Invoice Line
114 Sales Cr.Memo Header
115 Sales Cr.Memo Line
120 Purch. Rcpt. Header
121 Purch. Rcpt. Line
122 Purch. Inv. Header
123 Purch. Inv. Line
124 Purch. Cr. Memo Hdr.
125 Purch. Cr. Memo Line
169 Job Ledger Entry
203 Res. Ledger Entry
271 Bank Account Ledger Entry
281 Phys. Inventory Ledger Entry
297 Issued Reminder Header
304 Issued Fin. Charge Memo Header
5107 Sales Header Archive
5108 Sales Line Archive
5109 Purchase Header Archive
5110 Purchase Line Archive
5601 FA Ledger Entry
5625 Maintenance Ledger Entry
5629 Ins. Coverage Ledger Entry
5744 Transfer Shipment Header
5745 Transfer Shipment Line
5746 Transfer Receipt Header
5747 Transfer Receipt Line
5802 Value Entry
5832 Capacity Ledger Entry
5907 Service Ledger Entry
5908 Service Header
5933 Service Order Posting Buffer
5970 Filed Service Contract Header
5990 Service Shipment Header
5991 Service Shipment Line
5992 Service Invoice Header
5993 Service Invoice Line
5994 Service Cr. Memo Header
5995 Service Cr. Memo Line
6650 Return Shipment Header
6651 Return Shipment Line
6660 Return Receipt Header
6661 Return Receipt Line

The 480 field is editable in the following tables

Table No. Table Name
36 Sales Header
37 Sales Line
38 Purchase Header
39 Purchase Line
81 Gen. Journal Line
83 Item Journal Line
89 BOM Journal Line
96 G/L Budget Entry
207 Res. Journal Line
210 Job Journal Line
221 Gen. Jnl. Allocation
246 Requisition Line
295 Reminder Header
302 Finance Charge Memo Header
5405 Production Order
5406 Prod. Order Line
5407 Prod. Order Component
5615 FA Allocation
5621 FA Journal Line
5635 Insurance Journal Line
5740 Transfer Header
5741 Transfer Line
5900 Service Header
5901 Service Item Line
5902 Service Line
5965 Service Contract Header
5997 Standard Service Line
7134 Item Budget Entry
99000829 Planning Component

The 480 field has been added to the following buffer tables.

Table No. Table Name
49 Invoice Post. Buffer
212 Job Posting Buffer
372 Payment Buffer
382 CV Ledger Entry Buffer
461 Prepayment Inv. Line Buffer
5637 FA G/L Posting Buffer
7136 Item Budget Buffer

Codeunit 408 Dimension Management
Codeunit 408 Dimension Management is a function library that handles common tasks that are related to dimensions, such as copying from one table to another or from one document to another.

Many functions are deleted because there is no need for copying between dimension tables at the other hand many functions are modified.

Modified Functions

Function Name Modification Description
CheckDimSetIDComb New function that substitutes the other check functions and takes a Dimension Set ID as an argument instead of a dimension table.
CheckDimSetIDComb

CheckDocDimComb

CheckServContractDimComb

CheckDimBuffer

CheckDimComb

CheckDimValueComb

Delete. All usage should be changed to CheckDimSetIDComb.
GetDefaultDim Modify to return an integer Dimension Set ID instead of a set of records.
CopyJnlLineDimToICJnlDim

CopyICJnlDimToJnlLineDim

CopyDocDimtoICDocDim

CopyICDocDimtoICDocDim

Modify to work with DimSetID -> ICJnlLineDim

Deleted Functions

Functions that are deleted from codeunit 408 in connection with the Dimension Set Entries feature are listed below.

During the upgrade of application code from Microsoft Dynamics NAV 2009 or earlier versions to Microsoft Dynamics NAV 2016, the following functions are not available in Microsoft Dynamics NAV 2016.

If you have customizations that use one or more of the functions, you must upgrade that code accordingly.

InsertJnlLineDim

UpdateJnlLineDefaultDim

GetJnlLineDefaultDim

GetPreviousDocDefaultDim

GetPreviousProdDocDefaultDim

InsertDocDim

UpdateDocDefaultDim

ExtractDocDefaultDim

InsertProdDocDim

UpdateProdDocDefaultDim

InsertServContractDim

UpdateServcontractDim

UpdateDefaultDimNewDimValue

GetDocDim

GetProdDocDim

TypeToTableID1

TypeToTableID2

TypeToTableID3

TypeToTableID4

TypeToTableID5

DeleteJnlLineDim

DeleteDocDim

DeletePostedDocDim

DeleteProdDocDim

DeleteServContractDim

ShowJnlLineDim

SaveJnlLineDim

ShowJnlLineNewDim

SaveJnlLineNewDim

ShowDocDim

SaveDocDim

ShowProdDocDim

SaveProdDocDim

ShowTempDim

SaveTempDim

ShowTempNewDim

SaveTempNewDim

SaveServContractDim

MoveJnlLineDimToLedgEntryDim

MoveDocDimToPostedDocDim

MoveOneDocDimToPostedDocDim

MoveLedgEntryDimToJnlLineDim

MoveDimBufToJnlLineDim

MoveDimBufToLedgEntryDim

MoveDimBufToPostedDocDim

MoveDimBufToGLBudgetDim

CopyJnlLineDimToJnlLineDim

CopyLedgEntryDimToJnlLineDim

CopyDocDimToDocDim

CopyPostedDocDimToPostedDocDim

CopyDocDimToJnlLineDim

CopyDimBufToJnlLineDim

CopyDimBufToDocDim

CopySCDimToDocDim

MoveDocDimToLedgEntryDim

MoveDocDimToDocDim

MoveDocDimArchvToDocDim

MoveLedgEntryDimToDocDim

MoveProdDocDimToProdDocDim

MoveJnlLineDimToProdDocDim

MoveJnlLineDimToDocDim

MoveJnlLineDimToJnlLineDim

CopyLedgEntryDimToLedgEntryDim

MoveTempFromDimToTempToDim

TransferTempToDimToDocDim

MoveJnlLineDimToBuf

CopyICJnlDimToICJnlDim

TestDimValue

TestNewDimValue

MoveDimBufToItemBudgetDim. (Delete because the ItemBudgetDim Table is deleted.

GetServContractDim

MoveTempDimToBuf

UpdateSCInvLineDim

CopyJnlLineDimToBuffer

UpdateDocDefaultDim2

Code Examples of Changed Patterns in Modifications

Posting a Journal Line

Key changes are listed as follows:

  • Journal line dimension tables are removed.
  • A dimension set ID is created in the Dimension Set ID field.

Old Code

ResJnlLine.”Qty. per Unit of Measure” :=  SalesLine.”Qty. per Unit of Measure”;

TempJnlLineDim.DELETEALL;

TempDocDim.RESET;

TempDocDim.SETRANGE( “Table ID”,DATABASE::”Sales Line”);

TempDocDim.SETRANGE( “Line No.”,SalesLine.”Line No.”);

DimMgt.CopyDocDimToJnlLineDim( TempDocDim,TempJnlLineDim);

ResJnlPostLine.RunWithCheck( ResJnlLine,TempJnlLineDim);

New Code

ResJnlLine.”Qty. per Unit of Measure” := SalesLine.”Qty. per Unit of Measure”;

ResJnlLine.”Dimension Set ID” := SalesLine.” Dimension Set ID “;

ResJnlPostLine.Run(ResJnlLine);
Posting a Document
When you post a document in Microsoft Dynamics NAV 2016, you no longer have to copy the document dimensions.

Old Code

DimMgt.MoveOneDocDimToPostedDocDim(

TempDocDim,DATABASE::”Sales Line”,

“Document Type”,

“No.”,

SalesShptLine.”Line No.”,

DATABASE::”Sales Shipment Line”,

SalesShptHeader.”No.”);

New Code

SalesShptLine.”Dimension Set ID”  := SalesLine.”Dimension Set ID”
Editing Dimensions from a Document
You can edit dimensions from a document. For example, you can edit a sales order line.

Old Code

Table 37, function ShowDimensions:

TESTFIELD(“Document No.”);

TESTFIELD(“Line No.”);

DocDim.SETRANGE(“Table ID”,DATABASE::”Sales Line”);

DocDim.SETRANGE(“Document Type”,”Document Type”);

DocDim.SETRANGE(“Document No.”,”Document No.”);

DocDim.SETRANGE(“Line No.”,”Line No.”);

DocDimensions.SETTABLEVIEW(DocDim);

DocDimensions.RUNMODAL;

New Code

Table 37, function ShowDimensions:

“Dimension ID” := DimSetEntry.EditDimensionSet( “Dimension ID”);
Showing Dimensions from Posted Entries
You can show dimensions from posted entries, such as sales shipment lines.

Old Code

Table 111, function ShowDimensions:

TESTFIELD(“No.”);

TESTFIELD(“Line No.”);

PostedDocDim.SETRANGE(“Table ID”,DATABASE::”Sales Shipment Line”);

PostedDocDim.SETRANGE(“Document No.”,”Document No.”);

PostedDocDim.SETRANGE(“Line No.”,”Line No.”);

PostedDocDimensions.SETTABLEVIEW(PostedDocDim);

PostedDocDimensions.RUNMODAL;

New Code

Table 111, function ShowDimensions:

DimSetEntry.ShowDimensionSet(“Dimension ID”);
Getting Default Dimensions for a Document
You can get default dimensions for a document, such as a sales order line.

Old Code

Table 37, function CreateDim()

SourceCodeSetup.GET;

TableID[1] := Type1;

No[1] := No1;

TableID[2] := Type2;

No[2] := No2;

TableID[3] := Type3;

No[3] := No3;

“Shortcut Dimension 1 Code” := ”;

“Shortcut Dimension 2 Code” := ”;

DimMgt.GetPreviousDocDefaultDim( DATABASE::”Sales Header”,”Document Type”,

“Document No.”,0, DATABASE::Customer, “Shortcut Dimension 1 Code”,

“Shortcut Dimension 2 Code”);

DimMgt.GetDefaultDim(TableID,No,SourceCodeSetup.Sales,

“Shortcut Dimension 1 Code”, “Shortcut Dimension 2 Code”);

IF “Line No.” <> 0 THEN

DimMgt.UpdateDocDefaultDim( DATABASE::”Sales Line”,”Document Type”,

“Document No.”,”Line No.”, “Shortcut Dimension 1 Code”, “Shortcut Dimension 2 Code”);

New Code

Table 37, function CreateDim()

SourceCodeSetup.GET;

TableID[1] := Type1;

No[1] := No1;

TableID[2] := Type2;

No[2] := No2;

TableID[3] := Type3;

No[3] := No3;

“Shortcut Dimension 1 Code” := ”;

“Shortcut Dimension 2 Code” := ”;

GetSalesHeader;

“Dimension ID” :=  DimMgt.GetDefaultDimID(

TableID,No,SourceCodeSetup.Sales, “Shortcut Dimension 1 Code”,

“Shortcut Dimension 2 Code”, SalesHeader.”Dimension ID”, DATABASE::”Sales Header”);

Important

Most of the contents you find in this blog will be either inherited from MSDN or Navision Developer IT Pro Help. Some places images are also directly taken from these sites. Purpose is simple to try those stuffs and re-produce adding few things as per my understanding to make easy understanding for others and quick reference.

Here nothing under my own brand or authorship of the content. At any point of time we are just promoting Microsoft stuffs nothing personnel with same.

Hope stuffs used here will not violate any copyright agreement with them. In case by mistake or in-intestinally it happens and the Microsoft feels these should not be used Microsoft have full right to inform me about same and will be glad to take down any such content which may be violating the norms.

Purpose is to promote Navision and share with community.

FB Profile

Like FB Page

%d bloggers like this: