Van Vugt: How-to: Create a One-Page Matrix

[Blog bot]

Last week I conducted a NAV Development update to the development team of a Dutch NAV partner. One of the topics we touched was the One-Page Matrix introduced in NAV 2009 SP1. The What's New in Microsoft Dynamics NAV 2009 Service Pack 1 - Installation and Development manual contains a lab that let's you build a One-Page matrix, but the end result is not complete and the overview (what am I doing exactly?) is a bit missing. Therefor I decided to try my best and make up some kind of step-by-step recipe for this (see below).


But First ...
It's good to realize that a matrix is a very useful mean to show the relation between two entities (i.e. tables in NAV). In other words: to see what these two have in common. As in the screen below it might be of interest to get insight in what items our customers have ordered (not yet invoiced/shipped)



this relation between two entities (in our example Customer and Item) is to be found in a third entity being the Sales Line.

From a mathematical perspective a matrix is a 2-dimensional image, with the one entity (Customer) along the X-axis and the other (Item) along the Y-axis. Any combination of X and Y, being a cell in our matrix, represents the relation between this two specific instances of these two entities.

Knowing your maths you might ask if we could create a 3-dimensional matrix. No and Yes (or Jein in German),


No ...
... if we image ourselves something like a Rubik's cube.


Yes ...
... if we would add a filter to the matrix where it would allow us to show (for a specific filter value) a specific intersection of a 3-dimensional matrix.


One-Page Matrix Step-by-Step (high level)
At a high level my step-by-step recipe looks like this:

1. Creating matrix sub page
2. Creating matrix main page
3. Defining horizontal dimension (matrix sub page)
4. Setting horizontal dimension 1 (matrix main page)
5. Setting horizontal dimension 2 (matrix sub page)
6. Populating matrix cells (matrix sub page)
7. Updating page (matrix main page)
8. Browsing horizontal dimension (matrix main page)

One-Page Matrix Step-by-Step (detail level)
An adding all relevant details:


1. Creating matrix sub page

• Using Wizard
  1. Select source table (vertical dimension)
  2. Select page type (ListPart)
  3. Select representative rows for source table (row headings)
     • e.g. "No."/Code + Name/Description

2. Creating matrix main page

1. Using Wizard
   1. Select source table (vertical dimension)
   2. Select page type (Card)
   3. Set Options FastTab
      • Note: the wizard does only set Name and not Caption. See also my blog post How-to: Make Sure Your Pages Can Be Translated
2. Add MatrixForm Part
   • Link to matrix sub page, i.e. set PagePartID

3. Defining horizontal dimension (matrix sub page)

1. Add columns to matrix sub page: copy & paste from existing matrix sub page
   • e.g. PAG9231 - 32 columns
   • Not from PAG9237 (Resource Capacity) as each column contains validation code we do not need/want
2. Declare (global) variables
   1. MATRIX_CellData
   2. MATRIX_ColumnCaption
   3. Field1Visible through Field32Visible
      • Note: have a look at the Visible property of the columns controls
3. Declare function MatrixOnDrillDown
   • Out comment code for time being, we will get back to that later
   • Copy function as a whole from C/AL Globals (see my blog post Live is simple if you know how - C&P chapter 2)

4. Setting horizontal dimension 1 (matrix main page)

1. Declare function SetColumns
   • Copy from PAG491
   • Out comment not relevant parts
   • Declare (global) variables
     1. MATRIX_CaptionSet
     2. MatrixRecord - Link to horizontal dimension
     3. MatrixRecords - Link to horizontal dimension
     4. MatrixRecordRef
     5. MATRIX_PKFirstRecInCurrSet
     6. MATRIX_CaptionRange
     7. MATRIX_CurrSetLength
2. Set when opening page - OnOpenPage
   • call to SetColumns
     1. see PAG491
     2. Declare variable MATRIX_SetWanted

5. Setting horizontal dimension 2 (matrix sub page)

1. Declare function Load
   1. Copy from PAG9231
   2. Declare (global) variables
      1. MatrixRecords - Link to horizontal dimension
      2. MatrixRecord - Link to horizontal dimension
   3. Make sure that parameters also link to the right dimension

6. Populating matrix cells (matrix sub page)

1. i.e. Calculate content of crossings of vertical and horizontal dimensions (on matrix sub page)
2. Columns visibility
   1. OnInit
      1. Set visibility variables to TRUE
      2. Copy from PAG9231
   2. Declare function SetVisible
      • Copy from PAG9231
3. OnOpenPage
   1. Determine number of columns
      1. Copy from PAG9231
      2. Declare (global) variable
         • MATRIX_NoOfMatrixColumns
4. Calculate content
   1. OnAfterGetRecord
      1. Copy from PAG9231
      2. Declare (local) variables
         • MATRIX_CurrentColumnOrdinal
      3. Declare function MATRIX_OnAfterGetRecord
         • See PAG9231 for example, but code depends on the specifics of our matrix

7. Updating page (matrix main page)

1. Declare function UpdateMatrixSubform
   1. Copy from PAG213 (One-Page matrix!)
   2. Call of Load function (on sub page) should be updated to fit the definition of Load on sub page
2. Set when opening page (OnOpenPage)
   • Add call to UpdateMatrixSubform

8. Browsing horizontal dimension (matrix main page)

1. Buttons
   1. Next Set
      1. Copy from PAG491
      2. Add call to UpdateMatrixSubform
   2. Previous Set
      1. Copy from PAG491
      2. Add call to UpdateMatrixSubform
   3. Next Column
      1. Copy from PAG538
      2. Update code (incl. extending SetColumns function - parameter SetWanted and global variable MATRIX_SetWanted)
   4. Previous Column
      1. Copy from PAG538
      2. Update code (incl. extending SetColumns function - parameter SetWanted and global variable MATRIX_SetWanted)

9. Further
Now the matrix is doing quite well, but to make it complete we have some attributes left:

1. DrillDown (matrix sub page)
   • See PAG9231 for example, but code depends on the specifics of our matrix
2. Freeze pane (matrix sub page)
   • Set FreezeColumnID property of Repeater control
3. InsertAllowed/ModifyAllowed/DeleteAllowed (matrix main page)
   • Set to No
4. Editable=No (matrix sub page)
5. Format cells(matrix sub page)
   • i. Set AutoFormatType to 1
   • ii. Not DecimalPlaces
6. Toggle between "No."/Code + Name/Description in the coumn headings (matrix main page)
   1. Copy from PAG491
      1. Control on Options FastTab
      2. Code in SetColumns
      3. Declare (global) variable
         • ShowColumnName
      4. Declare function ShowColumnNameOnAfterValidate
         • Add call to UpdateMatrixSubform
7. Automatic Update of Page (matrix main page)
   1. Declare function UpdateThePage
      • Using WshShell.SendKeys

Notes

• Over time this post might change due to new insights
• Concluding every high level step (1 through 8) you should be able to run the matrix and see the effect of what you have done so far.
• Using the Windows Script Host Object Model will ask the user if this call to this external component is allowed:
  
  
• Of course NAV has a number of matrices that have most of he above in common. However they all have some differences too. Have a look at them to learn from these differences. The list below show the main pages for the standad One-Page matrices in NAV:
  Page ID
  
  Page Name
  
  113
  
  Budget
  
  213
  
  Resource Capacity
  
  214
  
  Res. Group Capacity
  
  538
  
  Dimension Combinations
  
  9252
  
  MyDim Value Combinations (previously form 539)
  
  7138
  
  Purchase Budget Overview
  
  7139
  
  Sales Budget Overview
  
  99000919
  
  Production Forecast

Читать дальше