Teach Me NAV

Config File Gotcha

David Roys — Fri, 03 Feb 2012 10:08:44 GMT

I came across something that was a bit weird in an implementation where every user in a Citrix deployed RoleTailored client suddenly connected to the TEST system instead of the LIVE system. I thought I’d share this gotcha to save others from making the same mistake.

When you start Dynamics NAV 2009’s RoleTailored client, there are a three ways for the client to determine which server to connect to. This is handy to know because most people want to have a live system and a test system shortcut on their desktop and it’s nice to know where you’re going when you start the client.

Default ClientUserSettings.config

The first method is the default method where the system simply reads your settings from the ClientUserSettings.config file which you can find in

%ProgramData%\Microsoft\Microsoft Dynamics NAV\

That’s pretty easy because you don’t need to do anything. The file gets created automatically and if you connect to a different server, the new value gets saved in the file. Not very useful for switching between Live and Test though.

Startup .config file

This one’s pretty neat. When you start the client, you can add a command line parameter to specify the location of the config file you want to use. The parameter is settings and here’s an example of the shortcut you would need.

Microsoft.Dynamics.Nav.Client.exe -settings:NavLive.config

But this is where the gotcha comes in. If you use this settings file to start the client and then connect to a different server, the change gets written back to the config file. That’s not so good when you were trying to use different files to point you to either Live or Test systems. It’s even worse if you are using Citrix to publish the application and every user is updating the same config file. Whilst you don’t get errors, it can be a bit confusing if one user starts up the LIVE system and then jumps to the TEST server which writes the settings back to the config file so the next time a user tries to open the LIVE system they end up in TEST. One solution to this is to make the config files read-only, the other is to use the startup URI.

Startup URI

I quite like this option for a couple of reasons. First of all, it’s really obvious which system you’re connecting to. Secondly, if you’re connecting to a system that is not your default server (remember the ClientUserSettings.config file?) then the RTC puts up a helpful message telling you. This is great for having two shortcuts that will take you into live or test and it’s not going to mess with your ClientUserSettings.config file either.

The syntax for the URI allows you to specify which company you want to start in too. You could use this to start up on a specific page if you like.

Microsoft.Dynamics.Nav.Client.exe "dynamicsnav://NAVSERVER/ServiceName/CompanyName"

The double quotes are needed if your company name contains spaces.

Service Tier and Classic Client Monstrous Nightmare with FieldRef

David Roys — Fri, 12 Aug 2011 09:49:27 GMT

I came across an error today that was a monstrous nightmare to track down. First of all, it worked in the Classic client but not in the RoleTailored client. Sigh. Don’t you just hate that? It means going through the pain of debugging the RoleTailored client.

The error was caused because the FieldRef.Value function for a FieldRef that points to an Option type field returns different values in the Classic client and RoleTailored client.

If you want proof, try running this Codeunit in both the RTC and Classic.

// ServiceTier returns different values for FieldRef.Value for Option
// fields compared to Classic client.

lRecordRef.OPEN(DATABASE::"Sales Header");

lFieldRef := lRecordRef.FIELD(1);

MESSAGE('ISSERVICETIER=%1\Document Type=%2',ISSERVICETIER,lFieldRef.VALUE);

You’ll see the following results. Classic client first…

Then the RoleTailored client…

You see that? One returns the string value and the other returns the numeric value for the option string.

The error we were getting was because the field was being used as an Integer which was throwing a run time error whenever it hit that particular bit of code but only when using the RTC.

Most of the time in finding this error was in tracking down the line of code that was causing the error to be thrown. I was reading the Statement of Direction for NAV the other day and noticed it suggests we’ll be getting an easy-to-use debugger for the RTC in a future version – I can’t wait! Let’s hope in the future I can say “break on error” and just run my RoleTailored client and find out what the issue is straight away.

Creating New Windows Logins in NAV Through a SQL Sproc

David Roys — Sat, 23 Jul 2011 09:02:42 GMT

If you’ve been following my on-going battle against the pigs, you’ll be pleased to know that I now have attained three stars on all of the levels in Angry Birds Seasons, Angry Birds, and Angry Birds Rio, so with no more pigs to kill until the next update, I figured I’d write another NAV blog.

One of the reasons I started blogging was to have a place where I could save little gems so they can be easily found later on. One of the directors at Intergen suggested that I blogged the information and then use Google to find it – so that’s what I did. This week I needed to find something I knew I’d done before, but it took me quite a while to track it down, so I figured it’s time to blog the solution so I need never struggle again – and who knows, it may help some of you out too.

Have you ever had a SQL Backup to restore to a SQL Server that does not allow mixed mode (no database logins, only Windows users) and you need to open the NAV database using NAV but don’t have a login? I created the following SQL code to do this, but you may find it useful to be able quickly add all of the Windows users for your team to a customer’s database. Or how about using the ability to call SQL Stored Procs from NAV (recently blogged about by Waldo) to allow new Windows users to be added to NAV from the RoleTailored client – now that sounds useful!

Don’t forget to tell SID

NAV stores security for Windows users with a SID which is a funny string that starts with “S” and the first thing I need is something that will give me the SID string for a Windows login.

Create the following function in the master database.

USE master     
GO     
CREATE FUNCTION fn_SIDToString     
(     
 @BinSID AS VARBINARY(100)     
)     
RETURNS VARCHAR(100)     
AS  
 BEGIN     
    IF LEN(@BinSID)%4<>0 RETURN(NULL)     
     
    DECLARE @StringSID VARCHAR(100)     
    DECLARE @i AS INT     
    DECLARE @j AS INT     
     
    SELECT @StringSID='S-'+CONVERT(VARCHAR,CONVERT(INT,CONVERT(VARBINARY,SUBSTRING(@BinSID,1,1))))     
     
    SELECT @StringSID=@StringSID+'-'+CONVERT(VARCHAR,CONVERT(INT,CONVERT(VARBINARY,SUBSTRING(@BinSID,3,6))))     
     
    SET @j=9     
     
    SET @i=LEN(@BinSID)     
     
    WHILE @j<@i     
    BEGIN     
        DECLARE @val BINARY(4)     
         
        SELECT @val=SUBSTRING(@BinSID,@j,4)     
         
        SELECT @StringSID=@StringSID+'-'+CONVERT(VARCHAR,CONVERT(BIGINT,CONVERT(VARBINARY,REVERSE(CONVERT(VARBINARY,@val)))))     
         
        SET @j=@j+4     
    END     
     
    RETURN(@StringSID)     
END

Between a Sproc and a Hard Place

Once you have this function, the following stored procedure will create a Windows Login, add it as a windows user, and finally make the user a member of the SUPER role. I’ve hard-coded the database name in this sproc, but you should be able to easily make this a parameter to the sproc if you wish.

CREATE PROCEDURE [dbo].[proc_CreateNavUser]    
@User  sysname    
AS  
SET NOCOUNT ON    
    
DECLARE @SQL VARCHAR(MAX)    
DECLARE @RetVal INTEGER    
    
SET @RetVal=0    
    
BEGIN TRY    
    IF(SELECT COUNT(*)    
       FROM master.sys.server_principals    
       WHERE name=@User)=0    
    BEGIN    
        SET @SQL='use master CREATE LOGIN ['+@User+'] FROM WINDOWS WITH DEFAULT_DATABASE=[master]'    
         
        EXECUTE(@SQL);    
    END    
     
    IF(SELECT COUNT(*)    
       FROM [Demo Database NAV (6-0)].sys.database_principals    
       WHERE name=@User)=0    
    BEGIN    
        SET @SQL='use [Demo Database NAV (6-0)] CREATE USER ['+@User+'] FOR LOGIN ['+@User+']'    
         
        EXECUTE(@SQL);    
    END    
END TRY    
        BEGIN CATCH    
    SELECT Retval=@RetVal    
     
    RETURN    
END CATCH    
    
BEGIN TRY    
    SET @RetVal=1    
     
    INSERT INTO [Demo Database NAV (6-0)].dbo.[Windows Login]([SID])    
    SELECT master.dbo.fn_SIDToString(sp.sid)    
    FROM master.sys.server_principals sp    
    WHERE sp.name=@User AND master.dbo.fn_SIDToString(sp.sid) NOT IN(SELECT sid    
                                                                     FROM [Demo Database NAV (6-0)].dbo.[Windows Login])    
           
--Add Roles (SUPER)    
           
    INSERT INTO [Demo Database NAV (6-0)].dbo.[Windows Access Control]([Login SID],[Role ID],[Company Name])    
    SELECT master.dbo.fn_SIDToString(sp.sid),'SUPER',''    
    FROM master.sys.server_principals sp    
    WHERE sp.name=@User AND master.dbo.fn_SIDToString(sp.sid) NOT IN(SELECT [Login SID]    
                                                                     FROM [Demo Database NAV (6-0)].dbo.[Windows Access Control]    
                                                                     WHERE [Role ID]='BASIC')    
     
    SELECT Retval=@RetVal    
END TRY    
        BEGIN CATCH    
     
END CATCH

Enjoy the srpoc. If you do write something that uses this – like a tool to create Windows logins in the RoleTailored client, why not share your solution too?

Now it’s time to play Cut the Rope.

Make me squeal

David Roys — Mon, 20 Jun 2011 10:07:14 GMT

If God had wanted me to work, he wouldn’t have given me Angry Birds.

I figured it was about time I wrote something about Dynamics NAV again, so setting my continuing mission to get three stars on all levels to one side, I’ve finally taken time to pass on a couple of tips around .NET Interop and executing SQL Stored Procs from within NAV (I used to work with someone that pronounced SQL as “Squeal” instead of “Sequel” hence the title of this post).

Way back in April (wow has it been that long?) I wrote about how you could use .NET Interop to execute SQL commands using NAV 2009 R2. In my example I executed a simple SQL command and showed how you could parse the dataset returned from the command.

When I finally came to put this into practice I discovered I had made two errors in my original post which proved to me that I am better at Angry Birds than I am at NAV programming and that, whilst I get many people reading my blog, it’s rare that people actually try out the things I write about.

The Host with the Most

My first mistake was when I said that I would use the session table to get the machine name of the SQL Server. Wrong! Yes it worked in my example, but that was only because I was running the NAV Server on the same box as the SQL Server. The “Host Name” field on the Session table is the machine name that initiated the connection, so when I moved my code to three tiers it didn’t work. So how do you find the name of the SQL Server you are connecting to?

Here’s one way that works. I created a view that includes the server name like so:

CREATE VIEW [dbo].[SQLConnectionDetails] AS
SELECT @@ServerName AS [Server Name],
DB_NAME() AS [Database Name]

Then I made a linked table in NAV over the top of the view. Now I can use SQLConnectionDetails.[Server Name] as part of my connect string. Phew!

Enum Nuts

My next mistake was when I assumed that the enumerated value for a SQL Stored Proc command type was 1. Wrong again! I’d made that assumption because the documentation on the properties of the CommandType showed a table with three options then they would be numbered 0, 1, and 2. Finding the correct integer value was not easy but in the end it was Waldo to the rescue. I knew I had read a post from Waldo about using enumerated types in .NET interop so I used his code to parse the value. Here’s how to do it for the SQL command type.

//Set the command type as StoredProcedure.
dnCommandType := dnSQLCommand.CommandType;
dnSQLCommand.CommandType := dnCommandType.Parse(dnCommandType.GetType(),'StoredProcedure');

Much better to have this code that is self-documenting than put in the numeric values but if you’re feeling lazy, the number values are: Text is 1, StoredProcedure is 4 and TableDirect is 512.

Pulling my heir out

My final problem (not a mistake this time but something I thought I would pass on) was when I came to try to add parameters to my stored procedure I hit a problem I thought I couldn’t get past.

I could not instantiate a Parameter collection on my SQL Command because NAV thought for some crazy reason that there were two Parameters properties. Now .NET (as far as I know) does not support overloaded properties so this is not possible and I can only put it down to a bug in the R2 release. I have reported this to the product team and I am assured this problem will be fixed in a later release.

So if I can’t add parameters to my stored proc, how can I get around it? My solution was to instantiate a variable of type System.Data.Common.DbCommand which the SQLCommand class inherits. I could then use the Parameters property of the Common.DbCommand and assign this to my SQLParameterCollection. Don’t you just love object oriented?

dnCommonDBCommand := dnSQLCommand;

// Need to use the dnCommonDBCommand.Parameters becuase the SQLCommand.Parameters gets confused in the R2 Release
dnSQLParameterCollection := dnCommonDBCommand.Parameters();
dnSQLParameterCollection.Add(pParameterName,pParameterValue);

Now I have instantiated my SQLParameterCollection, I can use the Add method and add my new parameters.

Well that’s it for now. Time to try to crack three stars on level 10. Die pigs, die!

20-30% Discount on Dynamics Books During May 2011

David Roys — Tue, 17 May 2011 07:25:11 GMT

Those people at PACKT have been busy making more and more books on Dynamics covering GP 2010, NAV 2009, AX 2009, and Sure Step, so whichever flavour of Dynamics you prefer, there’s something for you to get your teeth into.

To celebrate the recent publication of the Microsoft Dynamics GP 2010 Reporting book, PACKT has announced a series of attractive discounts on their Dynamics books:

Buy any Microsoft Dynamics printbook and get 20% off
Buy any Microsoft Dynamics eBook and get 30% off

So if you’ve been waiting to buy a copy of the book I wrote with Vjeko (Implementing Microsoft Dynamics NAV 2009) now’s your chance to order your copy and save some money. Our book has funny references and quotes, a comparison between the Chart of Accounts and a Hippo’s Bottom, and a section on how to perform analysis and design that uses super villains from a comic book as the users. There’s even some sensible stuff in there too. OK I’ll admit that some of the details about Web services is old news now and was written for NAV 2009, and there’s nothing on the things that came with SP1 and R2, but it’s a good fun read and, who knows, you might learn a thing or two along the way.

Execute SQL Command using .NET Interop

David Roys — Wed, 13 Apr 2011 10:12:00 GMT

OK, I’ll admit it, I’m obsessed with the .NET Interop features in NAV 2009 R2. My last two blog posts have been on this and now here’s another one.

Today I wanted to see if I could use the .NET Interop features in NAV 2009 R2 to execute a SQL Stored Procedure and extract the results. I know you can do this using COM, but I figured it would be fun to try this using .NET. The great thing about .NET is it’s so well documented. My starting point for this exercise was to search for executing a SQL Stored Procedure in C# which gave me this post http://support.microsoft.com/kb/320916

I then used that to figure out which .NET classes I needed to write my C/AL code.

In this sample I’m using a SQL Command rather than a stored procedure call, but that’s just so you can try it for yourself easily without needing to create a stored procedure. It’s easy enough to change it to a stored procedure and I left the code in there but commented out.

The worst thing about using this stuff is the number of variables you need to define. I put this down to the fact that the .NET interop does not support multi-dotting where I can write things like foo.bar.thing(). I’m hoping we’ll get multi-dotting in a future version.

So for this to work, you’ll need to create a whole bunch of variables. Rather than list them here, I’ve included a text export of my Codeunit as a download.

The example is really simple. I select the names of the companies in the current database and display the Row Count and then the first 5 company names.

Here’s my code:

//Use the session table to find the database and server name for the current connection
Session.SETRANGE("My Session",TRUE);
Session.FINDFIRST;

//Open a connection using windows authentication to the current session database and server
dnSQLConnection := dnSQLConnection.SqlConnection('server='+Session."Host Name"+';'+
'database='+Session."Database Name"+';'+
'Trusted_Connection=sspi');

//Create a DataAdapter
dnSQLDataAdapter := dnSQLDataAdapter.SqlDataAdapter();

{
// Example of using a stored procedure and not a select string

//Create a SQL Command on our connection
dnSQLCommand := dnSQLCommand.SqlCommand('myStoredProc',dnSQLConnection);
//Set the command type as StoredProcedure.
dnSQLCommand.CommandType := 1;
}

//Create a SQL Command on our connection
dnSQLCommand := dnSQLCommand.SqlCommand('select * from Company',dnSQLConnection);

//Assign the SQL command as the select command on my data adapter.
dnSQLDataAdapter.SelectCommand := dnSQLCommand;

//Create a dataset
dnDataSet := dnDataSet.DataSet;

//Fill the dataset by executing my command on the data adapter.
dnSQLDataAdapter.Fill(dnDataSet);

//Get the collection of tables
dnDataTableCollection := dnDataSet.Tables;

//Get the first table
dnDataTable := dnDataTableCollection.Item(0);

//Get the collection of rows
dnDataRowCollection := dnDataTable.Rows;

MESSAGE('There are %1 Rows', dnDataRowCollection.Count);

currentRow := 0;

REPEAT
//Get the currentRow row
dnDataRow := dnDataRowCollection.Item(currentRow);
MESSAGE('%1',dnDataRow.Item(1));
currentRow += 1;
UNTIL (currentRow = 5) OR (currentRow = dnDataRowCollection.Count);

//Tidy up
dnSQLDataAdapter.Dispose();
dnSQLConnection.Close();

In a New Zealand demo database this gives the following messages:

You can download the Codeunit here.

.NET Interop—I’m Lovin’ It!

David Roys — Wed, 16 Feb 2011 08:33:37 GMT

If this was a blog about grammar, I might be asking whether a verb like “to love” can be conjugated in a progressive tense to give us the phrase “I’m Lovin’ It”. But it’s not, it’s a blog about Dynamics NAV and I’m writing about .NET Interop in Dynamics NAV 2009 R2 which has got to be the coolest feature for NAV ever!

Let’s assume that you’re into Dynamics NAV and not dynamic verbs. The question I’m really asking here is how do you find the temporary path in Dynamics NAV? Easy, I hear your shout. You just use ENVIRON(‘TEMP’);

Aha, but what if you’re using the RoleTailored client and you want to find the temp directory on the client? If you try using the ENVIRON command you’ll get the following error message:

Previously I solved this problem in quite a convoluted way. I created a temporary file (albeit an empty one) in the temporary path on the NAV Server, then I transferred it to the client using the magic path, then I found the path of the location of the file where it got transferred to.

Does that sound crazy? Take a look at the ClientTempFileName() function in the 3-Tier Automation Mgt. codeunit if you don’t believe me. I used that function to get a client temporary file and then used the Path function to find the client temporary file path.

But now with the .NET Interop there is a better way.

Simply create a local variable of type DotNet and Subtype 'mscorlib'.System.Environment. Make sure you bring up the properties of the variable and set the RunOnClient property to Yes.

Now you can write code like this:

IF ISSERVICETIER THEN
MESSAGE('Temp is %1',dnEnvironment.GetEnvironmentVariable('temp'))
ELSE
MESSAGE('Temp is %1',ENVIRON('temp'));
EXIT;

Aaah. I’m Lovin’ It!

P.S. If you’re more interested in grammar than Dynamics NAV, check out http://grammar.quickanddirtytips.com/im-loving-it-grammar.aspx to find out why McDonald’s slogan is such bad grammar.

Regular Expressions in NAV

David Roys — Wed, 02 Feb 2011 07:32:55 GMT

A friend of mine recently asked if there was a standard NAV function to find the first alphabetical character in a string. STRPOS comes close but you can't say "tell me the first position for any one of these characters in this string" because it looks for an exact match of the substring and not any character from the substring.

The AL code to do this isn't hard but I figured this was a perfect excuse to play with the new .NET Interop feature in NAV 2009 R2.

With the release of NAV 2009 R2, we have the ability to use the . NET Framework directly from within AL code. Now we can use simple .NET classes to do things with strings that previously required several lines of code. I'm thinking of regular expressions of course.

The regular expression for any character between A and Z or a and z is [A-Z,a-z].

I created a function in a Codeunit called RegExFind that takes two parameters (a string to be search and a string containing the regular expression to match on). It returns an Integer which is the matching position of the first matching character (it returns 0 if it doesn't find a match).

To run the code, you need to add the Codeunit to a RoleTailored MenuSuite as .NET Interop only works on the service tier. You could put this code in a button on a page too, if you wish.

Here is the function:

RegExFind(SearchString : Text[250];RegEx : Text[250]) : Integer

dnMatch := dnRegEx.Match(SearchString,RegEx);

IF dnMatch.Success THEN
EXIT(dnMatch.Index+1)
ELSE
EXIT(0);

In my Function I have the following local variables:

Name	DataType	Subtype
dnRegEx	DotNet	'System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Text.RegularExpressions.Regex
dnMatch	DotNet	'System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Text.RegularExpressions.Match

Notice that we return the Index +1 (as in .NET all arrays start at 0 where in AL they start at 1).

The code to call the function is shown here:

SearchString := '1234 Lines of Code';

FoundPos := RegExFind(SearchString,'[a-z,A-Z]');

IF FoundPos > 0 THEN
MESSAGE ('Found %1 at position %2 in %3',COPYSTR(SearchString,FoundPos,1),FoundPos,SearchString);

SearchString := 'Now I look for 1 or 2 numbers.';

FoundPos := RegExFind(SearchString,'[0-9]');

IF FoundPos > 0 THEN
MESSAGE ('Found %1 at position %2 in %3',COPYSTR(SearchString,FoundPos,1),FoundPos,SearchString);

As you would expect, when I run this Codeunit from my RoleTailored client, I get the following output:

The only problem with the new .NET Interop is knowing what's out there in the framework. The chances are, there's something to handle your requirement and the good news is there'll be plenty of examples of how to use it - just as long as you can translate the C# code into an AL equivalent.

This simple example is just the tip of the iceberg for what you can do with regular expressions. Wouldn't it be nice to use regular expressions to validate user input?

Have you got any favourite .NET tricks that you’d like to do in AL code?

Book Review: Microsoft Dynamics NAV Administration

David Roys — Fri, 31 Dec 2010 03:18:03 GMT

Authors: Sharan Oberoi, Amit Sachdev

Publisher: PACKT

ISBN: 978-1-84719-876-1

Published: September 2010

Once upon a time you couldn’t get books on Dynamics NAV for love nor money, then in October 2007 PACKT published David Studebaker’s book Programming Microsoft Dynamics NAV. I read David’s book and wrote a review (http://gaspodethewonderdog.blogspot.com/2007/12/book-review-programming-microsoft.html) and became inspired to co-write my own book on Dynamics NAV.

Things are very different now; there are loads of books on Dynamics NAV for everyone covering Programming, Implementing, Designing, Cooking, and now Administrating. OK so maybe not cooking, but there is a programming cookbook which I reviewed on this blog (http://www.teachmenav.com/blogs/dave/archive/2010/11/28/book-review-microsoft-dynamics-nav-2009-programming-cookbook.aspx).

With so many books around, you may be wondering what this new book from Canadian-based authors Sharan and Amit offers that can’t be found elsewhere. This is the first book on Dynamics NAV that appears to be aimed squarely at beginners. I can see how this book may appeal to someone who has landed a job with a company that uses Dynamics NAV and wants a quick guide to some of the capabilities of the product and a high level view of how to perform some admin tasks. The book covers essential tasks like creating new users and assigning security permissions, taking backups and restoring backups, and importing programming modifications from a FOB file delivered by a partner. This is all useful stuff for anyone working as a system administrator for NAV. There are only 175 pages (not including index and preface) spread over nine chapters with lots of screen images so it’s not going to take you long to read this.

According to the blurb on the PACKT site, “this book is a tutorial guide that illustrates the steps needed to install, configure, deploy, and administer Dynamics NAV”. Dynamics NAV is a very complex system and this book does not even begin to cover all of the various tasks needed to install and configure the system, but if you need to know how to run the install wizard, this book will show you how to do it and give you a bit more of an idea of what is happening. There are some fundamental things missing, like how to configure the Service Principal Names (SPNs) needed to allow the system to run on three separate machines (SQL Server, NAV Server, and Client); however, this topic is well covered in the MSDN Library (http://msdn.microsoft.com/en-us/library/dd301254.aspx?ppud=4).

There was an interesting section on virtualisation, but like the rest of the book, it doesn’t go in to a great amount of detail. I guess for an administrator, this lack of detail is a good thing as you don’t want to get bogged down with pages and pages of technicalities, but you’re not going to be able to fly solo on most of these topics without getting help from a partner or reading some of the other material available through Customer Source, the online help, or the MSDN library.

Coincidentally, I used to work with one of the authors, Sharan Oberoi, when he worked for Ernst and Young in New Zealand, so I feel I need to focus on the positive aspects of this book and not dwell on the areas where, for me, it fell short. At times I felt as though I was reading a giant product brochure from Microsoft about Dynamics NAV, but on a more positive note, this book provides a good introduction to a wide variety of topics that may be of interest to a systems administrator.

Is there anybody out there?

David Roys — Sat, 27 Nov 2010 22:07:22 GMT

Way way back in July I ran a competition to give away 2 Microsoft Visual Studio 2010 Ultimate with MSDN subscription cards. I said I would announce the winners in September, but didn’t. You see the thing is, the response was pathetic. Only one entry who is of course now the proud owner of an MSDN subscription worth several thousand dollars.

I’ve got one more subscription card still to give away, so the first person to tell me which Pink Floyd song includes the lyrics “Is there anybody out there?” gets it.

The 12-month MSDN subscription has some restrictions with it being a NFR version (technical support benefits and MSDN Magazine are not included, and all software benefits, including Microsoft Office 2010 products, are for development and test purposes only).

The subscriptions need to be registered within 180 days of when I received them (that means you have until around the end of December 2010 to register, but I won’t be responsible for you neglecting to register—if you win a prize, register it straight away).

Book Review: Microsoft Dynamics NAV 2009 Programming Cookbook

David Roys — Sat, 27 Nov 2010 21:34:18 GMT

Author: Matt Traxinger

Publisher: PACKT

ISBN: 978-1-84968-094-3

Published: October 2010

Long ago when I was co-writing Implementing Microsoft Dynamics NAV 2009 one of my colleagues said to me, “You don’t want to write a book like that, you want to write a NAV cookbook.” I must admit I hadn’t read any programming cookbooks and didn’t know what he was talking about so I just smiled and nodded reassuringly before totally ignoring him.

Then, lo and behold, along comes a request from PACKT asking me to do the technical reviewing of a new book being written by Matt Traxinger called Microsoft Dynamics NAV 2009 Programming Cookbook. Now if there’s one thing I like, it’s getting free stuff, and I remember from my authoring of Implementing that the technical reviewers get a free book or two. I also remembered that the work of the technical reviewers on our book helped us (me and Vjeko) immensely and so I looked forward to helping Matt out if I could. Today I received my shiny new copy of Matt’s book (free stuff is so cool, heh, heh, heh) and I figured it was time I wrote something about it on my blog.

So who is Matt Traxinger? I thought I recognised the name, but had never dealt with him directly as far as I was aware. He’s on dynamicsuser.net under the user name MattTrax and he’s made more than 500 posts. Chances are he’s helped me out more than a couple of times. If you want the bio from the book, you can read all about Matt here. I can tell you that after working on reviewing this book he seems like a really great guy and when it comes to knowing about NAV well…

This book is packed full of useful stuff. I’ve been doing NAV programming for quite a while now and I know a thing or two, but I found plenty in the book that I could use straight away in my day to day work. The book is divided into 112 recipes that go from the noddy stuff (like basic building blocks of programming NAV) to pretty advanced topics that require creating .NET controls and automations using Visual Studio. It seems like there is no problem too big for Matt. If NAV can’t do it, he rolls up his sleeves, cranks up Visual Studio and finds a solution. That was the thing I loved about this book. Matt has a great attitude to working with NAV and shows what can be done if you’re prepared to think outside the box.

I loved this book. There’s something in it for everyone. The recipes are fun-sized morsels of knowledge and you can dib in and out when you need to solve a particular problem, or you can start from the beginning and work your way through if you just want to learn as much as you can.

There are plenty of sources of knowledge on NAV now including various books, blogs, and online articles; if you’re a completely stingy git, you may prefer to spend your time trawling through forums and trying lots of things rather than shelling out for Matt’s book. I know I’d rather save my time and go straight to one reference where I can get an easy step-by-step guide on how to get past my current problem and then move on.

If you program NAV, you need this book. Well done Matt!

Creating a view across all companies–part 3

David Roys — Sun, 29 Aug 2010 02:31:22 GMT

This is the final part in a three part series of blog posts. In part 1 I wrote about how you could use a couple of fields in the $ndo$dbproperty table to create a ConvertInvalidChars() function that would turn your NAV company name into the prefix for the table name as it is used in the SQL database. Then in part 2 I showed you how to create a SQL script that would dynamically create your view for all companies that exist in the database. In this final part, I’m going to show you how to link the view to a NAV table and talk about some of the things to watch out for.

If you’ve followed the previous parts, you should now have a SQL view called Company Vendor that contains every field from the Vendor table in NAV plus a new field called Company Name that contains the company name.

Linked Tables

NAV has a neat property on the table definition that allows you to say a table is linked to a view in the database that has the same name as the table name. You can see this property by bringing up the properties for the table and looking at Linked Object. In the next image, you can see the property for my table 50001 is set to Yes. There is another property called LinkedInTransaction and the use of this has often been mysterious, I think because the online help definition is so poor and also because it is very rare for me to try to update the data in the linked object. According to a dynamicsuser.net post from Dean McCrae, when you have LinkedInTransaction set to No, NAV uses a separate connection and separate transaction for accessing the linked tables. This means that if your main connection rollsback due to an error, it does not affect the transaction performed for the linked object. The purpose of LinkedInTransaction is to make the transactions performed on the linked objects isolated from the main transaction. So it only matters if you plan to allow transactions such as inserts, updates and deletes on this linked object. In this example we are not allowing that.

Creating the Linked Table

I have found the quickest way to create my linked table is to edit my original table (in this case the Vendor table) and then edit the properties to change the ID, Name and set the LinkedObject property to Yes and DataPerCompany to no. I can then save my table. Since my view already exists, it all gets plumbed up correctly. I can now run my table and I see data from both of my companies – but unfortunately I can’t see the company name, and that is because I haven’t added the Company Name field. When I edit my table and add the new Company Name field, I come across the first of the problems with this simple technique. I get a message telling me that “The Record variable must belong to 23 and not to 50001”. The reason for this error is that there is programming logic attached to various triggers on the table that is expecting the table to be the Vendor table (23) and not our new Company Vendor table (50001). In short, you are going to need to remove all programming triggers from your table definition. You can do this easily by pressing Ctrl+S to save the table, the error message will be displayed and when you click OK, you will be taken to the code editing window. Now simply press Ctrl+Home to get to the start of the code, hold the Shift key down and press Ctrl+End so that all text in all programming triggers is selected. Then press delete. Finally, call up the Global variables window, open the Functions tab and select all of the functions, then delete. This will remove those functions that get left behind as empty functions when we deleted the code.

Gotchas

Although it was really easy for me to create my linked table by saving the existing table, there are a couple of things wrong with this. First of all, I need to remember to remove the programming code from the table, otherwise bad things could happen unexpectedly (such as data being updated in related tables on validates). The other issue relates to FlowFields. The system does not complain about the flowfield definitions in the table but you should be careful of these. The flow fields (such as Balance) are used to sum up the remaining amount on the Vendor Ledger entries for this Vendor, but the system does not know that the Vendor actually exists in a different company. If you are lucky, the flow fields will simply display 0. If you are unlucky there will be a Vendor with the same code in different companies and as a result, you will see a value that is related to the wrong company. My advice to you is to remove the flowfields and flowfilters from your table definition. On the vendor table this is 56 different fields. It’s at this point that I’m starting to wonder whether I actually saved any time by copying the existing table. It’s not actually that hard to go down the list of fields and delete the ones you don’t think you’ll need. You may find that when you delete certain fields from the table, the system complains that the fields are used in an active key. This is a good reason to delete the keys from the table definition too and make the primary key “Company Name”, “No.”.

Making the Page

My final step is to make a page over my new table. Remember to make the page non-editable and to say Insert Allowed, Delete Allowed and Modify Allowed = No. We don’t want someone accidentally deleting records from the view, because this will delete the actual records from the linked tables (and I have found that it doesn’t care what the underlying table is, even tables like the General Ledger Entry table which you wouldn’t normally be allowed to delete from using a regular user’s license). Here’s a picture of my final page showing how I can search across multiple companies for a specific vendor name.

Have fun with your linked tables.

Creating a view across all companies—part 2

David Roys — Sun, 22 Aug 2010 04:12:14 GMT

In last week’s post I wrote about how you could use a couple of fields in the $ndo$dbproperty table to create a ConvertInvalidChars() function that would turn your NAV company name into the prefix for the table name as it is used in the SQL database. This week we’re going to write some SQL that will create a dynamic select statement that we can use to create our view.

What is Dynamic SQL?

Dynamic SQL is when we use programming code to build up our SQL Select statement in a text variable and then execute that statement. In the case of our all company view, we don’t know how many companies we have or what they’re called, so we’re going to need to write something that will read the companies in the database and generate the correct SQL statement for us.

Here a simple example that shows that you can build up your SQL statement as a string and then use the EXEC() command to execute it.

DECLARE @SQLCommand AS VARCHAR(4000)     
    
SET @SQLCommand = 'SELECT Name FROM Company'     
    
EXEC( @SQLCommand)

The results of running this SQL is exactly the same as typing SELECT Name FROM Company into a query window and pressing F5.

Going Loopy with Cursors

Now we need to build up our SQL statement by looping through each of the companies in the database. To do this we’re going to use a cursor. A cursor is good because it allows you to fetch data back from the database one row at a time and then do something with the results. The downside to cursors is that they are slow (and this is one of the reasons that the performance of NAV is not as good as it could be as it makes extensive use of cursors when fetching data from the server).

In addition to using a cursor, we’re going to use the UNION ALL statement that will join the results from our select statements together into a single dataset – this is useful since we are trying to create a combined view, which means we need a single result set. Here’s the code:

DECLARE @CompanyName AS VARCHAR(30)     
DECLARE @SQLCommand AS VARCHAR(4000) = ''     
DECLARE mycur CURSOR FOR SELECT      
                                    Name     
                         FROM        
                                    Company;     
    
OPEN mycur     
    
FETCH mycur INTO @CompanyName     
    
WHILE @@FETCH_STATUS = 0     
BEGIN     
    IF @SQLCommand <> '' SET @SQLCommand += 'UNION ALL '     
     
    SET @SQLCommand += 'SELECT [Company Name] = ''' + @CompanyName + ''', * FROM [' + dbo.ConvertInvalidChars( @CompanyName) + '$Vendor] '     
     
    FETCH mycur INTO @CompanyName     
END;     
    
--EXEC(@SQLCommand)     
    
PRINT @SQLCommand     
    
CLOSE mycur     
    
DEALLOCATE mycur

As you can see I’ve commented out my EXEC command and replaced it with a PRINT command so we can see the SQL that gets generated in the message window. Here’s the SQL code it generates (obviously it doesn’t generate all of the formatting, I just inserted that to make it easier to follow the code):

SELECT      
           [Company Name] = 'CRONUS Australia Pty. Ltd.',     
           *     
FROM        
           [CRONUS Australia Pty_ Ltd_$Vendor]     
UNION ALL     
SELECT      
           [Company Name] = 'CRONUS New Zealand Ltd.',     
           *     
FROM        
           [CRONUS New Zealand Ltd_$Vendor]

If you copied that SQL statement into a query window, you’d see a dataset that contains all of the fields from our Vendor table for each of the companies with a new first column called “Company Name”. Obviously you’d need to change this if you had an actual field called “Company Name” in your table.

Making the View

The only thing left to do now is put in the CREATE VIEW AS code to create our view. I’m going to call my view Company Vendor. Here’s the final SQL that will create my view.

DECLARE @CompanyName AS VARCHAR(30)    
DECLARE @SQLCommand AS VARCHAR(4000) = ''    
DECLARE mycur CURSOR FOR SELECT      
                                    Name    
                         FROM        
                                    Company;    
    
OPEN mycur    
    
FETCH mycur INTO @CompanyName    
    
WHILE @@FETCH_STATUS = 0    
BEGIN    
    IF @SQLCommand <> '' SET @SQLCommand += 'UNION ALL '    
    ELSE SET @SQLCommand += 'CREATE VIEW [Company Vendor] AS '    
     
    SET @SQLCommand += 'SELECT [Company Name] = ''' + @CompanyName + ''', * FROM [' + dbo.ConvertInvalidChars( @CompanyName) + '$Vendor] '    
     
    FETCH mycur INTO @CompanyName    
END;    
    
EXEC( @SQLCommand)    
    
PRINT @SQLCommand    
    
CLOSE mycur    
    
DEALLOCATE mycur

Next week we'll look at the final steps of making this view available as a linked table in NAV and talk about some of the potential gotcha’s.

Creating a view across all companies–part 1

David Roys — Sun, 15 Aug 2010 00:31:11 GMT

The Linked Table

Have you ever wanted to search across multiple companies in a NAV database simultaneously? It's really not that difficult as long as you're using a SQL database. In this series of blog posts, I'll give you a step by step guide on how to create a combined vendor list. This principal can be applied to any of the tables in NAV. First of all, we need to cover some basics.

NAV has the facility to create a table definition that is linked to a view in the database that has the same name as the table. This can be a useful way to directly access data from other systems from within NAV, but it can also be useful for aggregating data that already exists in the NAV database. This could be combining data from multiple companies into a single view, as in this example, or could be combining G/L Entries and Dimensions into a single screen with the ability to search and filter on the dimensions. You could even use this technique to speed up slow processes by using SQL's powerful set-based queries to replace a lot of effort needed by NAV's much slower cursor-based operations.

To follow this example you'll need a sample NAV database that you can play in without breaking anything and access to SQL Management Studio.

Getting Rid of Invalid Characters

If you’ve ever looked at the table names in a SQL database for Dynamics NAV, you’ll have noticed that the table names have the company name at the start and that certain characters have been replaced. In the demo database for New Zealand, we have two companies: CRONUS Australia Pty. Ltd. and CRONUS New Zealand Ltd. If I look for the Vendor table in SQL I’ll see the following two table names:

CRONUS Australia Pty_ Ltd_$Vendor
CRONUS New Zealand Ltd_$Vendor

As you can see it’s basically the company name with a dollar sign followed by the table name, but some characters have been replaced. This is because when NAV creates the underlying SQL tables, it replaces some “invalid characters”. It does the same for the field names too.

In this first part of our series on creating a view across all companies, we’ll create a function that will help us find the correct company name as used as the prefix for NAV tables.

The characters that get replaced are stored in a field called invalididentifierchars in a system table [$ndo$dbproperty] (the square brackets aren’t actually part of the table name, they just make tell SQL to ignore funny characters like the dollar sign and any spaces that may be part of the table name). There’s another useful field in the [$ndo$dbproperty] table that we’ll need for this exercise called convertidentifiers. This tells the system whether we want to bother converting identifiers or not. You can change both of these fields by going to the Classic Client for SQL and selecting Database > Alter from the File menu. Click on the Integration tab and you’ll see the options.

We need to check to see if the Convert identifiers flag is set and, if it is, remove each of the characters from our company name and replace with an underscore.

I’m going to create a SQL Function called ConvertInvalidChars that will do this for me. Here’s the SQL code. You should execute this in the NAV database.

IF OBJECT_ID( N'dbo.ConvertInvalidChars',N'FN') IS NOT NULL DROP FUNCTION dbo.ConvertInvalidChars;     
GO     
CREATE FUNCTION dbo.ConvertInvalidChars     
(      
       @p_StringToConvert AS VARCHAR(250)     
)     
RETURNS     
VARCHAR(250)     
AS  
 BEGIN     
    DECLARE @InvalidIdentifierChars AS VARCHAR(128)     
    DECLARE @X AS INTEGER     
    DECLARE @ConvertIdentifiers AS INTEGER     
     
    SELECT      
               @ConvertIdentifiers = convertidentifiers,     
               @InvalidIdentifierChars = invalididentifierchars     
    FROM        
               [$ndo$dbproperty]     
     
    IF @ConvertIdentifiers <> 1 RETURN(@p_StringToConvert)     
     
    SET @X = 1     
     
    WHILE @X <= LEN( @InvalidIdentifierChars)     
    BEGIN     
        SET @p_StringToConvert = REPLACE( @p_StringToConvert,SUBSTRING( @InvalidIdentifierChars,@X,1),'_')     
         
        SET @X = @X + 1     
    END     
     
    RETURN(@p_StringToConvert)     
END     
GO

Testing our Function

That previous bit of SQL will give us a SQL function that we can use in our code later on. Here’s an example of a select statement that uses the function.

SELECT      
           Name,    
           [No Invalid Chars Name] = dbo.ConvertInvalidChars( Name),    
           [NAV Vendor Table Name] = '[' + dbo.ConvertInvalidChars( Name) + '$' + dbo.ConvertInvalidChars( 'Vendor') + ']'    
FROM        
           Company

and here’s the output from that select statement.

Name	No Invalid Chars Name	NAV Vendor Table Name
CRONUS Australia Pty. Ltd.	CRONUS Australia Pty_ Ltd_	[CRONUS Australia Pty_ Ltd_$Vendor]
CRONUS New Zealand Ltd.	CRONUS New Zealand Ltd_	[CRONUS New Zealand Ltd_$Vendor]

Hopefully you can see where I’m going with this. Next week, I’ll show you how we are going to use this function to build a SQL View for combining multiple company tables together.

You Could Win Visual Studio 2010 Ultimate + MSDN

David Roys — Sun, 25 Jul 2010 00:02:17 GMT

In order to drive awareness of the recent launch of Visual Studio 2010, Microsoft has given me 2 x Not for Resale (NFR) Visual Studio 2010 Ultimate with MSDN subscription cards to give away.

Take a look on the Visual Studio Product Site and you’ll see that to buy one of these subscriptions will set you back a lot of money—but I’m giving them away for free!

What do you need to do?

As you know, Microsoft Dynamics NAV 2009 SP1 introduced a new feature called Control Add-ins. These are .NET controls that let you do all kinds of stuff like display customer locations using Bing Maps, display Visio diagrams, capture signatures, display charts, have spell checking, edit HTML…all within the RoleTailored client. What a lot of stuff! The thing is, you’re only really limited by your imagination – these are just some examples of controls that have already been created. What control would you create?

I’m not asking you to build one of these controls to win the prize, I just want you to think of an idea for a control add-in and write about it. Write a Word document describing your concept and include pictures (mock-ups or sketches) of how you see it working. You can use any version of Word for your submission, but don’t use other formats because if I can’t read it, you can’t win. Also all submissions must be in English and if you write in Text Speak or some strange form of English that I can’t understand, you’re chances of winning are slim. You’re submission should be around two pages in Word with a picture or two.

How do you submit your entry?

The purpose of this competition is to get people thinking about custom controls for NAV 2009 SP1 and sharing their ideas. I have created a Forum for you to upload your ideas so they are then shared with everyone. Write a forum post giving a brief outline of your idea and you should be able to attach a Word document to the forum post. The Control Add-ins Forum is here. You need to be registered on TeachMeNAV to be able to make posts. I will need to send an e-mail to you about the prize if you win, so make sure you use a real e-mail address when you register. I’ll put in a sample posting so you know what I’m looking for. I’ll be searching for your idea online so don’t bother just posting someone else’s idea or blog post.

Closing Date and Terms and Conditions

I’ll leave the forum open until the end of August 2010. You must make your submission before then to be in to win.
Only submissions made through the Control Add-ins Forum will be considered.
I am going to pick the winners and my decision is final.
The Visual Studio 2010 MSDN subscription cards are the only prizes. There is no cash alternative and if for any reason your subscription card does not work, you’ll need to sort this out with Microsoft and not with me.
I’ll announce the winning entries before the end of September 2010.
The competition is not open to Dynamics NAV MVPs (sorry guys but you already have an MSDN subscription, so let’s give someone else a chance).
By submitting your ideas, they are shared with the world. If someone decides to build your idea, good luck to them.

What’s the catch?

There’s no catch, but there are some conditions and limitations surrounding the prize.

Resources

The official Microsoft documentation about add-ins online at http://msdn.microsoft.com/en-us/library/dd983700.aspx

Freddy Kristiansen’s Blog http://blogs.msdn.com/b/freddyk/archive/2009/06/07/integration-to-virtual-earth-part-4-of-4.aspx

Christian Abeln’s blog on Add-ins http://blogs.msdn.com/b/cabeln/archive/2009/05/06/add-ins-for-the-roletailored-client-of-microsoft-dynamicsnav-2009-sp1-part1.aspx

Action Images

David Roys — Tue, 22 Jun 2010 10:55:00 GMT

When you want to attach an image to an Action in NAV 2009, you need to pick from one of the standard images and use it to set the Image property. There’s a library with all of the 2009 and 2009 SP1 images on MSDN (http://msdn.microsoft.com/en-us/library/dd568728.aspx). If you don’t know about Action Images, take a look at How to: Set an Image on an Action on MSDN.

There’s a saying that necessity is the mother of invention, and for me that is certainly true. I needed to find an image to use on a new action and it took quite a while to scroll through the list of 310 images, trying to find one that looked vaguely like the action I had created.

I figured it would be good if instead of scrolling through the entire list, I could search in a document of some kind. I started by cutting out each of the images from the MSDN library image and saving as an individual file with a name of the action image property value.

Next I loaded these into an Action Images Media Gallery on this site and attached meta-data tags that would make it easier to search. Below is a screen shot of the Tag Cloud for all 310 images.

Here’s an example of how it works. Let’s say I’ve created an action for adding reminders. I can look through the tags and see there’s an Alarm Bell tag. I click that and see the following images.

So there are 5 to choose from. I think I’d pick the “CreateReminders” that has the alarm and the sparkle. The Tag Cloud in the side bar shows all tags, but I'm struggling to get the view more link at the bottom of the cloud to show all of the tags.

This took a ridiculous amount of time to complete. I hope you find it as useful as I do. Oh and the link to the gallery? Here it is Action Image Tag Cloud.

Microsoft Dynamics NAV 2009 Application Design

David Roys — Tue, 01 Jun 2010 10:10:00 GMT

I found out today that Mark Brummel (Dynamics NAV MVP and blogger) has written a book on Dynamics NAV 2009. You can read about it on his blog post here, and you can pre-order from the PACKT web site.

The one thing I did notice was that the PACKT web site has undergone a significant redesign and looks really sharp. You should go an take a look.

Here is some blurb from the PACKT site.

What you will learn from this book :

Implement Microsoft Dynamics NAV ERP suite with a sample industry application throughout the book
Set up Dynamics NAV and customize it for various industries including fashion, retail, and the automobile industry
Get to grips with key Dynamics NAV features such as Inventory Valuation, Item Tracking, and Reservations
Learn about B2B and B2C Interfacing and the fundamentals of Application Design
Learn and customize application features designed by Microsoft such as Financial Management, CRM, Manufacturing, Distribution / Wholesale, and Retail and extend them safely
Design your applications to have a good balance between cost of ownership and functionality
Analyze operation data based on sales demographics using Dynamics NAV CRM
Extend your core applications using interfaces such as Flatfile, CSV, XMLports, ADO, EDI standards, and web services

Approach

This book is a tutorial in an easy-to-read style. It will show Dynamics NAV developers how to create applications of different kinds with sufficient examples throughout.

Who this book is written for

If you are a NAV consultant and developer, or designer of business applications you will benefit most from this book.

The book assumes that you have a basic understanding of business management systems and application development, with a working knowledge of Microsoft Dynamics NAV.

Well done to Mark for such a huge effort. I look forward to reading it.

Dropbox Rocks

David Roys — Sun, 04 Apr 2010 20:21:46 GMT

I’ve been using Dropbox to help with my writing. It’s a great tool and I think anyone that’s writing for a hobby should have it in their kitbag.

They sent me a link that helps people sign up that will give me more storage if you follow it (that’s what the referrals part at the end of the link is all about). It’s totally free and you get 2GB storage and backups for no charge. You can of course pay for more storage if you need it. Here’s the link…

https://www.dropbox.com/referrals/NTQ5NjE3MDc5

So what is Dropbox anyway?

It’s an online storage area that means you can easily access your writing from any computer.

A program installed on my computer allows me to work with files in a folder (nothing special, you just nominate which folder will be your Dropbox folder) then whenever you save to this folder or drag files into it, the file gets uploaded to the Cloud storage in the background. It’s that easy!

You can have Dropbox installed on many computers accessing the same account or you can access the files through the Web interface. I have it installed on my home and work computer and on my iPhone, which means that I can work on the same documents on any of these devices and don’t need to worry about keeping different versions in sync.

About This Report – No need to run reports twice

David Roys — Sun, 14 Mar 2010 03:18:42 GMT

It's been a while since I last made a post and there are a few reasons. Bejweled Blitz on the iPhone is a big part, but also, it seems that there has been a real period of consolidation for me just recently: taking a lot of the things I learnt and applying them on projects, so not a lot of new stuff to share, just lots of time-consuming work. That's the excuses for not blogging out of the way, now on with the post.

I came across a top tip yesterday courtesy of a work colleague and felt it was simply too good not to share. If you do any report development in NAV 2009, keep reading.

One of the mysteries of working reports in NAV 2009 is understanding what is in the dataset when the reporting services client renders the report. In NAV 2009 we had the undocumented Ctrl+Shift+Alt+F12 key press which you could use when previewing the report. This key press opens a window with a list containing the entire dataset, allowing you to copy and paste into Excel. This is great for figuring out how you should be sorting and grouping your dataset for the required results.

Then when SP1 for NAV 2009 came out we got the Ctrl+Alt+F1 (About This Report) key press that could be used to do the same, but for reasons that I can only assume are related to the way data is paged on the client, the first time you select the option after previewing the report you get told you need to run the report again and select the About This Report option again. This need to repeat your actions in order to get your goal is mildly annoying. This is where the top tip comes in. When your options page for the report is displayed, select Ctrl+Alt+F1, About This Page. Now when you preview the report you can press Ctrl+Alt+F1 and see the full dataset and you don’t need to run the report again. It's not perfect but better than having to run the report twice.

So what would be a better way of doing this? I would suggest a setting in the config file that says keep dataset for reports. This way, developers could have this setting on and end users could leave it switched off.

NAV 2009 Server Starts and then Stops

David Roys — Wed, 20 Jan 2010 00:31:38 GMT

Have you ever tried to create a NAV 2009 Server service that runs under a domain account and when you try to start it, the service starts and them immediately stops again.

Then, if you look in the Windows Event Log for the error message you see something like this…

The service MicrosoftDynamicsNavServer$DynamicsNAV failed to start. This could be caused by a configuration error. Detailed error information:System.ServiceModel.CommunicationException: The service endpoint failed to listen on the URI 'net.tcp://vm-dev-100118.rad.intergen.org.nz:7046/DynamicsNAV/Service' because access was denied. Verify that the current user is granted access in the appropriate allowedAccounts section of SMSvcHost.exe.config. ---> System.ComponentModel.Win32Exception: Access is denied

I’ve had this a few times and my sledgehammer solution has been to make the domain account that is running the service a member of the local machine Administrators group. OK, I realise this is overkill but it works.

Today I finally took the time to figure out the correct way of granting the permissions to the domain account to allow it to listen on the specified port.

First of all we need to know the SID for the domain account. There may be other ways to find this out, but here is a suggestion that works:

Start a command prompt running under the context of the user you want to know the SID for by typing runas /user:domain\nav2009nstuser cmd.exe in a command prompt where domain is your domain and nav2009nstuser is the account that is running the NAV Server Tier.
The system will prompt for the nav2009nstuser password.
A new command prompt opens and you will notice in the title of the window it says cmd.exe (running as domain\nav2009nstuser).
In this new command window type whoami /user
You will see the SID of the user account. Copy this as you will need it in the next step.

These instructions are meant to work on Windows 7, so apologies if some of these things don’t work for you.

Now you need to edit the C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\SMSvcHost.exe.config file or the C:\Windows\Microsoft.NET\Framework64\v3.0\Windows Communication Foundation\SMSvcHost.exe.config file.

The file includes a <runtime> element and beneath that a commented out section that shows what to include for the net.tcp permissions we want to add. Insert the following section below the </runtime> closing tag and before the start of the comment as so that part of the file looks like this…

</runtime>
<system.serviceModel.activation>
    <net.tcp listenBacklog="10" maxPendingConnections="100" maxPendingAccepts="2" receiveTimeout="00:00:10" teredoEnabled="false">
        <allowAccounts>
            <add securityIdentifier="S-1-1-1 this is your SID"/>
        </allowAccounts>
    </net.tcp>
</system.serviceModel.activation>
<!-- Below are some sample config settings:

replacing S-1-1-1 this is your SID with the correct SID you found in the previous step.

Now reboot the machine (restarting the service is not enough).

Your service should now start correctly. Thanks to Dominick Baier who posted on http://social.msdn.microsoft.com/Forums/en-US/wcf/thread/b1cc46af-a70d-4793-b5fe-b61450ef5387 which gave me the info I needed to get this working.

The specified path is invalid. When using UPLOADINTOSTREAM.

David Roys — Fri, 01 Jan 2010 02:16:21 GMT

I recently tried using UPLOADINTOSTREAM to load a file from the RoleTailored client into a temporary file on the NAV Server so that I could then process it. This is needed in NAV 2009 because it has three tiers and the tier on which the code executes does not necessarily have access to the files that are accessible on the client. Think about it, you’re c: drive is not the same place as the c: drive on the server.

I tried using the F5 C/AL Symbol Menu to paste the function call with its arguments because, to be frank, I can never remember stuff like this. This pasted the following line of code:

[Ok :=] UPLOADINTOSTREAM(DialogTitle, FromFolder, FromFilter, FromFile, NVInStream)

I then replaced the parameters with the appropriate text strings and variables which gave me this:

UPLOADINTOSTREAM('Profile Metadata', '', '*.XML', FileName, InStream);

FileName and InStream variables of type Text 1024 and InStream respectively.

When I tried to run this command I kept getting this error message:

Microsoft Dynamics NAV
---------------------------

The specified path is invalid.
---------------------------
OK
---------------------------

I couldn’t figure out why I was getting this error, but it turns out using an incorrect value in the FromFilter was causing the problem.

When I changed my line of code to be more like the example in the online help it all worked fine:

UPLOADINTOSTREAM('Profile Metadata', '', 'XML File *.XML| *.XML', FileName, InStream);

I searched for the error message and UPLOADINTOSTREAM in order to find the answer, but found nothing, hence the reason for this blog post.

Oh, and Happy New Year!

Book Review: Programming Microsoft Dynamics NAV 2009

David Roys — Sun, 20 Dec 2009 22:53:45 GMT

Author: David Studebaker

Publisher: PACKT

ISBN: 978-1-847196-52-1

Published: November 2009

Available from the PACKT web site and Amazon.com

When I saw that David Studebaker had written a new book on Programming Microsoft Dynamics NAV 2009, the first thing I wanted to know was whether this is a completely new book or a tarted-up version of his previous book which I reviewed in December 2007.

Now that I have finished reading it (and it’s a whopping 559 pages in 9 chapters) I can tell you that it’s a bit of both. There is a lot of new content in this book and the programming scenario that flows throughout the book has been completely redone and is, in my opinion, much improved. The whole book has a fresh new approach but I kept getting a sense of déjà lu. The ‘read it before’ feeling is not surprising: this is a re-write and if you open this book and the previous one side-by-side, you’ll see a lot of the content is fundamentally the same. That’s fair enough, there’s only so much you can write on the subject of programming NAV and the re-writing and corrections has improved the book greatly.

Each chapter finishes with a pop quiz with a number of carefully considered questions to help you determine whether you have grasped the basic concepts. The book is much easier to read than the previous version, and there are considerably less mistakes (I still managed to find a few although the majority were proofing errors rather than programming errors).

Sadly for me, the chapter on Forms has been re-written as a chapter on Pages which means this book is no longer a complete introduction to NAV programming and if you want to learn about creating Forms, we are told to buy the other book as well (or if you’re on a budget, you could consider reading the sample chapter of my book instead). There is no mention of Form transformation, which for me is a large part of NAV 2009 development. At the moment, the easiest way to develop and maintain NAV 2009 is to create Forms and use the Form Transformation tool to create the pages. This book is not going to help you do that, although you will get a good understanding of what pages are and what you can do with them. If you want some help with the more complex areas, such as Page Transformation, advanced Report Transformation or Matrix Pages, you should consider Rene Gayer’s book.

When I wrote my original review of Programming Microsoft Dynamics NAV two years ago, I said that this was the only book on programming NAV and that is reason enough to buy it. There are now two other books on NAV 2009, Rene’s book and the one I co-authored with Vjeko. There is Microsoft Dynamics NAV 2009 Developer and IT Pro Documentation available online at msdn and there are now some amazing blogs that cover advanced topics such as creating Web service-consuming applications and RTC Client Add-ins, so the question remains, should you buy this book?

If you are new to programming NAV and have not read David’s previous book, then this is a great book for you and you will learn a lot from it. Definitely buy it.

If you have read David’s previous book and are now looking for a guide to what’s new in NAV 2009 you should probably consider other sources that will give you the overview of what’s new with a lot more new content (such as my book, Rene’s book or the online articles and blogs).

Postscript for the E-book Enthusiasts

As part of my review of this book, I wanted to try reading the e-book version so that I could compare that to the printed copy. Printed books still have a lot of advantages over e-books, although I think you should consider buying both if you can afford it. This gives you the ease and comfort of reading a paper book with the convenience of being able to search the contents for a specific topic. There are some good deals when buying both the e-book and the p-book from the Packt web site, although it’s a shame you don’t get the e-book for free when you buy the printed copy. I first tried reading the e-book using my Sony PRS-505 Reader http://www.sony.co.uk/hub/reader-ebook . This uses electronic ink to display the text which provides a close-to-paper reading experience. The PDF document format is not ideally suited to the PRS-505. The large white space borders mean the text is tiny even if you read it in split-page landscape mode. You can reflow the text to increase the text size but this spoils the layout. The book is also so large that re-flowing it locked my reader for over 20 minutes. Next I tried using GoodReader (http://www.goodreader.net/goodreader.html) on my Apple iPhone to read the book. This was great. GoodReader lets you zoom the display to get rid of the large borders and then lock the horizontal scroll making it easy to flick through the text. I actually read quite a bit of the book like this and I found the text clear and the images crisp. If you have an iPhone or iPod touch, you should seriously consider this as an option.

Sample Book Chapters

David Roys — Fri, 27 Nov 2009 05:31:00 GMT

I wanted to find something from Chapter 7 of my book today and I realised that I hadn't posted links to the sample chapters. There are two sample chapters available. Chapter 7 is presented in two articles on the PACKT article network and Chapter 6 is available as a PDF download directly from the PACKT web site and also from MIBUSO.

Chapter 6: Modifying the System (PDF)

Chapter 7 Part 1: Extending the Application (part 1 as an online article)

Chapter 7 Part 2: Extending the Application (part 2 as an online article)

Where did my xRec go?

David Roys — Sun, 01 Nov 2009 22:08:19 GMT

In the RoleTailored client on a Page, the xRec does not contain a record when you are inserting new records into a sub page. Previously if you wanted to know where you had pressed F3 to insert a line you could take a look at xRec and would know which line you were on when you pressed F3.

Unfortunately in the RTC the xRec is completely blank (when checking in the field validation triggers).

Fortunately the xRec is not blank when it hits the onNewRecord() trigger so you can have a global variable on your form (I called mine g_MyXRec) and then assign it in that trigger, all is OK again.

Here is an example of the OnNewRecord in the Page 46 Sales Order Subform…

Type := xRec.Type;
CLEAR(ShortcutDimCode);
// My code added to allow me to see xRec -->
g_MyXRec := xRec;

With this code in place, I can now use g_MyXRec to see which record I was on when I pressed Ctrl+Ins.

Custom Control for Reporting Services Reports

David Roys — Mon, 19 Oct 2009 09:52:00 GMT

I recently presented a session at Intergen Dynamics Day called Dynamics NAV 2009 – a “deep dive” in which I showed a custom control that displayed a Graph depicting the Aged Debt for a customer. In this post, I’ll tell you a little bit about how I created this control, how to use it, and of course provide a link to the downloads. The control is free for your own use but as usual it is provided without support or warranty. Here is an example of the control linking to the Customer List (there's no sound on this video but I think it illustrates the control better than a picture).

(Please visit the site to view this media)

The control is based upon Freddy Kristiansen’s blog post and sample project for integration to Virtual Earth. In Freddy’s post, he creates a custom control that uses a browser to display a web page that he modifies dynamically to show the Bing Map location of a customer. This made me think that I could pinch Freddy’s code and with a few little tweaks, create a custom control that can display any URL in a mini-browser. Initially I was going to display a PDF, as I wanted to render a NAV 2009 report as a PDF and then display it in the browser control, but I couldn’t find a way to display the PDF without the Adobe Toolbar. Then I remembered I could create a Reporting Services report and use the ReportServer to display the report with no toolbar. Perfect!

I’m amazed at not only how powerful these new custom controls are, but also how easy it was to create one (thanks to Freddy’s sample).

I created the control in the VPC for NAV 2009 SP1 that can be downloaded from PartnerSource. To use this control yourself, you first need to create the stored procedure sp_CustAgingBand. This stored procedure takes a Customer No., the Company Name (as it is converted in SQL) and the date at which the graph should be based (since it is an aged debt graph we need a date).

Next, you need to deploy the Reporting Services report. You can do this by opening the Dynamics Day Reports solution and deploying to the http://nav-srv-01/ReportServer that you’ll find on the VPC image.

Then copy the URLControl.dll to the RoleTailored Client\Add-ins\ directory, and register it with a public key token of a1b0b65d07f64054. I called mine SmallURLControl.

Finally, I created a new Page called Customer Aged Debt Factbox (Page 90000) that uses the custom control and modified the Customer List page (page 22) to include my new Fact Box.

I’ve deliberately kept this post short. If you want to know about any of these parts, leave me a comment and I can make a more detailed post at a later date.

You can download the objects from here.