April, 2009

  • Microsoft Dynamics NAV Team Blog

    Dynamics NAV, customizing style sheets for export to MS Excel

    • 2 Comments

    A while a go we posted a blog decribing how to make some basic customizations to style sheets used for export to Excel.

    http://blogs.msdn.com/nav/archive/2009/02/18/customizing-style-sheets-for-ms-excel.aspx

    This is an update to that blog. You can download updated sample objects from the link below

    In addition to changes described there (customizing font styles, conditional formatting and adding totals), this will allow adding some basic document properties, choosing to display zero decimal fields as blank, and adding header and footer to the exported document. In addition, code changes added here would allow user to run send to excel, apply described customizations directly in exported document, then import the document to automatically generate customized style sheet. The objects are attached.

    These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.

    All feedback regarding issues or suggestion to this post are very welcome!  

    Jasminka Vukovic (jvukovic), Lars Lohndorf-Larsen (Lohndorf)

    Microsoft Dynamics NO


    Microsoft Customer Service and Support (CSS) EMEA

  • Microsoft Dynamics NAV Team Blog

    Beware the SQL Index property on NAV 5.0 SP1

    • 0 Comments

    The discussion of the changes starting with Microsoft Dynamics NAV version 5 regarding the use of Dynamic cursors has already been addressed in the article titled "Cursor Types" on the SE Blog - http://blogs.msdn.com/microsoft_dynamics_nav_sustained_engineering/

    However, we are seeing more and more cases where the use of the SQL Index property in NAV 5.0 SP1 is causing performance issues, so the purpose of this blog is to explain in more detail from an application perspective how this property can affect performance.

    From version 5.0 we are using Dynamic cursors, which means that SQL server will tend to choose an index that fits the ORDER BY clause more than the WHERE clause. This is not a problem as long as there is no conflict between the ORDER BY and the WHERE clause. This is where the SQL Index property comes in.

    SQL server should be able to choose the optimal index IF we create an index which matches the ORDER BY or the filter/range perfectly. NAV constructs the ORDER BY based on the NAV Key specification not the SQL Index specification. If a SQL Index value is specified on the NAV key and the fields do not exactly match the fields and the order in which they were specified in the Key, then there could be a conflict between the ORDER BY and the WHERE clauses.

    How does the issue present itself in the application? These are just a couple of examples we have seen of the impact the SQL Index specification can have on the application…

    Poor performance on large tables like Ledger Entry tables, if a user selects Sort on the form and changes from the primary key to a secondary key which has a SQL Index specification.

    Using the RunFormView and RunFormLink property of a form…If the RunFormView is set to a non-primary key,  SQL might not choose the first key matching the RunFormView if that key has a SQL Index specification. Depending on the keys defined on the table, there may not be another adequate key available, so a suboptimal index may be used. The ORDER BY may no longer match the WHERE clause (RunFormLink), resulting in very poor performance loading the form.

    Performance problems when SETCURRENTKEY/SETRANGE is used in code and the key specified has a SQL Index specification that does not match the key or the filters entered (WHERE clause).

    ************

    We are seeing issues mainly with customers who have upgraded to 5.0 SP1 from 5.0, because there were values specified for SQL Index in many tables in the base product in 5.0. Some other customers on pre-5.0 versions might also have issues if someone has done performance tuning on their database. In earlier version of NAV, SQL Index was used to improve performance in certain scenarios. 

    The recommendation is that the use of the SQL Index specification should be limited to very specific situations where thorough testing shows performance improvement. In the vast majority of scenarios, SQL will make the correct choice of Index, so the SQL Index specification is not needed and can actually cause poor performance.

    For more information on identifying these problematic queries, please see the SE Blog post mentioned at the top of this article.

    Laura K. Lake (lalake)

    Microsoft Dynamics NA


    Microsoft Customer Service and Support (CSS) North America

  • Microsoft Dynamics NAV Team Blog

    An almost exact decimal

    • 0 Comments

    Recently I was working with a very interesting case, involving Automation, and AL runtime. This involves the number of decimals that NAV supports; giving the sensation that rounding is done incorrectly.

    First, I would like to bring you your attention to Table 370 (Excel Buffer). This table provides a very simple way to read and write Excel files. This works pretty neatly, except when you start dealing with some “special numbers”.

    The issue, really, is that decimals, when transported via COM, are encoded in a VT_R8, which means that we have 64 bits to express all decimal values (according to MSDN, VT_R8 is an IEEE 8-byte), which basically means, they cannot express extremely precise values.

    Now, how exactly precisely is precise? Let’s work with 17.7.

    Well, don’t think that NAV, or for that matter COM, cannot express this “simple” value, the problem is really they way decimals are encoded.

    Let me first show you the issue, as it is pretty simple to reproduce.

    First, create an Excel file using Microsoft Excel. On cell A1 of a Sheet, type “17.7”. Now, this by itself is tricky, because what you need to type depends on your Locale. What I mean is seventeen and 7/10 (meaning, that you might have to use comma instead of a dot).

    Save the recently created file someplace that you can remember.

    Now create a codeunit, with one global variable of type record of Subtype “Excel Buffer”.

    Then open your file and refer to your Sheet, and read it, just like this:

    Codeunit 

    It is time to see the inserted value, for that, go to Table 370. You will see that the Cell value is in fact “17.699999999999999” (if you see 17.7 is because your NAV version is not as accurate as the one I am using, or the codeunit has already been modified for rounding).

    You must be thinking now that I am trying to sell you a platform bug as a feature, but this not the case. The problem, really, is that recent NAV builds recognizes more decimals (up to 18), and an 8 byte IEEE is not as precise. To illustrate this, look at the following table:

    Decimal Floating-Point:

    64 bit Hexadecimal (memory representation):

    17.699999999999995

    4031B33333333332

    17.699999999999996

    4031B33333333332

    17.699999999999997

    4031B33333333332

    17.699999999999998

    4031B33333333333

    17.699999999999999

    4031B33333333333

    17.700000000000000

    4031B33333333333

    17.700000000000001

    4031B33333333333

    17.700000000000002

    4031B33333333334

    17.700000000000003

    4031B33333333334

    17.700000000000004

    4031B33333333335

    In here, it is clear that the hexadecimal representation “4031B33333333333”, not only refers to 17.7, but also to some “neighboring” values (actually 4 values), so, the COM algorithms seem to choose the most likely one: the middle, which in fact, is really a close approximation.

    The reason why decimal accuracy was increased in NAV was precisely to improve calculations when dealing either with very large numbers, or very accurate ones, the consequence being that 64 bit representation cannot  deal with so many decimals.

    Fortunately, in order to prevent this from happening, you can round the values to be less precise, which in most cases will give you the values that you were in fact expecting, so for decimals, you could round to 4 decimals using variants (in order to know if you are dealing with a decimal number or not):

        "Cell Value" := XlWrkSht.Range(xlColID + xlRowID).Value;

        IF ("Cell Value".ISDECIMAL) THEN

        BEGIN

          "Cell Decimal Value" := "Cell Value";

          "Cell Decimal Value" := ROUND("Cell Decimal Value", 0.0001);

          "Cell Value as Text" := FORMAT("Cell Decimal Value");

        END

        ELSE

          "Cell Value as Text" := DELCHR(FORMAT(XlWrkSht.Range(xlColID + xlRowID).Value),'<',' ');

     

    Hopefully this will clarify why you can get this approximations, and how to deal with it in C/AL.

    Jorge Alberto Torres (jtorres)
    Software Developer Engineer

  • Microsoft Dynamics NAV Team Blog

    RIM Tool on NAV 2009

    • 0 Comments

    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 migration due to these changes.

    The previously published blog for 5.0 SP1 contains a change log of some code suggestions and workarounds for the more common and/or critical issues. 

    This blog addresses many of the same issues in NAV 2009. While some corrections were made between the release of 5.0 SP1 and 2009, not all fixes made it into the RTM release of 2009. The following change log can be applied to the 2009 objects.


    This change log applies to 2009 RTM 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 -1099 Code, etc.- 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) Negative decimal values are converted incorrectly, resulting in changed values.
    5) Expected token 'EOF' found '$'. Error on fields with $ symbol


    Changes are labeled with the associated number above.

     

    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];                    // changed #1 - changed from 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,'<>,./\+-&()%$','             '),'=',' ');  // Changed - added $ symbol and one additional space between single quotes #5

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

     

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

      NameIn := '_' + NameIn;                                   // Added #2

     

    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          // Added #2

      FieldNodeName := '_' + FieldNodeName;                           // Added #2

     

    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,'<>,./\+-&()%$','             '),'=',' ');  //Changed - added $ symbol and one additional space between single quotes #5

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

     

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

      NameIn := '_' + NameIn;                                   // Added #2

     

    EXIT(NameIn);

     

     

    * Codeunit 8611, Function InsertRecordWithKeyFields

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

    Before:

     

    VAR

          MatrixData@1001 : Record 8615;

          MigrationTableField@1007 : Record 8616;

          OSynchTypeConversion@1008 : Codeunit 5302;

          RecRef1@1006 : RecordRef;

          KeyRef@1003 : KeyRef;

          FieldRef@1004 : FieldRef;

          KeyFieldCount@1005 : Integer;

          ToValidate@1009 : Boolean;

     

        BEGIN

          MatrixData.SETRANGE(TableID,MatrixRecord.TableID);

          MatrixData.SETRANGE("No.",MatrixRecord."No.");

     

          KeyRef := RecRef.KEYINDEX(1);

          FOR KeyFieldCount := 1 TO KeyRef.FIELDCOUNT DO BEGIN

            FieldRef := KeyRef.FIELDINDEX(KeyFieldCount);

            ValidationFieldID := FieldRef.NUMBER;

            MatrixData.SETRANGE(FieldID,FieldRef.NUMBER);

            IF MatrixData.FIND('-') THEN BEGIN

              MigrationTableField.GET(MatrixData.TableID,MatrixData.FieldID);

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

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

            END ELSE

              IF KeyRef.FIELDCOUNT <> 1 THEN BEGIN

                ERROR(STRSUBSTNO(Text000,FieldRef.NAME,RecRef.NAME + ':' + FORMAT(MatrixData."No.")));

              END

          END;

     

          RecRef1 := RecRef.DUPLICATE;

     

          IF RecRef1.FIND('=') THEN BEGIN

            RecRef := RecRef1;

            EXIT

          END;

     

          RecRef.INSERT(NOT CompanySetupRun);

        END;

     

     

    After:

     

    VAR

          MatrixData@1001 : Record 8615;

          MigrationTableField@1007 : Record 8616;

          OSynchTypeConversion@1008 : Codeunit 5302;

          RecRef1@1006 : RecordRef;

          KeyRef@1003 : KeyRef;

          FieldRef@1004 : FieldRef;

          KeyFieldCount@1005 : Integer;

          ToValidate@1009 : Boolean;

          Field@1010 : Record 2000000041;                            // Added #3

          Datevalue@1011 : Date;                                     // Added #3

     

       BEGIN

          MatrixData.SETRANGE(TableID,MatrixRecord.TableID);

          MatrixData.SETRANGE("No.",MatrixRecord."No.");

     

          KeyRef := RecRef.KEYINDEX(1);

          FOR KeyFieldCount := 1 TO KeyRef.FIELDCOUNT DO BEGIN

            FieldRef := KeyRef.FIELDINDEX(KeyFieldCount);

            ValidationFieldID := FieldRef.NUMBER;

            MatrixData.SETRANGE(FieldID,FieldRef.NUMBER);

            IF MatrixData.FIND('-') THEN BEGIN

              MigrationTableField.GET(MatrixData.TableID,MatrixData.FieldID);

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

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

     

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

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

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

              ELSE BEGIN                                                                                   //  Added #3

                EVALUATE(Datevalue, MatrixData.Value);                                                     //  Added #3

                FieldRef.VALUE := Datevalue;                                                               //  Added #3

              END;                                                                                         //  Added #3

     

            END ELSE

              IF KeyRef.FIELDCOUNT <> 1 THEN BEGIN

                ERROR(STRSUBSTNO(Text000,FieldRef.NAME,RecRef.NAME + ':' + FORMAT(MatrixData."No.")));

              END

          END;

     

          RecRef1 := RecRef.DUPLICATE;

     

          IF RecRef1.FIND('=') THEN BEGIN

            RecRef := RecRef1;

            EXIT

          END;

     

          RecRef.INSERT(NOT CompanySetupRun);

        END;

     

     

     

     

    * 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;                   // Added #3

     

    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)          // removed #3

     

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

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

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

            ELSE BEGIN                                                                                   // Added #3

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

              FieldRef.VALUE := DateValue;                                                               // Added #3

            END;                                                                                         // Added #3

     

          END;

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

            TemplateMgt.UpdateRecord(DataTemplateHeader,RecRef);

     

     

     

    * Codeunit 5302, Function TextToDecimal

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

    Before:

     

    VAR

          PartArray@1003 : ARRAY [2] OF Text[250];

          IntegeralPart@1001 : Integer;

          FractionalPart@1002 : Integer;

     

     

    After:

     

    VAR

          PartArray@1003 : ARRAY [2] OF Text[250];

          IntegeralPart@1001 : Integer;

          FractionalPart@1002 : Integer;

          Sign@1005 : Integer;                   //add new local variable #4

     

    Before:

     

     

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

     

     

    After:

     

     

    IF STRPOS(InputText,'-') = 0 THEN     //added #4

      Sign := 1                           //added #4

    ELSE                                  //added #4

      Sign := -1;                         //added #4

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

     

     

    Laura K. Lake (lalake)

    Microsoft Dynamics NA


    Microsoft Customer Service and Support (CSS) North America

Page 1 of 1 (4 items)