MIcrosoft DYnamics NAV (MIDYNAV)

ROUND Function

2012-01-20T11:21:00.001-08:00

ROUND function in NAV, rounds the value of a numeric variable.

For Example: ROUND(1234.56789, 0.001, ‘>’) returns 1,234.568

NewNumber := ROUND(Number [, Precision] [, Direction])

You can give your own precision in this function.

For Example: ROUND(1234.56789,0.50,’=’) returns 1234.50 i.e. nearest 50

ROUND(1234.4956789,0.25,'<') returns 1,234.25

Series of Interview Video’s

2011-03-03T08:35:00.001-08:00

Software Advise, which is a research company on mrp software published a series of videos with the interview from Microsoft’s Director of ERP Marketing, Guy Weismantel. Click this link to view the videos.

How does Dynamics NAV validates the Credit Card number for Online Payments

2011-02-16T09:37:00.000-08:00

Dynamics NAV 2009 R2 has a new feature called “Online Services” and using this functionality you can accept and process credit card payments in Microsoft Dynamics NAV.This online credit card payment feature automates authorizing credit card amounts at the time of the order and processing the actual charge when the order is shipped and invoiced.

In order to use this online payment services, you need to setup payment services, customer payment methods and credit cards for the customers. While setting up the Number field in the Credit Card page (Customer Card—>Customers—>Credit Cards), Dynamics NAV automatically validates value for the correct credit card number and gives the error message for invalid credit card numbers.

This validation is not a real credit card number validation from the service providers but uses the algorithm called “Modulus10”. This algorithm was designed to protect against accidental errors, not malicious attacks. Most credit cards and many government identification numbers use the algorithm as a simple method of distinguishing valid numbers from collections of random digits. You can find the related C/AL code in the codeunit 827 “DO Payment Card Validation” function “IsModulus10”.

Locking and Unlocking Objects– Auto-Lock on Design

2010-12-24T00:57:00.000-08:00

In Microsoft Dynamics NAV 2009 R2, you can lock an object in Object Designer using Lock option. Along with Lock we have other options like Unlock and Force Unlock.

Along with these features you can automatically lock an object while opening the object in design mode.

This option is available in the Tools->Options->Auto-Lock on Design.

Even though you can lock objects, it is still possible for developers to have concurrency issues, as shown in the following examples.

A developer opens an object in the designer but does not lock it. The developer makes several changes to the object and saves the changes periodically. At the same time, a second developer locks the object, and the first developer cannot save design changes to the object. The first developer gets an error message that the object is locked by the second developer.
A developer locks an object. A second developer opens the locked object in read-only mode, and then the first developer unlocks the object. The second developer still cannot save design changes to the object even though the object is now unlocked because it is open in read-only mode.

System Indicator–Dynamics NAV 2009 R2

2010-12-24T00:38:00.001-08:00

Till now all NAV users including developers or end users felt little difficult to identify the different instances of NAV. Now we have a solution called system indicator in NAV 2009 R2 release to differentiate different instances like production environment and test environment.

Once you setup the system indicator in the company information, you can see the indicator text in the top right side of each page in the Role Tailored Client…Cool…

NOTE: Refer GetSystemIndicator function in the Codeunit 1 ApplicationManagement.

What’s New in Microsoft Dynamics NAV 2009 R2

2010-12-16T02:23:00.000-08:00

MSDN is updated with the latest help related to NAV 2009 R2 Application updates and Developer updates.

Please click the link and get updated with the latest stuff.

Book Review–Microsoft Dynamics NAV 2009 Programming Cookbook

2010-12-13T02:31:00.000-08:00

Intended Audience:

This book seems to more appropriate for people who already have experience with Dynamics NAV and may want to review or have a quick overview of the basics.

I believe that a person with some programming background can also use this as a quick jump start to be able to be able to work on Dynamics NAV.

Content/ Information:

The content of the book is appropriate as per the title. The author has given appropriate technical information to convince the reader. By appropriate information I mean to say that the only specific (more frequently used) information is provided with reference to standard Dynamics NAV coding (wherever necessary).

The sections “How it works” and “There is more”, are really helpful as they are short and to the point.

The author’s style of writing is more informal, without compromising the quality. I really appreciate this style, because I believe the intended audience of this book would rather like to quickly review or update themselves with the content rather than spend time on formal and lengthy notations.

Overall the style completely suits the intended audience.

How did the book affect me?

I really liked the book from Chapter 6 onwards. Because this book has touched the areas in Dynamics NAV which a developer would like to precisely go through. The topics covered under Chapter 6 to Chapter 12 are really interactive and at the same time some parts of it can be used like tips and tricks.

Usually these topics are not covered together and have to be search for and more over the explanations are too lengthy. I believe that these topics are very useful and can be quickly reviewed whenever required with less complexity.

How well the book has achieved its goal?

The book achieves its goal, to be a “cookbook” ! It is precise and to the point. So no complaints, but I would like to place some of my concerns especially in earlier chapters:

I would have liked if more functions and data types were covered in Chapter 1, as many a times it mentions us to refer to C/SIDE reference guide for other/ similar kind of functions/data types.
The book gives appropriate examples, but forgets to mention the syntax. Nothing major but I think it would help people (especially new to Dynamics NAV) more.

Overall the books do achieves its goal. Here is a quick link to this book.

Book Review - Microsoft Dynamics NAV Administration

2010-12-08T21:38:00.001-08:00

This is a helpful book for the beginners to get good idea on the following concepts:
                Installing Dynamics NAV and its components.
                Securing Dynamics NAV applications.
                Backup and Restore options.
                Performance Tuning.
                Reporting Dynamics NAV.
But the major chapters explained here are more or less available in different formats like MSDN and other Microsoft standard materials.
It would be good if the book covers more on Application Virtualization.
Overall I see this book as a very good guide to learn install, configure, deploy and administer Dynamics NAV.

Why manually renaming a record takes little time?

2010-11-22T22:52:00.001-08:00

In Dynamics NAV, if you try to modify the primary key field values it will take little time to change the value and also shows the window processing different tables.

When you try to change the primary key field value, internally it will check for the fields in all the tables which has the table relation to the primary key. That means system will check for the foreign keys related to the primary key and change the field value to the new value.

Let’s say you have tables like sales header & sales line and if you want to reflect the primary key changes in the sales header to sales line, provide the table relations in the sales line.

TEMPORARYPATH and APPLICATIONPATH Functions

2010-10-20T23:07:00.001-07:00

TEMPORARYPATH: This function returns the path to the directory where the temporary file for Microsoft Dynamics NAV is stored.

This function returns the following path in the classic client:

This function returns the following path In the RT client:

APPLICATIONPATH: This function returns the path to the directory where the executable file for Microsoft Dynamics NAV is installed.

This function return the following path in the classic client:

This function return the following path in the RT client:

CurrFieldNo

2010-09-21T08:31:00.000-07:00

We all know that CurrFieldNo contains the field number of the current field in the current form.

Today I noticed that even though it contains the field number, by the time of executing table trigger (like OnModify or OnDelete), it contains zero (not the last modified field number).

Book Review - Microsoft Dynamics NAV 2009 Application Design

2010-07-26T10:02:00.000-07:00

First of all, congrats and thanks to Mark Brummel for writing a fantastic book and sharing his knowledge in very straight forward manner. When I got a copy of this book, I was very eager to read this and after started reading, my interest also increased from chapter to chapter. I learned new points and recalled my knowledge while reading this book. Instead of explaining basics of NAV, he explained the core points which are important for every NAV professional.

Especially I liked the following points in his book:

‘Look, Learn and Love’ principle is good.
He not only explained the functionality and also the history of the functionality. For Example: Manufacturing.
How different functionalities are used in various vertical industries.
Application Life cycle chapter is useful.

In my opinion, it would be good if the book also covers the following points:

Section for keys and indexes.
Application Test Toolset.
More details about the MRP and MPS.

Mistakes:

In the Page 33, it is given as “Flow filters can have seven types” but it is flow fields.

Overall this book is suggestible for every NAV professional.
You can order this book from packtpub.

IndentationColumnName and IndentationControls properties

2010-07-23T11:45:00.000-07:00

IndentationColumnName and IndentationControls are the page properties in the NAV 2009. These properties are used to indent the controls or columns.

Transformation Tool will automatically convert the following type of code in the form and adjust the above two properties in the page to show the data as indented.

For Example: Form 18, CurrForm.Name.UPDATEINDENT := Indentation * 220;

Now the major problem in the pages is, you cannot use these properties for the fields that are editable and has a table relation.

For example, you cannot indent Item No. or Location Code in the Item Journal page because these fields are editable and has a relation with another table. If you try to indent these fields, while entering the data into these fields, field value is automatically updated with the first value.

If you press “1” in the Item No. field, it will be automatically filled with the first value starting with 1 like “1000”.

Microsoft Dynamics® Regional Partner Awards - 2010

2010-07-15T07:25:00.001-07:00

Microsoft announced the Microsoft Dynamics® Regional Partner Awards – 2010.

Here is the link to the press release.

Microsoft Dynamics Inner Circle and President Club Members

2010-07-10T11:24:00.001-07:00

Microsoft announced the Microsoft Dynamics Inner Circle and President Club Members for the FY10.

Here is the link to the list of winners and Congrats to them.

Dynamics NAV Testing Framework - Create Handler Functions

2010-06-25T04:50:00.001-07:00

Microsoft Dynamics NAV 2009 SP1 includes the following features to help you test your application:

Test codeunits
Test runner codeunits
UI handlers
ASSERTERROR statement

Application Test Toolset provided by Microsoft includes the Test Runner and sample Test Codeunits.

To create automated tests, you must write code to handle all UI interactions so that the tests do not require user interaction when running. To do this, you create the following special handler functions:

MessageHandler: Handles MESSAGE statements.
ConfirmHandler: Handles CONFIRM statements.
StrMenuHandler: Handles STRMENU statements.
FormHandler: Handles specific forms or pages that are not run modally.
ModalFormHandler: Handles specific forms or pages that are run modally.
ReportHandler: Handles specific reports.

In the following post, I included sample UI Handler Functions which can be used while creating test codeunits.

Signature: MessageHandler <Function name>(<Msg> : Text[1024])

Sample Code:

    [MessageHandler]
    PROCEDURE MessageHandler@1100499002(Msg@1100499000 : Text[150]);
    VAR
      Text001@1100499001 : TextConst 'ENU=Sales Order posted sucessfully.';
    BEGIN
      IF Msg <> Text001 THEN
        ERROR('Unknown Message');
    END;

Signature: ConfirmHandler <Function name>(<Question> : Text[1024]; VAR <Reply> : Boolean)

Sample Code:

    [ConfirmHandler]
    PROCEDURE ConfirmDialogYes@1102601013(Question@1102601000 : Text[1024];VAR Reply@1102601001 : Boolean);
    VAR
      Text001@1100499001 : TextConst 'ENU=Do you want to post the Sales Order?';
    BEGIN
      IF Question <> Text001 THEN
        ERROR('Unknown Confirm Text; %1',Question);
      Reply := TRUE;
    END;

Signature: StrMenuHandler <Function name>(<Options : Test[1024]; VAR <Choice> : Integer; <Instruction> : Text[1024])

Sample Code:

    [StrMenuHandler]
    PROCEDURE StrMenuHandler@1100499000(Options@1100499000 : Text[100];VAR Choice@1100499001 : Integer;Instruction@1100499002 : Text[100]);
    VAR
      Text000@1100499003 : TextConst 'ENU=&Ship,&Invoice,Ship &and Invoice';
    BEGIN
      IF Options = Text000 THEN
        Choice := 1;
    END;

Signature: FormHandler <Function name>(VAR <form name> : Form <form id>)

Sample Code:

    [FormHandler]
    PROCEDURE FormHandler@1100499001(VAR FormName@1100499000 : Form 21);
    BEGIN
      FormName.ActivateFields
    END;

Signature: ModalFormHandler <Function name>(VAR <form name> : Form <form id>; VAR <Response> : Action)

Sample Code:

    [ModalFormHandler]
    PROCEDURE ModalFormHandler@1100499002(VAR FormName@1100499000 : Form 342;VAR ReplyAction@1100499001 : Action);
    BEGIN
      ReplyAction := ACTION::LookupOK;
    END;

NOTE: UI Handler functions should be specified in the main test function as below.

Shortcuts in NAV 2009 (Special for List Page)

2010-06-02T00:16:00.000-07:00

In NAV 2009 SP1, we all know that Ctrl+Shift+V is to open the page in View Mode, Ctrl+Shift+E is to open the page in Edit Mode.

Along with the above two shortcuts, we also have Ctrl+Shift+L to open the list page in View Mode, Ctrl+Shift+K is to open the list page in Edit Mode.

But the second shortcuts (Ctrl+Shift+L, Ctrl+Shift+K) won’t work for all list pages. My learning today is, second shortcuts are only applied to the list pages which does not have a card page attached to it.

For Example: Payment Terms page which has no card page linked will show Ctrl+Shift+L and Ctrl+Shift+K.

For Example: Item List page which has card page (30) linked will show Ctrl+Shift+V and Ctrl+Shift+E.

Dynamics NAV Job Graphs

2010-06-01T21:35:00.000-07:00

Job Graphs website provided graphical analytical interpretation of different aspect of Dynamics NAV…

Refer the link to find the details…

How to insert records into NAV from Visual Studio

2010-05-24T23:00:00.000-07:00

Below is a simple console application to create a customer record from the visual studio.

namespace ConsoleApplication1
{
    using Customer;
    class Program
    {
        static void Main(string[] args)
        {
            Customer_Service service = new Customer_Service();
            service.UseDefaultCredentials = true;
            Customer.Customer customer = new Customer.Customer() { No = "123491", Blocked = Blocked.Ship};
            service.Create(ref customer);
        }
    }
}

This will insert customer record but Blocked field does not contains the correct value. This can be achieved by changing the line as below:

Customer.Customer customer = new Customer.Customer() { No = "123491", Blocked = Blocked.Ship, BlockedSpecified = true};

This is mostly required for all fields except the string type because I think web services request/responses are in xml and type conversions are required internally from string to other data type.

HideValue Property in NAV 2009 Pages

2010-05-20T09:23:00.000-07:00

May be many of you are aware of the new field property “HideValue” for the Pages.

If you set this property to TRUE, value in this field is not displayed in the Page. In the following scenario, I will explain how to automatically set this property to TRUE using the Transformation Tool, instead of changing the property value manually.

Let’s go to the customer card and go to OnFormat Trigger of the Name field. Adding one line of code will do the same functionality as HideValue property.

Transform the form to page using the Transformation Tool and check the output in Role Tailored Client.

Form Output:

Page Output:

Microsoft Unveils New Functionality for Microsoft Dynamics NAV 2009

2010-05-19T22:23:00.001-07:00

Microsoft unveils the new functionalities for Microsoft Dynamics NAV 2009. You can read the press release.

PRAGUE — May 19, 2010 — As part of Microsoft Corp.’s commitment to providing leading enterprise resource planning (ERP) solutions to midrange organizations worldwide, the company today unveiled Microsoft Dynamics NAV 2009 R2 at Directions EMEA 2010. Because of the investment in architecture for Microsoft Dynamics NAV 2009, customers are able to take advantage of the benefits of the software-plus-services vision through the R2 release more quickly than scheduled, including built-in integration with Microsoft Dynamics CRM and online Payment Service. Microsoft Dynamics NAV 2009 customers will benefit from the following:

•Microsoft Dynamics CRM integration. Today’s fast-paced business environment demands instant access to customers and vendors. Microsoft is providing built-in integration with Microsoft Dynamics CRM. Benefits of customer relationship management (CRM) integration include eliminating redundant data entries, keeping information up to date in both ERP and CRM solutions, and providing salespeople with the ability to quickly access detailed business information about contracts, pricing and product availability. Customers can choose between integration to on-premises Microsoft Dynamics CRM or Microsoft Dynamics CRM Online with the option to migrate to either option over time.

•Online Payment Service for Microsoft Dynamics NAV. This enables customers to process payment transactions from the Microsoft Dynamics NAV interface across multiple channels, including e-commerce, point of sale and call center transactions. The Payment Service works with leading payment processing services and all major credit cards.

•RoleTailored interface access for remote or roaming users. Microsoft Dynamics NAV 2009 R2 supports direct access from the Microsoft Dynamics RoleTailored experience over the Internet. This allows for remote or roaming users to take advantage of the richness and Microsoft Office integration of the RoleTailored interface and the many integration features connected to local resources, such as the operating system and Microsoft Office. This reduces the complexity and overhead compared with using other applications such as Citrix Systems and Terminal Services, especially for hosting partners offering cloud-based Microsoft Dynamics NAV deployments.

•Microsoft Application Virtualization support. The Microsoft Dynamics NAV 2009 R2 RoleTailored interface can be deployed using Microsoft Application Virtualization (App-V) technology, which is relevant for both on-premise and hosted solutions. This provides a better experience for the end user as all integration with local applications is done on the desktop. It also cuts IT costs by centrally managing Microsoft Dynamics NAV client installations with automatic deployment to the desktop after an update.

•Windows 7 user experience improvements. Through Microsoft Dynamics NAV 2009 R2,jumplists can be used to open recently accessed customers and vendors for increased business productivity and efficiency, and the icon overlay functionality provides information on system events and status streamlined with the Windows 7 user experience.

•Treemap visualization. Microsoft Dynamics NAV 2009 R2 showcases the possibilities around business data visualizations abilities in Microsoft Dynamics NAV through rich ad hoc data visualization. It provides treemap analysis that enables the comparison of any two values, for example sales and profits, which increases business productivity and business insight. The innovative client extensibility framework used allows partners to extend the user experience of Microsoft Dynamics NAV to integrate visualizations into any end-user scenario.

“We’re delivering this release in response to customer and partner feedback,” said Crispin Read, general manager of Microsoft Dynamics ERP at Microsoft. “Microsoft is committed to continuously bringing forth innovation in our ERP solutions, including the integration with cloud services. These latest enhancements to Microsoft Dynamics NAV 2009 are an important step in delivering solutions that drive real business productivity.”

“The new features in Microsoft Dynamics NAV 2009 R2 improve our business opportunities and benefit our customers. The fact that Microsoft is able to deliver faster than planned reaffirms our decision to build our solutions on Microsoft Dynamics NAV,” said Christian Sega, managing director at agiles Informationssysteme GmbH, an industry solution provider and consulting company specializing in horizontal workflow solutions built on Microsoft Dynamics NAV.

Microsoft Dynamics NAV 2009 R2 will be available in the fourth quarter of 2010.

Web Reference in Visual Studio

2010-05-11T05:19:00.000-07:00

As specified in one of the help scenario, I created a codeunit with two functions in it and published that to the web services along with the customer card and named both as ‘PageWithCapitalization’.

When I try to create a web reference in the Visual Studio, as the two web services I published has same Service Name, the Page web reference also has the methods of the codeunit.

sp_$ndo$loginproc Login Stored Procedure on the SQL Server Option

2010-05-10T00:02:00.001-07:00

A login stored procedure is a stored procedure that you can use to perform predefined functions after a user logs on to Microsoft Dynamics NAV with Microsoft SQL Server. A typical function would be to generate a message informing the user that the database is currently in single-user mode so that an administrator can perform database maintenance tasks and is therefore inaccessible.

The login stored procedure is run immediately after the user has logged on to SQL Server and opened a database and before Microsoft Dynamics NAV carries out any tasks including executing any C/AL triggers. The user must have successfully logged on to the server and have access to the database before the stored procedure is run.

Creating the Stored Procedure

The stored procedure is created in the database and has a predefined name and a list of parameters.

The stored procedure is called [sp_$ndo$loginproc] and has the following characteristics:

It takes two VARCHAR parameters: the name of the application and the C/SIDE version number. These parameters must be declared as part of the stored procedure but do not have to be used.
It can perform transactions. Microsoft Dynamics NAV uses a COMMIT to flush any outstanding transactions after the stored procedure has finished executing.
The RAISERROR statement can be used to display an error message in Microsoft Dynamics NAV and prevent the user from accessing the database.
The PRINT statement can be used to display a warning in Microsoft Dynamics NAV and allow the user to access the database.
If the stored procedure returns a value, it is ignored.
If the stored procedure does not exist, no action is taken by Microsoft Dynamics NAV and the login process continues as usual.

The following examples show how to create a login procedure in the Query Analyzer tool. The database must be selected before these statements are executed.

Example 1

The following code example displays a warning message in Microsoft Dynamics NAV and permits the login.

IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'sp_$ndo$loginproc' AND type = 'P')
DROP PROCEDURE [sp_$ndo$loginproc]
GO
CREATE PROCEDURE [sp_$ndo$loginproc]
@appname VARCHAR(64) = NULL,
@appversion VARCHAR(16) = NULL
AS
BEGIN
PRINT 'The system will be unavailable on Sunday April 1.'
END
GO
GRANT EXECUTE ON [sp_$ndo$loginproc] TO public
GO

Example 2

The following code example displays an error message in Microsoft Dynamics NAV and prevents the login.

IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'sp_$ndo$loginproc' AND type = 'P')
DROP PROCEDURE [sp_$ndo$loginproc]
GO
CREATE PROCEDURE [sp_$ndo$loginproc]
@appname VARCHAR(64) = NULL,
@appversion VARCHAR(16) = NULL
AS
BEGIN
IF SUSER_SNAME() IN ('ACCOUNTS\jim', 'SALES\bill')
RAISERROR ('Contact the system administrator.', 11, 1)
END
GO
GRANT EXECUTE ON [sp_$ndo$loginproc] TO public
GO

What makes Expert - Matt.Traxinger

2010-05-09T13:06:00.001-07:00

Today I read the following post from Mr. Matt.Traxinger and really impressed with it…

The what is not nearly as important as the why.
Obviously the most important part of being an expert is understanding what you're doing. Don't get that confused with knowing what you're doing. Just because you can perform the lab exercises in the functional and technical manuals with your eyes closed doesn't mean you're an expert. Knowing how to run Adjust Cost, even knowing what it does, is not the same as understanding why it needs to be run.

It's not what you know, it's who you know
If you know what every button, every object, and every line of code in the system does then congratulations. You should either be billing 2000 hours a year and making a six-figure salary or running your own solution center. It's important to remember that in an application as big as NAV you're never going to know it all. Even Microsoft MVPs and senior level professionals have questions sometimes. That's not to say that you shouldn't strive to learn as much as possible, but you should have a place, or better yet, specific people that you can bounce questions off of. Speaking from experience, it's not easy to learn when you don't have anyone to help you. If you need a place to start, I highly recommendwww.mibuso.com.

Love what you do
I've developed in my fair share of languages: Basic, C, C++, Java, Jython, C#, SmallTalk, Lisp, just to list the more well known ones. I've built windows applications, web sites, all sorts of things. I have to say, though, that I absolutely love being an ERP developer. If I spent my days writing OS and device driver code I would go insane. Whenever people ask me what I do, it's basically a conversation ender: Computer Programmer (I boil it down to a term they should be familiar with). Little do they know they're missing out on so much fun. I can't imagine doing anything different. If you don't feel the same way about what you do, then you should find something you do love. Don't spend your time trying to become an expert in something you don't want to do.

Transferability
If you can't put the things you know into words (that includes speaking, writing, coding, and anything else that involves knowledge transfer) then you cannot be considered an expert. The purpose of becoming an expert is not just to hoard everything you've learned, but to turn that knowledge into practical, useful tools. More importantly, your knowledge should be used to make other more knowledgeable. Think back to when you were the junior developer, wanting to succeed, but not knowing where to start. The ability to help others will only make you more successful.

MSDN Feedback

2010-04-23T05:06:00.001-07:00

Till now I know that, there is an option in MSDN to give feedback but I never used it before.

Today I also came to know the details about the MSDN Feedback, especially for Microsoft Dynamics NAV blog.

Click the link to know the details about it.

DataCaptionFields and DataCaptionExpr property

2010-04-18T23:34:00.000-07:00

This is a simple and known work around to set the form/page caption. This is particularly useful if you want to set an option field as form caption because changing the DataCaptionFields property won't work in the form and page.

In the following example scenario, I will include "Document Type" into the caption of the sales order form. This can be achieved by simply creating a function and changing the DataCaptionExpr property.

Create a function.

Add the following code in the function.

Change the DataCaptionExpr property as below:

Run the Sales Order and see the ouput.

Worldwide Partner Conference 2010

2010-03-27T11:23:00.001-07:00

Here is the link to the WPC 2010, to know the agenda and other details.

Transfooter and Transheader functionality in RDLC(SSRS) reports

2010-03-23T04:37:00.001-07:00

Here is a nice post which explains the Transfooter and Transheader functionality in RDLD(SSRS) reports for NAV 2009.

How to close the main form immediately after closing the sub form

2010-03-17T04:24:00.001-07:00

Here is the standard NAV example to close the main form immediately after closing the sub form.

1) Open the Form 5784 from the Object Designer.

2) Click the Modify button to open the “Source Document Filter Card” from the ‘Filters to Get Source Docs.’ form.

3) Close the “Source Document Filter Card”. This will close the main form “Filters to Get Source Docs.” as well.

You can close the form conditionally, using the following set of example code in the ‘Run’ button.

GetSourceBatch.USEREQUESTFORM(ShowRequestForm);
GetSourceBatch.RUNMODAL;
IF GetSourceBatch.NotCancelled THEN
CurrForm.CLOSE;

Calculated field on a page is only recalculated when the OnValidation trigger is run

2010-01-29T00:32:00.001-08:00

A page will only update a field if it detects that there is some code on the OnValidate trigger. This is done for performance reasons to avoid unnecessary updates.

Please click the link to read the complete story from the Microsoft Dynamics NAV Team Blog.

ERP_Data Philosophy

2010-01-22T01:37:00.001-08:00

Even though I have good knowledge on different aspects of ERP, today I noticed a very good data philosophy in the David Studebaker book related to ERP.

“Enter once, use everywhere”

Transformation Tool Issue

2009-12-23T22:38:00.001-08:00

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.

New Report Properties in NAV 2009 SP1

2009-12-16T00:00:00.001-08:00

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

Reporting for Microsoft Dynamics NAV 2009 (SP1)

2009-12-15T23:56:00.001-08:00

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.

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

2009-12-09T02:08:00.001-08:00

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>”

Product Suggestions for Microsoft Dynamics

2009-11-29T20:59:00.001-08:00

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.

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

2009-11-28T03:03:00.000-08:00

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

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:

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

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.

SAVERECORD and UPDATE

2009-11-27T22:14:00.000-08:00

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.

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

How to read BLOB data and export into a File

2009-11-26T23:14:00.000-08:00

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.

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

2009-11-26T03:04:00.000-08:00

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.

Object Subtype field in the AllObjWithCaption table

2009-11-08T21:49:00.001-08:00

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.

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…

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

2009-10-24T10:15:00.001-07:00

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

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.

Transformation Tool: Subfrom Menu Items to Pages

2009-10-23T12:10:00.001-07:00

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;

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.

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;

How to change SQL Server default backup location

2009-10-17T10:26:00.001-07:00

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.

This solved my problem and saved some time.

ProviderID property to System Parts

2009-10-12T09:18:00.001-07:00

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.

ProviderID property in NAV 2009

2009-10-09T10:17:00.001-07:00

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.

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.

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.

Error in NAV 2009 Reporting

2009-10-06T03:13:00.001-07:00

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

In Control Panel, select User Accounts.
In User Accounts, click Turn User Account Control On or Off.
Clear the Use User Account Control (UAC) to help protect your computer check box.

To set a program to run as an administrator

Right-click the .exe file, and then select Properties.
In the Properties window, on the Compatibility tab, in the Privilege Level section, select Run this program as an administrator.

How to become an MVP (Most Valuable Professional)

2009-10-04T05:05:00.001-07:00

Here is a nice post from Paul S. Randal on '”How to become an MVP”.

Click the link below:

How to become an MVP

How to Delete Personalization Settings

2009-10-02T09:25:00.001-07:00

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.

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.

Save View As in NAV 2009

2009-10-01T23:08:00.001-07:00

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.

Similar 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

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

Select the Save View As option like the following:

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

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

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.

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.

C/AL ASSERTERROR Statements

2009-10-01T22:27:00.001-07:00

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

2009-10-01T21:42:00.001-07:00

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.

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.

Search Option in NAV RoleTailored Client

2009-09-20T00:15:00.001-07:00

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

ENABLING MICROSOFT DYNAMICS NAV SERVER

2009-09-18T11:22:00.001-07:00

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.

How to Zoom in NAV 2009 Pages?

2009-09-16T21:50:00.001-07:00

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)

2009-09-16T10:27:00.001-07:00

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.

Transformging Subforms to Pages

2009-09-15T00:24:00.001-07:00

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.

Running Pages & Reports from Run

2009-09-11T03:15:00.001-07:00

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

2009-09-11T00:19:00.001-07:00

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

Debugging in NAV 2009

IMPORTOBJECTS

2009-09-04T01:54:00.001-07:00

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

2009-09-04T01:49:00.001-07:00

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.

Variable		Data type	Subtype
MyFilter		Record	Object

MyFilter.SETFILTER(MyFilter.Type,'=Report');

MyFilter.SETFILTER(MyFilter.ID,'%1..%2',50000,60000);

EXPORTOBJECTS('C:\MyExport.txt', MyFilter, 0);

InstructionalTextML Property

2009-09-04T01:31:00.001-07:00

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

Page.RUNMODAL

2009-09-03T23:54:00.001-07:00

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

2009-09-03T08:41:00.001-07:00

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.

COPY function in NAV 2009

2009-08-21T23:10:00.001-07:00

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.

ListPart (Sub form) controls from the Page Header

2009-08-18T02:54:00.001-07:00

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.

How the three-tier architecture works in NAV 2009

2009-08-17T04:58:00.001-07:00

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:

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

Open an elevated command prompt. Click Start, and in the search window, type Command Prompt.
Right-click Command Prompt, and then click Run as administrator.

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

On any server computer in the domain, click Start, and then click Run.
In the Open field, type dsa.msc.
This opens the Active Directory Users and Computers utility.
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.
Right-click the node for the domain where you have installed Microsoft Dynamics NAV, and then click Find.
In the Find Users, Contacts, and Group dialog box, type the name of the domain user in the Name field, and then press ENTER.
In the Search results area, right-click the domain user, and then click Properties.
On the Delegation tab, click Trust this user for delegation to specified services only, and then click Use Kerberos only.
Click Add to open the Add Services dialog box.
In the Add Services window, click Users or Computers, and then type the name of the domain user.
In the list of services for the domain user, click MSSQLSvc, which is the name of the SQL Server service.
Click OK to exit the Add Services dialog box.

Click OK to close all open dialog boxes.

Physical Location of Automation Server

2009-08-14T12:52:00.001-07:00

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

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

2009-08-14T01:45:00.001-07:00

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

Style property in NAV 2009 Pages

2009-08-12T09:39:00.001-07:00

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.

BLOB Fields with NAV & SQL

2009-04-28T23:55:00.001-07:00

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

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

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

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

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

(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

2009-04-28T23:10:00.001-07:00

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

2009-04-28T22:55:00.001-07:00

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

Default trace or Change Log in SQL Server

2009-04-28T04:57:00.001-07:00

We have all been subject to or know someone who has been in a situation where an object has been altered/created/deleted, without our knowledge, and the application comes to a screeching halt. After fixing the problem, your boss asks you some questions, like what happened, why did it happen, and who did it. SQL Server 2005 introduced a new type of trigger called a DDL trigger that can provide all the answers we need; however, you did not get a chance to implement this functionality. So... what do you do?

Some would tell their boss "I do not know, but I can find out" and then search franticly for 3rd party tools to read the transaction log, hoping for instantaneous salvation. What these few do not know is an answer is silently running in the background. SQL Server 2005 has built in functionality that gives administrators the answers to all these questions.

The answers lie in a new background trace called the default trace. The default trace is exactly what the name specifies, a trace. Default trace is always running in the background of your instance capturing events that administrators can use to troubleshoot problems. The default trace is enabled by default and does not burden the system because it is fairly lightweight. Chances are you had not even noticed this trace running on your instance. To those concerned about overhead, yes there is overhead, but in my mind the benefits far outweigh the minimal overhead. The default trace is not intended to replace DDL trigger functionality and should be used as a means to monitor an SQL Instance, or quickly obtain detailed information about problematic events.

The default trace does not capture all trace events, but captures enough information to become a powerful tool in your toolkit. The default trace captures key information including auditing events, database events, error events, full text events, object creation, object deletion and object alteration. From my experiences and observations on forums, I will be focusing on object level events. It seems that a greater number of people want the "who done it" answer for object DDL events.

The first piece of code is to check the default trace to see if it is enabled.

SELECT * FROM sys.configurations WHERE configuration_id = 1568

If this feature is not available, you will have to configure the advanced option "default trace enabled". Below is the code to enable the trace. Note: you will need the ALTER SETTNGS permission or be in the sysadmin or serveradmin fixed server role to reconfigure.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'default trace enabled', 1;
GO
RECONFIGURE;
GO

The next piece of information we need is the default trace file path, and the function below will return the current trace file. You can grab the initial trace file (log.trc) and rollup every trace file into a single table, but there is a higher overhead associated to bringing more data in. You should use the trace file that best represents the information you are looking for.

Note: the path is defaulted to the \MSSQL\LOG directory, but we can use the function below to get the path

--get the current trace rollover file
SELECT * FROM ::fn_trace_getinfo(0)

Now that we have all the information we need we can get into the trace data. Let's start by creating a new database call TraceDB.

USE [master] GO CREATE DATABASE TraceDB

Now open the trace file, as shown below. As you can see, we were able to gather some pretty significant information about who created the database and when the database was created. I have used category id of 5 and a trace_event_id of 46 to filter the data correctly. Event ID 46 represents Object:Created and category 5 is objects. I will provide queries that list all events and categories at the end of this article.

** Make sure to use your trace file path below. Yours may be different than mine.

SELECT
    loginname,
    loginsid,
    spid,
    hostname,
    applicationname,
    servername,
    databasename,
    objectName,
    e.category_id,
    cat.name as [CategoryName],
    textdata,
    starttime,
    eventclass,
    eventsubclass,--0=begin,1=commit
    e.name as EventName
FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc',0)
    INNER JOIN sys.trace_events e
         ON eventclass = trace_event_id
    INNER JOIN sys.trace_categories AS cat
         ON e.category_id = cat.category_id
WHERE databasename = 'TraceDB' AND
     objectname IS NULL AND --filter by objectname
     e.category_id = 5 AND --category 5 is objects
     e.trace_event_id = 46
     --trace_event_id: 46=Create Obj,47=Drop Obj,164=Alter Obj

You will see more than one entry per object create because these objects have two event sub classes -begin and commit. Each subclass will have an entry.
You can remove the databasename filter to get object creation events for all databases.

Results (Trimmed for Simplicity):

Zoom in | Open in new window

Now, we have seen what default trace is capable of. Let's create another object and repeat the query. This time around we are going to create a table called "MyTable". Use the following code to create the table.

USE [TraceDB] GO CREATE TABLE [dbo].[MyTable](
[id] [int] IDENTITY(1,1) NOT NULL,
[sometext] [char](3) NULL
) ON [PRIMARY]

Now query the default trace using the same query as above. Note you can use the ObjectName column to filter for the specific object you are looking for; otherwise all created database objects are returned.

WHERE databasename = 'TraceDB' AND
     objectname = 'MyTable' AND --filter by objectname
     e.category_id = 5 AND --category 5 is objects
     e.trace_event_id = 46
     --trace_event_id: 46=Create Obj,47=Drop Obj,164=Alter Obj

Results (Trimmed for Simplicity):

Zoom in | Open in new window

Let's take the demo a step further by altering MyTable. Issue an alter table statement and add a new column to MyTable, as shown below.

USE [TraceDB] GO ALTER TABLE MyTable
ADD col INT

We can now search trace information on the alter event for MyTable. We can use the same query as before but need to make a small modification. You must change the trace_event_id to 164 because event 164 represents the object:Altered event.

WHERE databasename = 'TraceDB' AND
     objectname = 'MyTable' AND --filter by objectname
     e.category_id = 5 AND --category 5 is objects
     e.trace_event_id = 164
     --trace_event_id: 46=Create Obj,47=Drop Obj,164=Alter Obj

Results (Trimmed for Simplicity):

Zoom in | Open in new window

Now lets drop MyTable and view the trace details. You must change the trace_event_id to 47 because event 47 represents the object:Deleted event, as shown below.

USE [TraceDB] GO  DROP TABLE MyTable

We can view trace data by changing the trace_event_id to 47.

WHERE databasename = 'TraceDB' AND
     objectname = 'MyTable' AND --filter by objectname
     e.category_id = 5 AND --category 5 is objects
     e.trace_event_id = 47
     --trace_event_id: 46=Create Obj,47=Drop Obj,164=Alter Obj

Results (Trimmed for Simplicity):

Zoom in | Open in new window

As you can see, default trace gives an administrator the ability to find the history of any DDL transaction. I want to point out that default trace is not limited to object DDL history. Among other things, default trace captures log growth events, which can be invaluable to troubleshooting disk capacity problems.

For example, say your log file spontaneous grows enormous. It is important to understand why the log grew spontaneously. No one would argue that one of the first place to look may be SQL Jobs. There are many commands within a job that can potentially cause the log to grow enormous, like reindexing, bulk inserts, bulk deletes etc. By using the trace data you can more easily identify the problem because you can pin-point the exact time the log file began to grow. This greatly reduces the number of possible suspects, which reduces the amount of time required to find the culprit.

The query below will pull all trace data using the log auto growth event. Note: You will not have any log growth for TraceDb because we have not done in large inserts to make the log grow. You should apply this query to another database where you want to monitor log growth.


SELECT
    loginname,
    loginsid,
    spid,
    hostname,
    applicationname,
    servername,
    databasename,
    objectName,
    e.category_id,
    cat.name,
    textdata,
    starttime,
    endtime,
    duration,
    eventclass,
    eventsubclass,
    e.name as EventName
FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc',0)
    INNER JOIN sys.trace_events e
         ON eventclass = trace_event_id
    INNER JOIN sys.trace_categories AS cat
         ON e.category_id = cat.category_id
WHERE databasename = 'TraceDB' AND
     e.category_id = 2 AND --category 2 is database
     e.trace_event_id = 93 --93=Log File Auto Grow

Summary:

The default trace is a valuable tool for the modern DBA's tool belt. It offers a wealth of information, while minimally impacting the system. The default trace is not a widely publicized feature of SQL Server 2005, but is slowly gaining fame. The default trace gives administrators the ability to get detailed information about auditing events, database events, error events, full text events, object creation, object deletion and object alteration events. With this much information at their fingertips, administrators are more productive and can more easily identify problems in a production environment. My recommendations are to look through the events and see what information already exists for your instances. Default trace should not only be used reactively but proactively. A proactive mentality will reveal small problems before they escalate to bigger problems.

Event and Category Queries

--list of events
SELECT *
FROM sys.trace_events

--list of categories
SELECT *
FROM sys.trace_categories

--list of subclass values
SELECT *
FROM sys.trace_subclass_values

--Get trace Event Columns
SELECT
    t.EventID,
    t.ColumnID,
    e.name AS Event_Descr,
    c.name AS Column_Descr
FROM ::fn_trace_geteventinfo(1) t
    INNER JOIN sys.trace_events e
         ON t.eventID = e.trace_event_id
    INNER JOIN sys.trace_columns c
         ON t.columnid = c.trace_column_id

All the Tables from All Databases

2009-04-27T23:28:00.001-07:00

sp_msforeachdb 'select "?" AS db, * from [?].sys.tables'

SQL Server Reporting Services (SSRS)

2009-03-19T23:05:00.001-07:00

In the SQL Server Reporting Services, each report is managed in a standard, text-based file format called Report Definition Language (RDL).An RDL file is an XML document with a standard definition for markup tags that define all of the properties for a report.

ZUP files in NAV 2009

2009-01-11T22:48:00.001-08:00

In the previous versions of Navision, all the user changes and settings will be stored in the .zup file(i.e. Recentely opened databases,etc...) and the user menu level table (i.e Shortcuts,etc...). This functionality has been changed for the RTC in NAV 2009.

There are new tables in NAV 2009, some of them are used to store user settings in RTC. Classic client user changes will be still stored in fin.zup file and user menu level table.

2000000071 Object Metadata

2000000072 Profile

2000000073 User Personalization

2000000074 Profile Metadata

2000000075 User Metadata

2000000076 Web Service

2000000078 Chart

As of my knowledge NAV 2009 RTC user changes/settings will be stored like below:

PersonalizationStore.xml: This file is located at \Administrator\Application Data\Microsoft\Microsoft Dynamics NAV.This file is used to store all user changes like resizing the columns in the pages. You can delete this file, If you want to roll back all the changes.

System will again create this file once you login and logout from the RTC.

Classic client user changes will be still stored in .zup file.

ClientUserSettings.config: This file is located at \Local Settings\Application Data\Microsoft\Microsoft Dynamics NAV.This file is created by installation setup, while installing RTC Client and this holds the parameters useful to call the Microsoft NAV Server. This file holds NAVServer, Instance name, NAV Server port. Once user click the Role Tailored Client to open, process will call the above file.

CustomSettings.config: This file is located at Program Files\Microsoft Dynamics NAV\60\Service.

I guess this file is called by the Microsoft NAV Server to connect the SQL Server, related database and holds other important parameters.

2000000071 Object Metadata: As the name specify (i.e. Metadata : Data about Data) this table holds data about all the objects in the database. Data into this table will be inserted while compiling the object.

2000000072 Profile: It is the customization of the user interface (Role Center) for the end-user to match a specific role. Profiles can be accessed from Administration-->Application Setup-->RoleTailored Client. This is the default setup for all users and we can customize the setup for the user using User Personalization.

2000000073 User Personalization: User Personalization is used to assign a configured profile to a user. User Personalization can be accessed from Administration-->Application Setup-->RoleTailored Client.

2000000074 Profile Metadata: This used to store the custom chages done to the profiles.We can delete all configuration changes made for the profiles from Profile Card--> Fuctions-->Clear Configured Pages.

2000000075 User Metadata: In the RTC, if user customize any pages or navigation panes a record will be inserted into this table. This used to store all custom changes done by the user to the pages in the RTC. We can delete all personalization change made by the user from User Personalization Card--> Fuctions-->Clear Personalized Pages.

2000000076 Web Service: I will study this functionality and update soon.

2000000078 Chart: I will study this functionality and update soon.

Implementing Microsoft Dynamics NAV 2009

2009-01-09T21:35:00.001-08:00

Microsoft Dynamics NAV 2009 is the latest release of the NAV application (formerly known as Navision) from the Microsoft Dynamics family of products that brings a 3-tiered architecture, web services enablement, and many more exciting features, to the well established Enterprise Resource Planning (ERP) solution. Here is the first book to show you how to implement Microsoft Dynamics NAV 2009 in your business and this book authors are David Roys,Vjekoslav Babić.

Implementing Microsoft Dynamics NAV 2009

Calculate Inventory Exceptions

2009-01-09T21:05:00.001-08:00

Calculate Inventory Batch in the Phys. Inventory Journal will process the items based on the filters given and inserts items in the physical inventory journal. In the physical inventory journal Qty. (Calculated) field value will be calculated from the item ledger entry table and Qty. (Phys. Inventory) field value will be calculated from the warehouse entries if the database is using warehouse management.

Calculate inventory report has two exceptional cases:

Case I: If the Item has Warehouse Entries and doesn't have single Item Ledger Entry then "Calculate Inventory" function in Phys. Invt. Journal will not create adjustment line.

Case II: (For Same UOM) Item's Qty. (Base) differs from Quantity in Warehouse Entries.

"Calculate Inventory" function in Phys. Invt. Journal is creating two adjustment lines with incorrect Quantity for the same item instead of creating one line.

Change Log Entry for the Object table

2009-01-09T05:58:00.001-08:00

Microsoft Dynamics Navision has below kind of object types:
Table, Form, Report, Data port, XMLport, Code unit and Menu suite.
Other than the above object types, there are other object types which are hidden and useful for data management.
i.e. Table Data, System, Field Number
Object (2000000001) table holds data for all the above object types.

When a table object (i.e. .fob file) is imported into the database, two records will be inserted into the Object table (2000000001).
1. First record with Type value 0 (i.e. for Table Data). This is useful to manage data in the specific company.
2. Second record with Type value 1 (i.e. for Table). This table object is unique for all companies in the database.

Because of this change log entry table holds two records with “Type of Change” Insertion, one with "Primary Key Field 1 Value” 0 and second record with "Primary Key Field 1 Value" 1.

This only applies to the table object and for the other objects like forms, reports and etc, only one record will be inserted into the object table and change log entry table.

Top 5 qualities of a great Microsoft Dynamics consultant

2009-01-08T04:20:00.001-08:00

Top 5 qualities of a great Microsoft Dynamics consultant:

Industry expertise
Application knowledge
Creativity
Patience
Communication

Click this to read this excellent post from Vjeko.

Report Designing in NAV 2009

2009-01-07T21:36:00.001-08:00

In NAV 2009, if a report consists of two indented data items, instead of running a nested loop, the records are returned in a flattened state, which means, both data items, the parent and the child are joined, and returned together.
There are two new components in the report objects, they are as follows:
• Request Page
• Report Definition Language (RDL) data
Report chagnes takes advantage of on SQL Reporting Services technology. i.e.
• Visual effects with charts and graphical representation.
• Richer aggregates, not only SUM but also AVG, MIN, MAX and more.
• The possibility to create interactive reports (interactive sorting, hide/collapse item).
• Out-of-the box export possibility to Excel and PDF for all reports.

Pages in NAV 2009

2009-01-04T23:04:00.001-08:00

Pages in NAV 2009 has distinct properties that are not available in forms PageType, CardFormID, InstructionalTEXTML, LinksAllowed.
In NAV 2009 field's visibility can be controlled by assigning an expression that returns TRUE or FALSE to its visible property instead of Yes or No.
Fileds in the pages have an extra property called ExtendedDataType. The options available are as follows:
• None
• Phone No.
• Url
• E-mail
• Ratio
Every page must have a container which is the top most element of the page.
Importance property of the filed in the pages is useful to show the data in this field without cramping all the information together.
New Pages can de deployed into the RoleTailored Client by adding the pages into the related Role Center page object.

Installing NAV 2009

2008-12-30T05:12:00.001-08:00

The Role Tailored client and Classic client can be installed on a different computer from where you install Microsoft Dynamics NAV Server.
To install the Role Tailored Client and the Classic Client, run the Setup.exe.
After accepting the license, two options will be available. one is Installing Demo NAV and choosing a installation option.
Installing Demo will install Role Tailored client, Classic Client for SQL and Classic Client for Native database.
Functionality for the Classic Client for SQL and Native Database remains same like the previous versions, Where as Role Tailored Client option is quite different and new in NAV 2009.

Things I learned in NAV 2009

2008-12-30T05:07:00.001-08:00

· Architecture changes that includes Role Tailored client. Classic client still remains with two tier architecture, where as the new Role Tailored Client support three-tire model with Role Tailored Client, Microsoft Dynamics NAV Server which executes business logic and SQL Server.
· The Role Tailored client provides a single document interface (SDI) environment,
· Microsoft Dynamics NAV Server: The new Microsoft Dynamics NAV Server is a .NET-based Windows Service application that works exclusively on SQL Server. It uses the Windows Communication Framework as the communication protocol for Role Tailored clients and Internet Information Services for Web services that access it.
· Microsoft Dynamics NAV Server provides an additional layer of security between the clients and the database. It leverages the authentication features of the Windows Communications Framework to provide another layer of user authentication and uses impersonation to ensure that business logic is executed in a process that has been instantiated by the user who submitted the request.
· Microsoft Dynamics NAV Web services can be used by customers and partners who want to use business logic or use a standard interface to access data from outside Microsoft Dynamics NAV.
· To allow you to write code that targets either the Classic client or the RoleTailored client, a new system property called ISSERVICETIER has been created. If code is being executed on the service, then it returns TRUE. Otherwise, it returns FALSE. You can then use ISSERVICETIER to write code that is explicit about whether it is run on the service.
· When you design a report for the Classic client, you design the layout in Section Designer. When you design a report for the Role Tailored client, you design the layout in an RDL editor, such as Visual Studio Report Editor.
· NAV 2009 has an option to import/export the objects in .XML format also.
· Pages contain fewer triggers than forms for two reasons: The RoleTailored Client has enhanced behavior that is implemented directly into the controls that run on it. Some code previously necessary is now obsolete. Pages are designed to optimize performance, which reduces requirements for existing form triggers.

. Field Groups: Field Groups is a new concept introduced in a table description. When a field displayed in a page in the RoleTailored Client has a relation with another table, the DropDown button displays several default fields from that table. These default fields are set from the Field Groups window, accessed from the table designer. To view the Fields Groups of a particular table:
In the Object Designer's Table list:
1. Select any table and then click Design.
2. Click View > Field Groups. The Field Groups window of that table
opens.

. Using Administration-->RoleTailored Client-->Profile, we can change the default role center for the user.

Reversing Output Posting

2008-01-03T02:30:00.001-08:00

There are also times when output posting must be reversed.An example of this would be if a data entry error occurred and an incorrect amount of output is posted to a production order.

To reverse an output posting, manually enter the posting date, Prod. Order No.,Item No., and Operation No. fields in the output journal, and enter a negative value in the Run Time field and output quantity field.You must also fill in the Applies-To entry field.This reverses the capacity and the item ledger entries.

Microsoft Dynamics CRM Editions and Licensing

2008-01-03T02:14:00.001-08:00

Editions:
Microsoft Dynamics CRM 4.0 Workgroup. This edition is limited to five, or fewer, users. It can be installed on Microsoft Windows Small Business Server 2003 R2 Enterprise Edition, any of the supported Windows Server 2003 editions, or Windows Server 2008 (when available). This version is limited to a single organization and a single computer that is running Microsoft Dynamics CRM Server.

Microsoft Dynamics CRM 4.0 Professional. This edition has no user limit and is limited to a single organization, but Microsoft Dynamics CRM 4.0 Professional can be installed on more than one computer in the same deployment.

Microsoft Dynamics CRM 4.0 Enterprise. There is no user limit for this edition. Additional features include support for multiple organizations, multiple server instances, and role-based service installation. Role-based services let you increase performance by installing component services on different computers.

New Production BOM in NAV 5.0

2007-12-28T04:31:00.001-08:00

In NAV 5.0 Production BOM’s are of two Types: Manufacturing and Kitting. If Type is kitting two other option values will be available for the Prod. BOM Lines Type.i.e. Resource, Setup Resource.

Welcome

2007-12-28T04:26:00.001-08:00

Hi Everybody,
Welcome to the Microsoft Dynamics Navision Blog.This blog is created to note down the key statements in navision.Anyone can share their knowledge here.Let us start the journey all together.

Love-
Dynamics Boy.

Disclaimer

2007-11-28T11:25:00.000-08:00

The information and data published on the blog is strictly for informational purposes.

Any opinions or viewpoints that are published herein are directly from the contributing author and does not represent the philosophy or viewpoints of Microsoft Corporation or any other company/website.

The information contained on this blog has not been reviewed by professionals with the knowledge required to validate that the data is accurate, complete, or reliable.

Comments on this blog are the sole responsibility of their writers and the writer will take full responsibility, liability, and blame for any libel or litigation that result from something written in or as a direct result of something written in a comment. The accuracy, completeness, veracity, honesty, exactitude, factuality and politeness of comments are not guaranteed.

In no event will we be liable for any loss or damage including without limitation, indirect or consequential loss or damage, or any loss or damage whatsoever arising from loss of data or profits arising out of, or in connection with, the use of this blog.

Through this blog you are able to link to other websites which are not under the control of this blog. We have no control over the nature, content and availability of those sites. The inclusion of any links does not necessarily imply a recommendation or endorse the views expressed within them.