Van Vugt's dynamiXs

All around NAV dev and test

Blog Post List
  • Luc van Vugt

    How-to: Create a One-Page Matrix by Example #2

    Using the step-by-step recipe, and basing on our first example, I have build another example One-Page for the following requirements:

    Customer Spending by Period:

    Display the total amount for each period (type, i.e. day, week, month, quarter, year, accounting period) what each of our customers has in order.

    In this case the design of our matrix would be:

    • matrix cells displaying the total amount
    • horizontal dimension being the periods
    • vertical dimension being our customers

    For this matrix I Will be using the same extensions to the Customer and Sales Line tables as in our first example, but as we need to be able to calculate the amount in the matrix cells for specific periods we need some small modifications to the Sales Line table:

    Key

    SumIndexFields

    Sell-to Customer No.,Type,No.,Shipment Date

    Amount

    Customer Spending by Period Matrix

    This One-Page matrix consists of two pages:

    • matrix main page: 62106 - Customer by Period Spending
    • matrix sub page: 62108 - Cust. Spending by Period Matrix

    As in the previous example the changes have been marked related to the steps as defined by my step-by-step recipe.

    Note

    In code below square brackets 6, 8 and i are displayed as Devil and Music and Idea.

    PAG 62106

    OBJECT Page 62106 Customer Spending by Period

    {

      OBJECT-PROPERTIES

      {

        Date=26-04-12;

        Time=12:00:00;

        Version List=Advanced Matrix Box;

      }

      PROPERTIES

      {

        CaptionML=ENU=Customer Spending by Period;

        SaveValues=Yes;

        InsertAllowed=No;

        DeleteAllowed=No;

        ModifyAllowed=No;

        SourceTable=Table18;

        PageType=Card;

        OnOpenPage=BEGIN

                     //STEP_4-BEGIN

                     SetColumns(MATRIX_SetWanted::Initial);

                     //STEP_7-BEGIN

                     UpdateMatrixSubform

                     //STEP_7-END

                     //STEP_4-END

                   END;

     

        ActionList=ACTIONS

        {

          { 1900000004;0 ;ActionContainer;

                          ActionContainerType=ActionItems }

          { 31      ;1   ;Action    ;

                          CaptionML=ENU=Previous Set;

                          ToolTipML=ENU=Previous Set;

                          Description=STEP_8;

                          Promoted=Yes;

                          PromotedIsBig=Yes;

                          Image=PreviousSet;

                          PromotedCategory=Process;

                          OnAction=BEGIN

                                     //STEP_8-BEGIN

                                     SetColumns(MATRIX_SetWanted::Previous);

                                     UpdateMatrixSubform

                                     //STEP_8-END

                                   END;

                                    }

          { 102     ;1   ;Action    ;

                          CaptionML=ENU=Previous Column;

                          ToolTipML=ENU=Previous;

                          Description=STEP_8;

                          Promoted=Yes;

                          PromotedIsBig=Yes;

                          Image=PreviousRecord;

                          PromotedCategory=Process;

                          OnAction=BEGIN

                                     //STEP_8-BEGIN

                                     SetColumns(MATRIX_SetWanted::PreviousColumn);

                                     UpdateMatrixSubform

                                     //STEP_8-END

                                   END;

                                    }

          { 103     ;1   ;Action    ;

                          CaptionML=ENU=Next Column;

                          ToolTipML=ENU=Next;

                          Description=STEP_8;

                          Promoted=Yes;

                          PromotedIsBig=Yes;

                          Image=NextRecord;

                          PromotedCategory=Process;

                          OnAction=BEGIN

                                     //STEP_8-BEGIN

                                     SetColumns(MATRIX_SetWanted::NextColumn);

                                     UpdateMatrixSubform

                                     //STEP_8-END

                                   END;

                                    }

          { 32      ;1   ;Action    ;

                          CaptionML=ENU=Next Set;

                          ToolTipML=ENU=Next Set;

                          Description=STEP_8;

                          Promoted=Yes;

                          PromotedIsBig=Yes;

                          Image=NextSet;

                          PromotedCategory=Process;

                          OnAction=BEGIN

                                     //STEP_8-BEGIN

                                     SetColumns(MATRIX_SetWanted::Next);

                                     UpdateMatrixSubform

                                     //STEP_8-END

                                   END;

                                    }

        }

      }

      CONTROLS

      {

        { 1900000001;0;Container;

                    ContainerType=ContentArea }

     

        { 1   ;1   ;Group     ;

                    CaptionML=ENU=Matrix Options }

     

        { 6   ;2   ;Field     ;

                    CaptionML=ENU=View by;

                    OptionCaptionML=ENU=Day,Week,Month,Quarter,Year,Accounting Period;

                    Description=STEP_9;

                    SourceExpr=PeriodType;

                    OnValidate=BEGIN

                                 //STEP_9-BEGIN

                                 SetColumns(MATRIX_SetWanted::Initial);

                                 UpdateMatrixSubform;

                                 //STEP_9-END

                               END;

                                }

     

        { 9   ;2   ;Field     ;

                    CaptionML=ENU=View as;

                    OptionCaptionML=ENU=Net Change,Balance at Date;

                    Description=STEP_9;

                    SourceExpr=QtyType;

                    OnValidate=BEGIN

                                 //STEP_9-BEGIN

                                 UpdateMatrixSubform;

                                 //STEP_9-END

                               END;

                                }

     

        { 33  ;1   ;Part      ;

                    Name=MatrixForm;

                    Description=STEP_2;

                    PagePartID=Page62108 }

     

      }

      CODE

      {

        VAR

          MatrixRecord@1004 : Record 2000000007;

          MatrixRecords@1000 : ARRAY [12] OF Record 2000000007;

          PeriodType@1001 : 'Day,Week,Month,Quarter,Year,Accounting Period';

          QtyType@1002 : 'Net Change,Balance at Date';

          MATRIX_ColumnCaptions@1010 : ARRAY [12] OF Text[1024];

          MATRIX_ColumnSet@1007 : Text[1024];

          MATRIX_SetWanted@1005 : 'Initial,Previous,Same,Next,PreviousColumn,NextColumn';

          MATRIX_PKFirstRecInCurrSet@1006 : Text[100];

          MATRIX_CurrSetLength@1008 : Integer;

          WshShell@1100409000 : Automation "{F935DC20-1CF0-11D0-ADB9-00C04FD58A0B} 1.0:{72C24DD5-D70A-438B-8A42-98424B88AFB8}:'Windows Script Host Object Model'.WshShell";

     

        PROCEDURE SetColumns@11(SetWanted@1001 : 'Initial,Previous,Same,Next,PreviousColumn,NextColumn');

        VAR

          MatrixMgt@1000 : Codeunit 9200;

          DateFilter@1002 : Text[30];

        BEGIN

          //STEP_4-BEGIN

          MatrixMgt.GeneratePeriodMatrixData(SetWanted,12,FALSE,PeriodType,'',

            MATRIX_PKFirstRecInCurrSet,MATRIX_ColumnCaptions,MATRIX_ColumnSet,MATRIX_CurrSetLength,MatrixRecords);

          //STEP_4-END

        END;

     

        PROCEDURE UpdateMatrixSubform@7();

        BEGIN

          //STEP_7-BEGIN

          CurrPage.MatrixForm.FORM.Load(PeriodType,QtyType,MATRIX_ColumnCaptions,MatrixRecords,MATRIX_CurrSetLength);

          //STEP_9-BEGIN

          UpdateThePage

          //STEP_9-END

          //STEP_7-END

        END;

     

        PROCEDURE UpdateThePage@10();

        BEGIN

          //STEP_9-BEGIN

          IF ISCLEAR(WshShell) THEN

            CREATE(WshShell, TRUE, TRUE);

          WshShell.SendKeys('{F5}');

          //STEP_9-END

        END;

     

        BEGIN

        {

          <changelog>

            <add id="STEP_2"

              dev="lvanvugt"

              date="2012-04-26"

              source="http://dynamicsuser.net/blogs/vanvugt/archive/2012/04/20/how-to-create-a-one-page-matrix.aspx"

              releaseversion="Advanced Matrix Box">Step 2 - One-Page Matrix Step-by-Step</add>

            <change id="STEP_4"

              dev="lvanvugt"

              date="2012-04-26"

              source="http://dynamicsuser.net/blogs/vanvugt/archive/2012/04/20/how-to-create-a-one-page-matrix.aspx"

              releaseversion="Advanced Matrix Box">Step 4 - One-Page Matrix Step-by-Step</change>

            <change id="STEP_7"

              dev="lvanvugt"

              date="2012-04-26"

              source="http://dynamicsuser.net/blogs/vanvugt/archive/2012/04/20/how-to-create-a-one-page-matrix.aspx"

              releaseversion="Advanced Matrix Box">Step 7 - One-Page Matrix Step-by-Step</change>

            <change id="STEP_8"

              dev="lvanvugt"

              date="2012-04-26"

              source="http://dynamicsuser.net/blogs/vanvugt/archive/2012/04/20/how-to-create-a-one-page-matrix.aspx"

              releaseversion="Advanced Matrix Box">Step 8 - One-Page Matrix Step-by-Step</change>

            <change id="STEP_9"

              dev="lvanvugt"

              date="2012-04-26"

              source="http://dynamicsuser.net/blogs/vanvugt/archive/2012/04/20/how-to-create-a-one-page-matrix.aspx"

              releaseversion="Advanced Matrix Box">Step 9 - One-Page Matrix Step-by-Step</change>

          </changelog>

        }

        END.

      }

    }

      

    PAG 62108

    OBJECT Page 62108 Cust Spending by Period Matrix

    {

      OBJECT-PROPERTIES

      {

        Date=26-04-12;

        Time=12:00:00;

        Version List=Advanced Matrix Box;

      }

      PROPERTIES

      {

        Editable=No;

        CaptionML=ENU=Customer Spending by Period Matrix;

        SourceTable=Table18;

        PageType=ListPart;

        OnAfterGetRecord=VAR

                           MATRIX_CurrentColumnOrdinal@1043 : Integer;

                           MATRIX_Steps@1044 : Integer;

                         BEGIN

                           //STEP_6-BEGIN

                           MATRIX_CurrentColumnOrdinal := 0;

                           WHILE MATRIX_CurrentColumnOrdinal < MATRIX_NoOfMatrixColumns DO BEGIN

                             MATRIX_CurrentColumnOrdinal := MATRIX_CurrentColumnOrdinal + 1;

                             MATRIX_OnAfterGetRecord(MATRIX_CurrentColumnOrdinal);

                           END;

                           //STEP_6-END

                         END;

     

      }

      CONTROLS

      {

        { 1100409000;0;Container;

                    ContainerType=ContentArea }

     

        { 1   ;1   ;Group     ;

                    GroupType=Repeater;

                    FreezeColumnID=Name }

     

        { 6   ;2   ;Field     ;

                    Description=STEP_1;

                    SourceExpr="No." }

     

        { 4   ;2   ;Field     ;

                    Description=STEP_1;

                    SourceExpr=Name }

     

        { 1011;2   ;Field     ;

                    Name=Field1;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[1];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[1];

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(1);

                                END;

                                 }

     

        { 1012;2   ;Field     ;

                    Name=Field2;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[2];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[2];

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(2);

                                END;

                                 }

     

        { 1013;2   ;Field     ;

                    Name=Field3;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[3];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[3];

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(3);

                                END;

                                 }

     

        { 1014;2   ;Field     ;

                    Name=Field4;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[4];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[4];

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(4);

                                END;

                                 }

     

        { 1015;2   ;Field     ;

                    Name=Field5;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[5];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[5];

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(5);

                                END;

                                 }

     

        { 1016;2   ;Field     ;

                    Name=Field6;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellDataDevil;

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaptionDevil;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(6);

                                END;

                                 }

     

        { 1017;2   ;Field     ;

                    Name=Field7;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[7];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[7];

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(7);

                                END;

                                 }

     

        { 1018;2   ;Field     ;

                    Name=Field8;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellDataMusic;

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaptionMusic;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(8);

                                END;

                                 }

     

        { 1019;2   ;Field     ;

                    Name=Field9;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[9];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[9];

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(9);

                                END;

                                 }

     

        { 1020;2   ;Field     ;

                    Name=Field10;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[10];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[10];

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(10);

                                END;

                                 }

     

        { 1021;2   ;Field     ;

                    Name=Field11;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[11];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[11];

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(11);

                                END;

                                 }

     

        { 1022;2   ;Field     ;

                    Name=Field12;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[12];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[12];

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(12);

                                END;

                                 }

     

      }

      CODE

      {

        VAR

          SalesLine@1100409000 : Record 37;

          PeriodType@1080 : 'Day,Week,Month,Quarter,Year,Accounting Period';

          QtyType@1081 : 'Net Change,Balance at Date';

          MatrixRecords@1000 : ARRAY [12] OF Record 2000000007;

          MATRIX_NoOfMatrixColumns@1084 : Integer;

          MATRIX_CellData@1085 : ARRAY [12] OF Decimal;

          MATRIX_ColumnCaption@1001 : ARRAY [12] OF Text[1024];

     

        LOCAL PROCEDURE SetDateFilter@1086(ColumnID@1000 : Integer);

        BEGIN

          //STEP_6-BEGIN

          IF QtyType = QtyType::"Net Change" THEN

            IF MatrixRecords[ColumnID]."Period Start" = MatrixRecords[ColumnID]."Period End" THEN

              SETRANGE("Date Filter",MatrixRecords[ColumnID]."Period Start")

            ELSE

              SETRANGE("Date Filter",MatrixRecords[ColumnID]."Period Start",MatrixRecords[ColumnID]."Period End")

          ELSE

            SETRANGE("Date Filter",0D,MatrixRecords[ColumnID]."Period End");

          //STEP_6-END

        END;

     

        LOCAL PROCEDURE MATRIX_OnAfterGetRecord@1091(ColumnID@1000 : Integer);

        BEGIN

          //STEP_6-BEGIN

          SetDateFilter(ColumnID);

          CALCFIELDS("Total Amount");

          IF "Total Amount" <> 0 THEN

            MATRIX_CellData[ColumnID] := "Total Amount"

          ELSE

            MATRIX_CellData[ColumnID] := 0;

          //STEP_6-END

        END;

     

        PROCEDURE Load@3(PeriodType1@1003 : 'Day,Week,Month,Quarter,Year,Accounting Period';QtyType1@1000 : 'Net Change,Balance at Date';MatrixColumns1@1001 : ARRAY [12] OF Text[1024];VAR MatrixRecords1@1002 : ARRAY [12] OF Record 2000000007;NoOfMatrixColumns1@1004 : Integer);

        VAR

          i@1005 : Integer;

        BEGIN

          //STEP_5-BEGIN

          PeriodType := PeriodType1;

          QtyType := QtyType1;

          COPYARRAY(MATRIX_ColumnCaption,MatrixColumns1,1);

          FOR i := 1 TO ARRAYLEN (MatrixRecords) DO

            MatrixRecordsIdea.COPY(MatrixRecords1Idea);

          MATRIX_NoOfMatrixColumns := NoOfMatrixColumns1;

          //STEP_5-END

        END;

     

        PROCEDURE MatrixOnDrillDown@4(ColumnID@1000 : Integer);

        BEGIN

          //STEP_9-BEGIN

          SetDateFilter(ColumnID);

          SalesLine.SETCURRENTKEY("Sell-to Customer No.",Type,"No.","Shipment Date");

          SalesLine.SETRANGE("Sell-to Customer No.","No.");

          SalesLine.SETFILTER("Shipment Date",GETFILTER("Date Filter"));

          FORM.RUN(0,SalesLine);

          //STEP_9-END

        END;

     

        BEGIN

        {

          <changelog>

            <add id="STEP_1"

              dev="lvanvugt"

              date="2012-04-26"

              source="http://dynamicsuser.net/blogs/vanvugt/archive/2012/04/20/how-to-create-a-one-page-matrix.aspx"

              releaseversion="Advanced Matrix Box">Step 1 - One-Page Matrix Step-by-Step</add>

            <change id="STEP_3"

              dev="lvanvugt"

              date="2012-04-26"

              source="http://dynamicsuser.net/blogs/vanvugt/archive/2012/04/20/how-to-create-a-one-page-matrix.aspx"

              releaseversion="Advanced Matrix Box">Step 3 - One-Page Matrix Step-by-Step</change>

            <change id="STEP_5"

              dev="lvanvugt"

              date="2012-04-26"

              source="http://dynamicsuser.net/blogs/vanvugt/archive/2012/04/20/how-to-create-a-one-page-matrix.aspx"

              releaseversion="Advanced Matrix Box">Step 5 - One-Page Matrix Step-by-Step</change>

            <change id="STEP_6"

              dev="lvanvugt"

              date="2012-04-26"

              source="http://dynamicsuser.net/blogs/vanvugt/archive/2012/04/20/how-to-create-a-one-page-matrix.aspx"

              releaseversion="Advanced Matrix Box">Step 6 - One-Page Matrix Step-by-Step</change>

            <change id="STEP_9"

              dev="lvanvugt"

              date="2012-04-26"

              source="http://dynamicsuser.net/blogs/vanvugt/archive/2012/04/20/how-to-create-a-one-page-matrix.aspx"

              releaseversion="Advanced Matrix Box">Step 9 - One-Page Matrix Step-by-Step</change>

          </changelog>

        }

        END.

      }

    }

     

  • Luc van Vugt

    How-to: Create a One-Page Matrix by Example

    Using the step-by-step recipe I have build an example One-Page matrix based on the following requirements:

    Customer/Item Spending:

    Display the total amount for each item what each of our customers has in order.

    Basically this line already reveals the design of our matrix:

    • Display the total amount ... -> matrix cells
    • for each item what ... -> horizontal dimension
    • each of our customers has in order -> vertical dimension

     To be able to build the matrix I need to do some preparation:

    Customer table 

    This table needs to be extended with the following three fields (that actualy do the calculations needed for the matrix cells):

    Field No.

    Field Name

    Data Type

    Length

    Description

    62.100

    Total Amount

    Decimal

     

    FlowField: sum of Amount- fields in Sales Line table for customer (incl. next FlowFilters)

    62.101

    Type Filter

    Option

     

    FlowFilter for Type field in Sales Line table
    OptionString =  ,G/L Account,Item,Resource,Fixed Asset,Charge (Item)

    62.102

    No. Filter

    Code

    20

    FlowFilter on No. field in Sales Line table

    Sales Line table

    The following secondary key should be added (to facilitate the FlowField on the Customer table) with Amount field as SumIndexField:

    Key

    SumIndexFields

    Sell-to Customer No.,Type,No.

    Amount

    Customer/Item Spending Matrix

    This One-Page matrix consists of two pages:

    • matrix main  page: 62105 - Customer/Item Spending
    • matrix sub page: 62107 - Customer/Item Spending Matrix

    In both objects (were possible) the changes have been marked related to the steps as defined by my step-by-step recipe.

    Notes

    • Change markers in the objects have be changed from AMBxxxx (AMB being the acronym for Advanced Matrix Box as mentioned in the Version List of each bobject) to STEP_x explicitly relating to the steps  as defined by my step-by-step recipe. For reading purposes all occurences of the change markers have colored green.
    • In code below square brackets 6 and 8 are displayed as Devil and Music

    PAG62105

    OBJECT Page 62105 Customer/Item Spending

    {

      OBJECT-PROPERTIES

      {

        Date=20-04-12;

        Time=12:00:00;

        Version List=Advanced Matrix Box;

      }

      PROPERTIES

      {

        CaptionML=ENU=Customer/Item Spending;

        SaveValues=Yes;

        InsertAllowed=No;

        DeleteAllowed=No;

        ModifyAllowed=No;

        SourceTable=Table18;

        PageType=Card;

        OnOpenPage=BEGIN

                     //STEP_4-BEGIN

                     SetColumns(MATRIX_SetWanted::Initial);

                     //STEP_7-BEGIN

                     UpdateMatrixSubform

                     //STEP_7-END

                     //STEP_4-END

                   END;

     

        ActionList=ACTIONS

        {

          { 1900000004;0 ;ActionContainer;

                          ActionContainerType=ActionItems }

          { 31   ;1 ;Action    ;

                          CaptionML=ENU=Previous Set;

                          ToolTipML=ENU=Previous Set;

                          Description=STEP_8;

                          Promoted=Yes;

                          PromotedIsBig=Yes;

                          Image=PreviousSet;

                          PromotedCategory=Process;

                          OnAction=BEGIN

                                     //STEP_8-BEGIN

                                     SetColumns(MATRIX_SetWanted::Previous);

                                     UpdateMatrixSubform

                                     //STEP_8-END

                                   END;

                                    }

          { 102  ;1 ;Action    ;

                          CaptionML=ENU=Previous Column;

                          ToolTipML=ENU=Previous;

                          Description=STEP_8;

                          Promoted=Yes;

                          PromotedIsBig=Yes;

                          Image=PreviousRecord;

                          PromotedCategory=Process;

                          OnAction=BEGIN

                                     //STEP_8-BEGIN

                                     SetColumns(MATRIX_SetWanted::PreviousColumn);

                                     UpdateMatrixSubform();

                                     //STEP_8-END

                                   END;

                                    }

          { 103  ;1 ;Action    ;

                          CaptionML=ENU=Next Column;

                          ToolTipML=ENU=Next;

                          Description=STEP_8;

                          Promoted=Yes;

                          PromotedIsBig=Yes;

                          Image=NextRecord;

                          PromotedCategory=Process;

                          OnAction=BEGIN

                                     //STEP_8-BEGIN

                                     SetColumns(MATRIX_SetWanted::NextColumn);

                                     UpdateMatrixSubform();

                                     //STEP_8-END

                                   END;

                                    }

          { 32   ;1 ;Action    ;

                          CaptionML=ENU=Next Set;

                          ToolTipML=ENU=Next Set;

                          Description=STEP_8;

                          Promoted=Yes;

                          PromotedIsBig=Yes;

                          Image=NextSet;

                          PromotedCategory=Process;

                          OnAction=BEGIN

                                     //STEP_8-BEGIN

                                     SetColumns(MATRIX_SetWanted::Next);

                                     UpdateMatrixSubform

                                     //STEP_8-END

                                   END;

                                    }

        }

      }

      CONTROLS

      {

        { 1900000001;0;Container;

                    ContainerType=ContentArea }

     

        { 12  ;1   ;Group     ;

                    CaptionML=ENU=Options }

     

        { 15  ;2   ;Field     ;

                    CaptionML=[ENU=Show Column Name;

                               NLD=Kolomnaam weergeven];

                    Description=STEP_9;

                    SourceExpr=ShowColumnName;

                    OnValidate=BEGIN

                                 ShowColumnNameOnAfterValidate;

                               END;

                                }

     

        { 33  ;1   ;Part      ;

                    Name=MatrixForm;

                    Description=STEP_2;

                    PagePartID=Page62107;

                    PartType=Page }

     

      }

      CODE

      {

        VAR

          MatrixRecord@1000 : Record 27;

          MatrixRecords@1010 : ARRAY [32] OF Record 27;

          MatrixRecordRef@1007 : RecordRef;

          MATRIX_SetWanted@1006 : 'Initial,Previous,Same,Next,PreviousColumn,NextColumn';

          ShowColumnName@1002 : Boolean;

          MATRIX_CaptionSet@1008 : ARRAY [32] OF Text[1024];

          MATRIX_CaptionRange@1011 : Text[100];

          MATRIX_PKFirstRecInCurrSet@1001 : Text[100];

          MATRIX_CurrSetLength@1004 : Integer;

          WshShell@11091689 : Automation "{F935DC20-1CF0-11D0-ADB9-00C04FD58A0B} 1.0:{72C24DD5-D70A-438B-8A42-98424B88AFB8}:'Windows Script Host Object Model'.WshShell";

     

        PROCEDURE SetColumns@6(SetWanted@1002 : 'Initial,Previous,Same,Next,PreviousColumn,NextColumn');

        VAR

          MatrixMgt@1000 : Codeunit 9200;

          CaptionFieldNo@1001 : Integer;

          CurrentMatrixRecordOrdinal@1003 : Integer;

        BEGIN

          //STEP_4-BEGIN

          CLEAR(MATRIX_CaptionSet);

          CLEAR(MatrixRecords);

          CurrentMatrixRecordOrdinal := 1;

     

          MatrixRecordRef.GETTABLE(MatrixRecord);

          MatrixRecordRef.SETTABLE(MatrixRecord);

     

          //STEP_9-BEGIN

          IF ShowColumnName THEN

            CaptionFieldNo := MatrixRecord.FIELDNO(Description)

          ELSE

          //STEP_9-END

            CaptionFieldNo := MatrixRecord.FIELDNO("No.");

     

          MatrixMgt.GenerateMatrixData(MatrixRecordRef,SetWanted,ARRAYLEN(MatrixRecords),CaptionFieldNo,MATRIX_PKFirstRecInCurrSet,

            MATRIX_CaptionSet,MATRIX_CaptionRange,MATRIX_CurrSetLength);

     

          IF MATRIX_CurrSetLength > 0 THEN BEGIN

            MatrixRecord.SETPOSITION(MATRIX_PKFirstRecInCurrSet);

            MatrixRecord.FIND;

            REPEAT

              MatrixRecords[CurrentMatrixRecordOrdinal].COPY(MatrixRecord);

              CurrentMatrixRecordOrdinal := CurrentMatrixRecordOrdinal + 1;

            UNTIL (CurrentMatrixRecordOrdinal > MATRIX_CurrSetLength) OR (MatrixRecord.NEXT <> 1);

          END;

          //STEP_4-END

        END;

     

        PROCEDURE UpdateMatrixSubform@7();

        BEGIN

          //STEP_7-BEGIN

          CurrPage.MatrixForm.FORM.Load(MATRIX_CaptionSet,MatrixRecords,MatrixRecord);

          //STEP_9-BEGIN

          UpdateThePage

          //STEP_9-END

          //STEP_7-END

        END;

     

        LOCAL PROCEDURE ShowColumnNameOnAfterValidate@8();

        BEGIN

          //STEP_9-BEGIN

          SetColumns(MATRIX_SetWanted::Same);

          UpdateMatrixSubform

          //STEP_9-END

        END;

     

        PROCEDURE UpdateThePage@10();

        BEGIN

          //STEP_9-BEGIN

          IF ISCLEAR(WshShell) THEN

            CREATE(WshShell, TRUE, TRUE);

          WshShell.SendKeys('{F5}');

          //STEP_9-END

        END;

     

        BEGIN

        {

          <changelog>

            <add id="STEP_2"

              dev="lvanvugt"

              date="2012-04-20"

              source="http://dynamicsuser.net/blogs/vanvugt/archive/2012/04/20/how-to-create-a-one-page-matrix.aspx"

              releaseversion="Advanced Matrix Box">Step 2 - One-Page Matrix Step-by-Step</add>

            <change id="STEP_4"

              dev="lvanvugt"

              date="2012-04-20"

              source="http://dynamicsuser.net/blogs/vanvugt/archive/2012/04/20/how-to-create-a-one-page-matrix.aspx"

              releaseversion="Advanced Matrix Box">Step 4 - One-Page Matrix Step-by-Step</change>

            <change id="STEP_7"

              dev="lvanvugt"

              date="2012-04-20"

              source="http://dynamicsuser.net/blogs/vanvugt/archive/2012/04/20/how-to-create-a-one-page-matrix.aspx"

              releaseversion="Advanced Matrix Box">Step 7 - One-Page Matrix Step-by-Step</change>

            <change id="STEP_8"

              dev="lvanvugt"

              date="2012-04-20"

              source="http://dynamicsuser.net/blogs/vanvugt/archive/2012/04/20/how-to-create-a-one-page-matrix.aspx"

              releaseversion="Advanced Matrix Box">Step 8 - One-Page Matrix Step-by-Step</change>

            <change id="STEP_9"

              dev="lvanvugt"

              date="2012-04-20"

              source="http://dynamicsuser.net/blogs/vanvugt/archive/2012/04/20/how-to-create-a-one-page-matrix.aspx"

              releaseversion="Advanced Matrix Box">Step 9 - One-Page Matrix Step-by-Step</change>

          </changelog>

        }

        END.

      }

    }

     

     

     

     

    PAG62107

     

    OBJECT Page 62107 Customer/Item Spending Matrix

    {

      OBJECT-PROPERTIES

      {

        Date=20-04-12;

        Time=12:00:00;

        Version List=Advanced Matrix Box;

      }

      PROPERTIES

      {

        Editable=No;

        CaptionML=ENU=Customer/Item Spending Matix;

        SourceTable=Table18;

        PageType=ListPart;

        OnInit=BEGIN

                 //STEP_6-BEGIN

                 Field32Visible := TRUE;

                 Field31Visible := TRUE;

                 Field30Visible := TRUE;

                 Field29Visible := TRUE;

                 Field28Visible := TRUE;

                 Field27Visible := TRUE;

                 Field26Visible := TRUE;

                 Field25Visible := TRUE;

                 Field24Visible := TRUE;

                 Field23Visible := TRUE;

                 Field22Visible := TRUE;

                 Field21Visible := TRUE;

                 Field20Visible := TRUE;

                 Field19Visible := TRUE;

                 Field18Visible := TRUE;

                 Field17Visible := TRUE;

                 Field16Visible := TRUE;

                 Field15Visible := TRUE;

                 Field14Visible := TRUE;

                 Field13Visible := TRUE;

                 Field12Visible := TRUE;

                 Field11Visible := TRUE;

                 Field10Visible := TRUE;

                 Field9Visible := TRUE;

                 Field8Visible := TRUE;

                 Field7Visible := TRUE;

                 Field6Visible := TRUE;

                 Field5Visible := TRUE;

                 Field4Visible := TRUE;

                 Field3Visible := TRUE;

                 Field2Visible := TRUE;

                 Field1Visible := TRUE;

                 //STEP_6-END

               END;

     

        OnOpenPage=BEGIN

                     //STEP_6-BEGIN

                     MATRIX_NoOfMatrixColumns := ARRAYLEN(MATRIX_CellData);

                     //STEP_6-END

                   END;

     

        OnAfterGetRecord=VAR

                           MATRIX_CurrentColumnOrdinal@1018 : Integer;

                         BEGIN

                           //STEP_6-BEGIN

                           MATRIX_CurrentColumnOrdinal := 0;

                           IF MatrixRecord.FIND('-') THEN

                             REPEAT

                               MATRIX_CurrentColumnOrdinal := MATRIX_CurrentColumnOrdinal + 1;

                               MATRIX_OnAfterGetRecord(MATRIX_CurrentColumnOrdinal);

                             UNTIL (MatrixRecord.NEXT(1) = 0) OR (MATRIX_CurrentColumnOrdinal = MATRIX_NoOfMatrixColumns);

                           //STEP_6-END

                         END;

     

      }

      CONTROLS

      {

        { 1900000001;0;Container;

                    ContainerType=ContentArea }

     

        { 1   ;1;Group  ;

                    Name=Group;

                    Description=STEP_9;

                    GroupType=Repeater;

                    FreezeColumnID=Name }

     

        { 2   ;2;Field  ;

                    Description=STEP_1;

                    SourceExpr="No." }

     

        { 4   ;2;Field  ;

                    Description=STEP_1;

                    SourceExpr=Name }

     

        { 1008;2;Field  ;

                    Name=Field1;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[1];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[1];

                    Visible=Field1Visibl e;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(1);

                                END;

                                 }

     

        { 1100409033;2;Field  ;

                    Name=Field2;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[2];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[2];

                    Visible=Field2Visible;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(2);

                                END;

                                 }

     

        { 1100409034;2;Field  ;

                    Name=Field3;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[3];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[3];

                    Visible=Field3Visible;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(3);

                                END;

                                 }

     

        { 1100409031;2;Field  ;

                    Name=Field4;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[4];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[4];

                    Visible=Field4Visible;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(4);

                                END;

                                 }

     

        { 1100409032;2;Field  ;

                    Name=Field5;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[5];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[5];

                    Visible=Field5Visible;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(5);

                                END;

                                 }

     

        { 1100409029;2;Field  ;

                    Name=Field6;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellDataDevil;

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaptionDevil;

                    Visible=Field6Visible;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(6);

                                END;

                                 }

     

        { 1100409030;2;Field  ;

                    Name=Field7;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[7];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[7];

                    Visible=Field7Visible;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(7);

                                END;

                                 }

     

        { 1100409027;2;Field  ;

                    Name=Field8;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellDataMusic;

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaptionMusic;

                    Visible=Field8Visible;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(8);

                                END;

                                 }

     

        { 1100409028;2;Field  ;

                    Name=Field9;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[9];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[9];

                    Visible=Field9Visible;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(9);

                                END;

                                 }

     

        { 1100409025;2;Field  ;

                    Name=Field10;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[10];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[10];

                    Visible=Field10Visible;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(10);

                                END;

                                 }

     

        { 1100409026;2;Field  ;

                    Name=Field11;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[11];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[11];

                    Visible=Field11Visible;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(11);

                                END;

                                 }

     

        { 1100409023;2;Field  ;

                    Name=Field12;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[12];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[12];

                    Visible=Field12Visible;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(12);

                                END;

                                 }

     

        { 1100409024;2;Field  ;

                    Name=Field13;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[13];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[13];

                    Visible=Field13Visible;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(13);

                                END;

                                 }

     

        { 1100409021;2;Field  ;

                    Name=Field14;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[14];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[14];

                    Visible=Field14Visible;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(14);

                                END;

                                 }

     

        { 1100409022;2;Field  ;

                    Name=Field15;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[15];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[15];

                    Visible=Field15Visible;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(15);

                                END;

                                 }

     

        { 1100409019;2;Field  ;

                    Name=Field16;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[16];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[16];

                    Visible=Field16Visible;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(16);

                                END;

                                 }

     

        { 1100409020;2;Field  ;

                    Name=Field17;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[17];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[17];

                    Visible=Field17Visible;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(17);

                                END;

                                 }

     

        { 1100409017;2;Field  ;

                    Name=Field18;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[18];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[18];

                    Visible=Field18Visible;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(18);

                                END;

                                 }

     

        { 1100409018;2;Field  ;

                    Name=Field19;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[19];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[19];

                    Visible=Field19Visible;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(19);

                                END;

                                 }

     

        { 1100409015;2;Field  ;

                    Name=Field20;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[20];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[20];

                    Visible=Field20Visible;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(20);

                                END;

                                 }

     

        { 1100409016;2;Field  ;

                    Name=Field21;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[21];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[21];

                    Visible=Field21Visible;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(21);

                                END;

                                 }

     

        { 1100409013;2;Field  ;

                    Name=Field22;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[22];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[22];

                    Visible=Field22Visible;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(22);

                                END;

                                 }

     

        { 1100409014;2;Field  ;

                    Name=Field23;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[23];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[23];

                    Visible=Field23Visible;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(23);

                                END;

                                 }

     

        { 1100409011;2;Field  ;

                    Name=Field24;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[24];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[24];

                    Visible=Field24Visible;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(24);

                                END;

                                 }

     

        { 1100409012;2;Field  ;

                    Name=Field25;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[25];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[25];

                    Visible=Field25Visible;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(25);

                                END;

                                 }

     

        { 1100409009;2;Field  ;

                    Name=Field26;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[26];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[26];

                    Visible=Field26Visible;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(26);

                                END;

                                 }

     

        { 1100409010;2;Field  ;

                    Name=Field27;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[27];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[27];

                    Visible=Field27Visible;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(27);

                                END;

                                 }

     

        { 1100409007;2;Field  ;

                    Name=Field28;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[28];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[28];

                    Visible=Field28Visible;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(28);

                                END;

                                 }

     

        { 1100409008;2;Field  ;

                    Name=Field29;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[29];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[29];

                    Visible=Field29Visible;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(29);

                                END;

                                 }

     

        { 1100409005;2;Field  ;

                    Name=Field30;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[30];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[30];

                    Visible=Field30Visible;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(10);

                                END;

                                 }

     

        { 1100409006;2;Field  ;

                    Name=Field31;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[31];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[31];

                    Visible=Field31Visible;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(31);

                                END;

                                 }

     

        { 1100409004;2;Field  ;

                    Name=Field32;

                    BlankNumbers=BlankZero;

                    Description=STEP_3|STEP_9;

                    SourceExpr=MATRIX_CellData[32];

                    AutoFormatType=1;

                    CaptionClass='3,' + MATRIX_ColumnCaption[32];

                    Visible=Field32Visible;

                    OnDrillDown=BEGIN

                                  MatrixOnDrillDown(32);

                                END;

                                 }

     

      }

      CODE

      {

        VAR

          SalesLine@1035 : Record 37;

          MatrixRecords@1002 : ARRAY [32] OF Record 27;

          MatrixRecord@1039 : Record 27;

          MATRIX_NoOfMatrixColumns@1001 : Integer;

          MATRIX_CellData@1042 : ARRAY [32] OF Decimal;

          MATRIX_ColumnCaption@1000 : ARRAY [32] OF Text[1024];

          Field1Visible@19069335 : Boolean INDATASET;

          Field2Visible@19014807 : Boolean INDATASET;

          Field3Visible@19062679 : Boolean INDATASET;

          Field4Visible@19074839 : Boolean INDATASET;

          Field5Visible@19043543 : Boolean INDATASET;

          Field6Visible@19067287 : Boolean INDATASET;

          Field7Visible@19067863 : Boolean INDATASET;

          Field8Visible@19039959 : Boolean INDATASET;

          Field9Visible@19008663 : Boolean INDATASET;

          Field10Visible@19006501 : Boolean INDATASET;

          Field11Visible@19052468 : Boolean INDATASET;

          Field12Visible@19013039 : Boolean INDATASET;

          Field13Visible@19079726 : Boolean INDATASET;

          Field14Visible@19077225 : Boolean INDATASET;

          Field15Visible@19035896 : Boolean INDATASET;

          Field16Visible@19003763 : Boolean INDATASET;

          Field17Visible@19049730 : Boolean INDATASET;

          Field18Visible@19007213 : Boolean INDATASET;

          Field19Visible@19053180 : Boolean INDATASET;

          Field20Visible@19014629 : Boolean INDATASET;

          Field21Visible@19060596 : Boolean INDATASET;

          Field22Visible@19021167 : Boolean INDATASET;

          Field23Visible@19047854 : Boolean INDATASET;

          Field24Visible@19045353 : Boolean INDATASET;

          Field25Visible@19004024 : Boolean INDATASET;

          Field26Visible@19011891 : Boolean INDATASET;

          Field27Visible@19057858 : Boolean INDATASET;

          Field28Visible@19015341 : Boolean INDATASET;

          Field29Visible@19061308 : Boolean INDATASET;

          Field30Visible@19010597 : Boolean INDATASET;

          Field31Visible@19056564 : Boolean INDATASET;

          Field32Visible@19017135 : Boolean INDATASET;

     

        LOCAL PROCEDURE MATRIX_OnAfterGetRecord@1047(ColumnID@1000 : Integer);

        VAR

          Cust@1001 : Record 18;

        BEGIN

          //STEP_6-BEGIN

          Cust.COPY(Rec);

          Cust.SETRANGE("Type Filter","Type Filter"::Item);

          Cust.SETRANGE("No. Filter",MatrixRecords[ColumnID]."No.");

          Cust.CALCFIELDS("Total Amount");

          MATRIX_CellData[ColumnID] := Cust."Total Amount";

          SetVisible;

          //STEP_6-END

        END;

     

        PROCEDURE Load@3(MatrixColumns1@1001 : ARRAY [32] OF Text[1024];VAR MatrixRecords1@1002 : ARRAY [32] OF Record 27;VAR MatrixRecord1@1000 : Record 27);

        BEGIN

          //STEP_5-BEGIN

          COPYARRAY(MATRIX_ColumnCaption,MatrixColumns1,1);

          COPYARRAY(MatrixRecords,MatrixRecords1,1);

          MatrixRecord.COPY(MatrixRecord1);

          //STEP_5-END

        END;

     

        PROCEDURE MatrixOnDrillDown@1100409001(ColumnID@1000 : Integer);

        BEGIN

          //STEP_9-BEGIN

          SalesLine.SETCURRENTKEY("Sell-to Customer No.",Type,"No.");

          SalesLine.SETRANGE("Sell-to Customer No.","No.");

          SalesLine.SETRANGE(Type,SalesLine.Type::Item);

          SalesLine.SETRANGE("No.",MatrixRecords[ColumnID]."No.");

          FORM.RUN(0,SalesLine);

          //STEP_9-END

        END;

     

        PROCEDURE SetVisible@6();

        BEGIN

          //STEP_6-BEGIN

          Field1Visible := MATRIX_ColumnCaption[1] <> '';

          Field2Visible := MATRIX_ColumnCaption[2] <> '';

          Field3Visible := MATRIX_ColumnCaption[3] <> '';

          Field4Visible := MATRIX_ColumnCaption[4] <> '';

          Field5Visible := MATRIX_ColumnCaption[5] <> '';

          Field6Visible := MATRIX_ColumnCaptionDevil <> '';

          Field7Visible := MATRIX_ColumnCaption[7] <> '';

          Field8Visible := MATRIX_ColumnCaptionMusic <> '';

          Field9Visible := MATRIX_ColumnCaption[9] <> '';

          Field10Visible := MATRIX_ColumnCaption[10] <> '';

          Field11Visible := MATRIX_ColumnCaption[11] <> '';

          Field12Visible := MATRIX_ColumnCaption[12] <> '';

          Field13Visible := MATRIX_ColumnCaption[13] <> '';

          Field14Visible := MATRIX_ColumnCaption[14] <> '';

          Field15Visible := MATRIX_ColumnCaption[15] <> '';

          Field16Visible := MATRIX_ColumnCaption[16] <> '';

          Field17Visible := MATRIX_ColumnCaption[17] <> '';

          Field18Visible := MATRIX_ColumnCaption[18] <> '';

          Field19Visible := MATRIX_ColumnCaption[19] <> '';

          Field20Visible := MATRIX_ColumnCaption[20] <> '';

          Field21Visible := MATRIX_ColumnCaption[21] <> '';

          Field22Visible := MATRIX_ColumnCaption[22] <> '';

          Field23Visible := MATRIX_ColumnCaption[23] <> '';

          Field24Visible := MATRIX_ColumnCaption[24] <> '';

          Field25Visible := MATRIX_ColumnCaption[25] <> '';

          Field26Visible := MATRIX_ColumnCaption[26] <> '';

          Field27Visible := MATRIX_ColumnCaption[27] <> '';

          Field28Visible := MATRIX_ColumnCaption[28] <> '';

          Field29Visible := MATRIX_ColumnCaption[29] <> '';

          Field30Visible := MATRIX_ColumnCaption[30] <> '';

          Field31Visible := MATRIX_ColumnCaption[31] <> '';

          Field32Visible := MATRIX_ColumnCaption[32] <> '';

          //STEP_6-END

        END;

     

        BEGIN

        {

          <changelog>

            <add id="STEP_1"

              dev="lvanvugt"

              date="2012-04-20"

              source="http://dynamicsuser.net/blogs/vanvugt/archive/2012/04/20/how-to-create-a-one-page-matrix.aspx"

              releaseversion="Advanced Matrix Box">Step 1 - One-Page Matrix Step-by-Step</add>

            <change id="STEP_3"

              dev="lvanvugt"

              date="2012-04-20"

              source="http://dynamicsuser.net/blogs/vanvugt/archive/2012/04/20/how-to-create-a-one-page-matrix.aspx"

              releaseversion="Advanced Matrix Box">Step 3 - One-Page Matrix Step-by-Step</change>

            <change id="STEP_5"

              dev="lvanvugt"

              date="2012-04-20"

              source="http://dynamicsuser.net/blogs/vanvugt/archive/2012/04/20/how-to-create-a-one-page-matrix.aspx"

              releaseversion="Advanced Matrix Box">Step 5 - One-Page Matrix Step-by-Step</change>

            <change id="STEP_6"

              dev="lvanvugt"

              date="2012-04-20"

              source="http://dynamicsuser.net/blogs/vanvugt/archive/2012/04/20/how-to-create-a-one-page-matrix.aspx"

              releaseversion="Advanced Matrix Box">Step 6 - One-Page Matrix Step-by-Step</change>

            <change id="STEP_9"

              dev="lvanvugt"

              date="2012-04-20"

              source="http://dynamicsuser.net/blogs/vanvugt/archive/2012/04/20/how-to-create-a-one-page-matrix.aspx"

              releaseversion="Advanced Matrix Box">Step 9 - One-Page Matrix Step-by-Step</change>

          </changelog>

        }

        END.

      }

    }

     

  • Luc van Vugt

    How-to: Create a One-Page Matrix

    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 lets 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 math 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 subpage
    2. Creating matrix main page
    3. Defining horizontal dimension (matrix subpage)
    4. Setting horizontal dimension 1 (matrix main page)
    5. Setting horizontal dimension 2 (matrix subpage)
    6. Populating matrix cells (matrix subpage)
    7. Updating page (matrix main page)
    8. Browsing horizontal dimension (matrix main page)

    One-Page Matrix Step-by-Step (detail level)

    And adding all relevant details:

    1. Creating matrix subpage

    In this first step we setup the first part of the skeleton for our One-Page matrix: i.e. the page structure for the mutli-columns subpage that will display the vertical and horizontal dimensions and their intersecting matrix cells. We create this subpage by using the wizard as introduced in NAV 2009 SP1:

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

    2. Creating matrix main page

    In this second step we setup the second part of the skeleton for our One-Page matrix: i.e. the page structure for the main page including FastTab(s) and subpage placeholder.

    We create this main page by also using the wizard:

    1. Start the Page Wizard
      2.
    2. Select source table (vertical dimension)
    3. Select page type (Card)
    4. Set Options FastTab

    To this we need to manually add MatrixForm Part that links to matrix subpage, i.e. set PagePartID

    3. Defining horizontal dimension (matrix subpage)

    Now that we have defined the vertical dimension, we need to create controls (i.e. columns) to define the horizontal dimension. For this we add columns to matrix subpage by a simple copy & paste from an existing matrix subpage: 

    • e.g. PAG9231 - 32 columns
    • Not from PAG9237 (Resource Capacity) as each column contains validation code we do not need/want

    To get the object compiled:

    • 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
    • Declare function MatrixOnDrillDown

    4. Setting horizontal dimension 1 (matrix main page)

    To be able to control the content of the column headings (horizontal dimension) from the main page we need to declare the function SetColumns (copy from PAG491). A call to SetColumns should be made from the OnOpenPage trigger (see PAG491) to initiate the headings content when opening the page.

    To get the object compiled:

    1. Out comment blocking and non-relevant parts
    2. 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
      8. MATRIX_SetWanted

    5. Setting horizontal dimension 2 (matrix subpage)

    Now that we have created a function to control the column headings from the main page, we also need an interface to exchange the result from main to subpage, i.e. a function called Load (copy from PAG9231).

    To get the object compiled:

    1. Declare (global) variables
      1. MatrixRecords - Link to horizontal dimension
      2. MatrixRecord - Link to horizontal dimension
    2. Make sure that parameters also link to the right dimension

    6. Populating matrix cells (matrix subpage)

    Having set the vertical and horizontal dimensions we should now calculate the content of the crossings/intersections of the vertical and horizontal dimensions (on matrix subpage) coding OnAfterGetRecord (copy from PAG9231). For this we need to determine number of columns being used in the OnOpenPage trigger.

    In order to have only relevant cells (i.e. columns) shown we set the columns visibility variables to TRUE (OnInit - copy from PAG9231) and declare function SetVisible (copy from PAG9231) to allow us to dynamically update the visibility.

    To get the object compiled:

    1. Declare variables
      • MATRIX_CurrentColumnOrdinal (local)
      • MATRIX_NoOfMatrixColumns (global)
    2. Declare function MATRIX_OnAfterGetRecord

      • See PAG9231 for example, but code depends on the specifics of our matrix

    7. Updating page (matrix main page)

    All code set to update the dimensions and intersecting matrix cells. Now one function is needed that will pass the updates from main to subpage: UpdateMatrixSubform (copy from PAG213 (One-Page matrix!)). Note that the call of Load function (on subpage) should be updated to fit the definition of Load on subpage and should be first called when opening page (OnOpenPage).

    8. Browsing horizontal dimension (matrix main page)

    The only remaining major part of our One-Page matrix is the set of buttons that alow us to browse through the horizontal dimension:

    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:

    Note that these attributes can be specific to each matrix page.

    1. Standard (i.e. to be found on standard matrix pages)
      1. DrillDown (matrix subpage))
        • See PAG9231 for example, but code depends on the specifics of our matrix
      2. InsertAllowed/ModifyAllowed/DeleteAllowed (matrix main page)
        • SSet to No
      3. Editable=No (matrix subpage)
      4. Format cells(matrix subpage)
        • i. Set AutoFormatType to 1
        • ii. Not DecimalPlaces
      5. Toggle between "No."/Code + Name/Description in the column headings (matrix main page)
        • 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
    2. Non-Standard
      1. Freeze pane (matrix subpage)
        • Set FreezeColumnID property of Repeater control
        • Note: although this is very useful, it's not set on standard matrix pages! Don't ask me why.
          •
      2. Automatic Update of Page (matrix main page)
        • Declare function UpdateThePage - Using WshShell.SendKeys

    Notes

    • Over time this post might change due to new insights:
      • 2012-04-30 first update
        •
    • 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 the 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 standard One-Page matrices in NAV 2009 SP1

      Page ID

      Page Name

      113

      Budget

      2213

      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

    Example How-tos

    1. How-to: Create a One-Page Matrix by Example
    2. How-to: Create a One-Page Matrix by Example #2
  • Luc van Vugt

    Naming Conventions

    Some useful resources to study and use when naming objects in NAV, whether it be tables, fields, variables or whatever other object:

    1. Microsoft Dynamics® NAV 2009 Developer and IT Pro Documentation: Naming Conventions discusses the naming of:
      1. Tables objects
      2. Form and Page objects
      3. Report objects
      4. Table fields
      5. Form buttons and menu items
      6. Codeunit objects
      7. Variables
      8. User-defined functions
      9. Form controls
         
    2. Microsoft Dynamics® NAV 2009 C-SIDE Introduction: Chapter 2 Tables in this course manual discusses a.o. the naming of
      1. Master tables
      2. Supplemental tables
      3. Subsidiary tables
      4. Ledger tables
      5. Register tables
      6. Journal tables
      7. Document tables
      8. Setup tables
         
    3. Microsoft® Language Portal: to find a translation for your local language term into ENU (vice versa)
       
    4. Microsoft Dynamics® NAV: the application itself is also a worthwhile reference; have a look at
      1. Object table (Object Designer!)
      2. Field table, by creating a new form/page based on the Field table (2000000041)
      3. Or somewhat more laborious: creating a translate export from NAV and search through it
         
    5. Microsoft Dynamics® NAV Captions and Abbreviations: in former days we used to have some kind of document that listed the abbreviations to used in the standard application; as per mine msconnect entry Terminology and Abbreviations List it should be released with the NAV '7'(2013!) Help toolkit.

    Notes

    • The first two resources are typically only addressing ENU naming matters, but where it concerns UI only, i.e. strings visible to users of the application, the third and fourth resource will be very helpful
    • And of course: the more consistent the terminology you use, the more efficient it can be translated and the better your customer will understand your application.
  • Luc van Vugt

    Poisson d'Avril - The Sequel, part #2

    With joy I want to share with you the news that I am awarded 2012 Microsoft® MVP. Thankful as, though not changing my live, it sure brought me 'things' I wouldn't have come across otherwise.

© 1995-2017 Dynamics User Group, all rights reserved. Terms of use |  Privacy | Contact us | Powered by Telligent Community