Microsoft Dynamics NAV

Team Blog

  • 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

  • Microsoft Dynamics NAV Team Blog

    Creating Dashboards in NAV 2009

    • 2 Comments

    Please note that I have posted a new blog for this subject here. /Claus Lundstrøm

    Hi!

    I want to share with you also another report using some of the new capabilities in point of creating dashboards in NAV 2009.

    image

    Based on reporting services and NAV data source it is possible to create dashboards for every department. This offers a perfect extension to the default charts directly in the role center.

    The report shows you one example based on the sales department to get a quick and/or deep overview about the current situation.

    If you are a developer you can also use the report for learning purposes:

    • Use charts
    • Use coding in expressions
    • Use DrillDown
    • Use own color palette in charts

    You can download the report from here

    Thanks,

    Rene - Microsoft Dynamics NAV MVP (navmvp@live.at)

  • Microsoft Dynamics NAV Team Blog

    Demo Reports

    • 3 Comments

    I want to share with you some reports that are using some of the new capabilities that are available for use with RDLC reports in Microsoft Dynamics NAV 2009.

    We have prepared 4 reports which uses these capabilities:

    Customer - Order Summary:

    image

    • Use of Conditional Formatting (Table colors)
    • Use of Conditional Formatting (Colors in cells)
    • Use of Charts
    • Use of Hyperlink External(Click the Dynamics Logo in the bottom)
    • Use of Embedding picture into report

    Sales Pipeline:

    image

    • Use of Conditional Formatting (Icons, expand to see the icons)
    • Use of Conditional Formatting (Table colors)
    • Use of Visibility toggle
    • Use of Hyperlink (Click the Dynamics Logo in the bottom)
    • Use of Embedding picture into report

    Quantity Explosion of BOM:

    image

    • Use of Visibility toggle. Notice that this is Visibility toggle for “Expand All” replaces the previous table shown. Look for Table 1 and Table 2 in Visual Studio to see how that is done.
    • Use of Conditional Formatting (Table colors)
    • Use of Drill Through to Page(Click “Where used?”)
    • Use of Hyperlink (Click the Dynamics Logo in the bottom)
    • Use of Embedding picture into report

    Customer - Top 10 List:

    With Bar Chart

    image

    With Pie Chart

    image

    • Use of Visibility toggle. Now you can switch between Bar chart and Pie Chart in the Report Viewer and are not required to run the Request Page to make this change.
    • Use of Percent
    • Use of Point Labels
    • Use of Hyperlink (Client the Dynamics Logo in the bottom)
    • Use of Embedding picture into report

     

     

     

     

     

    The reports can be downloaded from the attached zip file.

    Thanks,

    Claus Lundstrøm, Program Manager, Microsoft Dynamics NAV

  • Microsoft Dynamics NAV Team Blog

    Partner Tools available for Microsoft Dynamics NAV 2009

    • 2 Comments

    Permissions for Sample Roles for Microsoft Dynamics NAV 2009 contains information about which objects (table data) each role can access within the database and the roles equals Role Centers (User Profiles) in the RoleTailored client. These sample permissions can be used as a starting point when setting up permissions for the specific Role Centers. Please note that the permissions will not be sufficient in all cases and should be customized to the individual instance.

    The Developer's Toolkit (NDT) has also received a quick overhaul and now supports field groups. If you don't know NDT already, you should look to the NDT if you are looking for a Source Analysis tool or a Compare and Merge tool. It is recommended that you use version NDT_v3.00.400 if you are using it in connection with Microsoft Dynamics NAV 2009.

    Finally we have released the Language Modules for 12 Languages (AU, NZ, DE, DK, ES, FR, GB, IE, IN, IT, NA and NL). If you are not familiar with Language Modules, they enable you to add additional language(s) to an installation. This is particular useful in multicultural companies, e.g. a German installation with German and Italian speaking employees. Please read the instructions carefully and note that the Language Modules for Microsoft Dynamics NAV 2009 require the Page ID Hotfix to be installed first.

    -Torben Siggaard

  • Microsoft Dynamics NAV Team Blog

    Please Take the Microsoft Dynamics NAV Implementation Survey

    • 1 Comments

    To help us better understand your experience with implementing Microsoft Dynamics NAV, we've created a survey that is available until March 20. The survey is open to all Microsoft Dynamics partner employees directly involved in any phase of Microsoft Dynamics NAV implementation work. It should take about 10 minutes to complete. The data from the survey will not be publicized, but it will help us improve your experience with Microsoft Dynamics NAV in the future.

    To begin the survey, follow this link.

    - Jacob Winther, NAV UX Designer

  • Microsoft Dynamics NAV Team Blog

    Another Update to the Microsoft Dynamics NAV 2009 Developer and IT Pro Help

    • 0 Comments

    We've released an update of the Microsoft Dynamics NAV 2009 Developer and IT Pro Help to MSDN and the Microsoft Download Center. This is the second of our periodic updates to developer and IT Pro content since NAV 2009 released in November.

    This release includes:

    • Bug fixes and new content. About 17% of the topics have been updated or are new in this release.
    • Debugging with Visual Studio. Added new topics about debugging RoleTailored client objects with Visual Studio.
    • Delegation. Added new topics about setting up delegation.
    • Form Transformation. Added new troubleshooting topics.
    • Improvements to Walkthroughs

    You can download the updates and copy it into your NAV 2009 installation, updating the Help you receive from F1. If you have feedback on any of the content, please use the feedback link at the bottom of each page in the CHM, or use the Ratings and Feedback form on each MSDN page.

    - Bob, Elona, Jill, and John (the NAV dev & IT Pro writers)

  • Microsoft Dynamics NAV Team Blog

    The physical location of .net components

    • 0 Comments

    The virtual table 2000000046 "Automation Server" shows you the physical location of the automation servers (.dll file). But a .net component is not an automation server, so is not listed here. One of the main benefits of .net is exactly to be able to copy and paste files, keep installations simple, and maintain multiple versions. But registering .net components do a lot to hide this simplicity. Windows Explorer also hides the actual structure of the registered .net components.

     To see the real structure, use a command prompt instead. Go to the folder c:\windows\assembly. Windows Explorer Will just list your .net components. But go to the same location from a command prompt, and it is different:

    The folder contains sub folders like GAC, GAC_32 and GAC_MSIL. When the .net framework looks for a .net component, it goes into one of these folders - typically GAC_MSIL, but it depends on which version of .net framework is installed.

    The next thing the .net framework does when locating a .net component, is to go into the folder with the same name. This folder then contains a folder for each version of this component. If .net is looking for a specific version which does not exist, then if you create a new folder here with this version, then .net will look in this folder. Finally, here you have the actual .net component (dll file) which the .net framework will use.

     

    Lars Lohndorf-Larsen (Lohndorf)

    Microsoft Dynamics UK

    Microsoft Customer Service and Support (CSS) EMEA

  • Microsoft Dynamics NAV Team Blog

    Report Headers

    • 0 Comments

    In classic reports, adding a header to your report is as easy as just copying the Header section from an existing one.

    With reports for RTC you need to do a bit of manual work to get your report header. A standard header contains the following information:

    Report Name

    Company Name

    Time and date (=Globals!ExecutionTime)

    Page Caption and Number (=Globals!PageNumber)

    User ID (=User!UserID)

    Time and Date, Page Number and User ID are automatically defined as global variables in Visual Studio report designer. All you need to do, is: Add a TextBox, right click on it and select "Expression...", then select from the Globals-Menu:

    Globals

    The other fields (Report Name, Company Name and Page Caption) are labels, and have to be added following the steps described here:

    Labels in NAV2009 Report Design

    So these are the two ways (Globals and Labels) that you have to add information to your report headers as illustrated here:

     

    HeaderBlog

     

     

     

    Lars Lohndorf-Larsen (Lohndorf )

    Microsoft Dynamics UK

    Microsoft Customer Service and Support (CSS) EMEA

  • Microsoft Dynamics NAV Team Blog

    Labels in NAV2009 Report Design

    • 0 Comments

    When adding fields to a report, the caption is typically added automatically or with a bit of drag and drop from the DataSet in VS Report Layout. But if you want to add additional labels, for example Report Name in the header, then there are a few more steps you must follow.

    Of course, you can just add a new TextBox in VS Layout, and type in the value you want to show. This is simple but will not give you Multi Language. Using a label from the classic report design gives you Multi language, but is a bit more complicated. This is what you need to do:


    As usual, before you can use it in the layout you must add the label to the report sections, and then specify the caption you want to display. In this case, make sure to also specify a name in the DataSetFieldName-property of the label. For this example, let’s call it Report_Label.
    To get the label value (Caption) into the layout, you must first add it to the Table on the Body-part of the layout. Note: It must be part of the table, and not in a TextBox outside of the table. The name of the cell will default to the DataSetFieldName you specified (Report_Label). And the value must be =Fields!Report_Label.Value. You get this value automatically if you drag it from the Data Set into a detail-section of the table. The caption is only here to be available from the header part of the report, so you can set the Visible-property to No.

    Finally you can get the label from the table and add it to a header-section in the report layout: Add a new TextBox, then name it something unique, for example Report_Label1, and set Expression = =ReportItems!Report_Label.Value. Note that "Report_Label" refers to the cell you added above, not directly to the label on the sections.

    This picture shows the steps needed to get the label to show in the header:

    AddLabel

     

     

     

    Lars Lohndorf-Larsen (Lohndorf)

    Microsoft Dynamics UK

    Microsoft Customer Service and Support (CSS) EMEA

  • Microsoft Dynamics NAV Team Blog

    Join us at the Microsoft Dynamics NAV Sessions at Convergence in New Orleans

    • 0 Comments

    We're just two weeks away from Convergence 2009 in New Orleans from March 9-13. We have created more than 70 sessions and Hands on Labs in the Microsoft Dynamics NAV track. Many of the sessions and all Hands on Labs will be focused upon the newest release, Microsoft Dynamics NAV 2009. For the list of sessions see the session list on Convergence web site.

    You can familiarize yourself with the new RoleTailored User Experience, Web Services and Reporting capabilities. You can learn about the different application areas, such as Manufacturing, Distribution, Finance and Service Management.  There are also industry related sessions where you can hear how Microsoft Dynamics NAV is used in different industries.

    To register for Convergence 2009, or for more information, see the Convergence web site.

    -Eva Sachse

  • Microsoft Dynamics NAV Team Blog

    Cursor Types

    • 0 Comments

    One of the changes in Microsoft Dynamics NAV version 5, was to change from primarily making use of Fast-Forward cursor types to Dynamic cursors. The same change was implemented in version 4 from build 26410, which is Version 4, SP3 Update6(940718) + KB950920.

    The change of cursor type can also mean a change in behaviour. With Dynamic cursors, SQL Server more often optimises for the ORDER BY - part of a SQL query than is the case with Fast Forward cursors. This is because a result set based on a dynamic cursor has to include new rows. IF SQL Server were to choose an index that fits the WHERE clause then it would have to sort all rows according to the ORDER BY before returning the first row to the client and that by definition is a STATIC result-set.

     

    Take this query as an example:

    SELECT * FROM "Demo Database NAV (6-0)"."dbo"."CRONUS International Ltd_$Item Ledger Entry"

    WITH (READUNCOMMITTED)

    undefined (("Document No_"=

    ORDER BY "Item No_","Posting Date","Entry No_"


    With Fast-Forward cursors, in this example SQL Server is likely to try to optimise for the WHERE clause, which is "Document No.". But with the ORDER BY clause specifying a different sorting, SQL Server may then chose a clustered index scan instead.

    With Dynamic cursors, SQL Server is more likely to optimise for the ORDER BY clause, which is a valid and existing index. So in this exampe SQL server would chose an index scan on this index. You can see this by running the query from SQL Server Management Studio like this:

     

    declare @p1 int set @p1=-1

    --declare @p3 int set @p3=16+4096+8192 -- Fast Forward

    declare @p3 int set @p3=2+4096+8192 -- Dynamic

    declare @p4 int set @p4=1

    declare @p5 int set @p5=49

    --declare @p5 int set @p5=15 – FAST 15

    exec sp_cursoropen @p1 output,N'

    SELECT * FROM "Demo Database NAV (6-0)"."dbo"."CRONUS International Ltd_$Item Ledger Entry"

    WITH (READUNCOMMITTED)

    WHERE (("Document No_"=@P1))

    ORDER BY "Item No_","Posting Date","Entry No_"

    '

    ,@p3

    output,@p4 output,@p5 output,N'@P1 varchar(20)',

    'START'

    select @p1, @p3, @p4, @p5

    exec sp_cursorclose @p1

    You can enable / disable the 2nd and 3rd line to switch between Fast-Forward or Dynamic cursor, and see the result in the query plan that gets generated.

     

    How does this affect NAV?
    The change in behaviour can mean that certain queries that ran without problems may be slow, after a customer upgrades the platform to the builds mentioned above. In cases

    that we have seen, the problem has been limited to one or very few specific queries, typically on filtered forms, that were slow after upgrading the platform. Use the query from here:


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

    to help identifying which queries to trouleshoot. Note that the query shows the Cursor Type in the rightmost column. Then look at whether SQL Server has an index to match the ORDER BY clause.


    Also, be extra careful using the SQLIndex-property on keys. By setting this property, the ORDER BY-clause may not match a SQL index anymore, and a Dynamic cursor will have

    to scan the table.

     

    Why this change?
    Internal performance tests show that overall, Dynamic cursors give better performance and fewer blocks. So while the change may cause unforeseen changes in behaviour when a customer upgrades, overall we have seen better performance with Dynamic cursors.

     

    Lars Lohndorf-Larsen (Lohndorf)
    Escalation Engineer

     

  • Microsoft Dynamics NAV Team Blog

    Customizing style sheets for MS Excel

    • 2 Comments

    As of version 5.0, Dynamics NAV introduced the option to export forms to MS Office (Word or Excel). Data presented on the form are exported in an xml, formatted using style sheets then sent to Word/Excel/Other programs

    A Style Sheet tool for Dynamics NAV has later been released to help partners create custom style sheets for export to Word in an easy and user friendly way. A possibillity for extending functionality to export to Excel might be considered for future versions.

    Meanwhile, customizing style sheets for excel, though requireing basic knowledge in xml and style sheets, can be simplified with some coding handling basic formatting of style sheets.

    The FOB attached contains an example of how to create such a tool, allowing some basic customizations to excel style sheets.

    Style Tool for Excel (xlt)

    The Style Toos for EXceL (xlt) is designed to make it easier to customize style sheets for exporting to Excel. Style sheets have endless possibilities to format your Excel documents. This tool makes some of the most common possibilities simpler, but does not cover all possible changes, so it may not eliminate the need for editing style sheets in external tools, but may help you with some simple customizations.

    Note, this tool was created and tested on  5.01 update 1 client, and assumes using default NavisionFormToExcel style sheet (update 1) as a starting point for customizations.

    To use it with 5.0 client, you might need to modify the tool.


    This is what you can achieve with xlt:
    Define a (font) style to be used on document, for example Bold, color, underline, itallic, etc.
    Define a style for certain condition, for example "Net Change" < 0, "Customer No." = 10000, etc
    Add totals.


    How to use xlt:
    All functionality in the tool can be accessed from form 73000 "XLT Style Card". Run the form and insert a new record to create a new custom style sheet, and specify Code and Description.

    Before you can make changes to a style sheet, you need to import it by clickin on Style -> Import, and choose an existing style sheet for export to Excel (such as NavisionFormToExcel), either from the database or from a file.

    On the Destination tab, specify the table and form IDs that the style sheet will be used for.

    Example:
    Run form 73000 and press F3. Fill out Code:ChartOfAccounts, Description: export Chart of Accounts
    Import the standard NavisionFormToExcel style sheet (by default placed in <client folder>\Stylehseets), by clicking on Style-Import and selecting NavisionFormToExcel style sheet.
    In New XSLT file location field, specify the location of your customized style sheet (example: c:\temp\NewXsltFIleName.xslt).
    On Destination tab, specify TableID=15, select 'Style sheet for this form only' and select form 16, Chart of Accounts.

    Excel Styles:
    You are now ready to customize your style sheet. To create new style, click Design -> Excel Styles. This shows you the styles that were imported with the standard style sheet. From here you can modify existing, or create new ones. To create a new style, press F3, and enter a Style ID.
    Then click on "Edit in Excel (F9)" to define this new style. It will open up Microsoft Excel, and here you can apply any formatting you wish, TO THE TOP LEFT CELL. The style you apply to this cell and it's contents will be imported and applied to all the cells your style applies to. Once you are done applying your style, close and save Excel, and then go back to NAV and confirm to import the style.

    As an example, consider the following scenario: we want to modify the existing label (caption) style and also create a new style (red fonts) that we apply to records in Net Change column with negative value.
    To modify existing label style, click on Design-Excel Styles, press Page Up/Down to browse between records, find Style ID: Label and Press F9 (Edit in excel). In opened excel ark, go to top left cell, in Excel menu select Home-Font and select a different font (for example, Arial Black, size 14). Change also the color to blue. Close the excel and save the file. In NAV client, click on YEs on prompted question (Please apply format in Excel sheet.....). In the Style tab, check that new style is applied  (you should see the record with Option=Font, Property=ss:FontName and Value=Arial Black).

    You can apply same kind of change directly in the Style tab (instead of opening Excel), provided you are familiar with properties and values as defined in Excel. If modifying styles directly in Style tab, remember to click 'Update (F11)' to apply changes to style sheet.

    Note that

    1) moving away from the style by closing the form or moving to the next record will not save any changes you made manually (and not through excel), and

    2) the tool does not validate if changes you do manually are valid.

    Now we want to create a new style and apply it to Net Change column of records with net change < 0.
    Press F3 to create a new style, in Style ID field type RED. Click on F9 (Edit in Excel) and in opened excel sheet click on top left cell. In Excel menu, select Home-Font and select red font color. Close the Style Sheet, answer yes to save changes. In NAV Client answer yes to prompted question (Please apply format in Excel Sheet...). In General tab, You should now see properties defined for StyleID=RED


    Conditional formatting:
    After designing the style(s), then go back to the XLT Style card, and then click Design -> "Conditional Formatting" to apply this style to certain conditions. It is not recommended that you change any of the existing layouts here, but you can create a new one by clicking "Insert (F3)". This opens a mini-wizard where you can specify which columns and values this will apply to.

    To follow our example, we will now specify the condition for using the style created in last step, RED style.
    Close the Excel Styles form and in Xslt style card click on Design-Conditional Formatting. Click on F3 (or click on Insert) to create new condition (it is not recommended to modify any of the existnig, defualt ones). Wizard form opens. We will now create a condition to apply style red to NET Change column with values < 0. Select TableID=15 and Field 32 (Net Change). In Condition field, select'<' anad Value=0. Finally, In Choose style select RED. Click on Finish. You should now see your condition defined in General tab. Condition= (@name='Net Change') and (@value<'0').
    Click on Update to update the style sheet with new condition.


    Totals
    You can specify fields that you want to create totals for, by clicking on Design -> "Total Fields", and select one or more (numerical!) fields. You also need to add one line of C/AL code in codeunit 403 "Application Launch Management". The following line should be added to Codeunit 403, at the top of function LanuchApp:

    XLStyleSheetDataMgt.UpdateDataXML(DataXML,StylesheetID);

    where XLStyleSheetDataMgt is a new local variable, type Codeunit, subtype 73001 ("XLT Style Sheet Data Mgt")

    This will add totals to the data that you export to Excel, and it will add elements to the style sheet to handle these totals. Note: This will not work for RTC because RTC does not make calls to codeunit 403 (see blog here: http://blogs.msdn.com/nav_developer/archive/2008/12/16/dynamics-nav-2009-and-ms-office-integration-send-to-excel-and-word.aspx).


    Export style sheet:
    When you have created one or more style(s) and applied them to certain conditions, then you generate a new style sheet from the "XLT Style Card"  by clicking Style -> Export, and then select to export to file or database. If exporting to database, then a new style sheet will be saved in database, using the Description you have specified in the "XLT Style Card".


    Open form 18 (Chart of Accounts), in toolbar menu, click on send-to button. Select Microsoft Excel and select 'export Chart of Accounts' style sheet., click on OK.
    Chart of Accoutns shoud lbe exported to excelm, with labels in Arial Black font, size 14. Net change column where value < 0 should be marked RED and totals should be present for Net Change column.

    Other functionality:
      - From "XLT Style Card", tab "Standard Styles", specify a style to apply to totals.
      - Click Style -> Reset to re-load the stylesheet, which will erase any changes you have done since you imported it.

     

    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 tool are very welcome! 

     

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

    Microsoft Dynamics NO


    Microsoft Customer Service and Support (CSS) EMEA

  • Microsoft Dynamics NAV Team Blog

    Help Toolkit and Help Source Files for Microsoft NAV 2009

    • 1 Comments

    The Microsoft Dynamics NAV 2009 Help Toolkit and the Help Source files are now available for download from PartnerSource.

    We're making the source files for the core Microsoft Dynamics NAV 2009 Help available to Partners so that you can customize the Help to enhance your add-ins or to match the solutions you sell. You can edit the source files and recompile the Help files, or you can copy from the source files into your own Help or other documentation. The source files for the Group 1 countries (Australia, Canada, Denmark, France, Germany, Italy, Mexico, the Netherlands, New Zealand, Spain, the United Kingdom, and the United States) have been released. We will make available the source files for the other countries after NAV 2009 ships for those countries. 

    In a post in September, I mentioned that we were updating the NAV Help Guide and the tools in the Help Toolkit we provide to assist you in creating help. This release of the Microsoft Dynamics NAV 2009 Help Toolkit includes a completely re-written Help Guide, with How To topics, walkthroughs, and guidelines for creating Help and transforming Help for the Classic client into Help for the RoleTailored client. We've also replaced the GATE tool with Help Builder, which is a more robust tool for processing source files and compiling Help files. We've also fixed some bugs in the Generate HTML Help batch job that you can use to generate Help topics for tables, fields, forms, pages, and reports.

    If you have any questions or comments about these two releases, please send an e-mail to navhelp@microsoft.com.

    We continue to be interested in hearing what your needs are, so we can ensure that we are improving the process for customizing and extending Help. You can add your comments below or e-mail us at navhelp@microsoft.com. Here are some questions to get you started:

    • How do you use the content that we provide?
    • What types of Help do you provide to your customers to assist them in using your solution?
    • If you don't provide your customers with customized Help, what are the main reasons for not doing this? How does this impact your support costs?
    • How will you use the source files for all of our Help, in all available languages? Will you customize these CHMs? Repackage Help into another format? Or do something else?
    • Do you extend Help with information about the changes that you have made to Microsoft Dynamics NAV, such as new tables, forms, pages, etc.? How important is it to be able to extend Help? What would make this scenario more efficient for you?
    • Have you used the Help Guide? How well did it help you accomplish Help customization?
    • Do you know that we removed Company Notes in Microsoft Dynamics NAV 5.0 and why? Were you using Company Notes? What worked for you about this solution? What didn't work?
    • If you customize the content that we provide, how do you determine what has changed from version to version?

    It's important for us to ensure that we are providing an efficient way for you to customize the documentation. We're looking for volunteers to test our new Help processes, and provide us with feedback on how well these solutions meet your needs. In return, we'll offer extra assistance in using these processes. If you would like to participate, please e-mail navhelp@microsoft.com.

     - Paul Chapman

Page 37 of 47 (693 items) «3536373839»