Microsoft Dynamics NAV offers a functionality called Dimensions. Dimensions allow you to define data you want to capture with your transactions and report on.
Microsoft Dynamics NAV is the most flexible ERP system on the market, and Dimensions are one of the features providing flexibility.
One of the most popular uses of this functionality is to set up a Dimension that serves as Sub-G/L Account. In this use, the Dimension is thought to be in a hierarchy below the G/L Account.
This presentation discusses a software modification that allows you to define a hierarchical ranking between two Dimensions. One Dimension serves as a Main Dimension, the other Dimension will be a Sub-Dimension to the Main Dimension. T
Suppose your company uses a PROJECT dimension in Dynamics NAV to record project data.
Your company also uses a third dimension BUDGET to track budget data. You intend to budget within projects.
In the General Ledger Setup, Project is defined as second dimension. Budget is defined as third Dimension. This numbering does not constitute a hierarchy. The third Dimension is not subordinated to the second Dimension.
For every project, a distinct set of budget codes has been set up.Budget codes starting with the letter B belong to the Boston project. Budget codes starting with the letter P belong to the project of the development of a new product line. Budget codes starting with the letter S belong to the project of setting up a subsidiary in Brazil.
This lettering is a logic assignment of budget codes to projects. The program does not recognize this assignment and cannot check postings for correct combinations of project and budget codes.
It is possible that a bookkeeper posts an invoice entering project BOSTON and the Brazil Budget code S0220 Installation.
The posted invoice contains an impossible combination of project and budget code. It is cumbersome to reverse the invoice and enter it again.
The system should be set up so that it recognizes impossible combinations of projects and budget codes and does not allow the posting of transactions with such combinations.
Under Financial Management, Administration you can find Dimension Combinations. Here you can establish limits for the posting of dimensions.
Here you can establish which project should be allowed to be posted with which budget code. You do that by blocking all other combinations.
You quickly realize this spreadheet will be too big for your to fill out and to maintain manually.
The system should be modified in a way so that you would fill out the permissible combinations rather than the non-permissible combinations.
Hierarchical Dimensions – Table Design
Open the Object Designer and find Table 349 Dimension Value
Open Table 349 Dimension Value in Design Mode and enter a remark into the Documentation() trigger that serves as a record of your software change.
Add a new field 50000 Project Code to the table.
The new field has the Data Type Code and the Length 20. The Description PC766 gives later programmers an idea where this field came from.
Give Field 50000 Project Code a Table Relation to itself, Table 349 Dimension Value.
The TableRelation has a Table Filter for the Global Dimension Value 2, which in our database has been defined as PROJECT.
Hierarchical Dimensions – Page Design
To make the new field visible, it has to be added to Page 537 Dimension Values. Open Page 537 in the Object Designer and add a new line of Type Field. Type “Project Code” into the Field SourceExpr. The system will then fill the other fields.
Hierarchical Dimensions – Data Hierarchy
The page Dimension Values now shows a new column Project Code where you enter the Project Code a Budget Code should belong to.
Your database now contains a relationship between Project and Budget Code.
Now we need to set up a control mechanism that enforces this relationship when posting transactions.
Refreshing Hierarchical Dimensions
We designed a new Codeunit 50006 Dimension 3 Management which reads the assignment of Budget Codes to Projects and then blocks all other Dimension Value Combinations.
After a user edits any Budget Codes, Codeunit 50006 must be called.
When editing Budget Codes it is recommended to call the Dimension Value Indent function, because it ensures the totaling formulas are correct.
You can easily connect the blocking of Dimension Value Combinations to the indentation of Dimension Values. To this end Codeunit 409 Dimension Value-Indent has been modified.
As a result of this software modification the system refreshes the Dimension Value Combinations every time you indent Dimension Values. Now a Budget Code can only be posted with an allowed Project Code.
As you can see in this picture, Budget Code B0220 Installation is connected to Project Code BOSTON.
If the bookkeeper working at an invoice makes a mistake and enters another Project Code like SBRAZIL, the system does not post such an invoice and generates an error message.
Lookup for Hierarchical Dimensions
What we have accomplished so far with our software modification is that the system does not allow postings of transactions with wrong combinations of Budget Codes and Project Codes.
Now we want to make the bookkeeper’s live easier. When entering data into a purchase invoice, only Budget Codes belonging to the selected Project should be listed.
In this example invoice, Project Code BOSTON has been selected. When the bookkeeper clicks on the lookup arrow in field Budget Code, only the Boston Budget Codes should be listed.
Designing a Lookup for Hierarchical Dimensions
To limit the lookup of Budget Codes to the corresponding Projects, new pieces of software have to be added in three different places of the application.
Codeunit 408 DimensionManagement gets a new function LookupDim3ValueCode.
Table 39 Purchase Line gets a new function LookupShortcutDim3Code.
In Page 55 Purch. Invoice Subform we replace the standard lookup function call with our own call.
Result of a Limited Lookup for Hierarchical Dimensions
The result of our software modification can now be seen in the purchase invoice:
The bookkeeper selected Project BOSTON in the invoice line. If he now clicks the lookup arrow in field Budget Code, only the Budget Codes belongin to the Boston Project will be listed.
This helps prevent data entry errors and makes the live of the bookkeeper easier.
Author: Thomas Paulsen