October, 2008

  • Microsoft Dynamics NAV Team Blog

    3D charts. Chart Generator tool II

    • 1 Comments

    One of the new features in NAV 2009 is the new chart (KPI) object, described in more details here:

    NAV 2009 - How to generate charts / KPIs

    This post describes how to make 3D rotatable charts, and includes an update to the Chart Generator Tool which makes is simple to make your own 3D charts.

    For basics about how to make charts, please refer to the post linked above. To add the 3rd dimension to your charts, all you need to do is to add a Z-axis. This gives you charts that can look like this:

    Item stacks by location:

     ItemStacksbyLocation

     

    Customer balance by discount group:

    CustBalanceByDiscGroup

     

    A chart automatically becomes a 3D chart by adding a Z-axis to the chart definition, for example like this:

    - <ZAxis ShowTitle="false">

    <Field Name="Customer Disc. Group" />

    </ZAxis>

    And the presence of a Z-axis will automatically make the user able to rotate the chart.

    Below, you can find the following:

    1. Chart definition for the two charts pictured above, so you can import them into your own CROUS database
    2. Chart Generator Tool, updated so that it can handle Z-axis, so you can create your own charts.

    Lars Lohndorf-Larsen (Lohndorf )
    Microsoft Dynamics UK
    Microsoft Customer Service and Support (CSS) EMEA

     

    Chart definitions

     

    Item stacks by location:

    - <ChartDefinition xmlns="urn:schemas-microsoft-com:dynamics:NAV:MetaObjects" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Type="Column">

    - <Title>

    <Text ID="ENU">Items by location</Text>

    </Title>

    - <Table ID="32">

    - <Filters>

    - <Filter>

    <Field Name="Item No." />

    <Value>70000|70001|70002|70003</Value>

    </Filter>

    </Filters>

    </Table>

    - <XAxis ShowTitle="true">

    - <Title>

    <Text ID="ENU">Location Code</Text>

    </Title>

    <Field Name="Location Code" />

    </XAxis>

    - <ZAxis ShowTitle="false">

    <Field Name="Item No." />

    </ZAxis>

    - <YAxis ShowTitle="false">

    - <Measures>

    - <Measure Operator="Sum">

    <Field Name="Quantity" />

    </Measure>

    </Measures>

    </YAxis>

    </ChartDefinition>

     

     

    Customer balance by discount group:

    - <ChartDefinition xmlns="urn:schemas-microsoft-com:dynamics:NAV:MetaObjects" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Type="Column">

    - <Title>

    <Text ID="ENU">Customer Balance</Text>

    </Title>

    - <Table ID="18">

    <Filters />

    </Table>

    - <XAxis ShowTitle="true">

    - <Title>

    <Text ID="ENU">No.</Text>

    </Title>

    <Field Name="No." />

    </XAxis>

    - <ZAxis ShowTitle="false">

    <Field Name="Customer Disc. Group" />

    </ZAxis>

    - <YAxis ShowTitle="false">

    - <Measures>

    - <Measure Operator="Sum">

    <Field Name="Balance (LCY)" />

    </Measure>

    </Measures>

    </YAxis>

    </ChartDefinition>

     

    Chart Generator Tool II

    This is an improvement to the Chart Generator tool from this post. This version adds the possibility to set a Z-axis, and in this way make 3D charts.

     

    To use it, save the code below to a text file. Then remove all [RemoveMe] from the text after checking that the object types and IDs won't replace existing objects that you want to keep. Then import it into NAV from Object Designer, and compile.

     

    for instructions of how to use the tool, follow the link above to the original post.

     

    OBJECT Table [RemoveMe]72000 Chart Generator
    {
      OBJECT-PROPERTIES
      {
        Date=17/10/08;
        Time=12:00:00;
        Version List=CGT;
      }
      PROPERTIES
      {
        OnInsert=BEGIN
                   IF Chart.GET(Company,ID) THEN
                     ERROR('Chart already exists in table 2000000078. Run form 9182 and delete it, or use a different ID.');
                 END;

        LookupFormID=Form72000;
      }
      FIELDS
      {
        { 1   ;   ;Company             ;Text30         }
        { 2   ;   ;ID                  ;Code20        ;NotBlank=Yes }
        { 10  ;   ;Name                ;Text50        ;OnValidate=BEGIN
                                                                    Title := Name;
                                                                  END;
                                                                   }
        { 11  ;   ;Title               ;Text50         }
        { 15  ;   ;Type                ;Option        ;OptionString=Column,Point }
        { 16  ;   ;Table ID            ;Integer       ;OnValidate=BEGIN
                                                                    MODIFY;
                                                                  END;
                                                                   }
        { 17  ;   ;Table Name          ;Text30        ;FieldClass=FlowField;
                                                       CalcFormula=Lookup(Object.Name WHERE (Type=CONST(Table),
                                                                                             ID=FIELD(Table ID)));
                                                       Editable=No }
        { 20  ;   ;XAxis Field ID      ;Integer        }
        { 21  ;   ;XAxis Field Name    ;Text80        ;OnValidate=BEGIN
                                                                    IF "XAxis Field Name" <> '' THEN BEGIN
                                                                      FieldRec.SETRANGE(TableNo,"Table ID");
                                                                      FieldRec.SETRANGE(FieldName,"XAxis Field Name");
                                                                      FieldRec.FINDFIRST;
                                                                      "XAxis Field ID" := FieldRec."No.";
                                                                      "XAxis Field Name" := FieldRec.FieldName;
                                                                      "XAxis Field Caption" := FieldRec."Field Caption";
                                                                      "XAxis title" := FieldRec."Field Caption";
                                                                    END ELSE BEGIN
                                                                      "XAxis Field ID" := 0;
                                                                      "XAxis Field Name" := '';
                                                                      "XAxis Field Caption" := '';
                                                                      "XAxis title" := '';
                                                                    END;
                                                                  END;
                                                                   }
        { 22  ;   ;XAxis Field Caption ;Text80         }
        { 23  ;   ;XAxis title         ;Text80         }
        { 24  ;   ;XAxis Show Title    ;Boolean       ;InitValue=Yes }
        { 30  ;   ;YAxis fields        ;Integer       ;FieldClass=FlowField;
                                                       CalcFormula=Count("Chart Generator YAxis" WHERE (Company=FIELD(Company),
                                                                                                        ID=FIELD(ID)));
                                                       Editable=No }
        { 35  ;   ;ZAxis Field Name    ;Text80        ;OnValidate=BEGIN
                                                                    IF "ZAxis Field Name" <> '' THEN BEGIN
                                                                      FieldRec.SETRANGE(TableNo,"Table ID");
                                                                      FieldRec.SETRANGE(FieldName,"ZAxis Field Name");
                                                                      FieldRec.FINDFIRST;
                                                                    END;
                                                                  END;
                                                                   }
        { 38  ;   ;ZAxis Show Title    ;Boolean        }
      }
      KEYS
      {
        {    ;Company,ID                              ;Clustered=Yes }
      }
      FIELDGROUPS
      {
      }
      CODE
      {
        VAR
          Chart@1000 : Record 2000000078;
          FieldRec@1102601000 : Record 2000000041;

        BEGIN
        END.
      }
    }

    OBJECT Table [RemoveMe]72001 Chart Generator Filter
    {
      OBJECT-PROPERTIES
      {
        Date=17/10/08;
        Time=12:00:00;
        Version List=CGT;
      }
      PROPERTIES
      {
        OnInsert=BEGIN
                   TESTFIELD("Filter Field ID");
                 END;

      }
      FIELDS
      {
        { 1   ;   ;Company             ;Text30         }
        { 2   ;   ;ID                  ;Code20         }
        { 3   ;   ;Line No.            ;Integer        }
        { 10  ;   ;Filter Field ID     ;Integer       ;OnValidate=BEGIN
                                                                    IF "Filter Field ID" <> 0 THEN BEGIN
                                                                       ChartGen.GET(Company,ID);
                                                                       ChartGen.TESTFIELD("Table ID");
                                                                       FieldRec.GET(ChartGen."Table ID","Filter Field ID");
                                                                       "Filter Field Name" := FieldRec.FieldName;
                                                                    END ELSE
                                                                       "Filter Field Name" := '';
                                                                  END;
                                                                   }
        { 11  ;   ;Filter Field Name   ;Text30         }
        { 15  ;   ;Filter Value        ;Text30         }
      }
      KEYS
      {
        {    ;Company,ID,Line No.                     ;Clustered=Yes }
      }
      FIELDGROUPS
      {
      }
      CODE
      {
        VAR
          ChartGen@1102601001 : Record 72000;
          FieldRec@1102601000 : Record 2000000041;

        BEGIN
        END.
      }
    }

    OBJECT Table [RemoveMe]72003 Chart Generator YAxis
    {
      OBJECT-PROPERTIES
      {
        Date=17/10/08;
        Time=12:00:00;
        Version List=CGT;
      }
      PROPERTIES
      {
        OnInsert=BEGIN
                   TESTFIELD("YAxis Measure Field ID");
                 END;

        LookupFormID=Form72003;
        DrillDownFormID=Form72003;
      }
      FIELDS
      {
        { 1   ;   ;Company             ;Text30         }
        { 2   ;   ;ID                  ;Code20         }
        { 3   ;   ;Line No.            ;Integer        }
        { 10  ;   ;YAxis Measure Field ID;Integer      }
        { 11  ;   ;YAxis Measure Field Caption;Text30  }
        { 12  ;   ;Mearure Operator    ;Option        ;OptionString=Sum,Count }
        { 20  ;   ;Show Title          ;Boolean        }
      }
      KEYS
      {
        {    ;Company,ID,Line No.                     ;Clustered=Yes }
      }
      FIELDGROUPS
      {
      }
      CODE
      {

        BEGIN
        END.
      }
    }

    OBJECT Form [RemoveMe]72000 Chart Generator List
    {
      OBJECT-PROPERTIES
      {
        Date=17/10/08;
        Time=12:00:00;
        Version List=CGT;
      }
      PROPERTIES
      {
        Width=16500;
        Height=6710;
        TableBoxID=1;
        SourceTable=Table72000;
      }
      CONTROLS
      {
        { 1   ;TableBox     ;220  ;220  ;16060;5500 ;HorzGlue=Both;
                                                     VertGlue=Both }
        { 2   ;TextBox      ;0    ;0    ;4400 ;0    ;HorzGlue=Both;
                                                     Visible=No;
                                                     ParentControl=1;
                                                     InColumn=Yes;
                                                     SourceExpr=Company }
        { 3   ;Label        ;0    ;0    ;0    ;0    ;ParentControl=2;
                                                     InColumnHeading=Yes }
        { 4   ;TextBox      ;0    ;0    ;1700 ;0    ;ParentControl=1;
                                                     InColumn=Yes;
                                                     SourceExpr=ID }
        { 5   ;Label        ;0    ;0    ;0    ;0    ;ParentControl=4;
                                                     InColumnHeading=Yes }
        { 6   ;TextBox      ;0    ;0    ;4400 ;0    ;ParentControl=1;
                                                     InColumn=Yes;
                                                     SourceExpr=Name }
        { 7   ;Label        ;0    ;0    ;0    ;0    ;ParentControl=6;
                                                     InColumnHeading=Yes }
        { 8   ;TextBox      ;0    ;0    ;4400 ;0    ;ParentControl=1;
                                                     InColumn=Yes;
                                                     SourceExpr=Title }
        { 9   ;Label        ;0    ;0    ;0    ;0    ;ParentControl=8;
                                                     InColumnHeading=Yes }
        { 10  ;TextBox      ;0    ;0    ;550  ;0    ;ParentControl=1;
                                                     InColumn=Yes;
                                                     SourceExpr=Type }
        { 11  ;Label        ;0    ;0    ;0    ;0    ;ParentControl=10;
                                                     InColumnHeading=Yes }
        { 12  ;TextBox      ;0    ;0    ;1700 ;0    ;ParentControl=1;
                                                     InColumn=Yes;
                                                     SourceExpr="Table ID" }
        { 13  ;Label        ;0    ;0    ;0    ;0    ;ParentControl=12;
                                                     InColumnHeading=Yes }
        { 14  ;CommandButton;4400 ;5940 ;2200 ;550  ;HorzGlue=Right;
                                                     VertGlue=Bottom;
                                                     Default=Yes;
                                                     PushAction=LookupOK;
                                                     InvalidActionAppearance=Hide }
        { 15  ;CommandButton;6820 ;5940 ;2200 ;550  ;HorzGlue=Right;
                                                     VertGlue=Bottom;
                                                     Cancel=Yes;
                                                     PushAction=LookupCancel;
                                                     InvalidActionAppearance=Hide }
        { 16  ;CommandButton;14080;5940 ;2200 ;550  ;HorzGlue=Right;
                                                     VertGlue=Bottom;
                                                     PushAction=FormHelp }
        { 17  ;MenuButton   ;9240 ;5940 ;2200 ;550  ;HorzGlue=Right;
                                                     VertGlue=Bottom;
                                                     CaptionML=ENU=Chart;
                                                     Menu=MENUITEMS
                                                     {
                                                       { ID=18;
                                                         PushAction=RunObject;
                                                         CaptionML=ENU=Filters;
                                                         RunObject=Form 72001;
                                                         RunFormLink=Company=FIELD(Company),
                                                                     ID=FIELD(ID) }
                                                       { ID=20;
                                                         PushAction=RunObject;
                                                         CaptionML=ENU=XAxis;
                                                         RunObject=Form 72002;
                                                         RunFormLink=Field1=FIELD(Company),
                                                                     Field2=FIELD(ID) }
                                                       { ID=21;
                                                         PushAction=RunObject;
                                                         CaptionML=ENU=YAxis;
                                                         RunObject=Form 72003;
                                                         RunFormLink=Company=FIELD(Company),
                                                                     ID=FIELD(ID) }
                                                     }
                                                      }
        { 19  ;MenuButton   ;11660;5940 ;2200 ;550  ;HorzGlue=Right;
                                                     VertGlue=Bottom;
                                                     CaptionML=ENU=F&unctions;
                                                     Menu=MENUITEMS
                                                     {
                                                       { ID=22;
                                                         PushAction=RunObject;
                                                         ShortCutKey=F11;
                                                         CaptionML=ENU=Generate Chart;
                                                         RunObject=Codeunit 72000 }
                                                     }
                                                      }
      }
      CODE
      {
        VAR
          Chart@1000 : Record 2000000078;

        BEGIN
        END.
      }
    }

    OBJECT Form [RemoveMe]72001 Chart Filters
    {
      OBJECT-PROPERTIES
      {
        Date=17/10/08;
        Time=12:00:00;
        Version List=CGT;
      }
      PROPERTIES
      {
        Width=12150;
        Height=6710;
        TableBoxID=1;
        SourceTable=Table72001;
        AutoSplitKey=Yes;
        DelayedInsert=Yes;
      }
      CONTROLS
      {
        { 1   ;TableBox     ;220  ;220  ;11710;5500 ;HorzGlue=Both;
                                                     VertGlue=Both }
        { 2   ;TextBox      ;0    ;0    ;1700 ;0    ;ParentControl=1;
                                                     InColumn=Yes;
                                                     SourceExpr="Filter Field ID";
                                                     OnLookup=BEGIN
                                                                ChartGen.GET(Company,ID);
                                                                ChartGen.TESTFIELD("Table ID");
                                                                FieldRec.SETRANGE(TableNo,ChartGen."Table ID");

                                                                IF FORM.RUNMODAL(FORM::"Field List",FieldRec) = ACTION::LookupOK

    THEN
                                                                  VALIDATE("Filter Field ID",FieldRec."No.");
                                                              END;
                                                               }
        { 3   ;Label        ;0    ;0    ;0    ;0    ;ParentControl=2;
                                                     InColumnHeading=Yes }
        { 4   ;TextBox      ;0    ;0    ;4400 ;0    ;HorzGlue=Both;
                                                     ParentControl=1;
                                                     InColumn=Yes;
                                                     SourceExpr="Filter Field Name" }
        { 5   ;Label        ;0    ;0    ;0    ;0    ;ParentControl=4;
                                                     InColumnHeading=Yes }
        { 6   ;TextBox      ;0    ;0    ;4400 ;0    ;ParentControl=1;
                                                     InColumn=Yes;
                                                     SourceExpr="Filter Value" }
        { 7   ;Label        ;0    ;0    ;0    ;0    ;ParentControl=6;
                                                     InColumnHeading=Yes }
        { 8   ;CommandButton;4890 ;5940 ;2200 ;550  ;HorzGlue=Right;
                                                     VertGlue=Bottom;
                                                     Default=Yes;
                                                     PushAction=LookupOK;
                                                     InvalidActionAppearance=Hide }
        { 9   ;CommandButton;7310 ;5940 ;2200 ;550  ;HorzGlue=Right;
                                                     VertGlue=Bottom;
                                                     Cancel=Yes;
                                                     PushAction=LookupCancel;
                                                     InvalidActionAppearance=Hide }
        { 10  ;CommandButton;9730 ;5940 ;2200 ;550  ;HorzGlue=Right;
                                                     VertGlue=Bottom;
                                                     PushAction=FormHelp }
      }
      CODE
      {
        VAR
          ChartGen@1000 : Record 72000;
          FieldRec@1001 : Record 2000000041;

        BEGIN
        END.
      }
    }

    OBJECT Form [RemoveMe]72003 YAxis List
    {
      OBJECT-PROPERTIES
      {
        Date=17/10/08;
        Time=12:00:00;
        Version List=CGT;
      }
      PROPERTIES
      {
        Width=10000;
        Height=6710;
        TableBoxID=1;
        SourceTable=Table72003;
        AutoSplitKey=Yes;
        DelayedInsert=Yes;
      }
      CONTROLS
      {
        { 1   ;TableBox     ;220  ;220  ;9560 ;5500 ;HorzGlue=Both;
                                                     VertGlue=Both }
        { 2   ;TextBox      ;0    ;0    ;1700 ;0    ;ParentControl=1;
                                                     InColumn=Yes;
                                                     SourceExpr="YAxis Measure Field ID";
                                                     OnLookup=BEGIN
                                                                ChartGen.GET(Company,ID);
                                                                ChartGen.TESTFIELD("Table ID");
                                                                FieldRec.SETRANGE(TableNo,ChartGen."Table ID");

                                                                IF FORM.RUNMODAL(FORM::"Field List",FieldRec) = ACTION::LookupOK

    THEN BEGIN
                                                                  "YAxis Measure Field ID" := FieldRec."No.";
                                                                  "YAxis Measure Field Caption" := FieldRec."Field Caption";
                                                                END;
                                                              END;
                                                               }
        { 3   ;Label        ;0    ;0    ;0    ;0    ;ParentControl=2;
                                                     InColumnHeading=Yes }
        { 4   ;TextBox      ;0    ;0    ;4400 ;0    ;HorzGlue=Both;
                                                     ParentControl=1;
                                                     InColumn=Yes;
                                                     SourceExpr="YAxis Measure Field Caption" }
        { 5   ;Label        ;0    ;0    ;0    ;0    ;ParentControl=4;
                                                     InColumnHeading=Yes }
        { 6   ;TextBox      ;0    ;0    ;550  ;0    ;ParentControl=1;
                                                     InColumn=Yes;
                                                     SourceExpr="Mearure Operator" }
        { 7   ;Label        ;0    ;0    ;0    ;0    ;ParentControl=6;
                                                     InColumnHeading=Yes }
        { 8   ;CheckBox     ;0    ;0    ;1700 ;0    ;ParentControl=1;
                                                     InColumn=Yes;
                                                     ShowCaption=No;
                                                     SourceExpr="Show Title" }
        { 9   ;Label        ;0    ;0    ;0    ;0    ;ParentControl=8;
                                                     InColumnHeading=Yes }
        { 10  ;CommandButton;2740 ;5940 ;2200 ;550  ;HorzGlue=Right;
                                                     VertGlue=Bottom;
                                                     Default=Yes;
                                                     PushAction=LookupOK;
                                                     InvalidActionAppearance=Hide }
        { 11  ;CommandButton;5160 ;5940 ;2200 ;550  ;HorzGlue=Right;
                                                     VertGlue=Bottom;
                                                     Cancel=Yes;
                                                     PushAction=LookupCancel;
                                                     InvalidActionAppearance=Hide }
        { 12  ;CommandButton;7580 ;5940 ;2200 ;550  ;HorzGlue=Right;
                                                     VertGlue=Bottom;
                                                     PushAction=FormHelp }
      }
      CODE
      {
        VAR
          ChartGen@1000 : Record 72000;
          FieldRec@1001 : Record 2000000041;

        BEGIN
        END.
      }
    }

    OBJECT Form [RemoveMe]72004 Chart Generator Card
    {
      OBJECT-PROPERTIES
      {
        Date=17/10/08;
        Time=12:00:00;
        Version List=CGT;
      }
      PROPERTIES
      {
        Width=16170;
        Height=6490;
        SourceTable=Table72000;
      }
      CONTROLS
      {
        { 1   ;TabControl   ;220  ;220  ;15730;5280 ;HorzGlue=Both;
                                                     VertGlue=Both;
                                                     PageNamesML=ENU=General }
        { 4   ;TextBox      ;3850 ;990  ;2750 ;440  ;ParentControl=1;
                                                     InPage=0;
                                                     SourceExpr=ID }
        { 5   ;Label        ;440  ;990  ;3300 ;440  ;ParentControl=4 }
        { 6   ;TextBox      ;3850 ;1650 ;5500 ;440  ;ParentControl=1;
                                                     InPage=0;
                                                     NextControl=12;
                                                     SourceExpr=Name }
        { 7   ;Label        ;440  ;1650 ;3300 ;440  ;ParentControl=6 }
        { 8   ;TextBox      ;3850 ;2200 ;5500 ;440  ;ParentControl=1;
                                                     InPage=0;
                                                     SourceExpr=Title }
        { 9   ;Label        ;440  ;2200 ;3300 ;440  ;ParentControl=8 }
        { 10  ;TextBox      ;3850 ;3520 ;2750 ;440  ;ParentControl=1;
                                                     InPage=0;
                                                     SourceExpr=Type }
        { 11  ;Label        ;440  ;3520 ;3300 ;440  ;ParentControl=10 }
        { 12  ;TextBox      ;3850 ;2860 ;1700 ;440  ;ParentControl=1;
                                                     InPage=0;
                                                     SourceExpr="Table ID";
                                                     OnLookup=BEGIN
                                                                Object.SETRANGE(Type,Object.Type::Table);
                                                                IF FORM.RUNMODAL(FORM::Objects,Object) = ACTION::LookupOK THEN
                                                                  "Table ID" := Object.ID;
                                                              END;

                                                     OnAfterValidate=BEGIN
                                                                       CALCFIELDS("Table Name");
                                                                     END;
                                                                      }
        { 13  ;Label        ;440  ;2860 ;3300 ;440  ;ParentControl=12 }
        { 21  ;TextBox      ;12980;990  ;2750 ;440  ;ParentControl=1;
                                                     InPage=0;
                                                     NextControl=1102601000;
                                                     SourceExpr="XAxis Field Name";
                                                     OnLookup=BEGIN
                                                                TESTFIELD("Table ID");
                                                                FieldRec.SETRANGE(TableNo,"Table ID");

                                                                IF FORM.RUNMODAL(FORM::"Field List",FieldRec) = ACTION::LookupOK

    THEN
                                                                  VALIDATE("XAxis Field Name",FieldRec.FieldName);
                                                              END;
                                                               }
        { 22  ;Label        ;9570 ;990  ;3300 ;440  ;ParentControl=21 }
        { 20  ;TextBox      ;12980;1650 ;2750 ;440  ;ParentControl=1;
                                                     InPage=0;
                                                     SourceExpr="XAxis Field Caption" }
        { 23  ;Label        ;9570 ;1650 ;3300 ;440  ;ParentControl=20 }
        { 24  ;CheckBox     ;12980;2750 ;440  ;440  ;ParentControl=1;
                                                     InPage=0;
                                                     ShowCaption=No;
                                                     SourceExpr="XAxis Show Title" }
        { 25  ;Label        ;9570 ;2750 ;3300 ;440  ;ParentControl=24 }
        { 27  ;TextBox      ;12980;2200 ;2750 ;440  ;ParentControl=1;
                                                     InPage=0;
                                                     SourceExpr="XAxis title" }
        { 28  ;Label        ;9570 ;2200 ;3300 ;440  ;ParentControl=27 }
        { 1102601000;TextBox;12980;3410 ;1700 ;440  ;ParentControl=1;
                                                     InPage=0;
                                                     SourceExpr="YAxis fields" }
        { 1102601001;Label  ;9570 ;3410 ;3300 ;440  ;ParentControl=1102601000 }
        { 1102601002;TextBox;5720 ;2860 ;3630 ;440  ;ParentControl=1;
                                                     InPage=0;
                                                     SourceExpr="Table Name" }
        { 1102601003;TextBox;12980;4070 ;2750 ;440  ;ParentControl=1;
                                                     InPage=0;
                                                     SourceExpr="ZAxis Field Name";
                                                     OnLookup=BEGIN
                                                                TESTFIELD("Table ID");
                                                                FieldRec.SETRANGE(TableNo,"Table ID");

                                                                IF FORM.RUNMODAL(FORM::"Field List",FieldRec) = ACTION::LookupOK

    THEN
                                                                  VALIDATE("ZAxis Field Name",FieldRec.FieldName);
                                                              END;
                                                               }
        { 1102601004;Label  ;9570 ;4070 ;3300 ;440  ;ParentControl=1102601003 }
        { 14  ;CommandButton;13750;5720 ;2200 ;550  ;HorzGlue=Right;
                                                     VertGlue=Bottom;
                                                     PushAction=FormHelp }
        { 15  ;MenuButton   ;11330;5720 ;2200 ;550  ;HorzGlue=Right;
                                                     VertGlue=Bottom;
                                                     CaptionML=ENU=&Chart;
                                                     Menu=MENUITEMS
                                                     {
                                                       { ID=16;
                                                         PushAction=LookupTable;
                                                         ShortCutKey=F5;
                                                         CaptionML=ENU=L&ist }
                                                       { ID=26;
                                                         PushAction=RunObject;
                                                         CaptionML=ENU=Filters;
                                                         RunObject=Form 72001;
                                                         RunFormLink=Company=FIELD(Company),
                                                                     ID=FIELD(ID) }
                                                       { ID=17;
                                                         PushAction=RunObject;
                                                         CaptionML=ENU=YAxis;
                                                         RunObject=Form 72003;
                                                         RunFormLink=Company=FIELD(Company),
                                                                     ID=FIELD(ID) }
                                                       { ID=18;
                                                         MenuItemType=Separator }
                                                       { ID=19;
                                                         PushAction=RunObject;
                                                         ShortCutKey=F11;
                                                         CaptionML=ENU=Generate Chart;
                                                         RunObject=Codeunit 72000 }
                                                     }
                                                      }
      }
      CODE
      {
        VAR
          FieldRec@1001 : Record 2000000041;
          Object@1002 : Record 2000000001;
          XAxisField@1000 : Text[30];

        BEGIN
        END.
      }
    }

    OBJECT Codeunit [RemoveMe]72000 Chart Generator Mgt
    {
      OBJECT-PROPERTIES
      {
        Date=17/10/08;
        Time=12:00:00;
        Version List=CGT;
      }
      PROPERTIES
      {
        TableNo=72000;
        OnRun=BEGIN
                CreateXML(Rec);

                MESSAGE('Chart %1 was created / updated.',Chart.ID);
              END;

      }
      CODE
      {
        VAR
          Chart@1015 : Record 2000000078;
          ChartGen@1003 : Record 72000;
          ChartFilters@1008 : Record 72001;
          ChartYAxis@1012 : Record 72003;
          "3TierMgt"@1013 : Codeunit 419;
          XMLDoc@1000 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 4.0:{F6D90F11-9C73-11D3-B32E-00C04F990BB4}:'Microsoft

    XML, v4.0'.DOMDocument";
          DomNode@1001 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 4.0:{2933BF80-7B36-11D2-B20E-

    00C04F983E60}:'Microsoft XML, v4.0'.IXMLDOMNode";
          DomNode2@1006 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 4.0:{2933BF80-7B36-11D2-B20E-

    00C04F983E60}:'Microsoft XML, v4.0'.IXMLDOMNode";
          DomTextNode@1002 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 4.0:{2933BF87-7B36-11D2-B20E-

    00C04F983E60}:'Microsoft XML, v4.0'.IXMLDOMText";
          DomAttribute@1004 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 4.0:{2933BF85-7B36-11D2-B20E-

    00C04F983E60}:'Microsoft XML, v4.0'.IXMLDOMAttribute";
          DomNodeList@1009 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 4.0:{2933BF82-7B36-11D2-B20E-

    00C04F983E60}:'Microsoft XML, v4.0'.IXMLDOMNodeList";
          DomProcessInstruction@1005 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 4.0:{2933BF89-7B36-11D2-B20E-

    00C04F983E60}:'Microsoft XML, v4.0'.IXMLDOMProcessingInstruction";
          NameSpace@1007 : Text[80];
          i@1010 : Integer;
          TempFileName@1014 : Text[250];
          Debug@1016 : Boolean;
          DebugFileName@1102601000 : Text[250];
          IStream@1102601001 : InStream;

        PROCEDURE CreateXML@1(ChartGenerator@1000 : Record 72000);
        BEGIN
          //=== Enable debug and filename to save the xml document to disk
          Debug := FALSE;
          DebugFileName := '';
          //===

          CREATE(XMLDoc);
          XMLDoc.async(FALSE);

          // Initialize document and set namespaces
          NameSpace := 'urn:schemas-microsoft-com:dynamics:NAV:MetaObjects';

          DomNode := XMLDoc.createNode(1,'ChartDefinition',NameSpace);
          DomAttribute := XMLDoc.createAttribute('xmlns:xsd');
          DomAttribute.value := 'http://www.w3.org/2001/XMLSchema';
          DomNode.attributes.setNamedItem(DomAttribute);
          DomAttribute := XMLDoc.createAttribute('xmlns:xsi');
          DomAttribute.value := 'http://www.w3.org/2001/XMLSchema-instance';
          DomNode.attributes.setNamedItem(DomAttribute);

          // add chart type
          DomAttribute := XMLDoc.createAttribute('Type');
          CASE ChartGenerator.Type OF
            ChartGenerator.Type::Column:
              DomAttribute.value := 'Column';
            ChartGenerator.Type::Point:
              DomAttribute.value := 'Point';
          END;
          DomNode.attributes.setNamedItem(DomAttribute);
          XMLDoc.appendChild(DomNode);

          AddNode('ChartDefinition','Title','');
          AddNode('ChartDefinition/Title','Text',ChartGenerator.Title);
          AddAttribute('ChartDefinition/Title/Text','ID','ENU');
          AddNode('ChartDefinition','Table','');
          AddAttribute('ChartDefinition/Table','ID',FORMAT(ChartGenerator."Table ID"));
          AddNode('ChartDefinition/Table','Filters','');

          // Filters
          ChartFilters.SETRANGE(Company,ChartGenerator.Company);
          ChartFilters.SETRANGE(ID,ChartGenerator.ID);
          IF ChartFilters.FINDSET THEN
            REPEAT
              AddNode('ChartDefinition/Table/Filters','Filter','');
              AddNode('ChartDefinition/Table/Filters/Filter','Field','');
              AddAttribute('ChartDefinition/Table/Filters/Filter/Field','Name',ChartFilters."Filter Field Name");
              AddNode('ChartDefinition/Table/Filters/Filter','Value',ChartFilters."Filter Value");
            UNTIL ChartFilters.NEXT = 0;

          // XAxis
          ChartGenerator.TESTFIELD("XAxis Field Name");

          AddNode('ChartDefinition','XAxis','');
          AddAttribute('ChartDefinition/XAxis','ShowTitle',BooleanFormat(ChartGenerator."XAxis Show Title")); ///
          AddNode('ChartDefinition/XAxis','Title','');
          AddNode('ChartDefinition/XAxis/Title','Text',ChartGenerator."XAxis title");
          AddAttribute('ChartDefinition/XAxis/Title/Text','ID','ENU');
          AddNode('ChartDefinition/XAxis','Field','');
          AddAttribute('ChartDefinition/XAxis/Field','Name',ChartGenerator."XAxis Field Name");

          // ZAxis///
          IF ChartGenerator."ZAxis Field Name" <> '' THEN BEGIN
            AddNode('ChartDefinition','ZAxis','');
            AddAttribute('ChartDefinition/ZAxis','ShowTitle',BooleanFormat(ChartGenerator."ZAxis Show Title"));
            AddNode('ChartDefinition/ZAxis','Field','');
            AddAttribute('ChartDefinition/ZAxis/Field','Name',ChartGenerator."ZAxis Field Name");
          END;

          // YAxis
          ChartYAxis.SETRANGE(Company,ChartGenerator.Company);
          ChartYAxis.SETRANGE(ID,ChartGenerator.ID);
          IF ChartYAxis.FINDSET THEN BEGIN
            AddNode('ChartDefinition','YAxis','');
            AddAttribute('ChartDefinition/YAxis','ShowTitle',BooleanFormat(ChartYAxis."Show Title"));
            AddNode('ChartDefinition/YAxis','Measures','');

            REPEAT
              AddNode('ChartDefinition/YAxis/Measures','Measure','');
              AddAttribute('ChartDefinition/YAxis/Measures/Measure','Operator',FORMAT(ChartYAxis."Mearure Operator"));
              AddNode('ChartDefinition/YAxis/Measures/Measure','Field','');
              AddAttribute('ChartDefinition/YAxis/Measures/Measure/Field','Name',FORMAT(ChartYAxis."YAxis Measure Field

    Caption"));
            UNTIL ChartYAxis.NEXT = 0;
          END;

          TempFileName := TEMPORARYPATH + '\' + FORMAT(CREATEGUID) + '.xml';

          XMLDoc.save(TempFileName);

          IF NOT Chart.GET(ChartGenerator.Company,ChartGenerator.ID) THEN BEGIN
            Chart.Company := ChartGenerator.Company;
            Chart.ID := ChartGenerator.ID;
            Chart.Name := ChartGenerator.Name;
            Chart.INSERT;
          END;
          Chart.BLOB.IMPORT(TempFileName);
          Chart.MODIFY;

          IF Debug THEN
            XMLDoc.save(DebugFileName);

          // Clean up
          IF EXISTS(TempFileName) THEN
            ERASE(TempFileName);
          CLEAR(DomNode);
          CLEAR(XMLDoc);
        END;

        PROCEDURE AddNode@2(AddToNode@1000 : Text[120];NodeName@1001 : Text[120];NodeValue@1002 : Text[120]);
        BEGIN
          DomNodeList := XMLDoc.selectNodes(AddToNode);
          DomNode := DomNodeList.item(DomNodeList.length - 1);

          DomNode2 := XMLDoc.createNode(1,NodeName,NameSpace);
          DomTextNode := XMLDoc.createTextNode(NodeValue);
          DomNode2.appendChild(DomTextNode);
          DomNode.appendChild(DomNode2);
        END;

        PROCEDURE AddAttribute@3(AddToNode@1000 : Text[120];AttributeName@1001 : Text[120];AttributeValue@1002 : Text[120]);
        BEGIN
          DomNodeList := XMLDoc.selectNodes(AddToNode);
          DomNode := DomNodeList.item(DomNodeList.length - 1);

          DomAttribute := XMLDoc.createAttribute(AttributeName);
          DomAttribute.value := AttributeValue;
          DomNode.attributes.setNamedItem(DomAttribute);
        END;

        PROCEDURE BooleanFormat@4(Boo@1000 : Boolean) : Text[30];
        BEGIN
          IF Boo THEN
            EXIT('true');

          EXIT('false');
        END;

        EVENT XMLDoc@1000::ondataavailable@198();
        BEGIN
        END;

        EVENT XMLDoc@1000::onreadystatechange@-609();
        BEGIN
        END;

        BEGIN
        END.
      }
    }

     

     

  • Microsoft Dynamics NAV Team Blog

    Error - Cannot create more than one clustered index on table

    • 0 Comments

    This post describes one scenario in which this error occurs. There may be others.

    In the late 4.0 versions of the NAV executables, all tables in NAV had a clustered index created on SQL for the primary key (Index 0).
    However, the table objects in 4.0 versions did not have the Clustered property set on any of the keys.
    It was also possible on 4.0 to toggle that property (on/off) and NAV would allow you to leave the table with none of the keys flagged as clustered, but the primary index on SQL remained clustered.

    In the 5.0 versions, this behavior has changed. It is no longer possible to modify a table object to turn off the Clustered property on all keys. If you do this, save and compile the table, and then go back into designer and view keys, you will see that the 5.0 (and SP1) client has automatically turned on the Clustered property on the primary key. It is not possible in 5.0 to create a table with no clustered index.

     So, consider a scenario where you have a 4.0 database that has been converted to 5.0 SP1 executables. This is usually done one of two ways...

    1) Open the 4.0 database with the 5.0 SP1 executable and click Ok to convert
    2) Create a new database with the 5.0 SP1 executable and restore the 4.0 database backup using the NAV Client.

    With 5.0 SP1, using either of these two methods, the Clustered property on the 4.0 object's primary key is automatically flagged.

    If for any reason a 4.0 table object is later imported into the converted database, you end up with a situation where the clustered index remains on SQL, but the table object no longer has the Clustered property flagged. This doesn't cause any immediate problem, but let's say you later want to do a full object upgrade to 5.0 SP1 objects. In step 8 of the Upgrade Quickguide, you are instructed to import the new customized objects created in the compare and merge process. Some of the 5.0 SP1 table objects, for example Sales Header, have had the clustered index changed to a secondary key. When these tables are imported into the database, NAV will not drop the primary clustered index but will try to create the secondary clustered index, triggering the above error.

    The workaround for this issue is to go into the table object in design mode and flag the primary key as clustered, then try importing the 5.0 SP1 table object again. Because the Clustered property is set, NAV will drop and recreate the primary key (index on SQL) before creating the secondary clustered index.


    Laura K. Lake (lalake)

    Microsoft Dynamics NA


    Microsoft Customer Service and Support (CSS) North America

  • Microsoft Dynamics NAV Team Blog

    RIM Tool on 5.0 SP1

    • 0 Comments

    Although the RIM tool was originally designed to assist in the setup of Master data; i.e. Customers, Vendors, Items, etc., the reality is that many are using the Data Migration tool to bring in journal lines to set up beginning balances and to bring over historical data from legacy applications.

    With the release of 5.0 SP1, a new data type conversion routine was introduced with codeunit 5302 - Outlook Synch. Type Conversion. There are several known issues with data conversion due to these changes.

    All of these issue have been reported to development and are being considered for a fix in a future release of the NAV product.

    This post offers some code suggestions and workarounds for the more common and/or critical issues. Many of the suggested workarounds involve one particular codeunit in the Data Migration tool, 8611 – Migration Management, so the following combined change log should be helpful.


    This change log applies to 5.0 SP1 application objects ONLY.

    Issues addressed by the change log:

    1) Overflow on type conversion from Text to Text in Form 8626 when a text field larger than 100 chars is imported.
    2) Error on import of fields beginning with a number (i.e. 1099 Code) - The call to member selectSingleNode failed. msxml6.dll returned the following message: Expression must evaluate to a node-set.
    3) Imported dates are blank after migration data is applied.
    4) Decimal values are converted incorrectly. Ex. - 7,500 becomes 7.5 after the migration data is applied.
    5) Negative decimal values are converted incorrectly, resulting in changed values.
    6) Error - Expected token 'EOF' found '$'. Error on fields with $ symbol
    7) Error - The expression Text cannot be type-converted to a Boolean value (or Date value). This may occur on Setup Questionnaire, Data Migration or Data Template.


    Changes are labeled with the associated number above.


    *****Start Code fix*****

     

    Form 8626 Migration Records

    ---------------------------

    Before:

    CODE

      {

        VAR

          MatrixColumnCaptions@1000 : ARRAY [100] OF Text[100];

          MigrationData@1001 : Record 8615;

          MatrixCellData@1002 : ARRAY [100] OF Text[100];

          MigrationColumnField@1004 : ARRAY [100] OF Integer;

          MatrixColumnOrdinal@1003 : Integer;

          FormCaption@1005 : Text[1024];

          TableNo@1006 : Integer;

          Text001@1007 : TextConst 'ENU=%1 value ''%2'' does not exist.';

    After:

    CODE

      {

        VAR

          MatrixColumnCaptions@1000 : ARRAY [100] OF Text[100];

          MigrationData@1001 : Record 8615;

          MatrixCellData@1002 : ARRAY [100] OF Text[250];  // #1 Change Text[100] to Text[250]

          MigrationColumnField@1004 : ARRAY [100] OF Integer;

          MatrixColumnOrdinal@1003 : Integer;

          FormCaption@1005 : Text[1024];

          TableNo@1006 : Integer;

          Text001@1007 : TextConst 'ENU=%1 value ''%2'' does not exist.';

     

    * XMLport 8610 - Setup DataSchema

    ---------------------------------

    ...

    NameIn := DELCHR(NameIn,'=','Ù''`');

    NameIn := DELCHR(CONVERTSTR(NameIn,'<>,./\+-&()%','            '),'=',' ');

    NameIn := DELCHR(NameIn,'=',' ');

    EXIT(NameIn);

    ...

     

    After:

    ...

    NameIn := DELCHR(NameIn,'=','Ù''`');

    NameIn := DELCHR(CONVERTSTR(NameIn,'<>,./\+-&()%$','             '),'=',' ');  // #6 Change line- add $ symbol

    NameIn := DELCHR(NameIn,'=',' ');

    IF (NameIn[1] >= '0') AND (NameIn[1] <= '9') THEN    // #2 Add line

      NameIn := '_' + NameIn;         // #2 Add line

    EXIT(NameIn);

     

    * Codeunit 8611\Function FieldNodeExists ...

    --------------------------------------------

    Before:

    FieldNode := RecordNode.selectSingleNode(FieldNodeName);

    IF NOT ISCLEAR(FieldNode) THEN

      EXIT(TRUE);

    After:

    IF (FieldNodeName[1] >= '0') AND (FieldNodeName[1] <= '9') THEN         //  #2 Add line

      FieldNodeName := '_' + FieldNodeName;                                                   //  #2 Add line

    FieldNode := RecordNode.selectSingleNode(FieldNodeName);

    IF NOT ISCLEAR(FieldNode) THEN

      EXIT(TRUE);

     

    * Codeunit 8611, Function GetElementName

    ----------------------------------------

    Before:

    ...

    NameIn := DELCHR(NameIn,'=','Ù''`');

    NameIn := DELCHR(CONVERTSTR(NameIn,'<>,./\+-&()%','            '),'=',' ');

    NameIn := DELCHR(NameIn,'=',' ');

    EXIT(NameIn);

    ...

     

    After:

    ...

    NameIn := DELCHR(NameIn,'=','Ù''`');

    NameIn := DELCHR(CONVERTSTR(NameIn,'<>,./\+-&()%$','             '),'=',' ');  // #6 Change line - add $ symbol

    NameIn := DELCHR(NameIn,'=',' ');

    IF (NameIn[1] >= '0') AND (NameIn[1] <= '9') THEN    // #2 Add line

      NameIn := '_' + NameIn;     // #2 Add line

    EXIT(NameIn);

    * Codeunit 8611, Function ValidateFieldValue

    --------------------------------------------

    Before:

     

    Field.GET(RecRef.NUMBER,FieldRef.NUMBER);

    IF Field.Type <> Field.Type::Option THEN BEGIN

      IF Value <> '' THEN

        //IF CompanySetupRun THEN                                           // #7 Delete

          EVALUATE(FieldRef,Value)

        //ELSE                                                                               // #7 Delete

        //  FieldRef.VALIDATE(Value);                                         // #7 Delete

    END ELSE

      IF GetOption(Value,FieldRef.OPTIONCAPTION,OptionAsInteger) THEN

        IF CompanySetupRun THEN

          FieldRef.VALUE := OptionAsInteger

        ELSE

          FieldRef.VALIDATE(OptionAsInteger);

    IF NOT CompanySetupRun THEN

      IF NOT TestRelation(FieldRef) THEN

        FieldRef.VALIDATE;

    After:

     

    Field.GET(RecRef.NUMBER,FieldRef.NUMBER);

    IF Field.Type <> Field.Type::Option THEN BEGIN

      IF Value <> '' THEN

          EVALUATE(FieldRef,Value)

    END ELSE

      IF GetOption(Value,FieldRef.OPTIONCAPTION,OptionAsInteger) THEN

        IF CompanySetupRun THEN

          FieldRef.VALUE := OptionAsInteger

        ELSE

          FieldRef.VALIDATE(OptionAsInteger);

    IF NOT CompanySetupRun THEN

      IF NOT TestRelation(FieldRef) THEN

        FieldRef.VALIDATE;

    * Codeunit 8611, Function ModifyRecordWithOtherFields

    -----------------------------------------------------

    Before:

    VAR

          MatrixData@1002 : Record 8615;

          MigrationTableField@1000 : Record 8616;

          Question@1003 : Record 8612;

          Field@1007 : Record 2000000041;

          MigrationTable@1009 : Record 8613;

          DataTemplateHeader@1011 : Record 8618;

          QuestionnaireMgt@1006 : Codeunit 8610;

          TemplateMgt@1012 : Codeunit 8612;

          OSynchTypeConversion@1014 : Codeunit 5302;

          FieldRef@1001 : FieldRef;

          OptionInt@1008 : Integer;

          DateFormula@1010 : DateFormula;

          ToValidate@1013 : Boolean;

    After:

     VAR

          MatrixData@1002 : Record 8615;

          MigrationTableField@1000 : Record 8616;

          Question@1003 : Record 8612;

          Field@1007 : Record 2000000041;

          MigrationTable@1009 : Record 8613;

          DataTemplateHeader@1011 : Record 8618;

          QuestionnaireMgt@1006 : Codeunit 8610;

          TemplateMgt@1012 : Codeunit 8612;

          OSynchTypeConversion@1014 : Codeunit 5302;

          FieldRef@1001 : FieldRef;

          OptionInt@1008 : Integer;

          DateFormula@1010 : DateFormula;

          ToValidate@1013 : Boolean;

          DateValue@1500000 : Date;                   // #3 Add

    Before:

     

        IF MigrationTableField.FIND('-') THEN

          IF NOT IsKeyField(MigrationTableField.TableID,MigrationTableField.FieldID) THEN BEGIN

            FieldRef := RecRef.FIELD(MatrixData.FieldID);

            IF CompanySetupRun THEN

              ToValidate:= FALSE

            ELSE

              ToValidate := MigrationTableField.Validate AND NOT TestRelation(FieldRef);

            OSynchTypeConversion.EvaluateTextToFieldRef(MatrixData.Value,FieldRef,ToValidate)

          END;

          IF DataTemplateHeader.GET(MigrationTable."Data Template") THEN

            TemplateMgt.UpdateRecord(DataTemplateHeader,RecRef);

    After:

        IF MigrationTableField.FIND('-') THEN

          IF NOT IsKeyField(MigrationTableField.TableID,MigrationTableField.FieldID) THEN BEGIN

            FieldRef := RecRef.FIELD(MatrixData.FieldID);

            IF CompanySetupRun THEN

              ToValidate:= FALSE

            ELSE

              ToValidate := MigrationTableField.Validate AND NOT TestRelation(FieldRef);

            //OSynchTypeConversion.EvaluateTextToFieldRef(MatrixData.Value,FieldRef,ToValidate)          // #3 Delete

            Field.GET(RecRef.NUMBER, FieldRef.NUMBER);       // #3 Add line

            IF Field.Type <> Field.Type::Date THEN                 // #3 Add line

              OSynchTypeConversion.EvaluateTextToFieldRef(MatrixData.Value,FieldRef,ToValidate)      // #3 Add line

            ELSE BEGIN                     // #3 Add line

              EVALUATE(DateValue, MatrixData.Value);          // #3 Add line

              FieldRef.VALUE := DateValue;      // #3 Add line

            END;         // #3 Add line

          END;

          IF DataTemplateHeader.GET(MigrationTable."Data Template") THEN

            TemplateMgt.UpdateRecord(DataTemplateHeader,RecRef);

     

    * Codeunit 8611, Function ImportSetupDataXML

    --------------------------------------------

    Before:

     

                IF MigrationData.Value <> '' THEN BEGIN

                  ConvertXMLDates(FieldRef,MigrationData.Value);

                  FieldError(MigrationData,EvaluateValue(FieldRef,MigrationData.Value));

                  IF FORMAT(FieldRef.VALUE) <> '' THEN

                    IF FORMAT(FieldRef.TYPE) <> 'Option' THEN

                      MigrationData.Value := FORMAT(FieldRef.VALUE)

                    ELSE

                      MigrationData.Value := GetOptionString(FieldRef,FORMAT(FieldRef.VALUE));

    After:

                IF MigrationData.Value <> '' THEN BEGIN

                  ConvertXMLDates(FieldRef,MigrationData.Value);

                  FieldError(MigrationData,EvaluateValue(FieldRef,MigrationData.Value));

                  IF FORMAT(FieldRef.VALUE) <> '' THEN

                    IF FORMAT(FieldRef.TYPE) <> 'Option' THEN BEGIN                                    // #4 Add BEGIN

                      IF FORMAT(FieldRef.TYPE) <> 'Decimal' THEN                                       // #4 Add line

                        MigrationData.Value := FORMAT(FieldRef.VALUE);

                    END ELSE                                                                           // #4 Add END

                      MigrationData.Value := GetOptionString(FieldRef,FORMAT(FieldRef.VALUE));           

     

    * Codeunit 8611, Function ConvertXMLDates

    -----------------------------------------

    Before:

     

         CASE Field.Type OF

        

           Field.Type::Date:

             BEGIN

               IF EVALUATE(Date, Value,XMLFormat) THEN BEGIN

                 Value := FORMAT(Date);

                 FldRef.VALUE := Date;

               END;

             END;

     

    After:

     

          CASE Field.Type OF

            Field.Type::Decimal:                                             // # 4 Add line

              BEGIN                                                              // # 4 Add line

                IF EVALUATE(Decimal,Value) THEN BEGIN      // # 4 Add line

                  Value := FORMAT(Decimal,0,XMLFormat);     // # 4 Add line

     

                  FldRef.VALUE := Decimal;                           // # 4 Add line

                END;                                                           // # 4 Add line

              END;                                                             // # 4 Add line

            Field.Type::Date:

              BEGIN

                IF EVALUATE(Date, Value,XMLFormat) THEN BEGIN

                  Value := FORMAT(Date);

                  FldRef.VALUE := Date;

                END;

              END;

     

    * Codeunit 5302, Function TextToDecimal

    ----------------------------------------

    Before:

     

    InputText := CONVERTSTR(InputText,'.',',');

    IF STRPOS(InputText,',') = 0 THEN

      EXIT;

    PartArray[1] := GetSubStrByNo(1,InputText);

                                                                                

    After:

    InputText := CONVERTSTR(InputText,'.',',');

    IF STRPOS(InputText,',') = 0 THEN BEGIN                         // # 4 Add BEGIN

      IsConverted := TextToInteger(InputText,IntegeralPart);       // # 4 Add line

      IF IsConverted THEN                                                      // # 4 Add line

        DecVar := IntegeralPart;                                              // # 4 Add line

      EXIT;

    END;                                                                            // # Add END

    PartArray[1] := GetSubStrByNo(1,InputText);

     

    Before:

     

    DecVar := IntegeralPart + (FractionalPart / POWER(10,STRLEN(PartArray[2])));    

     

    After:

     

    IF STRPOS(InputText,'-') = 0 THEN  // # 5 Add line

      Sign := 1       // # 5 Add line

    ELSE        // # 5 Add line

      Sign := -1;      // # 5 Add line

    DecVar := (Sign * (ABS(IntegeralPart) + (FractionalPart / POWER(10,STRLEN(PartArray[2])))));  // #5 Change line

    *****End Code fix*****

     

     

    Laura K. Lake (lalake)

    Microsoft Dynamics NA


    Microsoft Customer Service and Support (CSS) North America

  • Microsoft Dynamics NAV Team Blog

    E-mail logging and The call to member Logon failed. Collaboration Data Objects returned following message: [Collaboration Data Objects - [MAPI_E_LOGON_FAILED(80040111)]]

    • 0 Comments

    In 90% of the E-mail logging errors I personally dealt with, the logon error is the most common error being filed by partner followed by The call to member Sender Failed. Collaboration Data Objects returned the following message: Collaboration Data Objects E_ACCESSDENIED(80070005). Both errors are annoying and a bit complicated to explain why this could occur.

    Let's assume for one minute yo have followed the following article:

    942793 How to set up e-mail logging in Microsoft Dynamics NAV 5.0
    https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;EN-US;942793

    The article indicates the E-mail logging should work by now. Fact is that the E-mail logging will probably not work and the above mentioned errors are logged. What did you do wrong? The answer is: "Nothing". Some more steps needs to be taken. Let me first describe what we need for the setup of E-mail logging.

    1. We need an Exchange 2003 or Exchange 2007 environment
    2. We need a dedicated NAS PC / Server with Outlook and Dynamics NAV client installed

     IMPORTANT NOTE:
    The NAS server with Outlook installed cannot be the Exchange Server computer:
    266418 Microsoft does not support installing Exchange Server components and Outlook on the same computer
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;266418

    3. We need a client PC plus Outlook
    4. We need a dedicated user account (let's call this account "e-mail logger") that is a SUPER user in NAV and local Admin on the NAS server / PC
    5. We need a user account for testing the E-mail logging
    6. We need the Outlook Security Settings form that ships with the ORK 2003

    IMPORTANT NOTE:
    If using Exchagne 2007, please do see the following blog I wrote earlier:
    http://blogs.msdn.com/nav/archive/2008/05/09/e-mail-logging-when-using-outlook-2007.aspx

    To avoid the MAPI_E_LOGON_FAILED error mesage, please do the followiung:
    - log on to the server or PC running NAS "using the "e-mail logger" user account 
    - configure the NAS MMC with the required properties, MAILLOG in NAV 4.0 and earlier releases and JOBQUEUE in NAV 5.0 and later releases
    - make sure the NAS service is configured with the "e-mail logger" user account via services.msc
    - create an Outlook profile for the "e-mail logger" user account => delete all other existing Outlook profiles
    - create two Public Folders for Storage and Queue

    IMPORTANT NOTE:
    Creating the two folders while being logged on as the "e-mail logger user" account means that the two folders have the "e-mail logger" user account as the owner. If the "e-mail logger" user account does not have permissions to create Public Folders, then have the Exchange Admin do this and have him manually add the "e-mail logger" user account as the orignal owner via Client Permissions within the properties of the Public Folders

    - start up Dynamics NAV client and configure the E-mail logging properties in Marketing Setup

    In general circustances, this will prevent the MAPI_E_LOGON_FAILED error messages. The NAS has direct access to the Outook Profile via the "e-mail logger" user account and the folder permissions are set OK. As the Dynamics NAV client runs on a different machien, just leave the NAS and the Outlook client running on the dedicated box.

    Most partners now get the other error message: The call to member Sender Failed. Collaboration Data Objects returned the following message: Collaboration Data Objects E_ACCESSDENIED(80070005). I will probably dedicate another full blog on that topic, but in general this can be caused by missing or ignored Outlook Security Settings form and / or Exchange Server allowing messages to come in without Sender. NAS cannot handle those message and will log the permissions error. The process will halt after that and the Queue folder will be filled with messages if an automatic way of filling the queue folder was selected.

    Also see this blog:
    http://blogs.msdn.com/nav/archive/2008/05/09/e-mail-logging-when-using-outlook-2007.aspx

    -- mmels

  • Microsoft Dynamics NAV Team Blog

    Using web services with Outlook Synchronization in a localized environment

    • 0 Comments

    While using NAS with OSYNCH parameter in a Dynamics NAV 5.0 + partnersource update 25874 or Dynamics NAV 5.0 SP1, we had to use the Localization Tips that were published on page 55 of the Setup and Installation Guide:

    When a localized version of Microsoft Dynamics NAV is used, some actions are required to align the version and country codes specified in the license (.flf) and system text (.stx) files of Microsoft Dynamics NAV C/SIDE Client, Application Server, and Outlook Add-in with the localization used.

    Important

    When you install Outlook Synchronization Update, the described alignment must be performed only after you have imported and compiled the Outlook Synchronization Update objects.
    For the details on installing the Outlook Synchronization Update, please refer to the Installing Outlook Synchronization Update appendix.

    Using SP1 this process could be easy. Using the Partnersource update, this could be a bit more difficult because at what point did you have to import the modified objects and compile them (see Note above). Using web services, this changed which is a BIG win for all of us.

    When using web services, the NAS is no longer required and there is no need anymore to copy the license and system text files manually anymore. After installing everything required and after publishing the web service in NAV, the following error will show up in the Outlook Add-in:

    NST Asynch Call completed with Error: Microsoft.Dynamics.Nav.Types.Exceptions.NavCSideException: A '=' is missing in TransferFormulaData.  WHERE(Soort=CONST(-1),Verkoper=CONST(EMS)); .

    NOTE: example has been taken from Dutch NAV 2009 CTP4.

    This is an error coming while executing the web service call to the NST (NST Asynch Call). The message we are receiving is in english, therefore it means the NST its working in english and you are filtering using translated field values in Dutch, e.g. Verkoper (Salesperson). 

    This means that you need to change the language of the NST. You do that through the Role Tailored Client, so no stx file mambo jambo anymore :-)
     
    One thing to keep in mind though, is that the values of the filters you put in the entities, are language dependent. That means that they will not change values if you suddenly change the language of the NST or NAS or classic client. You can test this on a local build by, first opening NAV on the language of the local build (let's say Dutch) on a fresh database, open the Outlook Sync. Entities or Outlook Synch User Setup tables. This will populate default values in the current language. Now, change the language into English, you’ll see that the field values are still on the local language. This can be solved actually but is a new feature.

    -- mmels 

  • Microsoft Dynamics NAV Team Blog

    An error has occurred during the synchronization process

    • 0 Comments

    After configuring demo entities TASK and APP in NAV and after creating a ToDo of Phone Call and / or after creating a ToDo of Meeting, there is an error in the Outlook Add-in:
    An error has occurred during the synchronization process. You can find the error details in the log file. Please contact your System Administrator.

    After enabling the debug log file, the following can be seen in the debug log file:

    Error: 25-09-2008 08:06:57 [4] Dump of exception type 'System.NullReferenceException'
    Message: Object reference not set to an instance of an object.
    Stack trace:    at Microsoft.Dynamics.NAV.OLSync.NAVSyncAddIn.OutlookModelProvider.OutlookObject.GetPropertyReturnedType(String propertyName)
       at Microsoft.Dynamics.NAV.OLSync.NAVSyncAddIn.Synchronization.MessageParser._ProcessElement(SynchronizationSchema synchronizationSchema, NavisionSynchronizationEntity navisionSynchronizationEntity, String synchronizationEntityCode, OutlookItem item, XPathNavigator elementNavigator, String navisionRecordId, ProcessedItemStatusStruc& processedItemStatusStruc)
       at Microsoft.Dynamics.NAV.OLSync.NAVSyncAddIn.Synchronization.MessageParser._ProcessOutlookItem(XPathNavigator outlookItemElement, SynchronizationSchema synchronizationSchema, NavisionSynchronizationEntity navisionSynchronizationEntity, OutlookSynchronizationEntity outlookSynchronizationEntity, EntryIdStorage entryIdStorage, Boolean isConflictResolution)
       at Microsoft.Dynamics.NAV.OLSync.NAVSyncAddIn.Synchronization.MessageParser.GetNextMessagePortion()
       at Microsoft.Dynamics.NAV.OLSync.NAVSyncAddIn.Synchronization.Common._ProcessNavisionData(MemoryStream incomingStream)
       at Microsoft.Dynamics.NAV.OLSync.NAVSyncAddIn.Synchronization.Common.DoSynchronization(BackgroundWorker worker, DoWorkEventArgs e)

    Outlook collections contain an incorrect translated value for links.

    To resolve the issue, you can do the following:
    1. remove all TASK and APP entities from Outlook Synch User Setup
    2. select Outlook Synch Entities
    3. select Code and press F6
    4. select APP
    5. select Outlook Collection other then Recipients
    6. press F6 and select Links manually and press OK
    7 select Code and press F6
    8. select TASK
    9. select Outlook Collection
    10. press F6 and select Links manually and press OK
    11. reenter the TASK and APP entity for the selected Users in the Outlook Synch User Setup
    12. register changes in Change Log

    -- mmels 

  • Microsoft Dynamics NAV Team Blog

    Outlook Add-in does not show up in Dynamics NAV 5.0, Dynamics NAV 5.0 SP1 and Dynamics NAV 2009

    • 0 Comments

    Sometimes it happens that after a reinstallation of the Outlook Add-in, the actual toolbar does not appear in Outlook. The toolbar can however be seen as a separate toolbar under View / Toolbars. The actual cause is that previous Outlook Add-ins were still active previous to the reinstallation.

    To resolve the issue, do the following:
    1. within Outlook, select Tools in the menu
    2. select Trust Center
    3. select Add-ins
    4. select Apply macro security settings to installed add-ins
    5. verify that COM Add-ins is selected
    6. press Go
    7. if the Microsoft Dynamics NAV Synchronization Add-in is selected, deselect it
    8. press OK
    9. within Outlook, select Tools in the menu
    10. select Trust Center
    11. select Add-ins
    12.  select Apply macro security settings to installed add-ins
    13. verify that COM Add-ins is selected
    14. press Go
    15. select the Microsoft Dynamics NAV Synchronization Add-in is selected
    16. press OK

    The Outlook Add-in toolbar should now be visible again.

    - mmels

  • Microsoft Dynamics NAV Team Blog

    The asymmetric stream

    • 0 Comments

    Since NAV’s Stream introduction, there has been confusion about when to use READ and when to use READTEXT; when to use WRITE and when WRITEXT.

    As it is documented, InStream.READ  and OutStream.WRITE are meant to be in binary format, which means that Text strings will be ‘zero terminated’ when written, and a ‘zero terminated’ is expected when you are using READ; however READTEXT will not expect a ‘zero terminated’ text, but can accept one.

    This very last feature makes the streams asymmetric, as you can write different binary formats than what you can read.

    Take the following code snippet:

    Streams code

    If we examine the file that gets created (in a binary text editor), you will notice that the Texts are in fact ‘zero terminated’, but the READTEXT will be able to read them individually.

    Now, this is where the fun begins. We can change the READTEXT to be READ (as it should have been) and everything will continue to work exactly the same…, almost. You will notice that the readCount now (when using InS.READ) will include the zero termination, whereas before the count was only for the characters in the text. Actually, if you put a return value in the WRITE (char 13), you will notice that the zero termination is included in the count (because actually that was the binary format of the Text).

    So, count in count := OutS.WRITE('My text.'); will be 9 (8 characters and a zero termination), whereas count := OutS.WRITETEXT('My text.'); will be 8. The reason why it was decided to return the written bytes, was precisely so that people would know how many bytes are sent through the Stream.

    Another thing to take in consideration when working with Streams are CODE types. When writing CODE types using OutStream.Write, they are NOT written in NAV binary format. So they are not symmetric with the FILE.WRITE function; however, InStream.READ(CodeType) will accept either data written using OutStream.WRITE(CodeType), or FILE.WRITE(CodeType).

    Another possibility is to write data using OutStream.WRITETEXT(Text) and read it via InStream.READ(Text). Just take in consideration that when using OutStream.WRITE(Text), the Text will be zero terminated and when using OutStream.WRITETEXT(Text), it will not. 

    Jorge Alberto Torres (jtorres)
    Software Developer Engineer

  • Microsoft Dynamics NAV Team Blog

    Simple query to check the recent performance history II - now including Query Plan information

    • 0 Comments

    One of the queries I use the most, is the pplan-cache query from this post:

    Simple query to check the recent performance history

    The good thing about the query is that it shows information that could otherwise require a lot of work (collecting and analysing traces files). And the query does not need any advance work or setup. It can just be run. So it's a very easy way to receive information from a system, which is often very useful as a first step in troubleshooting performance. For more details about the result of the query, refer to the post linked above.

    Below is a slightly enhanced version of the query. Since the query is based on the cache of compiled query plans, it is not a big step to extend it to also include the query plan itself, and even extract certain information from the plan if you know what you are looking for.

    So this query does the same as the original one, but with the following additions:

    • New column query_plan is included. It shows the query plan as xml which may be difficult to read, but it contains the full plan. Note: Some times, for no apparent reason, the query plan can't be retrieved, so it may not show the query plan on all lines.
    • cursor_type, just as an example of how to retrieve data from the query plan. If you find other things in the plans that may be interesting, then use the syntax to retrieve this further information.

    Here is the updated query

    SELECT TOP 100

    SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,

    ((CASE statement_end_offset

    WHEN -1 THEN DATALENGTH(st.text)

    ELSE qs.statement_end_offset END

    - qs.statement_start_offset)/2) + 1) as statement_text,

    execution_count,

    case

    when execution_count = 0 then null

    else total_logical_reads/execution_count

    end as avg_logical_reads,

    last_logical_reads,

    min_logical_reads,

    max_logical_reads,

    plan_handle,

    ph.query_plan,

    -- Query Plan Information

    case when

    ph.query_plan.exist('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtCursor/ns:CursorPlan/@CursorRequestedType)[1]') = 0

    then '' else

    ph.query_plan.value('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtCursor/ns:CursorPlan/@CursorRequestedType)[1]','nvarchar (max)')

    end as cursor_type

    FROM sys.dm_exec_query_stats as qs

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st

    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as ph

    ORDER BY max_logical_reads DESC

    :

     

    What I would really like, is to receive feedback on what parts of the query plans are useful. Then extend the query even further to include as much useful information as possible. For exdample, in some cases the query plan contains missing index-information. The lines below can be copied into the query above to include this information. Any feedback on whether this is useful or not, and whether other information from the query plans can be useful is really very welcome. You can add comments about this below.

    -- Missing Indexes

    ,case when ph.query_plan.exist('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:MissingIndexes/ns:MissingIndexGroup)[1]') = 0

    then ''

    else ph.query_plan.value('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:MissingIndexes/ns:MissingIndexGroup/@Impact)[1]','nvarchar (max)')

    end as missing_index_impact,

     

    case when ph.query_plan.exist('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:MissingIndexes/ns:MissingIndexGroup/ns:MissingIndex/@Table)[1]') = 0

    then ''

    else ph.query_plan.value('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:MissingIndexes/ns:MissingIndexGroup/ns:MissingIndex/@Table)[1]','nvarchar(max)')

    end as missing_index_table,

    case when ph.query_plan.exist('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:MissingIndexes/ns:MissingIndexGroup/ns:MissingIndex/ns:ColumnGroup/ns:Column/@Name)[1]') = 0

    then ''

    else ph.query_plan.value('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:MissingIndexes/ns:MissingIndexGroup/ns:MissingIndex/ns:ColumnGroup/ns:Column/@Name)[1]','nvarchar(max)')

    end as missing_index_field

     

    Lars Lohndorf-Larsen (Lohndorf)
    Escalation Engineer

  • Microsoft Dynamics NAV Team Blog

    Debugging in NAV 2009

    • 3 Comments

    UPDATE 25 August 2010! You cannot use the Express version of Visual Studio 2008 SP1 to debug Microsoft Dynamics NAV 2009. However, a useful and free visual debugger with slightly limited functionality - DbgClr.exe - can be downloaded as part of the .NET Framework 2.0 SDK: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=fe6f2099-b7b4-4f47-a244-c96d69c35dec

    --- 

    UPDATE 18 December 2009! The most up-to-date version of this content is available in the Microsoft Dynamics NAV 2009 Developer and IT Pro Help on MSDN: http://msdn.microsoft.com/en-us/library/dd301071.aspx.

    ---

     
    UPDATE!!! Debugging is not available in the Marketing Beta, so if you have access to a later build you can try this out. If not you will have to wait for RTM.
    Also note that SP1 for either Visual Studio 2005 or Visual Studio 2008 is required.
    ---

    In this post I will give you a example on how to debug in NAV 2009.

    1. EnableDebugging in CustomSettings.config found here: C:\Program Files\Microsoft Dynamics NAV\60\Service

    image

    2. Now Restart the “Microsoft Dynamics NAV Server” service

    image

    3. Now we have debugging enabled. Now start the RoleTailored client. This will take a bit longer to start up, because C# files are generated for each of the objects. (Pages, Codeunits, Reports etc.)

    4. Now we need to find a place to debug. In this example I will make a codeunit with a simple message and add this to an action on the Customer list.

    5. Create a Codeunit. Ex. 50000

    6. OnRun trigger place the following code: MESSAGE ('Debug test')

    image

    7. Save and compile Codeunit 50000.

    8. Now let us add this codeunit to a action. Design page 22 and navigate to the Actions on page 22.

    9. Insert a new action below the the Customer Action group

    image

    10. Hit F9 and enter this code on the Debug test -  OnAction trigger: CODEUNIT.RUN(50000);

    image

    11. Save and compile page 22

    12. OK we now have all code completed so now let us set a breakpoint in Visual Studio.

    13. Navigate to “C:\ProgramData\Microsoft\Microsoft Dynamics NAV\60\Server\MicrosoftDynamicsNavServer\source\Codeunit”

    14. Assuming that you have Visual Studio installed, (In my case I’m using Visual Studio 2008 with SP1) open Codeunit50000.cs

    image

    15. If you have UAC turned you will need to open Visual Studio with “Run as administrator”.

    16. Now we need to attach the NAV service. Select “Debug / Attach to process…”

    17. Attach Microsoft.Dynamics.Nav.Server.exe

    image

    18. Your screen should now look like this:

    image

    19. Now add a break point on the message “Debug test”

    image

    20, With this done now let us go to RoleTailored client.

    21. Navigate to Customer List and see that you have a new action called “Debug test” under “Related Information”

    image

    22. Now select “Debug test”

    23. Visual Studio will come back in focus with the breakpoint selected.

    image

     

     

     

     

     

     

     

     

     

     

     

    24. Now try to hit F5 and debugging will continue and RoleTailored client will come back in focus and show the message “Debug test”:

    image

    Thanks,

    Claus Lundstrøm, Program Manager, Microsoft Dynamics NAV

Page 1 of 1 (10 items)