Wednesday, December 23, 2009

Transformation Tool Issue

There may be possibility in some forms to show the records in bold & colors based on the conditions.

CurrForm."No.".UPDATEFONTBOLD(Condition);

CurrForm."No.".UPDATEFORECOLOR(Condition);

While converting this form to page using Transformation Tool, we will face compilation errors in page because same code is moved into the pages.

After the R&D, I found CodeRules.txt in the Transformation Tool does not have proper rules to transform the above kind of code in to pages.

For Example:

For CurrForm.”No.”.ENABLED(Condition), it is working properly because of the following marked code rules.

  • <find>
  • !currForm!.!var1!.ENABLED :=
  • <declareVariable>
  • !var1!Enable
  • <declareVariableType>
  • Boolean INDATASET
  • <replace>
  • !declaredVariable! :=
  • <moveValueToProperty>
  • !declaredVariable!
  • <movePropertyToControlName>
  • !var1!
  • <moveToProperty>
  • Enabled
  • <comment>

But for Currform.”No.”.UPDATEFONTBOLD(Condition), it is not working because there are not code rules for <moveValueToProperty> <movePropertyToControlName> <moveToProperty>

  • <find>
  • IF !var1! <> !var1!::!var2! THEN
  •   !currForm!.!var3!.UPDATEFONTBOLD := TRUE;
  • <replace>
  • !currForm!.!var3!.UPDATEFONTBOLD := !var1! <> !var1!::!var2!;
  • <find>
  • !currForm!.!var1!.UPDATEFONTBOLD :=
  • <declareVariable>
  • !var1!Emphasize
  • <declareVariableType>
  • Boolean INDATASET
  • <replace>
  • !declaredVariable! :=
  • <comment>

Solution: Either we should change the code rules or modify the page manually.

Wednesday, December 16, 2009

New Report Properties in NAV 2009 SP1

Microsoft Dynamics NAV 2009 (Sp1) reports has new properties to utilize the advantages in the Visual Studio report designing.

Using the following properties, you can design the RTC reports with additional functionalities:

  • EnableHyperlinks (Useful for Drill Down from a Report to a Page functionality)
  • EnableExternalImages
  • EnableExternalAssemblies

Tuesday, December 15, 2009

Reporting for Microsoft Dynamics NAV 2009 (SP1)

Microsoft has provided a very good demo script with scenarios on how to use the new reporting functionality in Microsoft Dynamics NAV 2009.

This documents covers the following functionalities.

  • Format text and add colour
  • Interactively sort table data in a report
  • Add charts
  • Save a report as a PDF or an Excel file
  • Drill down from a report to a page
  • Please click the link to download the Demo Script from the Partner Source.

    Wednesday, December 9, 2009

    How to determine the add-in's public key token

    To register an add-in, you include it in the Client Add-in table in Microsoft Dynamics NAV. To include an add-in in the table, you must provide the Public Key Token.

    1. At a command prompt, change to the directory that contains the sn.exe utility.

    For example, the default directory for Microsoft Visual Studio 2008 is C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin. (or) the default directory for sn.ext file is C:\Program Files\Microsoft.NET\SDK\v2.0 64bit\Bin.

    2.   Type the following command:

           sn.exe –T <assembly>

    Replace <assembly> with the add-in assembly's path and file name, such as Program Files\Microsoft Dynamics NAV\60\RoleTailored Client\Add-ins\MyCompany.MyProduct.RtcAddins.dll

    3. Press ENTER and not the public token key that is displayed.

    NOTE: Please use semicolons before and after <assembly> value like the following: <assembly>

    Sunday, November 29, 2009

    Product Suggestions for Microsoft Dynamics

    Today I came to know about the Dynamic suggestions link. This is used to give your product suggestions and Bug Reporting. This helps to make future versions of Microsoft Dynamics better.

    Please click the link to connect to the dynamicsuggestions.

    Saturday, November 28, 2009

    How to block New, Edit and View actions in the ListPage

    In Microsoft Dynamics NAV RT Client, pages has default actions like New, Edit, View and etc…imageIn the ListPage, New action is promoted and shown in the New Promoted Category. This action is not promoted in the card part. 

    image According to my requirement, I do not want New promoted action in the listpage. This can be achived by customizing the Actions like the following:

    image image

    But this process should need to be done in every client. The same requirement can be achieved by modifying the TIF information.

    For Customer List Example: Remove the CardFormID field value in the TIF information for the Customer List and transform the page to form..

    image image image

    Limitations: Double clicking the Customer List will not open the Customer Card (Standard Functionality). Work around is to create a new action to open the Card and promote this action.

    image

    Friday, November 27, 2009

    SAVERECORD and UPDATE

    In Microsoft Dynamics NAV, CurrForm.SAVERECORD or CurrPage.SAVERECORD is used to save the current record shown on the form/page. CurrForm.UPDATE(True or False) or CurrPage.UPDATE(True or False) is used to save the current record based on the parameter and updates the controls in the form/page.

    Using the two functions together in the form/page does not give any error normally but error will be displayed in pages if the following code is called before inserting the record.

    CurrPage.SAVERECORD();//Which save the record.
    CurrPage.UPDATE;//Which updates the controls.

    For Example: Add the above lines of code in the Type – OnValidate() in the “46 Sales Order Subform” page and try to insert the sales line.

    image This is because above set of code is trying to insert the record in two places one is using SAVERECORD and second is UPDATE (even though Parameter is FALSE).

    Thursday, November 26, 2009

    How to read BLOB data and export into a File

    In Microsoft Dynamics NAV tables, we can create BLOB fields to store large amount of data. It is not possible to read the data in the BLOB fields directly.

    The following steps shows the procedure to read the BLOB data. In this example, I have taken “User Metadata” table to read the data in the “Page Metadate Delta” field.

    1) Create a codeunit with the below variables.

    Name DataType Subtype Length
    UserMetadata Record User Metadata  
    Data InStream    
    Line Text   1024
    Pos Integer    
    File1 File    

    2) Add the following code to the codeunit.

    image 3) Save and Run the codeunit. Text file will be created in the given path with the data in the BLOB field.

    Thanks,

    Veerendra CH.

    Understanding the Difference Between GP, NAV, SL and AX

    Here is the nice post from Houston Neal on “Understanding the Difference Between GP, NAV, SL and AX”.
    Please click the link to read this post.

    Here is the another link from Houston Neal on Manufacturing Solution Advice.

    Sunday, November 8, 2009

    Object Subtype field in the AllObjWithCaption table

    In Microsoft Dynamics NAV, AllObjWithCaption table is one of the virtual table from the older versions.

    This AllObjWithCaption virtual table holds all the objects details like Type, ID, Name and Caption.

    One new field Object Subtype is added to this table in NAV 2009.

    image

    This field is used to store subtype of two object types. 1) Codeunits 2) Pages

    Codeunits has three Subtypes.

    1) Normal: These are normal coduenits.

    2) Test: Testing Codeunit

    3) TestRunner: Test runner codeunit, used to run the Test Codeunits.

    Pages has different subtypes like Card, List, Worksheet, Document, etc…

    Saturday, October 24, 2009

    How Run button in Object Designer opens the page in the RTC

    In the NAV 2009 SP1, it is possible to run the Page from the Object Designer.

    image In the Object Designer, Run button will open the related page in Role Tailored Client. I think this is achieved using the HYPERLINK function in Navision.

    HYPERLINK function passes a URL as an argument to an Internet browser.

    By adding the following code in OnPush Tirgger of any button will open the related page in Role Tailored Client.

    HYPERLINK('DynamicsNAV:////runpage?page=' + FORMAT(ID));

    Here ID valud should be Page ID.

    The above code can also be applied to open the reports in Role Tailored Client.

    HYPERLINK('DynamicsNAV:////runreport?report=' + FORMAT(ID));

    The above line of code can be added to any form/page to open the pages/reports dynamically.

    Friday, October 23, 2009

    Transformation Tool: Subfrom Menu Items to Pages

    Using Transformation Tool it is possible to transform forms to pages in NAV 2009.
    Forms like Sales Order, Purchase Order has Line Menu Button in the Main from and Transformation Tool automatically transforms the Line Menu button to the Sub page (ListPart). If the Menu Button has any Menu Items, Transformation Tool will also move the Menu Items to the Sub page.

    Logic behind this is,Transformation Tool moves the Menu Item to Sub page if it finds the code like “CurrForm.subform.FORM.FunctionName” in the Onpush of the menu item.
    Sales Order—>Line—>Reservation Entries, OnPush trigger has the following code.
    CurrForm.SalesLines.FORM.ShowReservationEntries;
    image

    For example if you want a confirmation message before opening the reservation entries, you can write the code in the function or you can write like below:
    IF CONFIRM(Text123,TRUE) THEN
      CurrForm.SalesLines.FORM.ShowReservationEntries;

    If the code is like above, Transformation Tool will not move this Reservation Entries menu item to sub page. It will create a new Line menu button in the main page and add the Reservation Entries menu item to that.
    image
    TIP: If your requirement needs to write code before calling the function in the subform, maintain the code in single line like the following:
    IF CONFIRM(Text123,TRUE) THEN CurrForm.SalesLines.FORM.ShowReservationEntries;

    Saturday, October 17, 2009

    How to change SQL Server default backup location

    As part of my job, I need to create databases regularly. Because of this I made a SQL Backup of the database with required objects and necessary setup. When ever I try to create a database, SQL Server is showing the default path of the backups.

    It is possible to change the default location of the SQL Server backup files:

    If we open the registry using REGEDIT or some other tool and if you navigate to this key:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLSERVER and change the BackupDirectory value to the required location.

    image This solved my problem and saved some time.

    Monday, October 12, 2009

    ProviderID property to System Parts

    Last week after posting the learning on ProviderID property, I am going through the mibuso and found any interesting post related to the ProviderID linking to the System Parts.

    I also tried to achieve this, but not able to find any solutions except create a new list page and adding system part to that.

    The solution kine has given is working.

    -Create a list page based on Sales Line.

    -Add only two line. One Container and another Part.

    -Change the Parttype to System and SystemPartID to Recordlinks for the part.

    -Add the new list page to sale order form as Factbox

    -Set the ProviderID and SubFormlink.

    This solution is working as temporary.

    Friday, October 9, 2009

    ProviderID property in NAV 2009

    ProviderID property is specifically for the RoleTailored client and not supported in the Classic Client.

    This property enables you to create a link from a Repeater or any other type of control to a Factbox. It could also be used to link two FactBoxes. For example, the Sales Order page (42), uses this property to update the Sales Line FactBox by creating a ProviderID link to the SalesLines FastTab.

    image

    For Example:

    If you open the Page 42 in design mode, <Control1906127307> “Sales Line FactBox” control ProviderID value is 58 which is the ID of the SalesLines control in the same page.

    image 

    That is the reason if you open the Sales Order page in Role Tailored Client, Sales Line Factbox details are updated based on the Sales line you selected.

    image

    Tuesday, October 6, 2009

    Error in NAV 2009 Reporting

    Sometimes the following error may occur while designing the reports for RT Client:

    “the provided metadata is not valid”

    Troubleshooting steps has given below or click the MSDN link.

    This error can happen when the following conditions are true:

    • You are using Windows Vista and User Access Control (UAC) is turned on.

    • The executable file that runs the Visual Studio development environment (devenv.exe) is set to run as an administrator.

    • The executable file that runs the Classic client with Microsoft SQL Server (finsql.exe) is not set to run as an administrator.

    To resolve this issue, either turn off UAC or set both finsql.exe and devenv.exe to run as an administrator.

    To turn off UAC
    1. In Control Panel, select User Accounts.

    2. In User Accounts, click Turn User Account Control On or Off.

    3. Clear the Use User Account Control (UAC) to help protect your computer check box.

    To set a program to run as an administrator
    1. Right-click the .exe file, and then select Properties.

    2. In the Properties window, on the Compatibility tab, in the Privilege Level section, select Run this program as an administrator.

    Sunday, October 4, 2009

    Friday, October 2, 2009

    How to Delete Personalization Settings

    In the NAV 2009 Role Tailored Client, it is possible to reset the user specified settings, automation decisions and file handling decision using “Delete Personalization Settings” option shown below”

    Open the Role Tailored Client.

    imageimage

    Help regarding the options available in the “Reset User-Specified Settings” form is shown below:

    Reset User-Specified Settings

    You can personalize the appearance of the RoleTailored client, or decide what to do when the Microsoft Dynamics NAV server requests permission to run unknown software on your computer. The "Reset User-Specified Settings" dialog box gives you the opportunity to undo these personalization's and decisions.

    User interface

    This category covers changes that modify the appearance of pages in the RoleTailored client. You can specify which elements (such as fact boxes and lists) are shown in a particular page, and also specify the size and position of elements. Click "Reset UI settings" to restore all pages to their original default layout.

    Automation objects

    An automation object is a software component that can run on a Windows computer. The Microsoft Dynamics NAV server may request to run an automation object on your computer. The first time this happens, the client prompts you for permission to run the object. If you choose “Always allow" or "Never allow,” the client does not prompt you on subsequent occasions when it receives a request to run an object of the same type. You can only revisit this decision by clicking "Reset Automation decisions". The next time the server wants to run an automation object, the client presents the original three choices.

    Client file access

    The Microsoft Dynamics NAV server may request to run or download a file to your computer. The client offers three choices: Run, Save, or Cancel. It also provides a checkbox, which is selected by default: "Always ask before opening this type of file." If you clear this checkbox, subsequent files of the same type are automatically handled in the same way as the original. You can only then revisit this decision by clicking "Reset file handling decision". The next time the server wants to run or download a file, the client presents the original three choices.

    Thursday, October 1, 2009

    Save View As in NAV 2009

    In the Classic Client of NAV, it is possible to send the forms or reports in the Navigation Pane to the shortcuts using Ctrl+Alt+S or right click on the Item and selecting the “Send to Shortcuts” option.

    imageSimilar kind of option also available in the NAV 2009 RT Client. The following exercise will show the procedure to add any form to the Navigation Pane.

    Open the Role Tailored Client and select the Sales Orders

    imageApply any filter to the Sales Order list page like the following:

    image Select the Save View As option like the following:

    image 

    In the Save View As form you can change the Name accordingly and can change the Activity Group also.

    image

    System will also for restart confirmation to effect the changes and select Yes and check Home in the Navigation Pane.

    image

    New view is added to the Home and If you open the page you can still see the filters applied previously. Cool….

    It is also possible to remove the views added to the Navigation Pane.

    Select the Customize Navigation Page option.

    image

    Select the page you want to remove and select Remove option (or) Select Restore Defaults to remove all the changes and restore to the default view.

    image

    C/AL ASSERTERROR Statements

    You use ASSERTERROR statements in test functions to test how your application behaves under failing conditions. The ASSERTERROR keyword specifies that an error is expected at run time in the statement that follows the ASSERTERROR keyword.

    If a simple or compound statement that follows the ASSERTERROR keyword results in an error, then execution successfully continues to the next statement in the test function. You can get the error text of the statement by using the GETLASTERRORTEXT Function.

    If a statement that follows the ASSERTERROR keyword does not result in an error, then the ASSERTERROR statement itself fails with the following error and the test function that is running produces a FAILURE result:

    TestAsserterrorFail: FAILURE

    An error was expected inside an ASSERTERROR statement.

    Example

    To create a test function to test the result of a failure of a CheckDate function that you have defined, you can use the following code. This example requires that you create a function called CheckDate to check whether the date is valid for the customized application and that you create the following text constant and variables.

    Text constant
    ENU value

    Text001 'The date is outside the valid date range.'

    Name
    DataType

    InvalidDate Date

    InvalidDateErrorMessage Text

    InvalidDate := 010184D;
    InvalidDateErrorMessage := Text001;
    ASSERTERROR CheckDate(InvalidDate);
    IF GETLASTERRORTEXT <> InvalidDateErrorMessage THEN
    ERROR('Unexpected error: %1', GETLASTERRORTEXT);


    Also check the output without ASSERTERROR function.

    Error List Table in NAV 2009

    In the older version of NAV, compilation of objects will only show the first compilation error.

    In the NAV 2009, if you compile multiple objects you will get all warnings and errors from all objects in a form called Error List.

    image

    image

    Error List form has Design button will be open the related object in design mode.

    These errors are stored in the new internal table called 2000000070 Error List.

    Sunday, September 20, 2009

    Search Option in NAV RoleTailored Client



    In Microsoft Dynamics NAV 2009 RT Client, it is possible to search for the pages, report and etc… using search box (Shortcut Ctrl+F3).


    Friday, September 18, 2009

    ENABLING MICROSOFT DYNAMICS NAV SERVER

    The Enable for Microsoft Dynamics NAV Server property is new in the Microsoft Dynamics NAV database. When you set this property, you are enabling the functionality of the three-tier architecture.

    This gives you access to the special properties and .NET code that are required for computers running Microsoft Dynamics NAV Server to access the database. To set the flag in the Classic client, on the File menu, click Database, and then click New (for a new database) or click Alter (for an existing database). On the Options tab, select Enable for Microsoft Dynamics NAV Server.




    Wednesday, September 16, 2009

    How to Zoom in NAV 2009 Pages?

    In the NAV Classic client forms using Tools-->Zoom (Ctrl+F8) we can see the all the fields and its values.

    In the NAV RT Client pages also it is possible to zoom the page using About the Page (Ctrl+Alt+F1) in the top right side of the page.



    NOTE: Zooming the Listpart (i.e. subform) is only possible by keeping the cursor in the subform and pressing Ctrl+Alt+F1 Keys. Clicking the About the Page button in the top right side of the page only works for the main forms.



    NOTE: In the classic client zoom option display the fields in the same orders  as the fields in the table. In the RT Client zoom option displays first primary key field values and the remaining fields in the alphabetical order and Field ID also displayed...Nice....






     


     


     

    How to place the Actions in the pages to new category (NAV 2009)

    Instead of placing the actions in the standard categories like New, Process and Report, you can also place your action to your own categories.
    The following steps will show the process with an example:

    In the object designer, design the page 30 Item Card.

    Move to the last blank line in the page.

    Go to the Actions using ViewàActions.

    Go to the properties of the Action you want to move to the new Category.

    Change the following properties:
      • Promoted to Yes
      • PromotedCategory to Category4. Category 4 to 10 are available in the NAV 2009 SP1, other than the standard categories like New, Report and Report.
      • PromotedIsBig to Yes
    Go to the Page properties and change the PromotedActionCategoriesML property value like below:
              ENU=New,Process,Report,New Category
    First three values should be same like New, Process, Report and from there you can give your own name. If you change these values standard page Categories will be changed.

    Run the page in RTC and see the result. You will find a new Category with "New Category" which has your action.

    Tuesday, September 15, 2009

    Transformging Subforms to Pages

    While transforming subforms to the pages using Transformation Tool, TIF information should include main form also. Otherwise the entire menu buttons related to subform will not be moved to pages.

    For Example: While transforming Form 46 to page, you should include 42 form also. Otherwise Subform menu's like "Line" will not be moved to the Page 46.

    Friday, September 11, 2009

    Running Pages & Reports from Run

    Running NAV 2009 Pages:

    Click Start à Run, and run this:

    DynamicsNAV:////runpage?page=90055


     

    Running NAV 2009 Reports in RTC;

    Click Start à Run, and run this:

    Dynamicsnav:////runreport?report=111    


     

    Debugging NAV 2009 Pages

    Here is the nice post from clausl on how to debug the NAV 2009 pages. Please click the below link

    Debugging in NAV 2009

    Friday, September 4, 2009

    IMPORTOBJECTS

    IMPORTOBJECTS Function

    Imports one or more objects from either a text file or an .xml file to the application.

    IMPORTOBJECTS(FileName[, Format])
    Parameters

    FileName

    Type: Text
    The path and name of the file from which you import objects.
    Format

    Type: Integer
    The type of the file from which you import objects. The valid values are 0 and 1. If you specify 0, then the file that you specify in the FileName parameter must be a text file. If you specify 1, then the file that you specify in the FileName parameter must be an .xml file. The default value is 0.
      Remarks

    This function is not supported on the RoleTailored client.

    This function achieves the same result as the Import item on the File menu in the Classic client.

    After you import an object with this function, the object is not compiled. To run the object, you must first manually compile it.

      Example

    This example imports an .xml file that contains page objects.

    IMPORTOBJECTS('C:\MyExport.txt', 1);

    EXPORTOBJECTS

    EXPORTOBJECTS Function

    Exports one or more objects to either a text file or an .xml file.

    EXPORTOBJECTS(FileName, ObjectTableRecord[, Format])
    Parameters

    FileName

    Type: Text
    The path and name of the file to which you export objects.
    ObjectTableRecord

    Type: Record
    Specifies a record in the Object table, which is table 2000000001. You can set filters on this record so that the exported objects are also filtered. For more information, see SETFILTER Function (RECORD).
    Format

    Type: Integer
    The type of the file to which you export objects. The valid values are 0 and 1. If you specify 0, then the data is exported in text format. If you specify 1, then the data is exported in XML format. The default value is 0.
      Remarks

    This function is not supported on Microsoft Dynamics NAV Server.

    This function achieves the same result as the Export item on the File menu in the Classic client.

    Exporting to an .xml file is only supported for form or page objects.

      Example

    This example filters all reports in the range 50000 to 60000 and then exports the filtered set of reports to a text file. This example requires that you create the following variable.

    VariableData typeSubtype
    MyFilterRecordObject
    MyFilter.SETFILTER(MyFilter.Type,'=Report');
    MyFilter.SETFILTER(MyFilter.ID,'%1..%2',50000,60000);
    EXPORTOBJECTS('C:\MyExport.txt', MyFilter, 0);

    InstructionalTextML Property

    Sets the Multilanguage value of the InstructionalText of the object. Use this property as a label.

    This property is mainly useful for the ConfirmationDialog type pages. Text specified in this property will be shown when the form is opened.
    InstructionalTextML ENU=The quantity on inventory is not sufficient to cover the net change in inventory. Do you still want to record the quantity?

    Reference Page # 342


    Thursday, September 3, 2009

    Page.RUNMODAL

    Like in the Standard forms in the NAV 2009, pages also have the option to run it as Page.RUNMODAL.

    CLEAR(SomePage)

    SomePage.XXX; // any user-defined function

    SomePage.SETTABLEVIEW(MyRecord);

    SomePage.SETRECORD(MyRecord);

    IF SomePage.RUNMODAL = Action::LookupOK THEN

    SomePage.GETRECORD(MyRecord)...


    If you want to use Page.RUNMODAL, we should be careful about the PageType property of the page.



    PageType property should be Worksheet to show the fields in the page in proper order like below.





    If the PageType property is not correct, then fields in the page will not be shown in the proper order.



    The page shown above is for example purpose.

    First, Previous, Next and Last Buttons

    In the NAV 2009 Classic client Card forms, it is possible to move between the records using First, Previous, Next and Last buttons.



    In the NAV 2009 RoleTailoredClient, first List page will be opened. 

    From the List page, Card page can be opened.



    The below steps will show how to add First, Previous, Next and Last buttons to the Card page

    • Open the Page 30 in design mode.
    • Go to the Action Designer using ViewàActions and add four Actions like below screenshot.

    • Change the properties of the Actions like shown in the below screenshot. 



    Image property should be changed according to the functionality of the button.

    Button        Image Property

    First            PreviousSet

    Previous    PreviousRecord

    Next            NextRecord

    Last            NextSet


    • Add the code for the new buttons like below screen.

    • Open the Item Card in the RoleTailoredClient.

    First: First button will go to the first record in the table.
    Previous: Previous button will go to the previous record.
    Next: Next button will go to the next record.
    Last: Last button will go to the last record in the table.

    Friday, August 21, 2009

    COPY function in NAV 2009

    COPY function in NAV 2009 has new parameter ShareTable.

    Record.COPY(FromRecord [, ShareTable])

    ShareTable specifies whether the function creates a copy of the record or creates a reference to a temporary record.

    If FromRecord and Record are both temporary and ShareTable is true, then the COPY function does not create a new copy of the record. Instead, the COPY function causes Record to reference the same table as FromRecord.

    The default value is false. If you specify false, all records are copied to Record from FromRecord.

    If ShareTable is true, both Record and FromRecord must be temporary; otherwise an error will occur.


    Tuesday, August 18, 2009

    ListPart (Sub form) controls from the Page Header

    In NAV 2009, it is not possible to get the selected subform record from the page header.

    CurrPage.SubFormControl.FORM.GETRECORD(myRecord)

    This will always give the first record in the subform.

    Page Header functionality should be moved to the subform in the pages.

    Monday, August 17, 2009

    How the three-tier architecture works in NAV 2009

    The Role Tailored architecture is a three-tiered architecture which contains the following levels:

    • Presentation level (Role Tailored client)
    • Business logic and communication level (Microsoft Dynamics NAV Server)
    • Data level (SQL Server database)

    The different tiers can be installed on different computers. You can have multiple instances of any of the components (though usually not on the same computer): multiple clients, multiple servers, and multiple database servers.


    Three terms keep coming up when setting up Client, NAV Server and SQL Server on different machines.

    • Delegation / Impersonation
    • Kerberos
    • SPN

    Delegation / Impersonation:

    When the Role Tailored client, Microsoft Dynamics NAV Server, and SQL Server are all installed on separate computers, the client interacts with the database through an intermediate computer, which is running Microsoft Dynamics NAV Server. The server is performing actions on the client's behalf. This process is known as impersonation.

    Delegation is when a front-end service forwards a client request to a back-end service so that the back-end service can also impersonate the client. Impersonation is typically used to check whether a client is authorized to perform a particular action, while delegation is a way of flowing impersonation capabilities, along with the client's identity, to a back-end service.

    All logins here are Windows logins. Impersonation would be a nice way for hackers to gain access, so therefore Windows requires a domain administrator specifically allows the NAV-server to impersonate users on the SQL Server.

    This is only when the NAV-Server and SQL Server runs on two different machines. When they run on the same machine, then Windows has already authenticated the end-user when they connected to the NAV-Server. So when the NAV-Server logs on to the SQL Server (on the same machine), Windows has already knows the end-user. In this case there is no need to set up delegation, and hence no need to worry about Kerberos and SPNs.

    But with multiple machines, delegation is needed. Delegation requires Kerberos.

    Kerberos:

    Connections between two users happen all the time. What Kerberos adds to such a connection is a certificate (ticket) which ensures that each of the two users can trust the identity of the other user. This is actually a kind of countermeasure against a user (hacker) impersonating another user.

    Windows requires that before it allows delegation, that the connection being delegated is a Kerberos connection. In this way, Windows has assurance that the user credentials being forwarded are valid. So when RTC connects to the NAV-server, it has to do this with a Kerberos connection.

    A Kerberos connection is between two Windows user accounts. A user doesn't know (shouldn't know) what user account the NAV-server runs under.

    This is where SPNs come in. So, finally we get to the SPNs:

    SPN:

    SPN (Service Principal Name) is a simple table that maps a service to a user account. Think of it as a table with two fields: Service-name, and Windows User name.

    When the NAV client wants to start a Kerberos connection to the NAV-server, it will connect to for example DynamicsNAV\Nav-Server:7046. Kerberos requiring a user name will then look this up in the SPN table and find the user name there. So, only if an SPN has been created for the account that runs the NAV-service, will Windows be able to then start a Kerberos connection. And the user will never need to know which that account the NAV-server is running under.

    When setting up SPNs, make sure that:

    1. The account that runs the NAV-service has an SPN that contains what the NAV client will connect to (Server name in the right format)
    2. Quite a common problem: Make sure that there are no duplicate SPNs. If you change the NAV-service to run under a different account you must set up SPNs for the new account. Then make sure to also remove the SPNs for the old account.

    So in short: Delegation / Impersonation require Kerberos. Kerberos connection to a service running an unknown account requires SPN. And, visa versa, if SPNs have not been set up correctly, then Kerberos won't work so then Delegation won't work either.

    Creating Service Principal Names

    The first step in setting up delegation is to create any necessary service principal names (SPNs). To make delegation more secure, Active Directory uses Kerberos to authenticate services. An SPN is the name by which a client uniquely identifies an instance of a service, using the account under which the service runs. To make delegation work, you must explicitly create an SPN when either the Microsoft Dynamics NAV Server service or the SQL Server service is running under a dedicated domain user account. We recommend using this configuration for the highest security. If both services are running under dedicated domain user accounts or under the same dedicated domain user account, you must create two SPNs: one for each service.

    Note: You have the setspn command-line tool installed on your server. In Windows Server 2008, the setspn tool is included if you have installed the Active Directory Domain Services server role. In Windows Server 2003, you must download the Windows Server 2003 Service Pack 2 32-bit Support Tools to get the setspn tool.

    To create a service principal name

    1. Open an elevated command prompt. Click Start, and in the search window, type Command Prompt.
    2. Right-click Command Prompt, and then click Run as administrator.
    3. At the command prompt, create the SPN. The syntax is:

      setspn –A InstanceName/FullyQualifiedDomainNameOfServer:Port Domain \User


    Delegating Access to the SQL Server Service

    Configuring delegation means explicitly configuring the Microsoft Dynamics NAV Server service to delegate its access to the database server on behalf of the RoleTailored client. To make the access more secure, you specify delegation to a specific service on a specific server.

    To delegate access to the SQL Server service

    1. On any server computer in the domain, click Start, and then click Run.
    2. In the Open field, type dsa.msc.

      This opens the Active Directory Users and Computers utility.

    3. For you to configure delegation, the functional level for the domain must be Windows Server 2003 or higher. To verify the domain functional level, right-click the node for the domain where you have installed Microsoft Dynamics NAV, and then click Raise Domain Functional Level. If the level is not at least Windows Server 2003, raise it to that level.
    4. Right-click the node for the domain where you have installed Microsoft Dynamics NAV, and then click Find.
    5. In the Find Users, Contacts, and Group dialog box, type the name of the domain user in the Name field, and then press ENTER.
    6. In the Search results area, right-click the domain user, and then click Properties.
    7. On the Delegation tab, click Trust this user for delegation to specified services only, and then click Use Kerberos only.
    8. Click Add to open the Add Services dialog box.
    9. In the Add Services window, click Users or Computers, and then type the name of the domain user.
    10. In the list of services for the domain user, click MSSQLSvc, which is the name of the SQL Server service.
    11. Click OK to exit the Add Services dialog box.

    Click OK to close all open dialog boxes.

    Friday, August 14, 2009

    Physical Location of Automation Server

    The virtual table 2000000046 "Automation Server" shows you the physical location of the automation servers (.dll file).

    Service Order-->Post-->Ship & Consume

    In the Service Management-->Service Order-->Post have 4 options.

    1) Ship

    2) Invoice

    3) Ship & Invoice

    4) Ship & Consume

    Ship: The program posts shipment of the items.

    Invoice: The program invoices items that have already been shipped.

    Ship and Invoice: The program invoices and ships the items.

    Ship and Consume: The program posts shipment and consumption on the order and creates a new service shipment document.

    If you fill the "Qty. to Ship" in the Service Lines and post the Service Order as Ship, program will created one positive service ledger entry with 'Entry Type' Usage and one negative Item Ledger Entry with 'Entry Type' as Sale. That means to complete the service order, system used the component that has posted as negative line in the ILE and that line has 'Entry Type' as Sale which means it has to be invoice to the customer.

    If you fill the "Qty. to Ship", "Qty. to Consume" in the Service Lines and post the Service Order as Ship and Consume, program will created two service ledger entries (First line with 'Entry Type' Usage and Positive Qty. Second line with 'Entry Type’ Consume and Negative Qty.) and one negative Item Ledger Entry with 'Entry Type' as Negative Adjustment. That means to complete the service order, system used the component that has posted as negative line in the ILE and that line has 'Entry Type' as 'Negative Adjustment' which means it will not invoice to the customer and used for the internal purpose and registered in the Service Ledger Entries as 'Entry Type' consume (i.e. Second Line).

    Wednesday, August 12, 2009

    Style property in NAV 2009 Pages

    Style Property

    This property sets a value that determines how a field on a page is formatted. This applies to page controls that have text
    Property Value:
    Value Format in edit mode Format in view mode
    Strong Bold Bold
    Attention Red Default
    Favorable Bold + Green Bold
    Unfavorable Bold + Italics + Red Bold + Italics

    Remarks:
    If the StyleExpr Property evaluates to true, then the value of the field is formatted as specified by the Style property.
    By default, this property is not set.
    This property is not supported if the data type of the SourceExpr of the field is one of the following:
    Code
    Boolean
    Binary
    BLOB
    GUID
    RecordID
    The Style formatting that is applied to a page in Edit mode, is different than the formatting that is applied in View mode.



    StyleExpr Property

    This property sets whether the Style Property is applied.
    Applies To:
    Page controls that have text

    Remarks:
    If the StyleExpr property evaluates to true, then the value of the field is formatted as specified by the Style Property. You can use either true, false, or a variable that evaluates to true or false. The default value is false.

    Tuesday, April 28, 2009

    BLOB Fields with NAV & SQL

    BLOB = [Binary] Large OBject

    Well, several tables in MS Dynamics NAV use such "BLOB"-fields to store binary data like pictures. This could be quite convenient for the application, for example for creating Item catalogues displaying such pictures, etc. but from a NAV/SQL performance perspective BLOB could also be a real pain – I'd like to explain why, and what I recommend to do to avoid the problems …

     

    The basics

    A NAV "BLOB" data-type is actually the "image" data-type in SQL Server (SQL also features other LOB types as "text" or "ntext" but they aren't relevant for NAV).

    [ Out of the box, NAV flags a "BLOB" field with the property "Compressed" = TRUE. If this is enabled, NAV uses a compression algorithm to save the data more compact. Have in mind that SQL Server does not recognize this "compression", so if you're using BLOB to transfer binary data from NAV to other SQL systems – which could be very smart, but that's a different story! – you should make sure to set "Compressed" to FALSE. But anyway … ]

    By default, every table in NAV has a "Clustered Index" – and even though this is not a necessity for SQL Server, this is a very good idea! The general rule is: "Every table has a Clustered Index". Period.

    An Index is an internal balanced-tree (B-tree) like structure, helping to retrieve records quickly. Every SQL Server table could have multiple indexes. The b-trees exist of "index pages" ("root nodes", "index nodes" and "leaf nodes") which store the indexes values. The difference between a "Clustered Index" (CI) and a "Non-Clustered Index" (NCI) is, that the "leaf node" level of a CI represents the table itself. Means: while all other index nodes only contain the indexed valued – e.g. "No." of table "Item" – the "leaf nodes" include all the other fields – e.g. "Description", "Item Category", etc.. Further, the CI defines how the data is physically stored and sorted within the table.

    Hence, each table could have one CI and multiple NCI. By default, the CI is generated on basis of the "Primary Key" in NAV (which is mostly a good idea), all other "Keys" – if enabled and maintained - will be created as NCI.

    As mentioned, the CI contains all fields of a table. Really all? No, not the "BLOB" fields. Fields of type "image" in SQL can store up to 2 Gigabytes of data (which requires about 256.000 "pages" x 8 KB). So if that LOB data would be included into the CI, this could dramatically increase the index size and severely degrade its performance is all aspects! That's why LOB fields are stored differently within the database, not as part of the "leaf nodes" of the CI (of course there are internal references).

     

    The problem …

    … in NAV is, that the "BLOB" fields are always read with any query on the related record. In SQL Profiler one could see those queries, for example when reading from the "Item" table:

    SELECT *, DATALENGTH("Picture") FROM "Navision"."dbo"."CRONUS$Item" WHERE …

    The "SELECT *" queries all fields from a table, thus, also the BLOB field(s). With "DATALENGTH" additionally the size of that "BLOB" is determined (C/SIDE uses this info to deal with the field). As the BLOB field is not part of the CI "leaf nodes", SQL Server has to perform additional internal operations to gather the LOB data. Due to the specific storage of that "image" fields, the data could be "spread" widely within the database (it cannot be granted that a LOB field is stored within one continuous array of pages), thus SQL Server has to perform a lot more "page reads" then (shown in SQL Profiler as a unusual high number of "Reads", e.g. >= 1000).

    And as always: a high number of "Reads" means a high consumption of cache space and CPU and may also result in physical I/O. Too many "Reads" waste precious system resources and degrade performance!

    So much about the technical side. But the question should also be: Why always reading the BLOB fields anyway?

    In NAV there are a lot of important and heavily used tables which are "affected" by BLOB fields, as "Item", "Customer" or "Vendor" (and maybe "Contact" and others). For example, the "Item" table is permanently queried when looking up items, creating an order, posting a shipment, posting an invoice, posting an item journal etc.. Within all the processes and transactions NAV fires a SELECT * on the table, reading the "Picture", too.

    But what is the need for a "Picture" when posting an order or processing physical inventory? There is none! Nope. Zip. Nada.Pointless.

    But using "BLOB" fields in such tables could be a real problem regarding performance.

     

    The solution

    Of course, the solution cannot be to abstain from using BLOB fields. Again: using BLOBs could be very smart, not just for storing pictures, but maybe also for XML data or documents.

    To avoid performance issues it is just necessary, to keep BLOBs away from business processes, and this could be done rather simple: Storing BLOBs in separate tables!

    For example, when it is about the "Picture" in table "Item", just create a new NAV table e.g. called "Item Picture", existing of just two fields: "Item No." (Code20) and "Picture" (BLOB). Once this new table exists, the "Picture" could be transferred quite simple with some NAV programming – or, even faster, with some TSQL:

    USE [Navision] -- change db name here

    GO

    INSERT INTO [dbo].[CRONUS$Item Picture] ([Item No_], [Picture])

    SELECT [No_], [Picture] FROM [dbo].[CRONUS$Item] WHERE DATALENGTH([Picture]) <> 0

    GO

    If the data has been transferred, the "Picture" could be erased from the old "Item" table – either by some C/AL code, or this TSQL:

    USE [Navision] -- change db name here

    GO

    UPDATE [dbo].[CRONUS$Item] SET [Picture] = NULL WHERE DATALENGTH([Picture]) <> 0

    GO

    (use the TSQL at own risk – no warranties or anything)

    Now the "Item" table is cleaned up – de-BLOBbed so to speak - so all kinds of business processes should not encounter trouble related to BLOB Reads. Then of course it is necessary to change the application, now looking up the "Picture" from the new table "Item Picture", but actually that's a piece of cake (e.g. ItemPicture.GET(Item."No.");). Have in mind that it isNOT necessary to disable the BLOB field in the table – just not using it is all it takes.

    I highly recommend to implement this wherever BLOB is used in "transactional tables"! It's just a minor effort, but could have remarkable impact on NAV/SQL performance. Further, to keep the BLOB's performance OK it is necessary to periodically maintain such tables with an ALTER INDEX REORGANIZE WITH LOB_COMPACTION (SQL 2005/2008) or DBCC INDEX_DEFRAG (SQL 2000) – see "Books Online" about details.

    Unit Cost used in Negative Adjustment

    If you are using Average Cost, Negative Adjustment will take the average cost on the day the negative adjustment is valued based on posting date and valuation date).

    For example, when you do a Negative Adjustment in Item Journal, Navision will default the unit cost with the Unit Cost from Item Card. It doesn't matter whether you accept the default cost suggested by Navision or change the unit cost to other cost, because the Adjust Cost - Item Entries batch job or the automatic cost adjustment function will adjust the unit cost back to the average cost based on the valuation date. Therefore, no matter what cost you put in the Item Journal during Negative Adjustment will not affect the cost used in the negative adjustment.

    If you are using Standard Cost, the unit cost in Item Journal will be defaulted to Unit Cost in Item Card, which is the current standard cost. Later, when the adjust cost function is executed, the unit cost posted by the negative adjustment will be adjusted to the unit cost of the positive entry which the negative adjustment is applied to. Let's consider the following example,

    Jan 01, 2008 - Item A is created and standard cost is set to $10.
    Jan 02, 2008 - Purrchase 10 units of Item A with $10.
    Feb 01, 2008 - Change standard cost of Item A to $12.
    Feb 05, 2008 - Purchase 10 units of Item A with $12.
    Feb 07, 2008 - Negative 2 units of Item A.

    So, on Feb 07 2008, when 2 units of Item A is entered into the Item Journal, Navision will default the unit cost to $12. Accept the cost and post the Negative Adjustment. The Negative Adjustment will be posted with $12. You can verify this by checking the Item Ledger Entries and Value Entries. Now, run the Adjust Cost - Item Entries batch job. After the Adjust Cost batch job completed successfully, check the Item Ledger Entries and Value Entries again. You should be able to see an adjustment entry with -$2 has been added to the Value Entries table to adjust the unit cost to $10.

    The Negative Adjustment will take $10 instead of $12 as unit cost because the negative adjustment entry has been applied to the purchase entry created on Jan 02 2008, which is $10.

    Top 30 'Top X List' Websites

    What really get on my nerves, even more so than Joe Pasquale stupid song, are the often too popular sites that keep on producing their "Top X Lists". What happened to good old original Content is King?. So if you can't beat them, join them. Here is my Top 30 list of websites that quite often product Top X lists of crap...

    1. Smashing Magazine
    2. Oddee
    3. Web Designers Pot
    4. Six Revisions
    5. 1st Web Designer
    6. Noupe
    7. Mashable
    8. Specky Boy
    9. Naldz Graphics
    10. Net Tuts+
    11. Smash!ng Apps
    12. Outlaw Design Blog
    13. [Re]encoded
    14. Hong Kiat
    15. PXLshots.com Blog
    16. Design Reviver
    17. WebUpon
    18. Instant Shift
    19. Web Urbanist
    20. Photoshop Roadmap Blog
    21. Digital Labz
    22. Freelance Folder
    23. abduzeedo
    24. Web Design Ledger
    25. Toxel
    26. Just Creative Design
    27. Spyre Studios
    28. Listphobia
    29. Digital Photography School
    30. Life Hacker