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:

Customer balance by discount group:

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.
  }
}

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

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)

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

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:

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 

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

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:

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

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,

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

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

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

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')

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

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

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

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

18. Your screen should now look like this:

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

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”

22. Now select “Debug test”

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

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

Thanks,

Claus Lundstrøm, Program Manager, Microsoft Dynamics NAV