Gaspode's Brain Dump

Goodbye World

2009-07-11T11:05:00.004+12:00

After 2 and bit years, it's time to say goodbye to Gaspode's Brain Dump. I'm not going to stop blogging (you can't get rid of me that easily). Instead I will be moving my blogging to the site I created to support my book www.teachmenav.com.

My new blog URL is http://www.teachmenav.com/blogs/dave/default.aspx

Thank you to everyone that has kept reading this site and has kept me blogging here over the years. I am hoping you will re-direct your feeds and favourites to the new site.

So why, you may ask, am I moving the blog. Well the thing is, I find the blogger blog editor a real pain to use. Uploading images is really too hard and I find myself having to write the post in HTML because I just can't get the layout right. With the new site, I am able to use Microsoft Live Writer to write my posts off line and can then simply post when I have finished. I can also re-post to my blog on dynamicsuser.net with just a couple of button clicks.

Since the new site is using Community Server, I am able to add Wikis, downloads and all sorts of other stuff.

Goodbye and hopefully, you'll follow my NAV exploits on http://www.teachmenav.com/blogs/dave/default.aspx

Dave.

Service Pack 1 (SP1) Marketing Beta VPC is now available for Microsoft Dynamics NAV 2009

2009-06-16T09:51:00.001+12:00

Take a look on PartnerSource and enjoy. https://mbs.microsoft.com/partnersource/newsevents/news/mdnavsp1mktgbetavpc.htm?printpage=false

Help is on the way

2009-06-01T20:49:00.000+12:00

I finally got chance to finish reading through the Microsoft Dynamics NAV Statement of Direction I blogged about last week in my post on the NAV 2009 service pack 1. There’s a lot of marketing waffle in there, as you would expect, but also quite a few interesting things to look forward to.

The online help in NAV is something I am quite passionate about, and have looked forward to the day when help can be easily modified. This section from the statement of direction caught my eye:

“We will reduce the cost to implement custom Help by eliminating CHMs as the delivery format for Help. We will provide mechanisms for partners to extend Microsoft Dynamics NAV Help at a low cost, by integrating Help authoring into the Microsoft Dynamics NAV development process, and by using Microsoft Word, HTML, and Microsoft SharePoint. We will make it easy to extend Help with content on a partner Website.”

Now that sounds pretty exciting. Editing help in Word? Deploying via SharePoint? Cool!

The Amazing World of Blogging

2009-05-26T22:00:00.003+12:00

I got a comment on a blog post tonight. That's pretty rare. But what's truly amazing is that the blog post was made in April 2007 -- over 2 years ago! It was my third ever blog post.

I guess this just emphasises that you never know who is reading what you write. Thank you to that annonymous commenter from Poland that decided to share their thoughts on that post. It really made my day.

The blog post was http://gaspodethewonderdog.blogspot.com/2007/04/goodnight-kiwi.html

Dynamics NAV 2009 SP1 Preview

2009-05-24T20:06:00.004+12:00

I love the Microsoft Dynamics NAV 2009 RoleTailored client (RTC) but there are those that are less enamoured with it. Some experienced NAV consultants, for instance, find it difficult to use because some of the most useful features of the old system were not implemented in the RTC. Thankfully the first NAV 2009 service pack (SP1) is on the horizon and, amongst other things, this service pack will re-introduce some of the functionality that we have so far struggled without.

The recently released Statement of Direction contains details of what to expect in this service pack.

"Microsoft Dynamics NAV 2009 Service Pack 1 (SP1) builds on all the innovations that were introduced with Microsoft Dynamics NAV 2009. With Microsoft Dynamics NAV 2009 SP1 we are continuing to improve productivity for customers and partners. We want to ensure an easier upgrade to the new RoleTailored user experience for Microsoft Dynamics NAV customers working on previous versions of Microsoft Dynamics NAV as well as adding the innovative Client Extensibility for both existing and new customers."

As an MVP I was fortunate enough to get my hands on the first community technology preview (CTP1) of SP1 and I like what I've seen. The statement of direction promises a release date of Q3 2009. If the guys at Microsoft are consistent with their previous release approach, we should be seeing a public beta made available to all partners in the next month or so. That would be great news; there are many partners that want to see what is coming as soon as possible and making beta and CTP releases available to partners is a smart move that can only help to ensure a higher quality in the final release.

The RoleTailored client has been with us for a while now and if I had to pick a “killer feature”, it would have to be the ability to personalise the application. The personalisation features in NAV 2009 RTC are pretty stunning and allow end users an unprecedented ability to fiddle: hiding fields, showing fields, promoting actions, adding reports, adding charts, and many more possibilities. You could spend more time making the system look "just right", rather than getting on with the more boring aspects of using the system (like doing your work). But what about SP1, are there any killer features in this release? Hell yes!

Page Search

The RoleTailored client allows the features that are needed for a specific role to be made readily available to the end user, but what about those features that are used less frequently? For those, you need to enter the "Departments Place": a labyrinth of options, links, groups and submenus. The first page of the Departments Place should have the text " Abandon hope all ye who enter here" in fiery letters across the top of the screen. I am convinced that there is a clever algorithm that will move options to a new location if you are finding them too easily. For me, SP1 is going to make the Departments Place a place I never go to, because I will be able to search for the pages I want to run and let the system do the work for me.

In January I started to work on a menu search feature that I have long desired in NAV and I made a couple of blog posts on my progress. Then I heard that this feature was to be included in SP1 and so I decided to wait. The new feature is superb. Let's say I want to find the Business Units

I can type into the search pages box at the top right and as I type, options are displayed in a drop down results list. As you can see here, the Business Units is found and I can either click on the left to open the list place, or click on the right to open the departments page that contains the option. This feature is worth SP1 on its own. In fact, I think this feature is reason enough to upgrade to NAV 2009.

Saved Views

Next on my list of goodies, is this save view feature. The Role Centre is the home page for NAV 2009 users in the RTC and in the home pane on the left, you can see filtered views of entities. This include such things as Sales Orders that open, ready to ship, delayed, and so on. Now the end users can create their own filtered lists by simply clicking the Sorting and Filtering activity button and selecting the “Save View As” option. Nice!

The result is a new filtered list in the navigation pane. Previously it was only possible to create these filtered views through programming.

Other Stuff

There are many other things promised, not all of which were available in CTP1. You can read the statement of direction if you want to know all of the new features and there are lots, but you will be pleased to know that we can expect those missing data entry features such as in-cell calculator, copy previous value, copy cell value, filter on current field, next record and previous record on task pages. In addition we are promised improved Matrix Pages and the good old Zoom feature is back.

I've always been one of those people that looks forward to what’s coming next and as a result I sometimes forget how good the NAV 2009 release is. Now I'm going to go back to the statement of direction and drool over the promised features in NAV "7".

Spot the Difference

2009-05-13T22:28:00.005+12:00

OK – can anyone tell me what’s unusual about this image?

Differences between RTC and Classic Client

2009-05-03T20:52:00.001+12:00

Probably the biggest headache when developing for Microsoft Dynamics NAV 2009 is when you find different behaviour between the same code executing in different clients. I have experienced this twice so far and both times took a little sorting out.

The first issue was when I tried to import a FOB file that referenced a DLL. The DLL had not been registered on the machine on which I made the import and, prior to NAV 2009, this was not a problem. If you did not have a particular DLL, you would not receive an error until you tried to access the DLL at run time or tried to recompile the object. The FOB files are shipped pre-compiled so there was no need to have the DLL registered on machine just to be able to import the objects.

What I found in NAV 2009 was that although the object imported without errors, an additional message reported that there were problems with one of the objects (can't remember the exact message) but basically what was happening was that once the objects had been imported, the system was attempting to create the C# code to be used by the Service Tier. Since the DLL being referenced could not be found, the C# code did not get created. As a result, when a part of the system tried to access some code in the Codeunit, a run time error occurred – but only on the RoleTailored client (since it relies upon the C# version of the code) the Classic client worked fine. As a solution, we made a point of ensuring that any DLLs required by the system were registered on a computer that was to be used for importing FOBs.

The second problem I came across was as a result of a slight difference in the handling of Option type fields. In the Classic client, if you were to delete the contents of an option field on a form (thus making it an empty string), the system would insert the first option value. Often the first option value is a single space. If you were to do the same on the RoleTailored client, you would get an error message telling you that blank is not a valid option and you would be reminded what the valid options were. This can be quite confusing and you need to remember to type a single space in order to "blank" the field. I've learnt to live with this, but at the end of the week I discovered that this problem can be quite confusing when you hit it from code. One of the programming samples in our book that we provided for download was the ability to use the Data Migration tool to import Dimensions as well data. This has proved quite useful, but I found that when I was extending the tool to work with Document Dimensions (so I could import documents and not just master data) was getting a strange error that I did not get in the classic client. The problem was that I had some code that was trying to pull an option value from an XML document and it was getting an empty string, since the node could not be found. You can probably guess where this is going. The evaluation of the empty string resulted in perfect execution in the classic client but a run time error in the RoleTailored client telling me that the option was not valid. Since this problem only occurred on the RoleTailored client, I was forced to try out the debugging on the service tier (I followed the instruction Vjeko wrote in our book) and it helped me to track down exactly what was going on. It was then fairly straight forward to only evaluate the field value if the text from the XML node actually contained a value. I quite enjoyed debugging in Visual Studio. It was nice to have a debugger that allowed me to step into, over and out of code, as well as being reliable and predictable.

NAV 2009 Performance Guide is here

2009-04-24T22:33:00.002+12:00

The long awaited NAV 2009 Performance Guide is here. You can download it from the mbs.microsot.com site.

You should download the document and read it to draw your own conclusions from the tests that were performed. I found the forty-page document quite heavy going with a lot of technical results (that I didn't fully understand) and some limited conclusions of the analysis. I did find the summaries useful, it's just that I could have used more summary and less data.

One interesting conclusion, for me, was that a single NAV Server appears to be able to comfortably handle 50 concurrent users. This was based upon the NAV Server application, being a 32-bit application that can work with 3GB of RAM or less, showing a 1.7GB memory consumption for 50 concurrent users. Obviously this is dependent on the type of hardware you are using and the scenarios your users are likely to be running. The document contains sufficient disclaimers on the findings so, as is often the nature of benchmarking results, this information is more of a guideline than a rule.

With respect to virtualisation, the team do not recommend using a Hyper-V virtualised environment for production use due to the reduced performance.

Personally I found that this document fell short of my expectations. It is clear that the team have put a lot of effort into their studies; however, I would like to see a guide to sizing hardware that is easy to use and understand. It would be nice if we could have some recommendations that answer the following questions:

What is the maximum number of users?
At what point should I separate the tiers on to three machines over two?
How many users should I be running per NAV Server?

I would also like to see the scenarios run in the Classic architecture to see what performance benefits we get from the three-tier architecture.

My New Toy - Sony PRS-505

2009-04-18T11:21:00.004+12:00

I’ve tried to read electronic books (ebooks) in the past but have always preferred paper books (pbooks). As my good friend Vjeko said, “I like reading lying down - and I most certainly don't like the feel of laptop lid hitting my forehead when I fall asleep :-)”

Having said all of that, I have bought a new toy that has changed my reading world and has convinced me that, whilst the pbook will probably never die out completely, the age of the ebook seems to almost upon us. The new generation of readers are become cheaper and more accessible and more and more publishers are getting their books out in ebook formats.

There are a lot of reading devices available, and if you’re interested in learning about them, I can recommend the forum http://www.mobileread.com/. I spent a while looking at the various devices on this site before a forum member offered to sell me a Sony PRS-505 second hand. I have now had the device for nearly two weeks and have finished reading “Simple Genius” by David Baldacci. David’s book is great and I really enjoyed it – he certainly is a very talented author, however I enjoyed reading the book even more because I was reading on my new reader.

If you’ve read ebooks on a phone or PDA or laptop, you’ll know that it can be a frustrating experience. The back-light on these devices can make your eyes tired. Laptops have a tendency to get hot and take a while to boot – not to mention the rate they chew through batteries. The Sony PRS-505 on the other hand does not use a traditional LCD display but instead using a technology called e-ink that has a similar effect to reading a glossy magazine it only uses power to change a page which means you will be able to read for hours and hours on a single charge. The internal storage and memory cards mean you can carry hundreds of books with you.

Incidentally, you can buy a PDF format e-book version of Implementing Microsoft Dynamics NAV 2009 from the PACKT web site. I’m looking forward to future devices that promise larger displays (good for reading A4 PDF documents) such as the Plastic Logic Reader, but for now, I’m really pleased with my new toy.

’sup dog?

2009-03-15T22:18:00.001+13:00

Well, it’s been a while since I last posted. I’ve been working on a new NAV 2009 implementation which has been keeping me very busy. I’ve battled with Kerberos delegation on a three-tiered architecture. I’ve got to grips with the form transformation tool and the transformation of MenuSuites. It’s been really good fun and I’ll share some of what I’ve learnt in some later posts. I definitely want to make a post on the Kerberos stuff because I really battled with it – but for now, you can be assured that the Installation guide on MSDN is right and although it seems a bit weird what you need to go through, it does work.

The Implementing Microsoft Dynamics NAV 2009 book seems to be doing well and I’ve finished reading it – am I the first person to read this? There are no reviews out there yet and I can’t really review my own book can I? It’s really quite good and I’m very pleased with the end result. I’m now working on getting a decent www.teachmenav.com site up and running so we can put up the downloads and links and maybe get a bit of discussion going around the topics we touched on in the book. We’ve put something up there that looks pretty and re-directs to other sites, but I want to make a mini-forum based on Community Server.

This weekend I’ve installed two pieces of software from two companies that are often portrayed as arch-enemies: Microsoft and Apple. On the Microsoft front I installed the new Windows Live tools including Windows Live Writer that I think has to be the best blogging tool I have ever come across. I’m very impressed where things are going with Microsoft and the quality of their software really blows my socks off. Windows 7 looks good, IE8 looks good. The Live services are getting to be pretty awesome.

And then on to Apple. Grrrr! I didn’t want to upgrade my iTunes, but it seemed to install some kind of Genius thing that seemed to corrupt my library and made the app hang. So I figured I’ll load the latest upgrade. An 81MB download and reboot later and the application runs at least, but where are my podcasts? All of my podcast subscriptions are gone. Apple make really nice hardware but I don’t know why people rave about their software. It does nothing for me.

Object Designer in RoleTailored Client

2009-01-31T09:09:00.023+13:00

Here’s a bit of fun for anyone that wants to play around with development in the new Virtual PC image for NAV 2009. I’ll take you through building an Object Designer page in the RoleTailored client. Obviously there are some things you won’t be able to do (like actually design objects) but you will find it useful – I have done – and it’s a good exercise to follow. Here’s an example of the end result.

As you may know, you can run pages (and some other objects) in the RoleTailored client by using a hyperlink command entered into your browser address bar. The thing is, I often can’t remember the syntax, and need to open my book (because I know I wrote the details there). You could also find the answer by searching the online help or searching blog posts. The syntax I use is: dynamicsnav:////runpage?page=358 as this will launch the page in my default company. Typically, I want to create a quick page or try something out without going to the trouble of adding the object to the MenuSuite, then finding it in the Departments Page and running it, so I use this hyperlink command. It works OK, but I figured it would be good to have a list place of objects that I could use in the RoleTailored client to run any page.
For a start, we’re going to need an Object Designer page. This exercise is based on the new virtual PC image for NAV 2009 – (you’re going to need a Developer’s License installed before you can do this exercise).

1) Launch the Classic Client with SQL Server and make sure you are running with a Developer’s license.

2) Run the Object Designer (Shift+F12), click the Page button to work with Pages in the Designer and click the New button to create a new Page.

3) In the New Page window enter Object in the Table field and select the option create a blank page of type List. Click OK.

4) In the Page Designer, you have a blank line. The top line needs to be a ContentArea Container, and the quickest way to get one in there is to put something in the Caption field, move off the line and then move back on to it and delete the contents of the Caption field.

5) Now on the next line down, create a record of type Group and SubType Repeater. This is how we get a table type control in a list. Move down to the next blank line. Your page should look like this.

6) Now click on the Field Menu button from the toolbar or select View | Field Menu from the menu bar.

7) Highlight each of the lines except the BLOB field. Then click back on to the Page Designer. It’s not a drag-and-drop, but more like a click-and-click.

8) The system will ask if you want to add the selected fields to the page. Select Yes while mumbling “duh?” to yourself. Your page should look something like this.

9) Now would be a good time to save your work. Hit Ctrl+S and enter Object ID 60000 and call it Object Designer. If you didn’t load your developer’s license on the Virtual PC image, you’re going to be pretty grumpy now.

10) I don’t want all of those fields to be immediately visible, but it’s nice to have them on the Page just in case I want to use them but we can hide them by setting the Visible property. For example, with your cursor on the Company Name field, press Shift+F4 to bring up the properties and set the Visible property to FALSE.

11) Change the Visible property to FALSE for DBM Table No. and Caption too. Save your changes again with Ctrl+S.

12) Now we want to add the ability to run our objects so we need an Action on the page. Move your cursor on the Page Designer so that it is on a blank line and select Actions from the View menu (if you are on a blank line this will give you Actions for the Page which is what we want).

13) Create a new ActionContainer with a SubType of ActionItems and then on the next line create an Action of type Action with a Caption of Run. We want to set some properties on this Action so that it will appear in our Action Pane when we run the Page.

14) Set the Image property to Start (that’s the name of the image), Promoted to Yes, and PromotedIsBig to Yes. I also set the PromotedCategory to Process.

You could try running our new page now with dynamicsnav:////runpage?page=60000 but you will discover that our Action Button does not appear. This is because we don’t have any code or properties against the button that will do anything. We also want to filter our records so we only see objects with a Type greater than 0. Let’s continue where we left off. First of all we’ll filter the records to only show a limited range of object type.

15) We need to set the SourceTableView property for the Page, so with a blank line on the Page Designer selected, press Shift+F4 to bring up the Page Properties.

16) Type WHERE(Type=FILTER(>TableData)) into the SourceTableView field (you can use the assist edit buttons to figure out this value if you wish, or you can just type it straight in to the field).

17) Close the Properties window and with the blank line still selected, bring up our Actions once more. This time, press F9 on our new Action when it appears to bring up the C/AL Editor window.

18) Enter the following Code.

19) Save everything with Ctrl+S and try to run our Page with dynamicsnav:////runpage?page=60000. Bingo. If everything worked OK, you should have something like this.

Nearly there. Just one tiny change to make. I’d like my new list to be available as a List Place in my Role Center, so I’m going to modify the default Role Center (on the Virtual PC image) which is Page 9018.

20) Edit page 9018 and with a blank line selected, bring up the Actions window to show the Actions for the Page.

21) Insert a new Action in the HomeItems Action Container. Type must be Action and set the Caption to Object Designer.

22) Finally, set the RunObject property for the Action to Page 60000, the system will translate that to Page Object Designer as you move off the field (assuming you have followed these instructions).

23) Save everything and restart the RoleTailored client (unfortunately the Refresh button doesn’t re-read the Role Center so we need to re-open the RoleTailored client).

24) That’s it. Our very own RoleTailored Object Designer. Well Object Runner really, but hopefully you learnt a few things along the way.

If you want the tool but not the learning experience, you can download the objects from DUG. As a matter of interest, you can use Mark Brummel’s discovery about running the Classic client reports from the RoleTailored client to run Forms as well. Maybe I’ll post that another day.

Where is that MenuSuite option in NAV 2009? Part 2.

2009-01-18T11:03:00.004+13:00

In my first post on the MenuSuite search, I discovered that in NAV 2009 we can get at the MenuSuite through a blob field on the Object Metadata table.

In this post, I'll go one step further to see if we can start to do something with the blob.

The first thing I wanted to know is: do I need to worry about merging the various MenuSuites? When the MenuSuite is exported as text it contains only the differences that should be applied to the previous MenuSuite. For example 1080 (Partner Level) stores the differences that need to be applied to 1020 (Regional level) which in turn stores the differences that need to be applied to 1010 (Dept – MBS level).

I used a text comparison tool to compare my 1080 XML export to my 1020 XML and I can see that the 1080 is the full combined MenuSuite – perfect! This means I don’t have to worry about merging these files together. If I search for the items I want in the MenuSuite with the highest number, I will be looking at the correct menu structure.

Next I need to know what I am looking for in the XML file. If I take a look through the 1080 file, I can see we are looking for an Actions node with an xsi:type attribute of “ActionDefinition”. This will give me the node that is going to run an option. Within this node I can see there is a CaptionML attribute that I can search to try and match the string I am looking for.

My next programming task is to see if I can get the XML document loaded into an XMLDOM automation control inside NAV. Once again, this was a lot easier than I thought it was going to be. I'm not doing much with the XMLDOM, simply loading it and then saving it again so I can check everythig is working. Here's the code:

OBJECT Codeunit 91360 Export MenuSuite Objects
{
  OBJECT-PROPERTIES
  {
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    OnRun=VAR
            l_ObjectMetadata@1000000004 : Record 2000000071;
            l_XMLDoc@1000000000 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 4.0:{F6D90F11-9C73-11D3-B32E-00C04F990BB4}:'Microsoft XML, v4.0'.DOMDocument";
            l_InStream@1000000001 : InStream;
          BEGIN
            // MenuSuite items in the RoleTailored client are stored in the range 1000..1999
            // They map to the Classic client numbers but with 1000 added to the number.

            CREATE(l_XMLDoc, FALSE, FALSE);

            l_ObjectMetadata.SETRANGE("Object Type", l_ObjectMetadata."Object Type"::MenuSuite);
            l_ObjectMetadata.SETRANGE("Object ID", 1000, 1999);

            // The XML document for the highest number contains all nodes not just the changes.
            // so we only need to do a FINDLAST
            IF NOT l_ObjectMetadata.FINDLAST THEN
              ERROR('Something went wrong. There was no Object Metadata for the RoleTailored MenuSuites.\'+
                    'This sample only works with NAV 2009.');

            l_ObjectMetadata.CALCFIELDS(Metadata);

            l_ObjectMetadata.Metadata.CREATEINSTREAM(l_InStream);

            l_XMLDoc.load(l_InStream);

            // Just to check to see if I have my XMLDoc loaded correctly, I'm going to save it and
            // then open it in my  browser.
            l_XMLDoc.save(TEMPORARYPATH+STRSUBSTNO('XMLDOM_%1.xml',l_ObjectMetadata."Object ID"));
            HYPERLINK(TEMPORARYPATH+STRSUBSTNO('XMLDOM_%1.xml',l_ObjectMetadata."Object ID"));
          END;

  }
  CODE
  {

    BEGIN
    END.
  }
}

Where is that menu option in NAV 2009?

2009-01-13T20:11:00.006+13:00

Ever since the Outlook-style menu was added to Dynamics NAV, I have struggled to find the options I want. Eventually I got used to where things were in the standard system but as soon as the end users or over-zealous consultants made modifications to the MenuSuite objects, I found I was lost once again.

So what is the solution? Obviously I needed a tool to help me find the option I was looking for. In my dream world, I could type "Payment" into a search box and have the following results presented to me:

Financial Management | Cash Management | Payment Journals
Financial Management | Payables | Payment Journals
Financial Management | Receivables | Documents | Customer Payment
Financial Management | Receivables | Setup | Customer Payment
Etc.

It would be really nice to then be able to click a button to launch the option and hopefully, by seeing where the option is, commit to memory where the item can be found.

Previously building a solution such as this was too hard (for me). This was mainly due to the export formats available to version prior to NAV 2009. You could sort of do this by exporting the MenuSuite objects as text and then write a program to read the text file and use this information to build the menu paths. I didn’t really want to have to figure out the file format of the MenuSuite export, and I didn’t really want to have to write the program to read it in. It never seemed quite worth the effort.

NAV 2009 has changed things and I thought that the ability to export objects as XML would make this task a lot easier. Unfortunately when I tried to export a MenuSuite in XML format, all I got was an empty XML file – not what I was hoping for. Back to the drawing board!

Then, when I was creating some programming samples for my book on Implementing Microsoft Dynamics NAV 2009 (buy it from amazon here), I discovered that the Object Metadata table has a blob field called Metadata that, for MenuSuite objects, contains the MenuSuite in XML format. How cool is that? Not only can I get the data in an easily-accessible format, but I can also do it programmatically by handling the XML document in the blob field.

If you wanted to see the contents of your MenuSuites in NAV 2009 as XML files, you could run the following Codeunit. Now I have the basic tools for the job, I can create a Page in NAV 2009 that will (hopefully) meet my requirements. I will, of course, post the progress here. Stay tuned.

Here's the Codeunit...

OBJECT Codeunit 91360 Export MenuSuite Objects
{
  OBJECT-PROPERTIES
  {
    Version List=;
  }
  PROPERTIES
  {
    OnRun=VAR
            l_ObjectMetadata@1000000004 : Record 2000000071;
          BEGIN
            // MenuSuite items in the RoleTailored client are stored in the range 1000..1999
            // They map to the Classic client numbers but with 1000 added to the number.

            l_ObjectMetadata.SETRANGE("Object Type", l_ObjectMetadata."Object Type"::MenuSuite);
            l_ObjectMetadata.SETRANGE("Object ID", 1000, 1999);

            IF NOT l_ObjectMetadata.FINDSET THEN
              ERROR('Something went wrong. There was no Object Metadata for the RoleTailored MenuSuites.\'+
                    'This sample only works with NAV 2009.');

            REPEAT
              l_ObjectMetadata.CALCFIELDS(Metadata);
              l_ObjectMetadata.Metadata.EXPORT(TEMPORARYPATH+STRSUBSTNO('Metadata_%1.xml',l_ObjectMetadata."Object ID"));
              HYPERLINK(TEMPORARYPATH+STRSUBSTNO('Metadata_%1.xml',l_ObjectMetadata."Object ID"));
            UNTIL l_ObjectMetadata.NEXT = 0;
          END;

  }
  CODE
  {

    BEGIN
    END.
  }
}

NAV 2009 Book - more details on PACKT site

2008-12-29T20:14:00.002+13:00

If you are interested in our book Implementing Microsoft Dynamics NAV 2009, you can find a couple of things of interest on the PACKT web site.

First of all, there is the table of contents for the book.

Then there is an article based upon some of the examples in Chapter 7 called Extending the Application using Microsoft Dynamics NAV 2009.

It's been a long year and as you can see from the table of contents, Vjeko and I have been very busy. 2009 promises to be a pretty exciting year as we start to see more and more people getting into the exciting world of Web services in NAV 2009.

For now, I would just like to wish you all a very Happy New Year!

A New Home for Vjeko's Blog

2008-12-24T07:23:00.004+13:00

Way back in February of this year, I made a post about a blog that appeared on my Google Alerts for Navision that really caught my eye.

Now Vjeko's blog has moved to a new home: http://www.NavigateIntoSuccess.com. It has a new sleek look, new features, but the same fantastic content that has kept many readers returning again and again.

If the number of comments a blog receives is a testament to it's success then Vjeko has done extremely well over this last year. His articles regularly receive many comments from readers and he always takes the time to answer the comments.

Vjeko often writes in-depth, well researched, and informative posts on Dynamics NAV and Sure Step. I am a little biased, since we have just finished co-writing a book; however, if you are not already a subscriber to his blog, go and check it out. Now that the book is finished, we will both have more time for blogging and forum posting.

Where did my Web service go?

2008-12-23T20:45:00.004+13:00

I was recently playing around with Codeunit Web services in NAV 2009 and after making a change which involved adding a function to my Codeunit, the Web service was no longer available for me to call.

I brought up the list of the services on my computer (http://localhost:7047/DynamicsNAV/ws/Services) and it was still showing in the list, but when I entered the URL for the actual Web service WSDL, I got an error.

It turns out, the reason was because one of the functions I had added to my Codeunit took an Automation object as a parameter. If you get this problem, the thing to do is bring up the properties for the function and set the Local property to Yes. Once that is done, the offending Function is hidden from the Web service and normality is restored.

Implementing Microsoft Dynamics NAV 2009

2008-12-11T11:12:00.005+13:00

What a great way to wake up! You can now pre-order our book. I woke up this morning to see that the link to pre-order the book with a discount was up on the PACKT site. Wow!

I have co-authored this book with my good friend Vjekoslav Babić, and I can see that he's been busy blogging about the book and letting everyone know. One of the reasons I have been so quiet in the blogoshpere and on my favourite forum over the last few months is because I've been working so hard to get this book finished.

We've really tried to make this book as informative as possible, while keeping the fun elements from our blogs. There have been some fantastic people involved in producing this book, with feedback from members of the product group and technical reviewing from seasoned NAV experts and bloggers (like Eric 'Waldo' Wauters). Thanks to everyone that has read this blog over the years and to those that have left the occasional comment.

The book is aimed at consultants and developers of NAV and gives an insight into the new features of NAV 2009, and also a series of tips and tricks and practical examples for configuring, modifying, and extending the application. I can honestly say that this is the best book on NAV 2009 I have ever written.

So what are you waiting for? Go and pre-order your copy now.

Dan Brown Loves NAV 2009 (and so do I)

2008-11-23T10:39:00.004+13:00

Well done to the product team for getting NAV 2009 released to market. It's been a long haul for them and what they've acheived is truly remarkable. Dan Brown has made a blog post on the NAV Team Blog (read it here) in which he talks about the emotion that people attach to the new user experience and you should really go and read it if you have not done so already.

I've been playing around with NAV 2009 since the first release was made available to partners in May of this year. I'd seen some pretty cool videos previously and I was surpised to find the product looked just like the video and was fast and stable. The new Role Center approach is going to take some thinking from partners and initially introduces an extra step in the configuration process, but over time I can see this dominating the entire implementation process with the focus being more on the people in the organzation, the roles they play and the processes they need to perform and less on the data and the functions that are required to work on the data.

2009 is going to be a great year for NAV and I think it's going to be a whole heap of fun putting the theory into practice. Have you had chance to use it yet? If so, leave me a comment and let me know what you think of it.

Watch out for the Demo Install

2008-11-19T21:50:00.003+13:00

I'm amazed at how simple the demonstration install of NAV 2009 is. Click one button and it goes ahead and installs everything you need. A word of warning though, if you use this option and you have an existing NAV version installed (such as NAV 5.0 SP1) then the Microsoft Dynamics NAV folder will get overwritten without prompting. You may find it worthwhile copying your Microsoft Dynamics NAV folder to another folder (such as Microsoft Dynamics NAV 5.0 SP1) before doing the NAV 2009 install.

He's Been!

2008-11-17T20:07:00.004+13:00

Santa came early this year and he's left me a shiny new download of Microsoft Dynamics NAV 2009. Yes that's right! It's finally here.

If you have access to PartnerSource, you can download it from the NAV 2009 Download Page.

I installed it on my home machine which is running Vista Home Premium with no SQL Server installed and I didn't really expect it to install. I ran the demo install and it just worked - it installed SQL 2005 Express, all the bits and created the demo database. I started the Microsoft Dynamics NAV Business Web Services service and the web services just worked!

Coolest thing for me? The date picker in the RoleTailored client on Vista is just so nice with animated zooming and swooshing and stuff. Nice!

Well done Microsoft NAV Product Group! This is a truly awesome version. I'm sure you feel like a well deserved rest after such a long haul.

CodeCamp 2008 - NAV Resources

2008-11-04T11:28:00.003+13:00

Presenting on NAV to CodeCamp was great fun. Here are some follow up links.

I was asked if there was a way to get hold of the product to play with. The answer is that the you can download NAV 5.0 from MSDN if you have a MSDN subscription and you can also download a license file. You’ll find it under business solutions. The product DVD contains a load of documents as well that includes the application designers guide which is a really good introduction to programming in Dynamics NAV.

If you do not have a MSDN subscription, you can download a restricted version with some development capabilities from one of the online forums at http://www.mibuso.com/dlinfo.asp?FileID=950 although you will probably need to register on the forum before being able to download the file.

Here are some useful URLs for anyone that is interested in NAV...

Free chapter of NAV programming book at: http://programming-microsoft-dynamics-nav.packtpub.com/
MIBUSO user Forum http://www.mibuso.com/
Dynamics User Forum http://dynamicsuser.net/
Microsoft’s Community http://community.dynamics.com/nav/home.aspx

I also wrote a blog post that shows how to use the Web Services enablement of NAV 2009 with InfoPath which I didn't get time to demonstrate. You can read that on this blog here.

Thanks to everyone that attended my session and for the interesting questions that were raised.

Freddy's Blog Delivers the Goods on NAV 2009

2008-11-03T13:35:00.002+13:00

It's great to see more and more MS guys blogging their stuff on Dynamics NAV 2009.

Freddys Blog from Freddy Kristiansen is the latest find for me and his first posts are out of this world. This is the sort of stuff that is only available from the product team and a big thanks to Freddy for taking the time to share these useful tips.

Other recent goodies can be found on Lars Lohndorf-Larsen's Blog and Claus Lundstrøm's Blog.

NAV 2009 Web Services and InfoPath

2008-11-02T15:03:00.017+13:00

I gave an introductory talk at Christchurch CodeCamp 2008 on Dynamics NAV from a Developer's Perspective yesterday but unfortunately ran out of time before being able to show this example of consuming a page web service in an InfoPath form, so I figured I'd make a blog post.

Lars Lohndorf-Larsen has already made a couple of posts on how to expose codeunits and pages as web services using the upcoming NAV 2009 three-tiered architecture and has provided a sample c# program for consuming them.

I'm using the Marketing Beta Release for this so any partner will be able to download the VPC image and repeat this exercise.

First of all, you'll need to expose the Customer Page (Object ID=21) as a web service with a service name of Customer. This is as simple as running form 810 Web Services and inserting a record with Object Type=Page, Object ID =21, Service Name=Customer and ticking the Published field.

If you enter http://localhost:7047/DynamicsNAV/ws/CRONUS_International_Ltd/Page/Customer in your web browser, you should see the WSDL for the page web service. It still amazes me how easy this is.

Now it's time to start up InfoPath. On the Getting Started page, click the Design a Form Template... option on the left hand pane.
In the Design a Form Template dialog, click the Web Service option and click OK.

Select the option to Receive and submit data and click the Next button. Paste the URL for the Customer Web Service into the field that is asking for the location of the data connection web service. If you used the right URL (I just copied it from my browser address bar after I had checked the WSDL for my web service) you should see the following screen when you click Next.

I found this screen a little confusing because I've never used InfoPath before I didn't realise it is wanting me to tell it which method can be used for reading data from the web service. Select the ReadMultiple option and click Next. On the next screen, you get an opportunity to name the data connection. I left mine as the default Main query and clicked Next. The next screen askes for the URL to the web service that will be used when users submit their changes. It defaults to the previous URL so just click Next. On the following screen you are asked to select the operation that will be used for submitting the data. I selected UpdateMultiple and clicked Next.
On this page we need to specify where the Customer comes from so I clicked the Modify button next to the Field or group text box and drilled down until I could select the Customer Node in the XML structure.
You can leave the name for the data connection as Main submit and click the Finish button. If all went to plan, you should now have a new template to drag your fields on to.
Drag the filter group on to the Query fields area of the design surface and select the option to add the groups as a repeating table. This way users will be able to add any filter fields to the read command. You can select the properties of these fields and set a default value - for example, Field defaults to No. and Criteria defaults to *. This will ensure you get some records back. You will also need the setSize parameter since this is mandatory for the ReadMultiple operation. Drag this on to the control surface and give it a default of 100. You should have a query section that looks something like this.

Now expand the dataFields node and drag the Customer node on to the fields area of the design surface and once again select Repeating Table as the option. This puts in every field which is way too much so just right click on the table after you have created it and selec the properties, then click the Change Binding button. You will see a tree that is expanded to show the customer node, that's fine, just click Next and you'll get on to a dialog where you can remove fields. For a quick way to remove all fields, click in the Columns in table list box and hold down Alt+R until there are no fields left - now you can click on the fields you want and add them. I used No, Name, Address and City. You should re-size the fields so they fit the available space and have a larger Name and Address field. Now it's time to run the form and see what happens. Click the Preview button. When prompted, select the option to connect rather than work offline. Hit the Run Query button and accept the dialog box and you should see something similar to the following.
Nice! OK now you can try editing the name of one of the customers and hitting the submit button. If you see a dialog saying "The form was submitted successfully, you've just updated the NAV data!

New Zealand Box-biting Championships 2008

2008-10-24T22:04:00.007+13:00

Our New Zealand Microsoft Dynamics NAV User Group had a fantastic two-day conference in Hanmer Springs this week.

Mason Robinson did a great job of organizing this event - which normally consists of a morning meeting with lunch, but this time comprised a coach journey to Waipara Springs Winery where we had a winery tour, tasting and lunch. We then journeyed on to Hanmer Springs where we checked in to our rooms and I gave a presentation on the upcoming Dynamics NAV 2009 release, focusing on the new RoleTailored client and the Web Service Enablement features.

The presentation was well received and I think a lot of the users are very interested in the new release. Rob from Fujitsu then did a demonstration of Demand Planner and Microsoft Forecaster and I finished the presentations with a short demonstration of the Reporting Services report viewer control in NAV 2009. The hard work sessions concluded with general discussions and information sharing from the many users that had attended this event and there was some very useful suggestions from many users and warnings about the differences in the NAV 5.0 Jobs module.

Our sessions finished and we had drinks, dinner and a quiz session in which our team came second (pah!) We finished the evening with a ridiculous box-biting competition that required us to pick up a cardboard box with our teeth without touching the floor with hands or knees. On each round the box was reduced in size until the final round had the last three contestants picking up a flat piece of cardboard from the floor with their teeth.

Here I am trying to pick up the box. This was as low as I could go and for some strange reason I thought that taking my shoes off would help me pick up the box by being lower to the ground.

After a few hours sleep we enjoyed a nice breakfast, a few recap sessions on NAV 2009 and a follow up on what we had learnt the day before and a demo from Sue from Microsoft which included the Edit in Excel feature. We had an hour or so in which we chatted and discussed issues on Reporting Services, NAV, etc. (and enjoyed a coffee next to the hot spas) followed by a coach journey back to Christchurch.

Thanks to everyone for such a fantastic time. I think we set the bar pretty high for this conference and I would be interested to know if anyone has managed to have a NAV user group meeting that tops this one.

NAV 2009 Edit in Excel

2008-10-24T21:54:00.004+13:00

At our recent NAV User Group conference, I had the pleasure of seeing some new NAV 2009 functionality demoed by our local Microsoft ERP Expert.

In the demo, Sue set a filter on a list of records (I think it was a vendor list) and then from the Actions command menu selected a new option called "Edit in Excel".

This looked similar to the Send to Excel feature with one difference. There was a new button on a Dynamics NAV tab of the Ribbon that allowed the data to be updated.

In the demo, Sue deliberately set the Responsibility Centre in the spreadsheet to an invalid value and the update returned an error message that the Responsibility Centre was not valid. She then corrected the data in the spreadsheet, hit the update button and the data was updated in NAV.

This was a fantastic demonstration of the new NAV 2009 web services in action. I'm pretty sure this option is not available in the Marketing Beta Release that is available to partners and the image Sue had was based on Windows Server 2008.

It looks like there's definitely more cool stuff coming in the NAV 2009 release and this ability to edit data in Excel is going to prove extremely useful to many users.

Careful with those Captions!

2008-10-06T20:37:00.003+13:00

On occasions I have needed a table that is similar to an existing table but with maybe some changes that I didn't want to have to make to the standard system (such as adding a field to the primary key).

One easy way to do this is to design the table you want to copy and use the File, Save As... menu option to save the table as a new table name and ID.

If you do this, make sure you change the Caption for the table to match the table name. Today I spent far too long investigating a problem with an error message telling me a record could not be inserted into table X since it already existed.

For some bizarre reason, the error was only being triggered when a commit was being called so there was some guesswork as to where the error was actually taking place. No matter what I tried it appeared that something really weird was going on. Eventually in a fit of frustration, I deleted the table in question and yet the error message still appeared.

The problem? Well the table had been created by copying another table and the caption had not been changed. As a result the error message was giving the wrong table name - causing great confusion.

Now here's a question for seasoned veterans. Does anyone know why the error was only appearing at the point of a commit or when all commits were removed after all other code in the transaction had executed? When you put a lock on a table, does NAV use SQL BEGIN TRANSACTION and then use COMMIT or ABORT if an error is thrown, or does it cache all transactions to the locked table and only send them to the database when the COMMIT is executed? I think I might do a little experiment and log the SQL commands to see what is going on. If this is the case then removing the LOCKTABLE command would have helped the debugger pinpoint my error.

New MVP Award

2008-10-04T12:27:00.003+13:00

Last week I found out that I have been awarded an MVP for Microsoft Dynamics NAV. This is a great honour and I am looking forward to the coming year with great enthusiasm. If you are at all interested in Dynamics NAV, you can't help but know that the new and exciting Dynamics NAV 2009 will be launched this year and with it there will be a heap of new challenges and possibilities. The great thing about Dynamics NAV is that there is always lots to learn. I will try to help others where I can by passing on what I discover.

I work with some amazing people and have had the pleasure of meeting many great programmers and analysts in my years of working with NAV. Big thanks to everyone that has supported me over the past few years. There are a number of cool things coming up from me and my good friend Vjeko Babic but I'll post more about that as the year progresses. Once again, thanks for supporting this blog.

Calling Web Services in the Marketing Beta of NAV 2009

2008-09-30T20:31:00.008+13:00

My first experience of using web services in the Marketing Release (CTP4) for Dynamics NAV 2009 resulted in a couple of errors. The first one I experienced was a bit of confusion over the way Visual Studio 2008 handles web services.

If I had read through Lars Lohndorf-Larsen’s blog post on how to consume a web service properly this wouldn’t have been a problem, but basically if you want to add a web service in VS 2008, it’s a little different.

When I went to add my service, there was no add web reference option.

I thought, hey that’s cool they’ve changed the name and updated the user interface, so I just selected this option and put in my address. But when I came to use the web service I got the shock of my life when none of my methods were there. Now I had to go back and read Lars’ post and this time I was determined to follow it step by step to see what I was doing wrong.

Sure enough Lars does say:

2) This step depends a bit on whether you use VS2005 or 2008. In VS2005, just rightclick on "References" in the Solution Explorer, and select "Add Web Reference". In VS2008, to get to the same place, rightclick on "References", then select "Add Service Reference", then click the Advanced button, and then click the "Add Web Reference" button.

So when you see this screen:

Click the Advanced button and then click the Add Web Reference button.

Now we’re back in familiar territory and everything works as it did previously.

Except – my console app didn’t work! I kept getting this error: "Path property must be set before calling the Send method."

I think this problem only happens when you are adding multiple web references to a single project (not uncommon) and I’m really not sure why it happens – maybe someone who knows a bit more about .NET and visual studio 2008 could add a comment as to why this happens.

Even though the URL property is set on the web reference, for some reason the service doesn’t know the URL, so I needed to explicitly set it within the code.

Here’s an example.

SSRef.SystemService SysServ = new SSRef.SystemService();
SysServ.Url = "http://localhost:7047/DynamicsNAV/ws/SystemService";
SysServ.UseDefaultCredentials = true;

I guess this is my first blog post on NAV 2009 although, like many people, I’ve been working with it a lot since the CTP3 release. I have to say that this is an amazing product and I really can’t wait to start using this for real. Nice one Microsoft!

Everybody Lies

2008-09-28T11:45:00.005+13:00

The Dr. House approach to requirements analysis

This posting was first published on the Intergen Blog Site on the 26th September.

Gregory House M.D. is a maverick medical genius who, in each TV episode, heads a team of diagnosticians in their attempts to diagnose an unfortunate patient’s mystery illness. House’s signature phrase is “Everybody lies” although, for his character, it’s more than just a saying, it’s a philosophy. In this article, I’ll demonstrate how the “everybody lies” approach can be applied to requirements analysis in order to reduce costs and improve overall project quality.

It seems odd to claim that everybody lies. Just as there’s no good reason for a patient to lie to a physician who is trying to save their life, we don’t expect a business person to lie when we’re gathering requirements. The modern-day philosopher, Homer Simpson, has this to say on the subject of lying: “Marge, it takes two to lie. One to lie and one to listen.” Homer’s insight helps us understand the fundamental problem: the users must tell us the truth but as analysts we are equally responsible for finding it.

Before I try to convince you of my rather tongue-in-cheek methodology, you should first understand that getting requirements right is a serious business. Research from Barry Boehm and Philip Papaccio has shown that defects introduced early in a project, such as in the requirements analysis phase, can cost 50 to 200 times more to fix later in the project than if they had been corrected close to the point at which they were introduced. 50 to 200 times – that’s a staggering difference!

Gathering requirements means capturing business problems, not computer problems. You don’t need to be Sherlock Holmes (or Gregory House for that matter) to understand that the first step in solving a problem is to clearly identify what the actual problem is. Building a solution based on the wrong requirements can be a costly mistake to make, but how exactly can we apply our new “everyone lies” approach to requirements analysis and avoid getting it wrong? One approach could be to shout “liar” every time a user describes a requirement, but some may find this a little disturbing. First we need to understand the nature of the lies and how to avoid them.

The first problem I have discovered is that people like to describe solutions and not requirements. If I had a signature phrase for requirements analysis it would be, “That’s not a requirement, it’s a solution!” I’ll admit it’s not as catchy as “everybody lies” but the sentiment’s the same.

A requirement is the answer to a “what” type of question and should always be expressed in business terms. A solution is more of an answer to a “how” type question. There’s an easy way to tell the difference between requirements and solutions: if you can implement it, it’s a solution.

Writing down a solution instead of a requirement happens frequently, but most of the time we get away with it because it just so happens that the solution we’ve written is correct. No harm, no foul; but what happens when the solution isn’t correct? Remember that equation we had before? 50 to 200 times more expensive to fix. That’s why we get the requirements signed off to make sure they’re correct, because no-one would sign off on requirements when they’re not correct. Or would they?

Having a signed off requirements document means nothing if the requirements are wrong – we’re still going to need to fix the defects and our goal is to reduce the project costs, not just our costs. Let’s assume that no one would sign off requirements that they know to be wrong, so it must be that they’re not understood, but this raises a new mystery: why do people agree to requirements when they don’t understand them?

The problem starts when we use the wrong language to describe requirements. If we’re not using business terms, we’re putting the business users at a disadvantage. It can typically go one of two ways: either the business user says, “Hey I’m sorry but I don’t understand this requirement so I can’t agree to it”, or they say, “I don’t understand this requirement but the consultant seems pretty confident that he’s right, so I’ll just keep quiet. If it’s wrong someone else will pick it up later on.”

So the next time you find yourself writing a requirements document, remember that you’re a detective and not a secretary. It’s your job to find the real problems in business terms and not simply record what you’re told. When people tell you that the captured requirements are correct, ask them to explain them to you, just to check they’re not lying.

In my next post, I’ll explore the “Tourette’s Syndrome” approach to handling support calls.

SQL Code Beautifier

2008-09-25T21:17:00.002+12:00

It's funny - I've looked for some kind of "improve my layout" type function in SQL Management Studio loads of times but never found it. Then (and I don't know why I never tried this before) I searched for SQL Code Beautifier in Live Search and came to this site: http://www.ubitsoft.com/products/t-sql-beautifier/index.php.

The resulting code looks great, AND you can get an HTML rendering to post in your blog posts. Nice!

Why did I need to beautify my code - well the first time I needed it was when I had to edit someone else's SQL that believed carriage returns were not necessary. The second time (that prompted me today) was when I wanted to see the result from creating a dynamic SQL command - I printed the command but it came out as a single line of text, so I wanted to see what it looked like to be able to check for errors.

This nice tool is definintely going to save me heaps of time. Maybe someone should write a C/AL code beautifier.

When Ctrl+C, Ctrl+V Fails--Send to Excel Saves the Day!

2008-09-25T21:05:00.003+12:00

The other day a very scary thing happened to me-copy and paste from NAV failed! I was on a sales order and I copied the sales lines to the clipboard and pasted them to Excel. Nothing new there, we've been able to do this for years and it's been a pretty cool feature of NAV but when I came to check the totals, I was horrified to see that a few of my sales lines were missing!

I tried again and still they were missing. After some investigation, I found the cause of the problem was that some of my items had a double quote in the description for the items. This is not uncommon, especially when you have descriptions with inch sizing in the description. The problem seems to be when the very start of a field is a double quote and there is no double quote at the end. Excel was taking from the start of the double quote to the next double quote as being a full description field (so one of my fields included 10 or so sales lines in the field.)

I was about to log this with Microsoft as a problem with NAV when I saw that if I typed similar data into a text file and copied and pasted into Excel, exactly the same thing happened.

Thankfully the new (version 5.0 new) send to Excel feature saved the day as the XML approach copes with the double quotes without a problem.

Where Am I? Now I know!

2008-08-14T20:33:00.003+12:00

I wrote some posts a while ago when Waldo's post on his blog inspired me to write a little tool similar to the one he was blogging about. I added some features and wanted it to look nice but I came up short in trying to get the FORM to stay open. I could find no way of stopping the user from clicking on the form and pressing ESC.

Today a person with a screen name of Viktoras on the Dynamics User site left a comment on Waldo's blog post explaining how to solve this problem and he was also kind enough to follow the link to my blog and leave a comment for me. So finally I was able to finish my little utility and I just need to tidy up a couple of things and then I can make it available for download. I realise that putting source code on a blog for people to copy and paste is a bit of a poor way to distribute code and so I will be putting the finished code on to a forum - I will write about it here of course!

Awesome Vista Sidebar Gadget

2008-08-14T19:51:00.005+12:00

App Launcher

I've only just started to use Vista's Sidebar gadgets in anger. Sure I had the inevitable clock and the Weather forecast and even a calendar but the few times I had searched for something in the gallery I came up with a real "meh!"

Today a colleague pointed me to this App Launcher gadget and I think it's brilliant. I love the fact I can just drag shortcuts on to the gadget to add them to the launcher. I love the fact that folder shortcuts open in the flyout and that I can open my Internet Explorer favourites too. I'm guessing the fact I have a widescreen monitor and the Gadgets are always on top helps. If you're not using Vista, then poor you. If you are using Vista and you haven't seen this one, you should really give it a go. Click on the link at the top of this post to download.

What's New in Microsoft Dynamics NAV 2009

2008-07-27T21:56:00.003+12:00

There is a new training course available for download from PartnerSource and CustomerSource. I'm not the first to blog about this, but NAV 2009 is definitely a hot topic, and I only came across this today, so if I've missed it, I'm sure there are others out there that will be interested. The upcoming release of Microsoft's ERP solution is much anticipated and accounts for a significant number of hits on this blog from search engines. It's not possible to write much about the product but hopefully that will change when the next release is made in September.

If you have access to PartnerSource, you can view the training course contents and powerpoint at this link. The CustomerSource URL is here.

Intergen in top 5% of Microsoft Dynamics Partners

2008-07-27T14:10:00.002+12:00

Intergen has been honoured with membership of the 2008 Microsoft Dynamics President’s Club which consists of the top 5% of Microsoft Dynamics partners worldwide.

Intergen received this top recognition from Microsoft during the Microsoft Worldwide Partner Conference 2008 in Houston, Texas. The honour reflects Intergen’s dedication to meeting their clients’ needs.

It's a great feeling to be part of a team that is recognised at this level. To read more, visit http://www.scoop.co.nz/stories/SC0807/S00056.htm.

Where did I put that trigger?

2008-07-22T22:37:00.005+12:00

I needed to put some SQL triggers on my NAV tables the other day and when I came to check which triggers existed today, I had forgotten how I did it originally.

Here's a quick SQL script that will show you all table triggers in the current database.


SELECT [Trigger Name]=TRIG.name, [Table Name]=PARENT.name 
FROM (select * from sys.all_objects where type = 'TR') TRIG
JOIN (select * from sys.all_objects) PARENT
ON TRIG.parent_object_id = PARENT.object_id

I love TRANSFERFIELDS.

2008-07-22T22:33:00.002+12:00

I've been doing a fair bit of coding recently and I must admit I love the concept of transferfields and the simplicity. It's really nice to know that when a customer wants a field to work it's way through to the posted documents from the source documents, you can do it all without writing any code.

Still, it would be nice if there were a way of stopping it from going wrong.

I hate TRANSFERFIELDS - Part II

2008-06-27T22:52:00.005+12:00

I hate the TRANSFERFIELDS command. The last time I ranted about it I seemed to stir up some strong feelings, so I figured it’s time to bring this one up again.

Last time I commented on how the unsuspecting developer has no way of knowing what is going to happen when they use this command due to the way it copies fields between tables using the field ID to map the tables. Since then I have been tripped up again by this evil function and instead of simply moaning about it, I thought I would suggest an alternative.

So here I am going to introduce the all-new transferfields functionality. Microsoft, please feel free to take this idea and implement it in NAV.

The TRANSFERFIELDS command is programmed the same as now but in order to use it, a field mapping between the two tables must be defined at the table definition level. Let’s say we want to transfer fields from the Purchase Header table and the Purch. Inv. Header tables. You can find an example of this in the Purch.-Post codeunit as follows:

PurchInvHeader.TRANSFERFIELDS(PurchHeader);

This line takes the fields from the PurchHeader record variable and transfers the fields to the fields with the same field ID in the PurchInvHeader record variable.

If you have not defined a filed mapping between these two tables, the above code will not compile. In order to define the mapping I see it working like this.

Go to edit the Purchase Header table and select View menu and select the Table Mappings option. This will launch a form similar to the following:

On this form, you can insert a new line and enter the To Table ID to identify the table you want to map to. It would probably make sense at this stage for the system to automatically insert mapping between the two tables based upon the matching field IDs of the two tables. In this respect replicating the existing behavior would be pretty easy.

To customize the field mappings for the tables, the user will simply click the assist edit button on the Mapped Fields field and this will show the Field Mapping form.

Here the user can set up the field mapping. There are a couple of great things about this approach, you can ignore fields that you would never want to copy, such as the No. field. You can also map fields with different IDs. The other great thing is that field mappings can be managed by end users without the need to write code. An example would be if the user creates a new custom field on the Purch. Inv. Header table called Original Purchase Order No. The user can then make sure that the purchase order number ends up in this field by just using the mappings.

We could even add some rules on what to do when the fields don’t match in type, i.e. should we truncate the string or throw a run-time error.

Help!

2008-06-23T21:43:00.004+12:00

In May I wrote a short piece for MSDynamicsWorld.com on on-line help and offered some thoughts on how it could be improved. In response to this article, I received a question asking how to configure the online help in Dynamics NAV.

In truth I have never done this. I knew I had read a document on how to do it but to be honest, it looked so hard I didn't bother. When I came to reply with the details, I really struggled to find the document I was looking for that described the help creation process. If you're looking for it, it's called NOHG.pdf

One of my points in the article is that we really need an easy-to-use help editor, similar to the one in Dyamics AX. Doh! I can't believe I just admitted something was better in AX!

Error: [Microsoft][ODBC SQL Server Driver]Database is invalid or cannot be accessed State ID: HY024

2008-06-23T12:02:00.003+12:00

What a great title for a blog post?

I got this error when a customer had tried updating their NAV 4.0 SP3 to use Standard security as opposed to Enhanced.

I suggested all sorts of fancy SQL to resolve the situation thinking it was a permissions error as I know that changing a database to use Standard causes some problems on permissions with the Session and Database File view.

It turns out the user had left the database in single user mode - a pre-requisite of being able to change the security model.

I used Activity Monitor in SQL 2005 Management Studio to kill the connected process, then logged in to NAV and used File, Database, Alter to remove the single user mode from the database. Problem sorted. But what about those missing permissions?

If you create a NAV database with standard security, the system will allocate permissions to the Database File and Session views to the application server role $ndo$shadow. However, if you change a database from Enhanced to use Standard, these permissions do not get assigned and you therefore hit errors unless you are a db_owner.

To resolve this you can grant the permissions yourself as follows:


GRANT DELETE ON [dbo].[Database File] TO [$ndo$shadow]
GRANT INSERT ON [dbo].[Database File] TO [$ndo$shadow]
GRANT REFERENCES ON [dbo].[Database File] TO [$ndo$shadow]
GRANT SELECT ON [dbo].[Database File] TO [$ndo$shadow]
GRANT UPDATE ON [dbo].[Database File] TO [$ndo$shadow]
GRANT DELETE ON [dbo].[Session] TO [$ndo$shadow]
GRANT INSERT ON [dbo].[Session] TO [$ndo$shadow]
GRANT REFERENCES ON [dbo].[Session] TO [$ndo$shadow]
GRANT SELECT ON [dbo].[Session] TO [$ndo$shadow]
GRANT UPDATE ON [dbo].[Session] TO [$ndo$shadow]

User Acceptance Testing - the Key to Surviving an ERP Go Live

2008-06-14T11:00:00.008+12:00

This posting was first published on the Intergen Blog Site on the 12th June.

When I came to New Zealand, nearly six years ago, I wanted to throw myself off a bridge with some elastic tied to my legs. I had heard that this was something Kiwis did and I wanted to fit in to my newly adopted environment. The world’s first commercial bungy jump started in the mid ‘80s in Queenstown, New Zealand. And it was there, on the Kawarau Bridge, that I took the plunge in 2002.

It was frightening but totally worth it. I knew it was safe but that didn’t stop me from being absolutely terrified. Sometimes in life, no matter how scared you are, you need to take a leap of faith. 5, 4, 3, 2, 1, bungeeeeeeeeey!

There are some similarities between bungy jumping and going live with an ERP solution and it was thinking about those similarities that inspired me to write this piece. Don’t panic, I’m not going to try and contort what I have to say about the ERP go live process to be all about bungy jumping using some clever metaphors. If, however, you do find yourself standing on the edge of an ERP implementation, you should read this article before jumping head first.

First of all, let’s consider the timing of the go live. How do you know when an ERP implementation is ready to go live? Is it when you have run out of time, or money? Often those, or some other seemingly arbitrary factor, are the main reasons for deciding on a go live date, but are you really ready to go live and how do you know?

The Six Ps

Like many things in life, prior preparation prevents pretty poor performance. In the case of an ERP implementation, the preparation comes in the form of user acceptance testing (UAT). User acceptance testing is often used as a project milestone for contractual reasons; completing UAT signifies that the solution has reached an acceptable level of stability and this in turn can be linked with the issue of who is going to pay for fixing defects. UAT is actually far more important than that — it is your key to project success. Imagine being the first person to bungy jump from the Kawarau Bridge. You’re standing 43 metres above the river with a bungy cord around your legs. Are you going to jump because you believe it should work in theory? I think not. Before you jump you’re going to do a little testing first: maybe throw a crash test dummy off the bridge to see if the harness holds, to ensure the rope is the right length. It’s important to iron out the issues in a safe environment first, and for an ERP implementation the safe environment is UAT.

Every issue that is found during UAT is one less issue that will need to be solved after go live, and the thing about go live issues is they can be really dangerous. When an issue occurs in a production system in a go live environment, it needs to be fixed quickly, and there is typically a great deal of stress associated with the issue. Being hurried in a stressful environment does not make for good programming and it certainly doesn't allow for well thought out design.

In order to avoid this stressful and potentially business-damaging situation you must start your preparation early, but what exactly is UAT. And, more specifically, what should you be testing?

Test the Entire Solution

In UAT, you are trying to simulate your go live situation. The closer you can get your UAT environment to your go live environment, the more confident you will be that you’re going to survive. Here’s a list of some of the things you need to be testing:

The configuration of the production server hardware and server component installation, such as database, application server, SharePoint server, IIS, report server.

The ability to install client software. It may be that you have a dedicated set of machines for the users to perform UAT, but if you are planning on installing client software as part of your go live, you should be testing your installation procedures by installing some software on some client machines that will be used for the testing. Go live day is not the day to hear, “Well it worked in the testing room - what’s different?”

The configuration of your production ERP environment. The testing must be performed using the system with configuration settings as they will be in the live environment. It is inevitable that you will need to make some configuration changes during UAT in order to resolve issues; however, you must ensure that the same changes are logged and applied to the live environment. And you should also be aware that any change in configuration could invalidate all testing that has been completed so far.

The data conversion process. Your data conversion process needs to be repeatable; you need to be able to extract the data to be converted consistently, hopefully using programs or scripts with a minimum amount of manual manipulation. The import of the data must obviously be done through dataports or other programs and the UAT is where you will test the success of those procedures. If you make changes to your data as a result of issues found during testing, you should ensure you can repeat this when you do go live and document the changes to the data conversion process.

The modifications that have been made to the system: the new reports, forms, codeunits and external programs, interfaces, and reports all need to be tested. All too often this is the only thing that is covered in UAT. Once again, corrections may mean that previously accepted tests need to be re-tested. This is known as regression testing—it’s not what you fixed that’s important, it’s the things you broke along the way that you need to be aware of.

The configuration of security privileges. This is to ensure that users can perform the tasks they need to perform without getting error messages and also to ensure that users do not have access to the data that they are not privileged to see.

Plan your testing and test your plan

The only way to have a successful UAT is to write down what you plan to test and record the issues you have found when they are tested. When you need to make the decision to go live or not, you will want to see the list of business process with lots of little ticks against them showing that they have been successfully performed. Remember the problem of regression testing: when you make a change to configuration, data conversion, or programming, you may as well erase all the ticks you have so far – it’s not what you fixed that’s important, it’s what you may have broken. If there are issues, you want to know what they are before you go live; it’s OK to go live with issues, it’s just important to know beforehand they are there and to have taken an informed decision.

When it comes to preparing test cases—well that’s another posting in its own right. Maybe one of our QA team can offer some advice in that area.

Get Some Help

If you have done your job properly, the go live should be painless. There may be a few surprises, but by and large you’ll be going live knowing that the critical things are going to work. It’s important to have good people around to help you through the go live process. When you’re standing on the platform with your towel wrapped tightly around your ankles, gazing at the horizon, it’s good to know that if you do freeze up, one of the professionals stood behind you is going to be quite happy to give you a little push.

Fast SQL Copy for Employee Portal

2008-05-25T14:22:00.002+12:00

Recently I wanted to copy the contents of my Employee Portal setup from one company to another and then eventually from my test system to my live system. Now I know I could probably do this with a dataport and, to be honest, I did try that but something didn’t quite work. I probably made a silly mistake, but it did inspire me to create a SQL stored procedure to do the job for me.

This stored procedure uses the INFORMATION_SCHEMA in SQL to find the field names of any matchining tables and creates some SQL for me to execute. For safety, I only generate the SQL and do not execute it, giving you a change to change your mind if you don’t like what it is about to do.

The really neat trick is the fact that it will handle fields with an AutoIncrement property set in NAV.

I adapted this from a similar stored procedure I created a while ago that would copy all tables from one company to another which is considerably faster than the NAV backup/restore. For Employee Portal tables I am just assuming they start with ‘EP’ – another good reason to inspect the generated SQL before you execute it.

If you use this code and destroy anything, you only have yourself to blame and I will take no responsibility. This code is posted here “as is” for the sake of sharing knowledge and I will not be held responsible for any loss of data or hair that may result from running it.

Here’s the code...


IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[NAV_EP_Copy]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
 DROP PROCEDURE dbo.NAV_EP_Copy
END
GO

CREATE PROCEDURE [dbo].[NAV_EP_Copy]
  @SourceCompany varchar(31), 
  @TargetDatabase varchar(31),
  @TargetCompany varchar(31) 
AS

--Version 2 - added target database and row count checks

DECLARE @SourceTableName varchar(100), @TargetTableName varchar(100)
DECLARE @TmpColumnName varchar(30), @TmpColumnType varchar(100)
DECLARE @SQLStr1 varchar(8000), @SQLStr2 varchar(8000), @SQLStr3 varchar(8000), @Values varchar(8000)
DECLARE @Identity int
DECLARE @ErrorCount int, @Debug int

SET @Debug = 1
SET @ErrorCount = 0

IF @Debug = 0 BEGIN
  PRINT 'Copying NAV Employee Portal'
  PRINT ''
  PRINT ' From ' + @SourceCompany
  PRINT ' To   ' + @TargetDatabase + ' ' + @TargetCompany
  PRINT ''
  PRINT ''
END

IF SUBSTRING(@SourceCompany,LEN(@SourceCompany),1) <> '$' BEGIN
  RAISERROR('Source Company Name must use SQL name (replace chars with underscore) and end in $.',1,1)
  SET @ErrorCount = @ErrorCount + 1
END


IF SUBSTRING(@TargetCompany,LEN(@TargetCompany),1) <> '$' BEGIN
  RAISERROR('Target Company Name must use SQL name (replace chars with underscore) and end in $.',1,1)
  SET @ErrorCount = @ErrorCount + 1
END

-- Debug mode does not do anything other than generate messages so who cares if there is data there alredy.
EXEC('set nocount on select top 1 1 from [' + @TargetDatabase + '].[dbo].[' + @TargetCompany + 'EP WP Request Table Tab'+']')

IF (@@rowcount > 0) BEGIN
 IF @Debug <> 1 BEGIN
  RAISERROR('Target EP WP Request Table Tab table has data.',1,1)
  SET @ErrorCount = @ErrorCount + 1
 END ELSE BEGIN
  PRINT '***** WARNING *****'
  PRINT 'Target EP WP Request Table Tab table has data.'
  PRINT 'Executing these commands will delete that data.'
  PRINT ' '
  PRINT 'Data Loss May Occurr.'
  PRINT '***** WARNING *****'
  PRINT ' '
 END
END

IF @ErrorCount > 0 GOTO ENDHERE

DECLARE srcTable CURSOR FOR
  SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_NAME LIKE(@SourceCompany+'EP%')

OPEN srcTable

FETCH NEXT FROM srcTable INTO @SourceTableName
WHILE @@FETCH_STATUS = 0 BEGIN
  
  SET @TargetTableName = @TargetCompany+SUBSTRING(@SourceTableName,LEN(@SourceCompany)+1,100)
  SET @SQLStr1 = ''
  SET @SQLStr2 = ''
  SET @SQLStr3 = ''
  SET @Values = ''

  IF (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMNPROPERTY(OBJECT_ID(TABLE_NAME),COLUMN_NAME,'IsIdentity')=1 AND TABLE_NAME = @SourceTableName) > 0 BEGIN
    SET @SQLStr1 = 'SET IDENTITY_INSERT [' + @TargetDatabase + '].[dbo].[' + @TargetTableName+ '] ON '
    SET @Identity = 1
  END

  SET @SQLStr1 = @SQLStr1 + 'TRUNCATE TABLE [' + @TargetDatabase + '].[dbo].[' + @TargetTableName+ '] '
  
  DECLARE srcColumn CURSOR FOR
    SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @SourceTableName
  
  OPEN srcColumn

  SET @SQLStr2 = @SQLStr2 + 'INSERT INTO [' + @TargetDatabase + '].[dbo].[' + @TargetTableName+ '] ('

  FETCH NEXT FROM srcColumn INTO @TmpColumnName, @TmpColumnType
  
  WHILE @@FETCH_STATUS = 0 BEGIN
    
    SET @SQLStr2 = @SQLStr2 + ' ['+@TmpColumnName+']'
    
    IF @TmpColumnType = 'timestamp'
      SET @Values = @Values + ' NULL'
    ELSE 
      SET @Values = @Values + ' ['+@TmpColumnName+']'
  
    FETCH NEXT FROM srcColumn INTO @TmpColumnName, @TmpColumnType
  
    IF @@FETCH_STATUS = 0 BEGIN
      SET @SQLStr2 = @SQLStr2+',' 
      SET @Values = @Values+',' 
    END
  END

  SET @SQLStr2 = @SQLStr2 + ')'
  SET @SQLStr3 = 'SELECT ' + @Values + ' FROM ['+@SourceTableName+'] ' 

  CLOSE srcColumn
  DEALLOCATE srcColumn

  IF @Identity = 1 BEGIN
    SET @SQLStr3 = @SQLStr3 + ' SET IDENTITY_INSERT [' + @TargetDatabase + '].[dbo].[' + @TargetTableName+ '] OFF '
    SET @Identity = 0
  END

  IF @Debug = 1 BEGIN
    PRINT @SQLStr1
    PRINT @SQLStr2
    PRINT @SQLStr3
  END ELSE BEGIN
    PRINT ''
    PRINT '-------------------------------------------------------------'
    PRINT @TargetDatabase + ' ' + @TargetTableName
    EXECUTE ( @SQLStr1 + @SQLStr2 + @SQLStr3 )
  END

  FETCH NEXT FROM srcTable INTO @SourceTableName
END

CLOSE srcTable
DEALLOCATE srcTable

ENDHERE:

Where Am I? - Still Wondering.

2008-05-12T17:31:00.003+12:00

Well I got the image problem sorted out thanks to a suggested from Vjeko. He said that I should open the image in Paint and re-save it as this has sorted a similar issue out for him in the past.

Well that didn't work, but...

What I did do was capture a screen shot of the icons and save those as a bitmap using a product called HyperSnaps and this imported into NAV fine and looks really nice.

Where Am I?

2008-05-09T21:53:00.007+12:00

I was really inspired by Waldo’s blog posting on how to see which database you’re in. Take a look on Waldo's Blog if you haven’t seen it before. This is something that our users have been asking for since version 4.0 first did away with the old form-based menu system (something they could easily modify to show them if they were in the live or the test system.)

I really wanted to make a super-nice version of Waldo’s form but I came up short in a couple of areas. Who knows, maybe someone reading this blog will be able to offer some advice as to how to overcome the problems I found.

First of all, let me give you my requirements.

I want the system to provide immediate visual cues to show the users which database or company they happen to be in. The required information is to provide something eye-catching, to show a large bit of text (like “TEST SYSTEM”) and to provide the database name, company name and finally give me a big area where I can put a support message. Oh and one more thing: when I copy my database from the live system and restore it to my test database, I want the messages and visual cues to stay the same – that’s right, I don’t want to have to edit the data to make my test system say it’s the test system every time I restore it from the live backup.

The solution I came up with is pretty much based on Waldo’s solution so I’m not taking credit for this. I just figured, it would be nice to share this with anyone that’s interested and maybe someone with a bit more time can iron out the imperfections. I should also say that I only ever intended this to work with SQL databases so there’s no need to point out that this will not work with a native database.

Before we get stuck in to the How, let’s take a look at the solution I came up with. Like Waldo’s solution, I have some code in my CompanyOpen() trigger in codeunit 1 ApplicationManagement that will launch a form.

My form will look for details in the setup table matching the current company and database and if it doesn’t find a record, it creates one and displays the following image.

I then have a setup form that allows me to edit the info that’s going to be displayed.

The Database and Company get filled in automatically. The Title field get’s displayed, the title Background Colour is an “unimplemented feature” :-)

The Image Type allows you to select from Live, Test and Unknown options which will show one of the following icons.

I downloaded these icons from http://tpdkcasimir.deviantart.com/ and made some adjustments to set the background colour to be the same as NAV’s background colour and resize them (when I say I did it, I mean someone with far more talent than me did it for me.)

Now this is where I hit the first weird NAVism – or maybe I was just doing something wrong. Whenever I put the icons on the form, they got stretched. Anyone know what I’m doing wrong? I thought at first that NAV had some kind of minimum size so I resized the Icons to fit the size of my NAV graphic and these got stretched again to look like this.

But at least you can see the idea – and the way the other data fields are being updated from the database.

The Support message appears at the bottom of the form, the Show Info tick allows you to suppress the message in certain company/database combinations – like maybe your live system. The custom background colour is another unimplemented feature.
I made a list form first and then decided a card form is a little easier to set up the data.

OK now let’s look at how I did this.

To start with, how am I going to solve the problem of my data not getting lost when I restore my live system? Well, there’s some other data that doesn’t get lost when you restore over your database in SQL and that’s the user license. The way the system keeps that is by storing it in a table called $ndo$srvproperty that lives in the master database. So I’m going to create a new table in the master database and use that to store details on my NAV databases/companies. Here’s the SQL to create the table and grant permission to public:


USE [master]
GO
/****** Object:  Table [dbo].[IntergenNAVWhereAmI]    Script Date: 05/09/2008 20:06:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[IntergenNAVWhereAmI](
 [timestamp] [timestamp] NOT NULL,
 [Database] [varchar](30) NOT NULL,
 [Company] [varchar](30) NOT NULL,
 [Title] [varchar](50) NOT NULL,
 [Title Background Colour] [int] NOT NULL,
 [Image Type] [int] NOT NULL,
 [Support Message] [varchar](250) NOT NULL,
 [Title Background Custom Colour] [int] NOT NULL,
 [Show Info] [tinyint] NOT NULL,
 CONSTRAINT [IntergenNAVWhereAmI$0] PRIMARY KEY CLUSTERED 
(
 [Database] ASC,
 [Company] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
GRANT SELECT, DELETE, INSERT, UPDATE ON IntergenNAVWhereAmI TO PUBLIC
GO
SET ANSI_PADDING OFF

OK. So now we have a table we can work with. The next thing is that in each database I’m going to use this with, I’m going to need a view that references this table so NAV can access it via a linked table (you know that NAV can have linked tables that are based upon views which point to tables in other databases right?)

Here’s my code to create the view:


USE [Demo Database NAV (5-0)]
GO
/****** Object:  View [dbo].[Intergen NAV Where Am I]    Script Date: 05/09/2008 20:04:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[Intergen NAV Where Am I] AS
SELECT [timestamp]
      ,[Database]
      ,[Company]
      ,[Title]
      ,[Title Background Colour]
      ,[Image Type]
      ,[Support Message]
      ,[Title Background Custom Colour]
   ,[Show Info]
  FROM [master].[dbo].[IntergenNAVWhereAmI]

If you don’t create this view you won’t be able to compile the table in NAV since the table is a linked table.

Now you can import my objects into the database. Sorry for the massive amount of code but at least you can paste this into a text file and import it (I decided to stick the code at the end of the post rather than in the middle of this text.)

So we’re nearly there. The next thing is to add this code to your codeunit 1 CompanyOpen() function:


IF GUIALLOWED THEN
  FORM.RUN(FORM::"Where Am I");

You’ll probably need to hack the code to change the path for the bitmaps and create the bitmap files in order to get the form to compile and obviously you must have the view created in the database before you can compile the table.

That’s it. Obviously there’s a whole heap of other things to do like to make the setup form accessible from somewhere (menu option.)

Sadly I couldn’t address the problem of being able to click on the Form and press ESC. I really want the form to stay in the background and never come to the front but I don’t think I can do this either.

So if anyone knows how to solve the these problems or feels like finishing it off, feel free to have a go – just post a comment and a link to wherever you post your stuff.

This still isn’t a great solution and I think Microsoft really need to add this as a standard feature – and make it so we can change something really useful like the window colour, etc.

Oh yer, this code is posted without warranty or guarantees. If you decide to try and implement this code and you break something – don’t call me.

Here's the code...


OBJECT Table 50500 Intergen NAV Where Am I
{
  OBJECT-PROPERTIES
  {
    Date=08/05/08;
    Time=[ 2:37:12 PM];
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    DataPerCompany=No;
    LinkedObject=Yes;
  }
  FIELDS
  {
    { 1   ;   ;Database            ;Text30         }
    { 2   ;   ;Company             ;Text30         }
    { 3   ;   ;Title               ;Text50         }
    { 4   ;   ;Title Background Colour;Option     ;OptionString=[ ,Green,Red,Yellow,Custom] }
    { 5   ;   ;Image Type          ;Option        ;OptionString=[ ,Live,Test,Unknown] }
    { 6   ;   ;Support Message     ;Text250        }
    { 7   ;   ;Title Background Custom Colour;Integer }
    { 8   ;   ;Show Info           ;Boolean        }
  }
  KEYS
  {
    {    ;Database,Company                        ;Clustered=Yes }
  }
  CODE
  {

    BEGIN
    END.
  }
}

OBJECT Form 50500 Where Am I Setup List
{
  OBJECT-PROPERTIES
  {
    Date=08/05/08;
    Time=[ 2:38:09 PM];
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    Width=16500;
    Height=6710;
    TableBoxID=1000000000;
    SourceTable=Table50500;
  }
  CONTROLS
  {
    { 1000000000;TableBox;220 ;220  ;16060;5500 ;HorzGlue=Both;
                                                 VertGlue=Both }
    { 1000000001;TextBox;0    ;0    ;4400 ;0    ;HorzGlue=Both;
                                                 ParentControl=1000000000;
                                                 InColumn=Yes;
                                                 SourceExpr=Database }
    { 1000000002;Label  ;0    ;0    ;0    ;0    ;ParentControl=1000000001;
                                                 InColumnHeading=Yes }
    { 1000000003;TextBox;0    ;0    ;4400 ;0    ;ParentControl=1000000000;
                                                 InColumn=Yes;
                                                 SourceExpr=Company }
    { 1000000004;Label  ;0    ;0    ;0    ;0    ;ParentControl=1000000003;
                                                 InColumnHeading=Yes }
    { 1000000018;CheckBox;8202;770  ;1700 ;440  ;ParentControl=1000000000;
                                                 InColumn=Yes;
                                                 ShowCaption=No;
                                                 SourceExpr="Show Info" }
    { 1000000019;Label  ;0    ;0    ;0    ;0    ;ParentControl=1000000018;
                                                 InColumnHeading=Yes }
    { 1000000005;TextBox;0    ;0    ;4400 ;0    ;ParentControl=1000000000;
                                                 InColumn=Yes;
                                                 SourceExpr=Title }
    { 1000000006;Label  ;0    ;0    ;0    ;0    ;ParentControl=1000000005;
                                                 InColumnHeading=Yes }
    { 1000000007;TextBox;0    ;0    ;3157 ;0    ;ParentControl=1000000000;
                                                 InColumn=Yes;
                                                 SourceExpr="Title Background Colour" }
    { 1000000008;Label  ;0    ;0    ;0    ;0    ;ParentControl=1000000007;
                                                 InColumnHeading=Yes }
    { 1000000013;TextBox;16333;0    ;4400 ;0    ;ParentControl=1000000000;
                                                 InColumn=Yes;
                                                 SourceExpr="Title Background Custom Colour" }
    { 1000000014;Label  ;0    ;0    ;0    ;0    ;ParentControl=1000000013;
                                                 InColumnHeading=Yes }
    { 1000000009;TextBox;0    ;0    ;1980 ;0    ;ParentControl=1000000000;
                                                 InColumn=Yes;
                                                 SourceExpr="Image Type" }
    { 1000000010;Label  ;0    ;0    ;0    ;0    ;ParentControl=1000000009;
                                                 InColumnHeading=Yes }
    { 1000000011;TextBox;0    ;0    ;4400 ;0    ;ParentControl=1000000000;
                                                 InColumn=Yes;
                                                 SourceExpr="Support Message" }
    { 1000000012;Label  ;0    ;0    ;0    ;0    ;ParentControl=1000000011;
                                                 InColumnHeading=Yes }
    { 1000000015;CommandButton;9240;5940;2200;550;
                                                 HorzGlue=Right;
                                                 VertGlue=Bottom;
                                                 Default=Yes;
                                                 PushAction=LookupOK;
                                                 InvalidActionAppearance=Hide }
    { 1000000016;CommandButton;11660;5940;2200;550;
                                                 HorzGlue=Right;
                                                 VertGlue=Bottom;
                                                 Cancel=Yes;
                                                 PushAction=LookupCancel;
                                                 InvalidActionAppearance=Hide }
    { 1000000017;CommandButton;14080;5940;2200;550;
                                                 HorzGlue=Right;
                                                 VertGlue=Bottom;
                                                 PushAction=FormHelp }
  }
  CODE
  {

    BEGIN
    END.
  }
}

OBJECT Form 50501 Where Am I
{
  OBJECT-PROPERTIES
  {
    Date=09/05/08;
    Time=[ 7:52:40 PM];
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    XPos=0;
    YPos=0;
    Width=17270;
    Height=5060;
    Editable=No;
    BackColor=11250603;
    BorderStyle=None;
    CaptionBar=None;
    Minimizable=No;
    Maximizable=No;
    Sizeable=No;
    SaveControlInfo=No;
    SavePosAndSize=No;
    SaveColumnWidths=No;
    InsertAllowed=No;
    DeleteAllowed=No;
    ModifyAllowed=No;
    SaveTableView=No;
    OnOpenForm=BEGIN
                 SetDetails();
               END;

    OnQueryCloseForm=VAR
                       l_ApplicationManagement@1000000000 : Codeunit 1;
                     BEGIN
                       //MESSAGE(FORMAT(l_ApplicationManagement.CanInfoFormClose));
                     END;

  }
  CONTROLS
  {
    { 1000000009;Frame  ;0    ;0    ;17270;5060 ;Focusable=No;
                                                 ShowCaption=No;
                                                 Border=No }
    { 1000000001;Image  ;0    ;0    ;2310 ;2310 ;Name=Tick;
                                                 ParentControl=1000000009;
                                                 InFrame=Yes;
                                                 Bitmap=C:\Users\davidr\Desktop\tips.bmp }
    { 1000000000;Image  ;0    ;0    ;2310 ;2310 ;Name=Warning;
                                                 ParentControl=1000000009;
                                                 InFrame=Yes;
                                                 Bitmap=C:\Users\davidr\Desktop\Warning.bmp }
    { 1000000002;Image  ;0    ;0    ;2310 ;2310 ;Name=Question;
                                                 ParentControl=1000000009;
                                                 InFrame=Yes;
                                                 Bitmap=C:\Users\davidr\Desktop\Help.bmp;
                                                 OnPush=VAR
                                                          l_IntergenNAVWhereAmI@1000000001 : Record 50500;
                                                        BEGIN
                                                        END;
                                                         }
    { 1000000005;TextBox;3520 ;2530 ;7260 ;660  ;Editable=No;
                                                 Focusable=No;
                                                 ParentControl=1000000009;
                                                 InFrame=Yes;
                                                 Border=No;
                                                 FontSize=12;
                                                 CaptionML=ENZ=Database;
                                                 SourceExpr=g_DatabaseName }
    { 1000000006;Label  ;110  ;2530 ;3300 ;660  ;ParentControl=1000000005;
                                                 FontSize=12 }
    { 1000000003;TextBox;2310 ;0    ;14850;2310 ;Name=Title;
                                                 Editable=No;
                                                 Focusable=No;
                                                 ParentControl=1000000009;
                                                 InFrame=Yes;
                                                 BackTransparent=Yes;
                                                 Border=No;
                                                 FontSize=24;
                                                 SourceExpr=g_Title }
    { 1000000004;TextBox;3520 ;3300 ;7260 ;660  ;Editable=No;
                                                 Focusable=No;
                                                 ParentControl=1000000009;
                                                 InFrame=Yes;
                                                 Border=No;
                                                 FontSize=12;
                                                 CaptionML=ENZ=Company;
                                                 SourceExpr=g_CompanyName }
    { 1000000007;Label  ;110  ;3300 ;3300 ;660  ;ParentControl=1000000004;
                                                 FontSize=12 }
    { 1000000008;TextBox;110  ;4290 ;17050;660  ;Editable=No;
                                                 Focusable=No;
                                                 ParentControl=1000000009;
                                                 InFrame=Yes;
                                                 Border=No;
                                                 FontSize=12;
                                                 CaptionML=ENZ=Database;
                                                 SourceExpr=g_SupportMessage }
  }
  CODE
  {
    VAR
      g_Title@1000000000 : Text[50];
      g_DatabaseName@1000000001 : Text[50];
      g_CompanyName@1000000002 : Text[50];
      g_SupportMessage@1000000003 : Text[250];

    PROCEDURE SetDetails@1000000001();
    VAR
      l_IntergenNAVWhereAmI@1000000001 : Record 50500;
      l_Session@1000000000 : Record 2000000009;
    BEGIN
      CurrForm.Tick.VISIBLE(FALSE);
      CurrForm.Warning.VISIBLE(FALSE);
      CurrForm.Question.VISIBLE(FALSE);

      l_Session.SETRANGE("My Session", TRUE);

      IF  l_Session.FINDFIRST THEN
        g_DatabaseName := l_Session."Database Name"
      ELSE
        g_DatabaseName := 'Database Not Found!';

      g_CompanyName := COMPANYNAME;

      IF NOT l_IntergenNAVWhereAmI.GET(g_DatabaseName, g_CompanyName) THEN BEGIN
        l_IntergenNAVWhereAmI.Database := g_DatabaseName;
        l_IntergenNAVWhereAmI.Company := g_CompanyName;
        l_IntergenNAVWhereAmI."Image Type" := l_IntergenNAVWhereAmI."Image Type"::Unknown;
        l_IntergenNAVWhereAmI.Title        := g_CompanyName;
        l_IntergenNAVWhereAmI."Title Background Colour" := l_IntergenNAVWhereAmI."Title Background Colour"::" ";
        l_IntergenNAVWhereAmI."Image Type" := l_IntergenNAVWhereAmI."Image Type"::Unknown;
        l_IntergenNAVWhereAmI."Support Message" := 'This database was automatically added to the ''Where Am I'' register.';
        l_IntergenNAVWhereAmI."Show Info" := TRUE;
        l_IntergenNAVWhereAmI.INSERT;
      END ELSE
        IF NOT l_IntergenNAVWhereAmI."Show Info" THEN
          CurrForm.CLOSE;

      CASE l_IntergenNAVWhereAmI."Image Type" OF
        l_IntergenNAVWhereAmI."Image Type"::Live :
          BEGIN
            CurrForm.Tick.VISIBLE(TRUE);
          END;
        l_IntergenNAVWhereAmI."Image Type"::Test :
          BEGIN
            CurrForm.Warning.VISIBLE(TRUE);
          END;
        l_IntergenNAVWhereAmI."Image Type"::Unknown :
          BEGIN
            CurrForm.Question.VISIBLE(TRUE);
          END;
      END;

      g_Title := l_IntergenNAVWhereAmI.Title;
      g_SupportMessage := l_IntergenNAVWhereAmI."Support Message";
    END;

    BEGIN
    END.
  }
}

OBJECT Form 50502 Where Am I Setup Card
{
  OBJECT-PROPERTIES
  {
    Date=08/05/08;
    Time=[ 5:15:14 PM];
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    Width=9790;
    Height=6160;
    InsertAllowed=No;
    DeleteAllowed=No;
    SourceTable=Table50500;
  }
  CONTROLS
  {
    { 1   ;Frame        ;220  ;220  ;9350 ;4950 ;HorzGlue=Both;
                                                 VertGlue=Both;
                                                 ShowCaption=No }
    { 2   ;TextBox      ;3850 ;440  ;5500 ;440  ;Editable=No;
                                                 ParentControl=1;
                                                 InFrame=Yes;
                                                 SourceExpr=Database }
    { 3   ;Label        ;440  ;440  ;3300 ;440  ;ParentControl=2 }
    { 4   ;TextBox      ;3850 ;990  ;5500 ;440  ;Editable=No;
                                                 ParentControl=1;
                                                 InFrame=Yes;
                                                 SourceExpr=Company }
    { 5   ;Label        ;440  ;990  ;3300 ;440  ;ParentControl=4 }
    { 6   ;TextBox      ;3850 ;1540 ;5500 ;440  ;ParentControl=1;
                                                 InFrame=Yes;
                                                 SourceExpr=Title }
    { 7   ;Label        ;440  ;1540 ;3300 ;440  ;ParentControl=6 }
    { 8   ;TextBox      ;3850 ;2090 ;2750 ;440  ;ParentControl=1;
                                                 InFrame=Yes;
                                                 SourceExpr="Title Background Colour" }
    { 9   ;Label        ;440  ;2090 ;3300 ;440  ;ParentControl=8 }
    { 10  ;TextBox      ;3850 ;2640 ;2750 ;440  ;ParentControl=1;
                                                 InFrame=Yes;
                                                 SourceExpr="Image Type" }
    { 11  ;Label        ;440  ;2640 ;3300 ;440  ;ParentControl=10 }
    { 12  ;TextBox      ;3850 ;3190 ;5500 ;440  ;ParentControl=1;
                                                 InFrame=Yes;
                                                 SourceExpr="Support Message" }
    { 13  ;Label        ;440  ;3190 ;3300 ;440  ;ParentControl=12 }
    { 14  ;TextBox      ;3850 ;3740 ;1700 ;440  ;ParentControl=1;
                                                 InFrame=Yes;
                                                 SourceExpr="Title Background Custom Colour" }
    { 15  ;Label        ;440  ;3740 ;3300 ;440  ;ParentControl=14 }
    { 16  ;CheckBox     ;3850 ;4290 ;440  ;440  ;ParentControl=1;
                                                 InFrame=Yes;
                                                 ShowCaption=No;
                                                 SourceExpr="Show Info" }
    { 17  ;Label        ;440  ;4290 ;3300 ;440  ;ParentControl=16 }
    { 18  ;CommandButton;7370 ;5390 ;2200 ;550  ;HorzGlue=Right;
                                                 VertGlue=Bottom;
                                                 PushAction=FormHelp }
  }
  CODE
  {

    BEGIN
    END.
  }
}

Does anyone use NAV in Wyoming, West Virginia, New Mexico, or Alaska?

2008-05-01T22:44:00.005+12:00

My blog has just reached 1000+ visitors from the USA. Cool! But there are four states missing. It's sad, but I had to look on another web site to find out what the missing states were - Google Analytics doesn't show the state names of those that have no visitors and I'm not good at the geography of the USA.

Now excuse me for being a complete dunce, but are there any people actually living in Alaska? Do they have internet connections?

Before I moved to New Zealand I wasn't even sure if they had cinemas, so as far as I know Wyoming doesn't even have a state lottery. Maybe West Virginia is three-quarters forest. Who knows, maybe New Mexico was the testing ground for the first atomic bomb. Perhaps Alaska is the United State's largest state and is over twice the size of Texas; measuring from north to south the state is approximately 1,400 miles long and measuring from east to west it is 2,700 miles wide.

The internet's an amazing thing isn't it? Anyway, I'm going to look for some blogs in Alaska and leave a comment or a track back.

On a completely unrelated topic, I'm now a Dynamics NAV expert (see the current featured article on MSDynamicsWorld.com.) Now when I started my first job many years ago, an old and wise programmer told me what he thought of experts. He said, "So you're the new expert are you? Well X is an unknown quantity and spurt is a drip under pressure. X-spurt. Geddit?"

Ahhh. English humour.

Partner Sauce

2008-04-30T21:22:00.003+12:00

PartnerSource seems to be going through something of a revolution. If you use the Global English site, you have probably been using the new saucy version for a few days now. If, like me, you sign in to a localized version, you may be unaware of the changes that are coming.

So what is so good about the changes that warrant a blog post? I’ll tell you what, three little letters: R S S.

It is now possible to subscribe to an RSS feed for News, Sales & Marketing, Support & Deployment and Training & Certification, meaning you read the news in a Vista sidebar gadget, from within Outlook or on your mobile phone. But for me the ultimate feeds are the Most Recent KB Articles and Most Viewed KB Articles. Now you may think that getting excited about this is a sure sign that I need to get out more or at least get a hobby, but believe me this is going to make my life so much easier.

It seems that I am not the only one that has struggled to find out what is going on in the NAV world without a lot of digging around and checking PartnerSource every day, just in case there is something new. For more details on the changes to PartnerSource and a warm feeling of love from Microsoft, check out the link
Get Ready for Exciting, New Changes to PartnerSource

For some strange reason whenever I clicked on the RSS Feed links on the NAV product site, my Internet Explorer crashed – this was because of Skype and as soon as I uninstalled Skype on my machine it all worked fine. I will report this to the team to see if they can fix it up. The non-product site RSS feeds worked fine.

You can find the NAV-related product feeds on this page: https://mbs.microsoft.com/partnersource/solutions/nav/

Runtime Errors Suck!

2008-04-23T21:08:00.007+12:00

I hate runtime errors. I think that functions in C/AL that can generate runtime errors should be deprecated where possible or altered in their function. Let me explain with an example.

Let's say you have two fields both called Description on two different tables. One of your tables is the Item table and the other is a new table called "New Item" (OK so I'm not going to win any prizes for imaginative table names in this example.) You want to make an assignment from one field value to another so you would do something like this:


l_NewItem.Description := l_Item.Description;

When your code runs the Description gets copied across and everyone's a happy bunny. But, let's say your customer says they want their item description to be made 20 characters bigger. What happens then?

Well let's assume that you increase the size of the Item Description field by 20 characters. Everything still works. Or so you think.

When NAV executes the line of code that previously worked fine on a record that has more characters in the new larger string than will fit in the other string, you (or more likely the first user that comes across that bit of functionality in your production system) will get a Runtime Error. Blurgghhh!

I have programmed in a few languages and C/AL is the only language I have ever come across that does this. What would other languages do? They would truncate the value and continue. They would assume, "hey, the programmer wants me to put a 50 character string in a 30 character string, he must know what he's doing, so I'll just give him as much as I can."

I like that, it's friendly, it makes me feel warm and fuzzy. Why can't C/AL do the same?

But there is a far worse function that can throw runtime errors and this should be banned altogether! TRANSFERFIELDS.

TRANSFERFIELDS is evil. It has to go.

What does it do? Well it, er, transfers, erm, fields (duh?) It is used to transfer fields from one table to another. Sounds cool doesn't it? How does it decide which fields should be copied? It uses the field ID.

What? The totally arbitrary field ID? Surely not, that would be crazy. Yup you heard it the field ID. NAV will attempt to make an assignment between two fields with the same ID, but different names and different types. And what do you think happens if the field types are incompatible?

Run Time Error.

The reason this function is evil is it lures the unsuspecting programmer into its little trap. It looks innocent. It looks like it may save you some time. Don't be lazy. Assign the fields one by one. Think about it, if you have 10 lines of code that assign each field from one table to another, anyone reading your code knows exactly what is happening. If you really want to be good, create a function on the table called CopyFromItem() or something similar. Then do the field assignments in the function so your code would look something like this:


l_NewItem.CopyFromItem(l_Item);

Now isn't that better?

So what prompted me to have this little rant? Well I am doing an upgrade from v3.70 to v5.00 at the moment and the damn thing just failed with a run time error. Some of the code in the standard upgrade toolkit gave me this error message:


The two fields below must have the same type:  
Field: JobTaskNo <-- Table 
Table: Temp Job Task Phase Step Comb. <-- Temp Phase Step Task Doc Line 
Type: Code20 <-- Integer

Can you guess what caused this error?

TRANSFERFIELDS!

So if the expert coders at Microsoft get caught out, what chance do we have?

Field Captions and the CaptionClass Property

2008-04-14T22:28:00.004+12:00

I recently needed to have a field that had a changing field caption based upon some conditions. I knew I had seen this in the standard system for sales orders where the Unit Price field changes between “Unit Price Incl. VAT” and “Unit Price Excl. VAT” depending on how you tick the “Prices Including VAT” field so that is where I started.

If you look on the Unit Price field properties on the Sales Line table you will see that the CaptionClass property is set to GetCaptionClass(FIELDNO("Unit Price")).

That looks like a function call so I take a look in the functions on the table.


GetCaptionClass(FieldNumber : Integer) : Text[80]
IF NOT SalesHeader.GET("Document Type","Document No.") THEN BEGIN
  SalesHeader."No." := '';
  SalesHeader.INIT;
END;
IF SalesHeader."Prices Including VAT" THEN
  SalesPricesIncVar := 1
ELSE
  SalesPricesIncVar := 0;
CLEAR(SalesHeader);
EXIT('2,' + FORMAT(SalesPricesIncVar) + ',' + GetFieldCaption(FieldNumber));

Well this seems to be returning either ‘2,1,Unit Price’ or ‘2,0,Unit Price’ depending on whether the Sales Header has the “Prices Including VAT” field set to TRUE or FALSE. How weird is that? Clearly that is not what is displaying on the form.

If you take a look in the C/SIDE Reference Guide (select the option from the Help menu of the application,) there is an interesting line that says “The expression is then interpreted by Trigger 15 in CodeUnit 1.”

Let’s take a look at that codeunit trigger.


CaptionClassTranslate(Language : Integer;CaptionExpr : Text[80]) : Text[80]
CommaPosition := STRPOS(CaptionExpr,',');
IF (CommaPosition > 0) THEN BEGIN
  CaptionArea := COPYSTR(CaptionExpr,1,CommaPosition - 1);
  CaptionRef := COPYSTR(CaptionExpr,CommaPosition + 1);
  CASE CaptionArea OF
    '1' : EXIT(DimCaptionClassTranslate(Language,CaptionRef));
    '2' : EXIT(VATCaptionClassTranslate(Language,CaptionRef));
    '3' : EXIT(CaptionRef);
  END;
END;
EXIT('');

This is one of those funny functions that gets called by the system whether you like it or not – you don’t pass the parameters to it but you can guess that what the values contain. I am guessing that in my example the CaptionExpr will contain either ‘2,1,Unit Price’ or ‘2,0,Unit Price’.

On examining the code, I can see that we are pulling out a number from the start of the string into a variable called CaptionArea (which in our case is 2) and using that to either run a new function or return the part of the string that appeared after the number. In our example we are calling VATCaptionClassTranslate(Language,CaptionRef).

So, let’s take a look at what this function does:


VATCaptionClassTranslate(Language : Integer;CaptionExpr : Text[80]) : Text[30]
CommaPosition := STRPOS(CaptionExpr,',');
IF (CommaPosition > 0) THEN BEGIN
  VATCaptionType := COPYSTR(CaptionExpr,1,CommaPosition - 1);
  VATCaptionRef := COPYSTR(CaptionExpr,CommaPosition + 1);
  CASE VATCaptionType OF
    '0' : EXIT(COPYSTR(STRSUBSTNO('%1 %2',VATCaptionRef,Text016),1,30));
    '1' : EXIT(COPYSTR(STRSUBSTNO('%1 %2',VATCaptionRef,Text017),1,30));
  END;
END;
EXIT('');

This function starts by stripping out another parameter into a variable called VATCaptionType and the remainder of the string goes into CaptionRef. Then as you can see the VATCaptionType is evaluated and it returns either ‘Unit Price Excl. VAT’ or ‘Unit Price Incl. VAT’. To know this you have to know that Text016 and Text017 contain ‘Excl VAT’ and ‘Incl. VAT’ respectively.

So that’s it. A good example of how to achieve dynamic field captions using the standard application.

But just to round off, what if I wanted to have my own field with a dynamics caption? Well if you go back to the CaptionClassTranslate function in codeunit 1, you’ll see that option 3 will simply return the value that you passed it back to the caption for the field. This is how you would do it.

Let’s say that we are implementing NAV for a client that wants three addition fields on the Customer Card, the currently have them in their old system called “User Field 1”, “User Field 2” and “User Field 3”. Don’t you just hate that sort of thing? Anyway they say that they want to change the caption to something more meaningful but they can’t decide on what to call them (it’s a lame example I know but it’s late so stick with me.) Being a cunning NAV developer you decide to create three setup fields on the Sales & Receivables Setup table called “Field Caption 1”, “Field Caption 2” and “Field Caption 3”. You can then let the users type the caption they want in these fields and use them for the field captions for the new fields you will add on the customer card.

First of all, you create the fields on the Customer table as “User Field 1”, “User Field 2” and “User Field 3”. Then you create a little function on the Customer table that will take an integer parameter (valued as 1, 2 or 3) and will return a Text value that is the right caption. Let’s call our function “UserFieldCaption”. It might look something like this:


UserFieldCaption(p_FieldNo : Integer) : Text[80]
l_SalesReceivSetup.GET('');
CASE p_FieldNo OF
  1: EXIT(l_SalesReceivSetup."Field Caption 1");
  2: EXIT(l_SalesReceivSetup."Field Caption 2");
  3: EXIT(l_SalesReceivSetup."Field Caption 3");
  ELSE
    ERROR('UserFieldCaption function on Customer table called with invalid field number of %1',p_FieldNo);
END;

Now on our User Field 1 on the Customer table we would set the CaptionClass property to


'3,'+UserFieldCaption(1)

The other two User Field CaptionClass values will be similar – hopefully you can figure this out yourself.

Once you have compiled everything, set the values of the captions on the Sales & Receivables Setup and Open the Customer table from the object designer. There you should see the caption for your new fields using the value you entered on the setup table.

Try Catch in Dynamics NAV

2008-04-09T21:53:00.009+12:00

This has to be up there on my list of "all time desirable features" for NAV. As you probably know NAV has an ERROR function that is used to, well, throw errors. It will abort the current transaction (and roll back to the point of the last commit) and display the text message that you selected in an error dialogue box.

The thing is, sometimes you don't want it to do the abort and rollback. For example if we are importing a file or maybe a series of files, we may just want to log the error somewhere and carry on with the next file. You can do this in NAV 5.0 using the new GETLASTERRORTEXT function.

Here's an example of how to do it.

First of all, create a simple codeunit that, when run, will throw an error:

OBJECT Codeunit 50000 ThrowError
{
  OBJECT-PROPERTIES
  {
    Date=09/04/08;
    Time=[ 9:34:55 PM];
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    OnRun=BEGIN
            // Call a function that throws an error.
            DoSomething();
          END;

  }
  CODE
  {

    PROCEDURE DoSomething@1000000000();
    BEGIN
      ERROR('Hey Look I threw an error.');
    END;

    BEGIN
    END.
  }
}

When you run this codeunit you get the following error message displayed.

Now create another codeunit that will call the first one and trap the error it generates:

OBJECT Codeunit 50001 Test Throw Error
{
  OBJECT-PROPERTIES
  {
    Date=09/04/08;
    Time=[ 9:35:58 PM];
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    OnRun=VAR
            l_ThrowError@1000000000 : Codeunit 50000;
          BEGIN
            IF NOT l_ThrowError.RUN THEN
              MESSAGE(GETLASTERRORTEXT+ ' - or did I?');
          END;

  }
  CODE
  {

    BEGIN
    END.
  }
}

When you run this second codeunit, even though it calls the first codeunit, you don't see the error message. Instead you see this:

You'll need to make a COMMIT before trying to trap the return code from running the codeunit but if you have uncommitted transactions you'll get a run time error telling you this. Unfortunately this only works when you Run a codeunit so you either have to use lots of codeunits or write some kind of clever dispatcher that allows you to set the action on the codeunit first and then run it.

So my most desired feature would require some language additions to C/AL. Introduce a TRY CATCH language construct – it would work similar to an IF ELSE statement. Here is an example:


TRY
  BEGIN
    DoSomething();
    DoSomethingElse();
  END
CATCH
  BEGIN
    MESSAGE(GETLASTERRORTEXT);
  END;

I have put the BEGIN and END in the CATCH part to illustrate how it work in a similar manner to the IF ELSE construct but it wouldn't be needed. The neat thing about this is you would not need to use a codeunit just to be able to trap the error. The same rules regarding commits, etc. would apply.

I guess I'll just nip over to the Connect site and suggest this little beauty for the product team to ponder over.

Get Stuff Done – Go Home Early - Play with your Wii

2008-03-25T23:00:00.010+13:00

I've been using ActionThis since the early beta stages and I love it! The ActionThis team are running a promotion whereby you can sign up for a 1-month free trial if you sign up with the promotional code. After that you can continue to use the website for free!

Click this link to go to the site http://www.actionthis.com/product/trial.aspx

Enter the Referral Code INT521

You can use ActionThis to help you and your team work together more effectively, using the power of the web combined with Microsoft Office.

Thousands of people worldwide use http://www.actionthis.com/ to manage the tasks small businesses, teams and their partners need to complete to succeed. Delegate tasks from Microsoft Outlook, connect with your team on the ActionThis task management website, track progress and take action with live reports delivered to your email inbox. ActionThis is free to try, and simple to use. Less time following up, more tasks completed, your business is more productive. ActionThis was designed and developed by Intergen in New Zealand and will help you and your team get things done.

How ActionThis helps you get stuff done:
Use Microsoft Outlook to create and assign tasks to yourself, your team, your partners,
Organize and access these tasks from anywhere using Microsoft Outlook or the http://www.actionthis.com/ website,
Keep track of progress, projects, and workload with reports emailed to your email inbox,
Keep on top of overdue tasks with live alerts designed to help you take action quickly,
Export and analyze your progress with Microsoft Excel,
Telephone and email support is free.

Try it for free. Sign up for a one month free trial at http://www.actionthis.com/product/trial.aspx and use this referral code: INT521.

Dynamics NAV Gets Connected

2008-03-25T20:31:00.002+13:00

In my first ever blog post I wrote about a site where ideas for new product features can be posted. Today I came across a news article on PartnerSource that pointed me to a new place for logging feature enhancements, the Connect for Microsoft Dynamics site. You will need to register a Windows Live ID in order to use the site, which site is a big improvement on the old public forum.

Microsoft seems to be committed of late to listening to feedback from partners and customers and this is a very welcome move. Recently they requested feedback on how the online help can be improved through Convergence and some of the public forums. This new listening, caring Microsoft makes me feel warm and fuzzy and I truly believe that we should all be feeding back to Microsoft where we think the product can be improved. Long gone are the days when this was a pointless exercise, so sign up and give it a go!

The site seems to have been active since October 2007 but there are only 7 suggestions for NAV – maybe it hasn't been that well publicised? Well here's a suggestion to get things going. If you want to vote for this suggestion you can register and click the rating. Here is the suggestion:

Every implementation of NAV I have ever been involved with has a test system and a live system. I am assuming this is a universally accepted practice – you don't want to be applying programming modifications to your live system without testing them first. In nearly every implementation of NAV I have been involved with, there have been instances at least one user has been logged in to the live system and thought they were logged in to the test system and they have mistakenly posted entries in their live system. The request I commonly receive is to make it so that it is immediately obvious to the users which system they are in: live or test. This should be immediately visually obvious – to me there is only one way to achieve this and that is to change the colour scheme of the windows. You can change the text in the title bar (via 3^rd party utilities or by renaming the company) but this is not immediately visually obvious. Another less common requirement is for users that need to work in more than one company at once and they want to see which company they have open. Again they want something that is instantly obvious and don't want to be reading titles of windows. So my suggestion is: allow the Window Colour and Appearance options to be specified at a Company AND Database level. The Database level is required so that if a user restores their live system over their test system (something they frequently need to do) they do not lose the all important colour settings. At the database level it could be stored in a table similar to the $ndo$srvproperty table in the master database – this would have one record for each NAV database. Restoring a database would not overwrite this value. At the company level, it would be set in a table that is company specific.

Quick “Debugging” Tip

2008-03-18T20:23:00.003+13:00

Sometimes I want to check something in a bit of code and I don't want to have to step through the debugger. Maybe I want to check the filters that are being applied to a table, maybe it's the value in a particular field. Here's a quick bit of code that you can use to check on a value and, if you're not happy with the value abort the process.

In my example, I wanted to check the table view that had been applied to a record variable. I want the program to stop at the point of my message (something the MESSAGE function doesn't do as NAV saves up messages for a convenient point in time.) I also want to be able to abort the execution in order to give me chance to fix something and try again (something you can't do when debugging with the debugger unless you kill the NAV application in a brutal way.)

Here's the code I put in:

IF NOT CONFIRM(g_JobLedgerEntry.GETVIEW) THEN

ERROR('');

Notice that I'm using the Silent Abort from Vjeko's blog. This will display a confirmation box with the details I am looking for. If I click No, the process aborts and rolls back, allowing me to change some values and run it again.

Look at me! I’m a balloon!

2008-03-14T21:26:00.002+13:00

This has got nothing to do with Dynamics NAV so if you're looking for news on ERP systems, leave now. If you are squeamish about medical procedures then you should also leave. Hi Dad – just you and me reading this now.

I am gluten intolerant. That means if I eat anything with Gluten in it I feel crook. Today I went for a Esophagogastroduodenoscopy to see if I have coeliac disease (the disease sounds bad but basically it means you can't eat gluten without it making you crook and you have damage to the bits of your gut that help you absorb nutrients.) There is no cure other than to stop drinking beer, eating pizza, burgers, toast, pasta, etc. So, not too bad, right?

Anyway, back to the procedure. I was given the choice of a local anaesthetic spray (that numbs the throat) or the spray and a sedative. I was told that if I had just the local I would be able to watch the procedure on a video monitor. I was also told that the guy before me just had the local and he was able to keep himself calm, control his breathing and he got through it fine. I was also told that the sedative would make me feel drowsy and unable to do pretty much anything for the rest of the day. I was attracted to the idea of watching the procedure on the video monitor (my wife says this is the geek in me winning out of the sensible part of me.) Now if you ever find yourself in the unfortunate position to be asked if you want a sedative before someone shoves a piece of hose down your throat and pumps your stomach up like a balloon, the correct answer is "Hell yes!"

As for the video: the doctor stood in front of my screen so I didn't see a thing! But the procedure was so unpleasant that there could have been a small family of pixies living in my stomach and I wouldn't have cared. There were several people watching the procedure (I am guessing they were students) and none of them would make eye-contact afterward. This was probably due to the strange retching-gagging-belching noise I was making and the look of fear on my face. I think they were thinking "Dear God! What did we just do to that man?"

So the purpose of this blog posting is that if anyone is searching Google with the question: "Should I take the sedative before having an EGD, OGD, upper GI endoscopy (UGIE), or gastroscopy" then they can read this and know that no matter how appealing seeing their insides on a video screen seems, you should take the drugs!

Dynamics NAV SQL Security Roles

2008-03-12T20:59:00.002+13:00

I'm studying for the Installation and Configuration exam at the moment and have found some interesting stuff in the training material that I often need to know. I was recently asked about the SQL permissions needed to be able to create new users in Dynamics NAV and, thanks to my studies, was able to go straight to the right page in the document. I was interested to see that this topic also recently surfaced again in the DynamicsUser forum. This table is for Dynamics NAV 5.0.

Invoking the synchronization process or modifying the User table

sysadmin server role. Alternatively both a member of the securityadmin server role and a member of the db_owner database role for this database.

Creating a database

sysadmin or dbcreator server role. Alternatively, the user must have been granted the create database permission. The user must also have public access to the model database.

Altering a database

sysadmin or dbcreator server role. Alternatively a member of the db_owner or db_ddladmin database role for this database.

Creating tables within a database

sysadmin server role or be a member of the db_owner database role for this database.

Now you could argue that you don't want to grant these SQL rights to the user just to let them add users to the ERP database. This raises an interesting question: which is better, giving a user db_owner role membership and securityadmin server role or knowing that any user of the application could potentially add other users to the database? Personally I like the thought that I can pick and chose which user gets these rights from a SQL administrator's role and not leave it up to the users of the ERP to decide.

It's Good to be Yellow!

2008-03-07T12:50:00.007+13:00

I normally don't repost news from other sites, but here are a couple of items that are interesting because they are Intergen-related. It's also a nice easy way of making a post without needing to do any research or put in any real effort.

Firstly, our boys in Vegas at MIX08 have demoed some rather cool technology called TextGlow. It's a Silverlight 2 application that displays Open Office XML you can read some more about it here or on the Intergen Blog. Or view it for yourself on the textglow site. You will need to install Silverlight 2.0 beta which you can get to from that site and once it is installed restart your browser.

Secondly, I get an excuse to put a video clip on my blog. Take a look at this. This Runtime Page Optimiser speeds up web sites by between 200% and 400%

Want proof? Try this: go to http://www.websitepulse.com/help/tools.php and use the “web page test” to compare http://www.actionthis.com/ against your fave NZ website.

You can sign up for the beta program by emailing ed.robinson@actionthis.com. Tell him Gaspode sent ya.

It's nice working with such clever guys.

SIFTing through the CRUD

2008-02-28T11:38:00.004+13:00

CRUD (Create, Read, Update, Delete) has to be one of my all time favourite computer acronyms. I also like PICNIC (Problem In Chair Not In Computer) but that's a whole different story.

SIFT (Sum-Index Flow Technology) is a clever trick used by NAV to give you incredibly fast displays of aggregated totals. The Net Change and Balance fields on the Chart of Accounts screen are probably the best known examples. These fields are instantly drawn and can be filtered by dimension, posting date, etc. I have not seen this in the other ERP systems I have used and it is pretty impressive and powerful. However, with great power comes great responsibility and SIFT has its price. To get this fast performance, NAV makes a trade and instead of doing the work at the time of reading the data, NAV does the work when you create, update or delete the data. This can be OK for records that are not updated very often but you do need to be careful how many FlowFields and SumIndexes you add to tables. There's no such thing as a free lunch (unless of course you work in sales.)

I recently tried to make an update on a table with a relatively large number of records. My SQL script was simple and all it did was set one field to be the product of another two fields (e.g. Line Amount = Qty * Price). The dataset being updated contained just over 2 million records and I killed the query after 3 hours of execution. I couldn't understand why the thing was taking so long. Then I remembered SIFT! I don't pretend to be a SQL expert and there are lots of clever people out there that have written lots of clever stuff on database optimisation but what I do know is that even if my SIFT update takes a fraction of a second then 2 million fractions of a second can be several hours.

When you have a SIFT index on a table, NAV adds a SQL Trigger to the table with lots of fancy SQL code to update a number of SIFT tables with the aggregated totals. Fortunately, NAV provides the ability to disable the maintenance of SIFT tables in SQL. You can also fine-tune how many levels of data should be maintained which can again improve performance. When you go into the table designer and view the keys you can show an extra column called MaintainSIFTIndex. I went into my table designer and un-ticked this field for the SumIndex keys that were totalling the field I was updating. I compiled the table which dropped the table trigger that maintains the SIFT tables and deleted the SIFT table.

I then ran my SQL query again. It completed in 8 minutes!

I went back in to the table designer and re-activated my SIFT maintenance option and re-compiled. NAV put the triggers back and re-created my totals. This took 5 minutes. As you may know reads are the least expensive type of database operation with inserts, updates and deletes being considerably more time consuming. It is much faster to get the sum index totals updated all in one go rather than bit by bit over and over for every record that is updated.

I also have a data-conversion routine for a NAV upgrade that takes a very long time to execute – the next time I run it I am going to disable my SIFT maintenance on the tables first (and possibly disable the SQL maintenance of some of the indexes which also add time to transactions). Once the routine has completed I will rebuild the indexes and SIFT tables – it will be interesting to see how much of a performance gain I get by doing this.

I see from the posting on the Sustained Engineering Team Blog that the imminent SP1 release for NAV 5.0 will allow the Indexed Views feature of SQL 2005 to be used for SIFT instead of the current method. It will be interesting to see what affect this has on performance.

SQL Error when Importing FOB file.

2008-02-23T17:17:00.004+13:00

I frequently get support calls from customers that get an error message similar to the following when trying to import a FOB that includes changes to tables:

The following SQL Server error(s) occurred while accessing the TestTable table: 1088,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find the object "Cronus New Zealand Ltd_$TestTable" because it does not exist or you do not have permissions.

SQL:
ALTER TABLE "DATABASENAME"."dbo"." Cronus New Zealand Ltd_$TestTable" ALTER COLUMN "Message" VARCHAR(240) NOT NULL

The solution is simple and can be found in the standard documentation. In order to be able to make changes to table definitions, you need to be a member of the sysadmin server role or be a member of the db_owner database role for this database.

You may need to ask your SQL database administrator to grant these privileges to your user account and it is likely they will grant you the lower db_owner privileges rather than sysadmin and still grumble about it.

OK problem solved. Everyone’s happy, but…

If you’re anything like me, you may wonder why this is the case. Why do you need to be granted special rights by a database administrator for this task when all other rights are granted to you from within the NAV application? To be able to answer this question we need to understand a little bit about how NAV users access the database.

I first came across this issue when trying to access the NAV SQL database from other applications (such as Excel or Reporting Services) and I soon discovered that my NAV login did not allow me access to the data contained in the NAV database. New users added to Dynamics NAV through the NAV application will have no permissions on the database (well actually the user has been granted the public database role which means they have permission to connect to the database but not to do anything else.)

Incidentally, when you create a Windows login (as opposed to a Database Login) from the Dynamics NAV client, the system will create the SQL Login and map the user to the database with the public role. If you are trying to use a Database login, you must create the SQL Login first through SQL Server Management Studio.

So the question remains: how can we read the data from the tables when we have no rights to read the data? The magic happens through something called an “Application Server Role”. If you look under Security for the database you are using in SQL Server Management Studio, you will see groups for Users, Roles, Schemas, Asymmetric Keys, Certificates and Symmetric Keys. Open the Roles and then expand the Application Roles and you will one called $ndo$shadow. This role has all the permissions needed to read from every table in the NAV database.

An application server role is used to solve exactly the problem we are investigating, that is, how can you give users full access to a SQL database through your application (in this case NAV) but not let them do anything when they try and use Excel to query the data? Since security is controlled by NAV, when the user tries to access the data directly they are by-passing the permissions that have been given to them by the NAV security administrator.

The $ndo$shadow application server role is created by the application and is given a password that is too big for users to remember even if they knew how to find it. When the user runs the application, one of the first things that NAV does is run a stored procedure to set the application server role for the current session using this big password. The SQL documentation tells us that this will allow the user to use the rights allocated to the application server role for the current session until the session is terminated.

In a mysterious move, Microsoft changed this security model in version 4.0 and introduced a whole bunch of $ndo$shadow application server roles with a funny GUID thing on the end. It looks like these new roles were used for each user instead of having one for all users. I am a bit unsure as to how these roles worked since they didn’t actually work very well and I soon stopped using them. Any changes to security needed to be synchronized to these roles and many users complained of the system locking up for long periods of time. In 4.0 SP3 (I think) Microsoft introduced the ability to switch this new security mode off and revert to the good old single $ndo$shadow role for everyone. Interestingly the option allows you to select your security as being either “Enhanced” or “Standard”. I think they would have been better off calling these options “Rubbish” and “The one that works”.

So now we know how we get access to the data when our user account doesn’t have any rights, but the question of why we need dbo rights on a user account (that is not being used) to change tables is still unanswered.

I spent a few hours trying to figure this out using SQL Profiler and stepping through various scenarios. After a long frustrating session, I gave up and went to bed. Then I figured it out. Before NAV changes to use the $ndo$shadow application server role, it first checks to see if your user is a dbo and if you are a dbo it doesn’t switch to the application server role. Simple really – but worth noting that if you are a dbo, you are using your own credentials and permissions and not those of the application server role so adding permissions to the $ndo$shadow role will have no effect.

I just installed Snap Shots on my Blog

2008-02-16T12:12:00.000+13:00

Introducing Snap Shots from Snap.com

I just installed a nice little tool on this site called Snap Shots that enhances links with visual previews of the destination site, interactive excerpts of Wikipedia articles, MySpace profiles, IMDb profiles and Amazon products, display inline videos, RSS, MP3s, photos, stock charts and more.

Sometimes Snap Shots bring you the information you need, without your having to leave the site, while other times it lets you "look ahead," before deciding if you want to follow a link or not.

Should you decide this is not for you, just click the Options icon in the upper right corner of the Snap Shot and opt-out.

F5 Fumbles Find Fat-fingered Frustration

2008-02-13T23:25:00.000+13:00

Let's face it the C/AL editor is a dog. And I don't mean the "man's best friend" type of dog. I mean the type of dog that chews your slippers and pees on your carpet. The only improvements I have seen over the last 6 years are the ability to find functions using the search command and the new version 5.0 ability to indent and outdent blocks of code using the tab key.

Visual Studio programmers may have difficulty reading now as their eyes are filled with tears of laughter. Vjeko has some interesting theories as to why the C/AL editor is so bad. I wish I knew why the IDE takes a back seat for the Dynamics NAV team. Even the much-anticipated NAV "6.0" uses the same development tools (as far as I can tell from the screen shots.)

Today for the first time I realised how crazy it is to put the most useful key for C/AL developers (the F5 key which brings up a kind of poor-man's intellisense) right next to the killer F4 key which is used to delete the current line of code with no prompt or undo. Thanks for that! Save your work frequently or be very careful when reaching for the F5 key.

So what can we do to help ourselves since improving the development tools does not appear high on the list of proposed new features?

I have found my life easier since starting to use a variable naming convention that identifies if a variable is a parameter, local or global. I know this is pretty radical and against the standard programming conventions (something that should not be necessary if the IDE provided good visual cues for the developer.) For me it's simple. I put p_ in front of my parameters, g_ in front of my globals and l_ in front of my locals and I then find it so much easier to follow my code. Programming logic errors of using a local variable by mistake when you meant to use the global variable of the same name disappear in an instant.

The other thing which the NAV coders don't seem to be big on, which I find very important, is to write code for people not computers (something Vjeko has also written about) which I recently re-discovered when flicking through my old copy of Steve McConnell's excellent book "Code Complete."

Steve talks about writing "self documenting" code through the use of good function and variable names and clarity in preference to efficiency. For me the most important thing is to comment the intent of the code. How many times have you struggled to find a solution to a buggy piece of code because it is not really clear what the original programmer intended the code to do? Steve talks about designing your code in PDL (Program Design Language) which seems to be a kind of Pseudo-Code. He says it is a good idea to make you program with the PDL as comments and then fill in the code around the comments. Hey presto! You now have commented code with "headlines" of comments that can be read to illustrate the intent and purpose of the code without forcing the hapless support engineer to decipher your code.

Avoid smart programming that is just hard to follow. I recently had to go through the 3.70 to 5.00 upgrade codeunit and I was really struggling to understand why the main update routine was called twice with a slightly changed variable until I realised that the first time was just to count how many records we needed to process so we could display a progress indicator. Now this may be common practice for NAV upgrade routines but to me it was a frustrating waste of my time to have to figure this out. If two different functions had been used with names like CountRecordsForUpdate() and UpdateRecords() then I would not have had to spend time understanding what was going on.

So I would change Vjeko's rule number 2 from...

Rule No. 2: Make sure others will know what you did.

to...

Rule No. 2: Make sure others will know what you intended to do.

Dynamics NAV IDE team - I think it's time you surveyed your partners to get a list of features they want in their Dev tools for C/AL.

Dynamics NAV Application Developers - I think you need to use comments and write code that is easier to follow.

Now it's time for me to get back to my C/AL editor. Here boy!

Dynamics NAV 2009 – A Paradigm Shift for Business Solutions

2008-02-13T23:04:00.000+13:00

Dynamics NAV is already a highly-successful, award-winning ERP solution but the forthcoming release is set to bring something quite extraordinary to the middle-tier business solutions market.

The Dynamics NAV team promises a ground-breaking user interface, a state-of-the-art technology platform, and web-services enablement while remaining true to their core product values of simplicity, adaptability and ease-of-use. You have to admit that when it comes to telling you how good their products are going to be, nothing can compete with the Microsoft marketing machine.

To read more, click this link to my posting on the Intergen Blog site... The Changing Face of ERP

Who is Blogging on Dynamics NAV? (in English)

2008-02-10T22:00:00.001+13:00

I thought doing a roundup of blogs on Dynamics would be a nice easy way to make a post. I can’t believe how many there are. You can get a larger list of blogs on any subject through Technorati or doing a blog search in Google.

I have ignored any blogs that are in funny languages (i.e. not English) and any that seem to have only just started, given up after one post or not posted for a long time. The order of the blogs is the order in which they appeared in Technorati at the time of writing and then some others that I know of that didn’t appear. I am being very subjective and different people want different things from Blogs. Personally I want some form of entertainment around the subject matter that doesn’t make me think too much. If I learn something from reading the post – this is a bonus. If I missed your blog, I’m sorry. Drop a comment on the end of this post with the URL and I’ll add it (unless I don’t like it – in which case I’ll pretend I never got your comment.)

Plataan
http://plataan.typepad.com/microsoftdynamics/
I think this is a team effort but I can’t find details about the writers in English. The blog has fairly regular posts on Dynamics NAV. Technical stuff and news. I don’t read this blog too often but I do have it on my RSS Feeds. The content I have read seems good, solid, but not really that entertaining.

Kine’s info
http://msmvps.com/blogs/kine/default.aspx
I think this is the first Dynamics NAV blog I read. Not a regular poster but almost guaranteed to teach you something. This is on my RSS list.

Waldo’s Blog
http://dynamicsuser.net/blogs/waldo/default.aspx
Ahh Waldo, what would we do without you? Eric Wauters is a frequent blogger and often has the most read posts on the dynamicsuser.net forum. I really appreciate the time Waldo puts into his blog. Some of the content is a roundup of information that is available elsewhere, but that’s not necessarily a bad thing. If it’s about NAV and worth knowing, you can be almost certain to read about it on Waldo’s blog. Some postings are very technical but I often learn stuff.

Microsoft Dynamics Sustained Engineering Team
http://blogs.msdn.com/microsoft_dynamics_nav_sustained_engineering/default.aspx
Microsoft Dynamics Sustained Engineering Team. Regular posts direct from the guys that know. You should definitely add this to your regular feeds. Often technical in nature (as you would expect) it is good to hear from the Dynamics Team.

Gaspode’s Brain Dump
http://gaspodethewonderdog.blogspot.com/
The blog from the dog. I try to post the maximum amount of entertainment for the minimum amount of effort. I try very hard not to “re-post” content. I tried having a blog on dynamicsuser.net instead of blogspot as this gave me a very large number of hits but I felt a bit constrained. I didn’t feel I could really rant about Microsoft or post non-Dynamics stuff there. I like being able to review Google Analytics stats on my site – something I don’t think I could do on the other site, so I put up with the low readership and lack of comments.

Bart van Beek (Dynamics NAV Dev)
http://mibuso.com/blogs/bartvanbeek
I’d not come across this before writing the article but the site looks nice and contains details of a whole bunch of NAV add-ons you can download from MIBUSO.

Revolution1210.com
http://www.revolution1210.com/nav.asp
This is another one I had not heard of before researching this post. I like this one because Ian has posted a link to my blog in his top 10 of NAV blogs. This is a great blog. Lots of content that are just the right length. I am definitely going to take some time and read through the old posts and add this one to my RSS.

Microsoft Dynamics NAV Team Blog
http://blogs.msdn.com/nav/default.aspx
Another blog from the Dynamics Team. You have to subscribe to this. Infrequent posts but it’s always good to get it straight from the horse’s mouth.

Mark Brummel
http://dynamicsuser.net/blogs/mark_brummel/
Not a regular poster but I like Mark’s blog. One of the first ones to make it onto my RSS list. Mark’s series of posts from TechEd 2007 were a real stand out. A great effort and real contribution to the community.

Confessions of a Dynamics NAV Consultant
http://dynamicsuser.net/blogs/alexchow/default.aspx
I think Alex Chow gets the award for the best blog name. Not many posts but I love the posting on a question of morality. There needs to be more of this sort of thing. Blogging on NAV doesn’t have to be all about code and solving problems. Sometimes it’s good to just blow off some steam. The other posts look very useful and some handy tips in there that could save you some pain.

DynamicsUser.NET Admin’s Blog
http://dynamicsuser.net/blogs/admin/default.aspx
Not many posts but Erik P. Ernst is the administrator of dynamicsuser.net and an MVP. I have learnt interesting stuff and come across other good blogs through Erik’s posts.

Nuno Maia’s Dynamics Blog
http://dynamicsuser.net/blogs/nunomaia/default.aspx
Not many posts but a lot of good stuff. This guy knows his stuff. This one is on my RSS list.

Navigate Into Success
http://www.navigateintosuccess.com
I like this guys blog. Funny. Informative and he clearly knows what he is talking about. Plus he’s added comments on my blog and linked to it on his blog roll. What a guy! Definitely worth a look.

Thanks for the comments! Here are some of the ones I missed:

Belgian Dynamics Community
http://dynamicscom.be/blogs/
This seems to be largely syndication of other blogs with more than a few appearances of posts from Waldo. But there were some original posts on there and I particularly liked the post from Fontini on certification. Not all posts are in English (funny that being Belgian!)

Singleton
http://dynamicsuser.net/blogs/singleton/
David Singleton is well known within the Dynamics NAV community for his extensive work and posting on the DynamicsUser forum and for his The Dynamics Book project that is now a wiki site. The posts go way back to 2006 but they average at just over one a month. Maybe the comment is enough: The Blog of David Singleton (Need I say more?)

Microsoft Dynamics NAV UK Blog
http://blogs.msdn.com/uknav/
I guess it's hard for an official blog like this to post anything interesting. It is typical to get lots of posts about news that is available on PartnerSource and links to official downloads. Not my cup of tea.

Navision-Girl
http://www.navision-girl.com
This was one of the first blogs that regularly appeared on my Google Alerts. I must admit I didn't like it that much because of the "obvious" nature (to me) of some of the posts. Having said that I took a look for the first time in a long while after it was added to the comments and I really liked what I saw. I think I will be re-subscribing to this one.

Here are some others that I had on my RSS feeds that I didn't mention earlier:

Navision Freak's Blog
http://navisionfreak.blogspot.com/
No posts for a while but there is some good stuff on here. All the cross-over NAV and SQL topics are particularly interesting to me.

Navision Tips & Tricks
http://navitips.blogspot.com/
Again no recent posts but I really like what is there. Worth a browse.

Stefano Demiliano
http://demiliani.com/blog/Default.aspx
Regular posts of the highest technical quality from a guy who really knows. I can't beleive I missed this one from my first post. There are a fantastic variety of topics on technologies that are going to become more and more important for Dynamics consultants. Definitely subscribe to this one!

Tips dBits - Tricks Navision / Dynamics NAV
http://tricksnavision.blogspot.com/
Good solid content but nothing new since October last year. Some good tips in the old posts are worth a read.

Dynamics Blog - Check this out!

2008-02-10T07:53:00.000+13:00

I have a "Google Alert" set up to keep me up to date with news and blog postings that mention Dynamics NAV, Navision, etc. It is rare that the links are that interesting, but today it came up with a link to a blog that shows there are some real gems out there! I have even added the link to my Favourite Blogs links on the right. You can find the blog at http://dynamicsblog.wordpress.com/.

The first handful of posts were very useful, well written and funny. Presenting technical and useful material in a manner that is interesting to read is not an easy thing to do. The author also writes about nearly giving up due to a lack of comments or readers. I know what that's like too!

Nice one Vjeko! Let's hope you keep the posts coming.

Why doesn’t my filter work?

2008-02-04T20:57:00.001+13:00

I was going through some code for an upgrade and I came across some code that I thought contained an error. The code was looking at some setup fields that contained a range expression (in the format 1000..1999) and was trying to determine if a given G/L Account No. was within the range.

The code had a comment that said “cannot use SETRANGE as this does not work!” The code then went on to find the position of the two dots in the filter string “..” and then take the minimum and maximum parts of the range and use a set range function in order to determine if the record was within the range. Sadly it was also fetching each record that matched the range and comparing that to the current G/L Account No. to see whether the number matched.

“Aha!”, I thought. “Aha! – They need to use a SETFILTER and not a SETRANGE – the fools!”

So in order to prove my point (to no one in particular), I created a quick test form on which I tried to determine if a G/L Account No. was within some range fields I had created. I intended to use the SETFILTER function on a new record variable for the G/L Account table. My plan was to use this with the FINDFIRST command to see if I could find any records that matched the filter and were equal to my current G/L Account.

I got a real shock when my filter did not work.

My filter looked something like this:

SETFILTER(“No.”, ‘%1&=%2’, “Rev. Acct Range”, “No.”);

It is using the string substitution capabilities of the SETFILTER command together with my revenue account filter variable and my current account number field. If my “Rev. Acct Range” variable is set to 1000..1999 and my current G/L Account No. field is 1222 then I would expect my filter to be 1000..1999&=1222 which means “is between 1000 and 1999 and is also equal to 1222”. This, I hope you agree, should be true and I can certainly find a record that matches this filter, therefore I know that my record is within range.

So why doesn’t this work?

In order to find out, I needed to enable the debugger and look at the filter that was actually being set on my table record. When I debugged it I saw that the string substitution was putting single quotes around my range so my filter looked like this:

‘1000..1999’&=1222

This filter string means “is equal to the string ‘1000..1999’ and is equal to 1222.” This can never be true.

I couldn’t believe it! The person that had written the original code was right – it doesn’t work. But then I had a cunning idea. I could build a string containing my combined filters and use that instead of the %1 and %2 substitution parameters.

The following code is so simple but works and is much more efficient than the original code. It is also more robust since any valid filter can be used in my setup fields and not just one that contains two dots.


GLAcc.RESET;
TempFilterString := "Rev. Acct Range" + '&=' + "No.";

GLAcc.RESET;
GLAcc.SETFILTER("No.", TempFilterString);
IF GLAcc.FINDFIRST THEN
  "Revenue Acct." := TRUE
ELSE
  "Revenue Acct." := FALSE;

Marking Records - Gotcha!

2008-01-29T17:19:00.000+13:00

Here’s a simple little modification that requires a developer’s license (you need to write some code.) In version 5.0 I have a Job No. field in my G/L Journal and I want to be able to filter my G/L Journal so that only records that have a Job No. that is blocked are displayed. My journal gets created through an interface (in case you were wondering how these lines got entered.)

I knew I could to this by Marking the records where the job was blocked and then showing only Marked records. This is the Gotcha: the default parameter for the Mark and MarkedOnly functions is false meaning it will effectively not do anything. The online help tells you this but still it’s a bit of a puzzler if you don’t do these things very often.

My code for Only Records with Blocked Jobs is as follows:

CLEARMARKS;

IF Rec.FINDSET THEN

REPEAT

IF "Job No." <> '' THEN BEGIN

lJob.GET("Job No.");

IF lJob.Blocked <> lJob.Blocked::" " THEN BEGIN

lBlockedJobsFound := TRUE;

Rec.MARK(TRUE);

END;

UNTIL Rec.NEXT = 0;

IF lBlockedJobsFound THEN

MARKEDONLY(TRUE)

ELSE BEGIN

MARKEDONLY(FALSE);

MESSAGE('No blocked jobs found in this journal batch.');

END;

My code for showing all records is as follows:

CLEARMARKS;

MARKEDONLY(FALSE);

Still, it would be nice if the end-user could have applied this filter as they would be able to in Microsoft CRM. Maybe one day future versions of NAV will allow the user to define filters using related tables too.

Happy New Year to you all!

2007-12-29T23:22:00.001+13:00

Well it’s nearly the end of 2007 and I can’t let the year finish without wishing you all a Happy New Year! Thank you to everyone that has taken the time to subscribe to or read my blog over the last few months. I started this blog in April to help me learn to write. It’s been challenging but fun.

Since starting the blog, I’ve had over a 1000 visits from over 60 countries (see map). Incredibly I have not had a single comment posted. I guess I should not be surprised since I regularly read blog posts but rarely make a comment. If you read this, make a comment – even if it’s just to say “Hi”.

The most popular content by far has been my write up of Dynamics NAV 5.1 Reporting and Web Services. I took the time to review the video preview of the upcoming release and wrote what I saw and gave my opinion. This goes to show how much interest there is in the new version of Dynamics NAV (now codenamed as “6.0” but more than likely to be called “Dynamics NAV 2009.”)

I spent a lot of time researching and writing a series of postings on the new send-to-application feature in NAV 5.0. This exercise was time consuming and ultimately fruitless. It’s hard to know what to write about. I wanted this blog to be original and tried to avoid re-posting news from other sites or forums. I am still perplexed as to how you get your blog postings included in the Google Alerts for Dynamics NAV.

Probably the most satisfying moment was when the good people at PACKT publishing agreed to send me a free copy of David Studebaker’s book on programming Microsoft Dynamics NAV in return for a review posted on my blog and Amazon. I was really stoked to get something free from my blog. I am pleased with my book review but I don’t think David or Packt were that impressed.

2007 has been a tough year but I am looking forward to 2008. IT is a funny business, we’re always looking for the next big thing. I’m sure as soon as I get my hands on the new Dynanics NAV I’ll be wanting the next realease. One of the real highlights of my working year has been working with one of the industries real stars. Chommy Cash is a NAV and .NET programmer extrordinaire who is sadly leaving us to start an Alpaca farm in the Himalayas. Chommy, if you’re reading this (which I’m sure you won’t since you never read anything I write), I’ll miss you and wish you all the best for the future.

Enjoy your celebrations, and remember to add to your resolutions: “I must leave comments on the blogs I read.”

Test Drive Microsoft Dynamics NAV 5.0

2007-12-11T16:57:00.000+13:00

This link came up on my Google Blog Search for Dynamics NAV:

http://www.dynamicsnavtestdrive.com/

By logging onto the test drive environment using your web browser, you will be able to experience Microsoft Dynamics NAV first hand without the need of installing it on your computer. You can explore the product on your own, or follow along guided exercises and demos.

Book Review: Programming Microsoft Dynamics NAV

2007-12-10T21:34:00.000+13:00

Author: David Studebaker

Publisher: Packt Publishing

ISBN: 1904811744

Published: October 2007

This is currently the only book that covers programming Microsoft Dynamics NAV and that for most people will be a good-enough reason to buy it.

The book is intended to help reduce the time needed for non-NAV programmers to become productive with NAV’s unique programming language and development tools. From its coverage of terminology and basic concepts to advanced topics and techniques, this book is not just for the NAV-newbie; there is enough advanced content and good advice to prove useful for the seasoned NAV developer too. According to the preface, the book is also intended to help managers and those considering purchasing or enhancing the product to become familiar with the level of customisation the product can provide.

David Studebaker has been programming NAV since 1996 and, with over 40 years IT experience, he is well-qualified to write this book. He is currently a principal of a company that provides development, consulting, training and upgrade services for NAV resellers and firms using NAV. It is rare to get the opportunity to work with someone with such experience and this book may be the closest you’ll ever get to having a true veteran provide mentoring and advice.

Overall this is a good book, but it could have been a lot better. The publisher claims this is a “Fast-paced and to-the-point... [book with] …clear explanations and practical example code.” However, I found some of the early chapters rambling, slow and tedious with more than a handful of mistakes. The chapter on fields in particular contained a number of basic errors that should have been picked up by the proof reader or reviewers. At times I felt as though I was the first person to actually read this book.

Whilst David is clearly a very experienced NAV developer, he is not a great writer, so do not expect an easy read. Some explanations are long-winded and prone to tangential wanderings. It seems that, subconsciously at least, David is aware of his propensity to ramble when, after a 5-page-explanation of the “Date Formula” data type including a programming example, he writes: “It may seem that we overemphasized this experiment. But you got to see a lot more here than just date calculations.” A good editor could have helped us all at this point.

I really wanted to like this book and essentially I do. The content is undoubtedly good, so do not be put off by my criticisms. In value-for-money terms this book is exceptional: try to see how much consultancy or training time 60 US Dollars will buy you!

David has done a great job in completing this book and has obviously invested a lot of time for what must be a tiny market. If you work with NAV you should buy this book.

3-part Format String

2007-11-26T20:02:00.000+13:00

I mentioned in the previous post that you could use a 3-part formatting string in the format property of your numeric field to display blank when the number is 0.

Here is an example of a 3-part format property setting. If you put this in the Format field:

="$#,###;($#,###);#"

Then this says:

When Positive use $#,### which is “Prefix with Dollar sign, blank when zero, comma thousands separator, no decimal places”
When Negative use ($#,###) which is “Same as positive but within brackets”
When 0 use # which is “Blank”.

This works quite nicely. Don't try using special characters like "C" for currency in your 3-part format string because this special character is itself interpreted as a 3-part string and you'll find it doesn't work.

Reporting Services Divide by Zero Error in Report Expression

2007-11-26T19:42:00.000+13:00

It’s a real pain – if I have a field that I want to calculate as one field divided by another, I have found that I get a divide by zero error when one of the fields is 0.

Normally, I would do a quick check on the field I am dividing by (the divisor) before using it in a calculation. In a field expression, the only way to include this kind of check is with an inline if statement. So I would have something like this in my report expression:

=IIF(Fields!Budget.Value = 0, "", Fields!Budget.Actual / Fields!Budget.Value)

But there is a problem with this. The False part of the IIF function still gets evaluated and your field shows #Error instead of a blank as expected.

There is a really simple way around this – create your own VB.NET function and call this from within the expression. Creating a VB.NET function is really easy as long as you are playing nicely in the sandbox (i.e. not trying to access any of the machine’s resources.) Here is a sample function that you can paste into the Code property of the report:

Public Shared Function VarPercent(ByVal Actual As Decimal, ByVal Budget As Decimal) As Decimal
If Budget = 0 Then
Return 0
End If
Return (Actual / Budget)
End Function

To use the function, just put the following in your expression:

=code.VarPercent(Fields!Actual.Value,Fields!Budget.Value)

That’s it!

If the divisor is 0, we return 0. We could of course return an empty string – although you can just as easily do this by formatting the field using a formatting expression that shows blank for zeros.

Wasps: Are they really Yellow?

2007-11-20T20:55:00.000+13:00

Ashely has written a great blog posting on the Intergen blog site titled Wasps: Are they really Yellow?

This reminded me of why I wanted to start blogging. I wanted to write and I thought that blogging was a good way to practice writing. I think most bloggers find that, however motivated they once were, they run out of time and ideas and posting gets harder and harder.

The Intergen blog is a great place for Intergenites to post ideas and thoughts. If you like technology, you should subscribe. At the very least, you should take the 5 minutes or so to read Ashley's post. Who knows, you could maybe even leave a comment.

Microsoft Dynamics NAV "6.0" Video - You have got to see this!

2007-11-17T17:49:00.000+13:00

I have just finished watching the video that I downloaded from the link on the NAV 6.0 Technical Preview page, if you don't have access to PartnerSource, you can download it from mibuso. Microsoft, I salute you! The video is stunning - never mind the whitepaper (which is just "interesting") this is amazing stuff.

Some highlights for me are:

The interactive date picker that animates zooming in and out between month and day views.
The Freeze columns feature for sales lines (Very Cool - geddit? Freeze Columns, Cool? - Never mind!)
Additional fields feature - yes I know we've seen this before but I had forgotten how neat it is.
The web service demo. Wow! This is so incredible. Microsoft have really got their act together on this. It seems to be everything we could want.
The SharePoint integration. Wow again! This is just astounding. I am running out of superlatives. Employee Portal is a good idea but a bit of a pig to implement. This is just amazing in its simplicity, beauty and elegance.

Just when you think it can't get any better, the piece de resistance is the jaw-dropping, pant-wettingly-stunning demo of using WPF to manipulate supply and demand. OK so this is future stuff but - what a future!

Developing Solutions for Microsoft Dynamics NAV "6.0"

2007-11-15T19:31:00.000+13:00

Interestingly it seems Microsoft are starting to get more info on the next release of NAV out into the channel. If you have access to partnersource, you can download a new whitepaper called Developing Solutions for Microsoft Dynamics NAV "6.0". If you don't have partnersource access, you should keep an eye out for a download on mibuso or dynamicsuser.net.

The document includes a description of the architectural changes in Dynamics NAV "6.0", Development Enhancements, Functionality Redesign Needs, Multiple Client Support, Step-by-step Walkthroughs for creating and modifying pages and reports.

The whitepaper is also available on a new technical preview page that includes a video of the new NAV 6.0 features.

Intergen Silverlight Twilight

2007-11-14T21:26:00.000+13:00

I have just returned home from Chris Auld's Twilight session on Microsoft Silverlight and was so impressed by some of the demo applications, I felt the need to blog. Chris is a very entertaining presenter and is always a pleasure to watch; and whilst his moustache was definitely lacking in substance, his presentation included enough eye-candy to keep this addict satisfied.

Sadly the demo demons stopped Chris from being able to show the best of the bunch: an xbap application developed for the British Library in order to show some of their rare books. If you have Vista or Windows XP with version 3.0 of the framework, you can view this for yourself here. It's quite scary that I had never heard of xbap before tonight. Chris also showed the New York Times reader which has often been touted as the first WPF killer-app and the usual array of Silverlight based video tools.

Silverlight is cool, there is no doubt about it and the future of computing is set to be great fun. Sometimes I wish I could go back and study again - the resources that are available for our children and the technology at their disposal is quite incredible. The lucky buggers they don't know they're born. Now when I was a lad we had it tough. We used to 'ave to get up out of shoebox at twelve o'clock at night and lick road clean wit' tongue. We had two bits of cold gravel, worked twenty-four hours a day at mill for sixpence every four years, and when we got home our Dad would slice us in two wit' bread knife.

If you want to read the rest of that sketch you can find the transcript of the Four Yorkshiremen here.

vbNewLine in Reporting Services Expression

2007-11-12T09:58:00.001+13:00

Just a reminder for me because I keep forgetting. If you want to have new lines in a text box control in reporting services, you can add several fields together and use the vbNewLine reserved word.

For example:

=Fields!Address.Value+ vbNewLine
+ Fields!Address_2.Value+ vbNewLine
+ Fields!City.Value+ vbNewLine
+ Fields!Country_Name.Value

NoteBurner - Well-written Software that Just Works!

2007-10-27T23:08:00.000+13:00

I realise it's unusual to actually purchase music, but I love my i-Pod and the fact that if I hear a song I like on TV I can usually purchase it within a few minutes (after a quick search on www.imdb.com). I wanted something to convert my i-Tunes purchased tracks to MP3 so I could listen to them on my mobile phone and other audio players. I came across NoteBurner and was impressed by the trial software (that works like the full version but limits the track duration to 3 minutes) so I bought it (I think it only cost 50 bucks or so.)

Now I have a Vista machine and I am used to most things not working and I did not expect NoteBurner to fair any better. Well here is the shocker. It installed and it works perfectly. Well done guys! What a great piece of software.

If you want to check this out for yourself, you can go to http://www.noteburner.com/glc/

Fixed-width Text File Editor - Excel.

2007-10-14T23:10:00.000+13:00

Recently I needed to work with NAV to produce some fixed width text files. I wanted a tool to help me check the output file against my file specification. What I really needed was something that let me see the contents of the file nicely split up by column with the columns numbered along the top of the file.

I did a quick search on Google and found little. Then I thought, what about Excel? Maybe I could use Excel to somehow show the file in the format I wanted. Well It was remarkably easy.

I created two worksheets. My first sheet simply contained one cell which contained the full text of the record in my file. Each row contained one record.

In my second sheet, I created a series of column headings and then used the MID function to take a single character from my first sheet based upon the column I was in. The formula looked like this:

MID('Text File'!$A1,Columns!B$1,1)

I then copied this function to all of my cells. I also put in some row headings and some conditional formatting to change the colour of the background if the length of the field was 0 (i.e. no character.)

After generating my text file, I would open it in notepad, select all (Ctrl+A) and copy it, then paste into the Text File worksheet of excel and check the data in the columns against my specification.

It did the job quite nicely.

All Blacks Out - Dynamics NAV 5.1 Cancelled - Whatever Next?

2007-10-12T23:00:00.000+13:00

Just in case you haven't already heard, Dynamics NAV 5.1 has been canned. Instead, it will be called Dynamics NAV 6.0 and will be delayed until Q4 2008.

Why? My guess is they just couldn't finish it in time and so, rather than delay it again, they killed it. Now before I start ranting, I have to admit this decision has positive aspects. Releasing an unstable 5.1 product would have shown a lack of vision surpassed only by Wayne Barnes' recent performance in the All Blacks-France World Cup Quarter Final.

The Dynamics NAV 6.0 product (previously known as 5.1, previously known as 5.0) promises to be something special and it is important that it works.

A useful SQL Tip - Coalesce.

2007-10-02T19:35:00.000+13:00

I do a bit of Reporting Services work from NAV databases and recently a client wanted a list of Names to be presented as a comma delimited list.

Now I knew I had done this before and there was a really smart way of doing it but it took me a while to find it again. The trick is to use the coalesce function. You need to declare a variable to select into and then select from that so this is for use in a table-function or stored procedure.

Try this out in a NAV database when you have more than once company.

DECLARE @CompanyList VARCHAR(1000)

SELECT @CompanyList = COALESCE(@CompanyList + ', ', '') + Name
FROM Company

SELECT @CompanyList

The NAV Team has its own Blog!

2007-10-02T19:26:00.000+13:00

Wow! Great news. Finally the NAV team has its own blog. I am looking forward to some great content - particularly with all the changes coming in NAV. I have read some of the blogs that the team have linked to and hope to see some equally in-depth coverage on this new site. I particularly liked the posts on the Dynamics NAV Sustained Engineering blog. This is what we want!

The URL is http://blogs.msdn.com/nav. Enjoy!

You start coding - I'll ask what they want.

2007-09-17T21:14:00.000+12:00

I have found myself in a rather unique position. I am working as a developer on a project in which I was the analyst that wrote the programming specifications. As a result I am finding that I approach problems differently when wearing different hats. As an analyst, I think I write decent specs, mainly due to my programming experience. It is really interesting to find myself on the other side of the fence having to chomp my way through garnish and trimmings, in order to find "the meat" of the problem. This experience has challenged the way I approach solution design and programming work and will hopefully allow me to improve in both areas.

When I wear my analyst's hat, I expect certain things from a developer. I expect that they will read the spec first. It seems obvious but you would be surprised at how often I get called to explain details around a solution design when the developer has not actually read the spec. With my analysts hat on I would suggest the following steps to programming against a spec:

Read the spec.
Read the spec again.
Ask questions of the analyst/domain expert to clear up any areas where you are not entirely sure what is meant.
Plan how you are going to implement the solution and break the spec down into a series of "programming tasks".
Code the programming tasks. Make sure you test your work as you go.
Read the spec again. This time you can "tick off" all of the tasks that needed to be completed. Maybe you missed something the first time around.

I also expect that if I have written a spec for something that will simply not work, or could have been done better, the developer will call me and discuss the solution. I do not think I know better than other people, I just know that without a solution design, nothing will happen.

Now I hope I can follow my own advice. So far - I am finding it hard work. The temptation to start coding at the first bit of the spec I understand is almost overwhelming. Developers just love to get coding!

Programming Microsoft Dynamics NAV

2007-09-03T13:28:00.001+12:00

A new book by David Studebaker covering Programming Microsoft Dynamics NAV is on the horizon and, if you are quick, you can still get a pre-order discount from the PACKT publishing site.

Unlike the guys on Dynamics User that have commented on Mark Brummel's blog posting, I have not met the author, so I cannot vouch for his experience; but, I am looking forward to getting my hands on a copy. If David Singleton, Erik P. Ernst and Mark Brummel are saying this guy knows his stuff then it must be good.

I have been working with ERP for a number of years and have always been disappointed in the lack of books available for the products I have used. Go to www.amazon.com and search for Dynamics NAV or Navision and you will see what I mean.

Hopefully this is a sign of things to come. I am keeping my eye out for "Configuring Dynamics NAV for Dummies" ;-).

Any Dataport in a Storm

2007-08-30T20:32:00.000+12:00

I'm doing some programming and it's fun! I haven't done any serious programming for a few years and it's good to get back into it, but today I came across some top tips for programming dataports and I don't think I would ever have guessed. What a perfect subject for a blog posting.

Dataports are Dynamics NAV's object for importing and exporting data. The dataport looks a bit like a report when the user launches it with some options to specify field filters and an Options tab for selecting Import/Export and for picking the file name to be used.

For my dataport, I wanted to provide the user with a couple of fields to pick the Gen. Journal Template Name and Gen. Journal Batch Name that the journal lines are to be inserted into. When I added a field to the Request Form, the standard Import/Export radio buttons and the File name field dissappear.

In this post, I'll tell you about a couple of tricks that will help you to put these fields back on the request form and also how to put lookups on the journal fields. I wanted my dataport to look like this:

As you can see, I have the usual File Name and Direction radio buttons and have my two new fields with lookup buttons for the user to select the correct value.

So let's start with the File Name field. Create a global variable of Text 1024 and use this as the Source Expression property for a new text box on the request form. Now here's the trick. If you want the file requester dialogue box to appear without any programming needed, simply make sure the control has an ID of 1. That's it! Thanks to a work colleague for that, I never would have guessed and I thought I was facing a lot of coding around the common dialogue requester.

Next challenge for me was the direction radio buttons. Not being an experienced NAV programmer, I thought that these would use a Boolean control as a Source expression. I tried this and I could not get the buttons to click, no matter what code I added. One of the postings on Dynamics User pointed me to using an Option type field. I created a new global variable as an option string with choices of Import and Export. I then assigned the variable to both of the radio buttons but specified the Import option as the Option property for the top one and Export option as the the Option property for the bottom one. How easy was that?

Now on to the look-up buttons and pick lists. The Journal Template was easy, I just set the TableRelation property on the field to be "Gen. Journal Template". The next bit was a little harder. I wanted the journal batch look-up to only show batches that belong to the journal template I had already selected.

To start, I looked at the G/L Journal form as I knew that this had a batch name field that provided a lookup that worked in the way I wanted. I saw that the field was using a LookupName function in codeunit 230. I put in some code in the OnLookup trigger for the field (after setting the Lookup property to Yes of course) as follows:

GenJournalLine.SETFILTER("Journal Template Name", GenJournalLine."Journal Template Name");
GenJnlManagement.LookupName(GenJnlBatch,GenJournalLine);

On this line, GenJnlManagement is my variable of type codeunit (230), GenJnlBatch is my variable of code 10 which is storing my batch name and GenJournalLine is a record type variable of type Gen. Journal Line (that is required by the function.) The filter, I figured would provide the magic of only showing the batch names for the correct template.

When I called this function, I got an error saying that I needed to set a filter on the Batch Name field. This seemed a bit weird to me since I was trying to lookup the field, so why would I be filtering on it?

I then figured that maybe NAV wanted to know where to put the cursor when it displays the lookup form so I added this line before the call to the codeunit function.

GenJournalLine.SETFILTER("Journal Batch Name", '-');

This line tells NAV to find the first Batch Name that matches the filters I have set on my record handle.

All was sweet! A few little error checks and validations and I was in business. Hopefully, if someone has the same issues and searches for "How to use Radio Buttons in a Request Form" or "Filter must be set before calling LookUpName name function" they will find this post and get a nice shortcut.

Dynamics NAV 5.1 – Reporting and Web Services

2007-08-09T22:55:00.001+12:00

The presentation from Convergence 2007 gave me my first real look at Dynamics NAV 5.1. Following on from my previous post on the Role-tailored User Experience, here are some details on the use of Reporting Services and exposing NAV logic as Web Services.

Reports

Reports in Dynamics NAV 5.1 utilise Microsoft SQL Server Reporting Services. This, we are told, allows:

Richer visual effects
Interactive reports
Export to Excel, PDF on all reports

The transformation tool will (hopefully) create the report for you but you may need to translate some of the code by hand. You will then have the option of adding in your own SSRS specific elements such as interactive elements, dynamic sorting and graphs.

The Development environment in the demonstration still resides in the Dynamics NAV "classic" client but hopefully this will be sorted out before the release (they certainly have plenty of time.)

I am guessing the report dialogue box is going to get a face-lift as the version I saw did not look that great. First of all you get a filtering dialogue box which looks like the Filter pane on the List Place and when you accept the filter, you get a sorting dialogue. I am guessing that if a request form was used, this would have popped up somewhere as well.

Jesper opens the report for editing in the normal way and then, from the Tools menu, selects an option to Transform the Layout (there is another option to Transform the Request Form.) Selecting this option opens a Microsoft Visual Studio window with a Report.rdlc in design mode. I have been using Reporting Services for a good number of years but I wasn't familiar with this rdlc file extension. It turns out that Microsoft added the capability to host reporting services reports in .NET applications using client report definition supported in .NET 2.0. I don't know much about this but you can find out more on msdn.

This little part of the demo has answered the question for me as to how the team are intending to use Reporting Services within the Dynamics NAV application and I think they are being very clever. By using this approach they are giving the users some of the power of reporting services but still keeping control of the reports within the application. This does mean that you will still need a NAV license to view the reports but I am guessing they will make a MOSS-based report viewer.

In addition to the .rdlc file there is a .xsd file (not sure of the purpose of this but it could be a definition of the dataset.) The report has Website Data Source with a Dataset and ResultSet. The information in the report is based on the dataset that is already defined in the report (no hacking SQL here.) It looks like the dataset contains all of the fields from all of the tables within scope plus the values of variables in a single dataset.

Jesper closes Visual studio and returns to NAV and the designer detects that the rdlc file has been changed and asks if you want to load it against the report definition. The whole process certainly seemed quite painless. I would be interested to see if you can use custom assemblies in reports (such as a barcode renderer or other custom graphics renderer.) From the article on msdn it appears that this is supported in rdlc but I am not sure if the NAV application will allow it.

I have to say I found the reports section very impressive and from what I have read, it will be possible to schedule the reports and distribute them using the NAV application so the lack of Report Server hosting is not such a drawback.

Web Services

This section was presented by Kris Rafnsson.

This is where the NAV team have really stuck to that core value of simplicity. Take the page or codeunit you wish to expose as a web service and add them to a new Web Service table in NAV. Give the service a name and tick the Published check box. You now have a web service. How cool is that? Thankfully the demonstration then goes on to show a couple of uses of the new web service.

The first example shows how you can build a simple application in InfoPath that allows you to query NAV customers in less than 5 minutes – without writing a single line of code!

The URL for the web service in the demo is port 3000 on the machine running the service tier followed by WebServices, the company name, Page (presumably code unit would say Codeunit here) then the name that you gave the service followed by a navws extension.

The methods that are available after adding the page as a web service are:

Delete
ModifyAll
Modify
InsertAll
Insert
IsModified
Find
Get

I am not sure what happens to any custom functions that may also be on the page.

The filter that is exposed allows filtering on any field – just as you would in the full user experience. All of the fields from the table are returned as data. Once Kris ran the form, he was able to pull back data for all customers matching a given filter. It was a nearly trivial example but impressive nonetheless. I would have loved to have seen him use the view and update option to post changes back to NAV – I can only assume that this still needs some work.

SharePoint Server 2007 (MOSS) allows the web services to be consumed from NAV in a similar way without writing any code. In the second example Kris creates a Business Data Catalogue Application (he used a pre-prepared XML file for the demo but said that there will be some tools to help build these xml files hopefully ready prior to the release) to use with the NAV web service.

After importing the application definition, there is an entity available called Customer_Service – I am guessing this can be named in the xml file. He then creates a site and page as normal in SharePoint but selects the option for a Business Data List (a part that displays a list of items from a data source in the Business Data Catalogue.) He then links up the Business Data List to the newly created Business Data Catalogue Application.

When the new page is run, there is an actions drop down button and a nice interface for specifying any number of field filters. He hits the Retrieve Data button and the list of data comes back. He clicked on the No. field for the customer and a new Details form is displayed showing all fields that customer.

What he showed is a SharePoint hosted NAV form with no coding and none of the configuration that is needed for the Employee Portal application. From the looks of things the security and different options that you would have in the Employee Portal are taken care of at the SharePoint end when defining the Business Data Catalogue Application. I was very impressed by this and it looked a lot more polished than the current Employee Portal offering.

Some ideas for how the web services could be used are given by Jesper as:

Master Data Management
Maintaining Exchange Rates
Integration with other Applications
Specialised Clients
Mobile scenarios

This really is exciting stuff. Come on Microsoft – let's have more of this! Let's have an opportunity to learn about this great technology you are building and maybe make some suggestions along the way.

Dynamics NAV 5.1 - Introduction

2007-08-08T21:00:00.001+12:00

This presentation from Convergence 2007 gave me my first real look at Dynamics NAV 5.1. Jesper Lachance Ræbild and Frank Fugl do a great job and if you are interested in the next release of Dynamics NAV, I recommend you take 50 minutes to watch the presentation.

The Dynamics NAV 5.1 release is all about adding cutting-edge technology on top of the rich functionality provided in the 5.0 release. Jesper says that there is no new application functionality in 5.1 compared to 5.0 (although there has been mention of RFID support in other material.) The message to companies wanting this new technology is to upgrade to NAV 5.0 now in preparation for 5.1.

Dynamics NAV 5.1 will provide:

A Role-tailored User Experience
Web Services
A 3-tier Architecture
.NET Compliance

The way the Dynamics NAV team have delivered the technology has kept to NAV's core values of simplicity, flexibility and ease of use.

The new architecture will use the same application logic, the same office integration, and the same outlook integration as we currently have in version 5.0 so you will be wise to spend your time learning these new features and understanding the application enhancements.

Jesper tells us that the new architecture is meta-data based, and to go from NAV 5.0 to 5.1 you will need to use a transformation process to create the new pages and reports. Microsoft will deliver transformation tools that will take the application and forms and transform them into the new technology. There may be some manual intervention required, but the tools should take you most of the way.

Role-tailored User Experience

Role centre is the new term for the home page that is configured to meet the requirements of the user. The main screen comprises a Navigation Pane and an Activities Pane (tailored to the role being played by the user.) Jesper makes an analogy to a car that remembers your preferences for seat, steering wheel, and mirror positions in order to meet your driving needs. A number of roles from the Dynamics customer model will be delivered in conjunction with tools to allow new roles to be created.

Selecting options from the Navigation Pane takes the user straight into a list view which Jesper called a "List Place" (rather than the card form used in earlier versions.) Apparently, research has shown that in previous versions people would open the menu option and then immediately launch the list from the card in order to be able to search.

When looking at the Sales Order List Place, we see Actions, Related Information and Reports drop down menus. Linked to the list are fact boxes that update relative to the selected record and can show details such as Customer Statistics, Customer Details and Notes.

The main window has an address bar that can be used to navigate in a similar way to breadcrumbs on a web site. There are browser-style forward and back buttons allowing you to easily go back to your previous location (a feature that I think will prove very popular.) Microsoft has clearly put a lot of thought into the new user experience and their efforts appear to have paid off.

The pages can be personalised by the individual users but can also be configured by a systems administrator and distributed out to the users or groups of users.

The character of NAV has remained and I think existing users will adapt easily to the new features. A good example of this is the way record filtering has been implemented. The principal is the same as the old-style record filters but the use of a nice Filter Pane, a new layout and some extra text and buttons helps to make the filtering process more intuitive. Including a few English words such as "Show Results", "Where" the field name "is" made me realise how alien the old filtering form really was. It is good to see that the team have kept the powerful expressions that are used to denote ranges, relative and logical selections. I would like to see a more graphical filtering pane similar to those found in CRM 3.0 or SQL 2005 Report Builder but maybe this will come with time.

The list can be opened in a new window by selecting an option from the Action menu or by using a key-combination. This gives a similar experience to opening folders in a new window from within Outlook. It appears that the design team have used Outlook as their inspiration for how an application should present information and options to the user. The team have done well and the demonstration certainly showed a system that is likely to be well received.

There are many customisation options that should meet the needs of most users. Some of the new features are quite exciting such as the Freeze Pane–a really useful feature similar to the Freeze Panes option in Excel that allows you to lock columns on the left hand side when scrolling horizontally. It reminded me a little of the old Matrix form control but obviously it is showing fields form a single data set and not a cross-tab.

Double-clicking a record in the list place mirrors the Outlook paradigm well and behaves similarly to the process of opening an e-mail from your inbox in Outlook. The details for the record open in a new window called a Task Page. This is similar to the CRM 3.0 experience and I know that some users may not like this. I like it but it does lead to quite a few windows being opened.

The tabbed-form control has been replaced with new Fast Tabs that allow more than one tab to be open at any time with the ability to promote certain fields to be displayed on the "tab band" (my term not their's) when minimised.

The customisation of the task pages allows the fast tabs to be removed from the display or customised to a field level. Each field can now have different levels of importance Standard, Promoted and Additional. A promoted field will be displayed on the fast tab band so that it is visible without needing to open the tab. The additional fields will be temporarily hidden until the user clicks on a control to reveal the additional information. I really like this and it shows a lot of thought from the design team.

It appears that the old drop-down and look-up buttons have been replaced with a single combo-box-style control which makes a lot more sense. I have not seen any evidence of what happens to assist-edit buttons and drill-down fields. The phrase Jesper used was "taking away the clutter" – something I know a lot of users will applaud.

The action pane is similar to the ribbon in Office 2007 although it does not behave exactly the same. I prefer the Office 2007 ribbon. You can think of the Action Pane as a way of promoting common actions from the Actions menu for quick access (you can also increase the size of actions on the Pane which makes them larger and therefore more easily accessible.) Jesper says they have taken "elements of the Office 2007 user experience and added them into Dynamics NAV."

The export to office using XSLT stylesheets works in the same way as NAV 5.0 so the old stylesheets can still be used.

In my next posting, I'll discuss the presentation of the new reporting environment and the use of Web Services.

Dynamics NAV 5.1 is soooo coooool!

2007-08-07T17:57:00.000+12:00

OK so I may have done a bit of ranting about Dynamics NAV 5.1 being late and that it is going to suck. And I may have made some reference to the Dynamics Development Team being nasty fat hobitses. I am sorry. I was wrong.

Not only does Dynamics NAV 5.1 appear to not suck at all. It appears that it is going to be pretty mind-blowing. The Development Team have really pulled one out of the hat with this one and if we need to wait a while to get it stable then so be it.

So what changed my mind? I have just watched a demo (which you can download from MIBUSO) and the way they have tackled the architecture is simply superb. The demo of creating an InfoPath form that consumes a Dynamics NAV Page as a webservice is pretty stunning. Stunning in how simple it is!

I am so excited, I think I'm going to watch it again. My precious!

Dynamics NAV 5.1 in Feb 2008 - But I Wants It!

2007-07-29T22:27:00.000+12:00

Every time I read about the ever-slipping release date of Dynamics NAV 5.1, I crawl around on the ground grumbling to myself "but I wants it, I wants it, nice smeagle, nasty fat hobitses."

I realise this is an irrational response but the nasty fat hobitses at Microsoft in charge of the NAV 5.1 release schedule* have driven me to it. Now maybe someone can explain to me why we (and by that I mean the great unwashed, average reseller or contractor) are not allowed to sign up for a beta programme for Dynamics products via the connect site - as we can with other Microsoft products. The only explanation I can think of is that maybe the package sucks so badly that the nasty fat hobitses are scared to release it.

I remember when Axapta came out (initially XAL 3.0 and then Atlanta). I was so excited by the potential of the product I could hardly contain myself. When I got my hands on the first beta releases I was amazed at how much it sucked. I guess that's the thing with any radical new technology shift for ERP products. Evolution makes better products than revolution.

I am just hoping that amongst all the hype on role-centric user interfaces, portals, web-services, .NET, Microsoft remember that users and consultants just want a stable product that allows bean-counters to produce their statutory reports and managers to get the information they need to make good decisions. In order to get there, we need good documentation, configurable and relevant help a community of resellers that know the product inside and out.

So how can Microsoft make the product stable, get good documentation and teach their resellers all about the product? Let 'em have it! Warts and all. Yes it will suck in the early stages, but let's make it a better product together.

* I am not actually suggesting that the good people at Microsoft in charge of the release schedule for Dynamics NAV 5.1 are in fact nasty, fat, or hobits.

Do Cats Burp?

2007-07-29T17:59:00.000+12:00

The other day my daughter was pretending to be a cat. She climbed on my knee, leant close to my ear and said "burp." Now I have no idea why her game involved a burping cat, but she seemed to find it very funny. Her game made me wonder: do cats burp?

I have owned two cats for over 13 years and I cannot remember hearing one of them burp. Dogs - now I know dogs burp. So the question has to be - are cats physically incapable of burping, or do they sneak off to do it in private?

OK - so now I go off and Google the question and it seems that there are a lot of cats that do. I guess our cats are just more polite. My favourite quote I found was on the Guardian Unlimited Notes and Queries page:

"Never mind dogs, does anyone else have a burping guinea pig? Mine's at it all the time but especially after munching on organic cucumber. Is he unique?"

Dynamics NAV 5.0 - Send to Application Feature - The Continuing Saga

2007-06-06T19:38:00.000+12:00

Did you ever start something and wish you hadn't?

I thought it would be fun to blog about Dynamics NAV 5.0. I thought it would be easy to write a short posting following the investigation of new features. Why oh why did I start with the send to application feature? It has turned out to be far more work than I had expected. Oh well...

First of all, I would like to mention that I have already written too many posts on this subject. You can see them all by clicking the Send to Application Label on this blog. I won't say how many there are because, no doubt, I will need to add more when I finally figure things out.

I think I should mention Mark Brummel's Reporting from TechEd 2007 posting in which he says:

One of the issues with the office xml interface is that the changing of the stylesheets is very difficult unless you like reading xml schema's.

Fortunately they have changed that and added a stylesheet manager that allowes you to create them in a wysiwyg way. Great! It will be released shortly but there is no official date.

If you have been reading my posts on this topic, you will know that you probably already have a wysisyg editor for the stylesheets needed to transform xml data to Word documents - that is - Word itself. The more I look at the results of using the wml2xslt transform inference tool and compare this to the example stylesheets supplied with NAV 5.0, the more I think that maybe this is not going to work - it looks as though someone really did code this sample stylesheets by hand. I still can't get elements from within the same node to appear in different parts of the document. If anyone has any ideas, please post a comment.

Now the reason I am writing another post on this topic is the changes I suggested making to codeunit 403 will break the standard stylesheets that come with the CRONUS database. Oops.

I have figured out a way to create the stylesheet without hacking the codeunit that also leaves the existing stylesheets in their current working form.

To start with - don't edit codeunit 403 to add the xmlns attribute. Instead, generate the Customer Raw xml file as described but then open the xml file in notepad and manually add the xmlns="NAV" attribute at the end of the Object opening tag.

Now you can follow the previous instructions for creating the Customer Seed xml file (although I still haven't figured out how to use same elements in different parts of the document.)

Finally, use Notepad to open the Customer Seed xsl file (the transform that is generated by the wml2xslt utility) and find the xmlns attribute for the "NAV" namespace. When I tried this, it was ns0 as follows:

... xmlns:ns0="NAV"

Use search and replace to replace all ocurrences of "ns0:" with "" (ignore the quotes so you are replacing it with blanks.) Save the edited xslt file and you're done.

You should now be able to use this xslt file to transform the NAV xml as required - and all your standard stylesheets will still work. Phew!

Dynamics NAV 5.0 – Send to Application Feature – Part 4

2007-06-04T08:40:00.000+12:00

This is the last part of a series of postings on using the new send to application feature in Dynamics NAV 5.0.

In Part 3 I showed you how to use Word to create a seed xml document that can be used to create a transformation template. It was late in the evening when I wrote this and there were a couple of mistakes in the posting. I had implied that you could take the SalespersonName element from the Customer element and move it down to the bottom of the letter. Unfortunately when I worked through my own example this did not work (the XSL does not include the text as expected.) I am sure there is a way to do this but I do not know how. If you are reading this and you know about such things, why not post a comment?

In this post, I will show you how to create the XSLT from the seed document you created in Part 3. The first thing you need to do is download the transform inference tool from the Microsoft downloads site. The download will install the tool: a command-line executable with some documentation. I accepted the default install path so, for me, it was installed in "C:\Program Files\Microsoft Office 2003 Developer Resources\Microsoft Office 2003 WordprocessingML Transform Inference Tool\".

In Part 3 I wrote about creating a seed document. If you have not done that, you will need to go back and follow those steps before being able to use the transform utility. I am assuming that you have a seed document stored as “customer seed.xml”.

Open a command prompt and change directory to the location of the inference tool. Type the following command:

WML2XSLT "c:\customer seed.xml"

Since we have two namespaces in our seed document, you will see a dialogue box asking you to select the namespace you wish to use for the transform.

Tick the option for the namespace you put your NAV document in and hit OK.

Now take a look at the location where you stored your seed document. You will see a document with the same name but a with a ".xsl" extension.

The quickest way to test your XSL is to take your raw xml file and edit some of the data (try changing the customer name). Then save the raw xml file and open it in Word. When you open an xml file in Word, it prompts you for a style sheet to apply. Select the style sheet you created from your seed. You should now see a Word document that looks like your seed but with the changed data values from the raw file.

If everything is working properly, you can now import this style sheet for use with the send to application feature. For details on how to do this, go back to Part 1. Note that our transformation uses elements that are only available for the Customer card export, so when you import this style sheet, do it as a style sheet for form 21 only. You should also ensure that the application for the style sheet is Word.

That’s it. I don’t know if this is the best way of creating your XSLTs but it certainly works for me. The nice thing is that I don’t need to know the XSLT language in order to create the transform (which I think will be true for the majority of people that will need to create new templates.) I must stress that this series of postings is made with no warranty or support. If you follow this, you’re on your own. If anyone finds a better way of creating XSLT transforms for use with Dynamics NAV then I would like to know – please post a comment.

Dynamics NAV 5.0 – Send to Application Feature – Part 3

2007-06-03T22:57:00.000+12:00

This is my third posting on the new send to application feature in Dynamics NAV 5.0. After my initial introduction, my second post explained how to create an XSLT that would show the raw xml produced by NAV.

In this post I am going to show you how you can use a graphical editing tool (that you probably already have) to create your own XSLT. You will need the XSLT we created in Part 2 so if you haven’t read that yet, I suggest you jump back and start there.

I have read a few posts on this topic asking whether there are any tools that can help you create XSLT transformations without having to hack xml in a text editor. You may be surprised to learn that Microsoft Word (2003 or 2007) can be used to format your raw xml file to create a seed file and a transform inference tool (provided by Microsoft) will then create the XSLT for you.

There is one little snag (isn’t there always?) The transform inference tool (what a great acronym that makes ;-) needs the raw xml file to have a namespace attribute. Unfortunately NAV does not include a namespace attribute in the xml document it creates so the first thing we need to do is change the code that generates the xml so that this attribute is added.

Open up Codeunit 403 - Application Launch Management and press Ctrl-End to get to the bottom of the code. The third-to-last function is called AddElement and is used to add all of the elements to our raw xml file. In this example, I am going to use a namespace of ‘NAV’ – but you could use anything, provided it is not used by other XML documents (this is why you often see a URL in the name space.) Add a new line in the code so it looks like this:

ChildNode := ParentNode.ownerDocument.createNode('element', NodeName, '');
ChildNode.text := NodeText;AddAttribute(ChildNode,'xmlns','NAV'); // My new line to set the namespace attribute.

ParentNode.appendChild(ChildNode);
CreatedChildNode := ChildNode;

Now we get to the point where you’ll need the XSLT we created in Part 2. Hopefully you have this hooked up to export to Internet Explorer. I am going to create a new letter template to be generated from the customer card so, first of all, you need to export a customer record to give us our raw file. Launch the customer card (Sales & Marketing > Sales > Customers) and click the Send Options button on the toolbar. Select the Internet Explorer application and the Style Sheet we created in Part 2. Hit the Send button and you should see Internet Explorer launch with an XML document.

In the image, I have collapsed the Control element to show the Customer, WorkDate, Salutation, LetterBody, ComplimentaryClose, CompanyInfo, and DecimalSeparator elements. You’ll notice that the Control element has a different namespace to the other elements (it shows xmlns=“”.) This is because the Control elements are already in the DataXML document when our codeunit 403 runs so it does not get the xmlns attribute added by our line of code. Since we are only going to use the extra elements and not the control elements, let’s not worry about this.

I would expect there will be some changes to the way NAV creates this XML document in the future. As you will see, Word does not work very well with empty elements where the data is actually stored within the attributes. I would not be surprised if the Control elements are changed so that value attribute is actually the text of the control node rather than being an attribute. I would also expect that a namespace attribute will be added – but who knows? Maybe Microsoft has some other grand plan for making it easy for users to create their own XSLT transformations.

From within Internet Explorer, save the XML file as “customer raw.xml” and open the file in Word. I am using Word 2007 but I am pretty sure the same features are available in Word 2003.The first thing you will notice is a whole bunch of empty tags. The first time I saw this, I figured I was doing something wrong – until I realised that the values of the form controls are attributes and Word does not display the attributes in the Word document.

We can get rid of these Control elements. Double click the first Control tag and press Delete. You should now see the Object element with the Customer and other elements beneath it.

Now you can format this document how you want. Create tables, add graphics, etc. When you move data, be sure to copy all of the tags. For example, if you want to put the customer’s name, you would need to ensure it is still within the parent tags of Customer and Object.

Above you can see my re-formatted document. I find it easier to work with the format with the XML tags displayed. Then I remove the tags by removing the tick from the Show XML tags in the document. It looks like this:

Save your document in an xml format when you’re happy with the layout. In Word 2007, this means saving as a Word 2003 xml Document. Make sure you remove the tick from save data only. If you don’t remove this tick, your transformation won’t work. I find it easier to save my document with a name “customer seed.xml” as this lets me easily identify which file contains the raw data and which is the seed for my transformation.

That’s all for now. In my next post, I’ll show you how to take your seed document and generate the XSLT you’ll need for NAV to generate these documents for you.

Dynamics NAV 5.1 – Incredible New Features

2007-05-26T09:49:00.000+12:00

There is a new version of Dynamics NAV and it has got me pretty excited. Not because of its new features, or technology, or roles-based user interface, but because I have never used it.

There is a magical time in a product’s life when it is perfect, and, for me, Dynamics NAV 5.1 is there. Having never used the product, I can unreliably inform you it is the best version of Dynamics NAV yet. The roles-based client is intuitive and a pleasure to use; the online help has been completely re-written with a focus on the roles within an organisation and the business processes that keep it running.

In a stroke of genius, Microsoft has completely re-engineered the application architecture in order to make all functions available to the NAV developer. Any actions that can be triggered by a user can now be triggered through code. This is probably the one non-feature I find most exciting. NAV developers can now create tools that will dramatically improve the Dynamics experience for re-sellers and end-users alike. The testing tool is a prime example of this. Similar to the Record Macro feature in Microsoft Office, it is now possible to record a series of actions, save them as a script (using your preferred language of C/AL, C# or VB.NET) and finally edit the code to produce testing programs. This will allow new features to be automatically tested to ensure they meet requirements and do not introduce nasty side-effects to other parts of the application. The application comes with a set of pre-defined localised test cases that allow Dynamics NAV resellers to develop their own applications and have total confidence that they have not broken the standard application.

Programming objects within Dynamics NAV can now be exported and imported through code (either using the built-in programming tools or via the web-service interface.) This, coupled with the fact that all programming objects are now stored internally in an open XML format, means that Dynamics NAV is now limited only by the imagination of the business community and the skills of the developers. Being able to hook into events makes it possible to write tools that will revolutionise version control within business applications. One of the sample applications illustrates this feature by waiting for customers to make modifications to their programming objects, and then sending a copy of the change to the Dynamics NAV Partner. No longer will we need to worry about lost, undocumented, or inappropriate programming changes.

These new features are available for a limited time only. Eventually, Microsoft will release Dynamics NAV 5.1 and my fantasy vapourware version will be replaced with the real thing, but until that day, I have the perfect ERP system.

Debuggered

2007-05-20T21:46:00.000+12:00

I don't know how it happened but I broke my debugger, but it's OK; I fixed it! After reading some posts on MIBUSO (or was it dynamics user?), it seems I am not the only person this has happened to. Thankfully, reading the forums also pointed me to the solution.

The symptoms of the problem are that the debugger for Dynamics NAV 5.0 (and earlier versions) launches but the code window is not displayed. On my machine this happened for version 5.0, version 4.0 SP3, 4.0 SP2, clean installs, you name it. The only thing I could do was close the debugger window, which caused the window to close and then be re-launched after a short pause (I figured this was because the program was getting to the next trigger. You can see similar behaviour if you close the debugger when it is working properly). I kept closing the window and eventually returned to NAV (giving me chance to turn the debugger off).

I logged this as a support issue with Microsoft but the suggestions they gave me (based upon previously solved incidents) didn't help. The premise of their suggestions was that the debugger and application communicate using TCP/IP and that something (most likely an anti-virus program or firewall client) was intercepting this communication and preventing the debugger from displaying the code.

After trying various options suggested by Microsoft, I finally got fed up and searched for the solution myself. I was quite surprised to find lots of people with the same problem - although there were a large number of postings from people that had either not found a solution or not updated their post. I think the best posting I found was on the dynamics user group (although I think the same solution was shown on MIBUSO).

For me the solution was to use Add/Remove programs from the control panel to remove a program called "Advanced Gateway Client". I don't remember installing this program and after I removed it the only difference I noticed was that my debugger worked again. Debuggered!

Dynamics NAV 5.0 – Send to Application Feature – Part 2

2007-05-13T21:48:00.001+12:00

In my previous post on this topic, I looked at the send to application feature in Dynamics NAV 5.0. I explained that you can send data from NAV to Word, Excel or any other application by using an XSLT template to transform the XML data into a format that the target application understands. I also introduced the WordprocessingML language that can be used to create Word documents as XML files – a feature that Microsoft introduced in office 2003.

In this post I am going to show you how to see what NAV is producing as the starting XML file before any transformation is applied. Then we'll look at the WordprocessingML schema and create a simple Word document using Notepad. If you want to see the results of your efforts, you'll need Word 2003 or 2007 installed.

The first thing I wanted to know when I saw this new feature was how it worked. I understood the concepts of XSLT and WordprocessingML and applying transformations to XML files but I had never done it in practice. I didn't know what NAV was giving us to work with. In order to be able to make my own transformations, I needed to first know what my starting point was.

XSLT is a language that can be used to transform an XML file from one format to another. I knew that I could load my own XSLT file as a template for NAV to use but I didn't know what my starting XML file would be. I would like to pretend I am some kind of XSLT guru, but the truth is I just asked some people at work. My question was "what do I need in an XSLT to make my output file a copy of my input file?" The hardest part was convincing people I was not crazy and listening patiently as they explained that there was no point in applying a transformation that would make the output file identical to the input file. I think the answer ultimately came from a Google search – but it helps to know what you are looking for.

Open up Notepad and enter this text:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:template match="/">
<xsl:copy-of select="/" />
</xsl:template>
</xsl:stylesheet>

Now save the file as "Copy All.xslt".

Since the file is so short, I will give you my expert explanation of its contents. The first line is probably really interesting. All I know is you need it - so put it in there for all of your XSLT files otherwise they won't work. It's something to do with defining this document as being a stylesheet and indicating that anything that starts with xsl: in the document will be defined in the schema that is referenced by the namespace provided. Incidentally, if you paste the http://www.w3.org/1999/XSL/Transform URL into a web browser, you'll see a link to the XSL Transformations specification. If you really want to learn about XSLT this is probably a good starting point.

The second line says find the start of the file you are reading.

The third line says copy everything without changing it.

Lines four and five are the closing tags for lines two and one respectively.

Now we have our transform, let's look at applying it to NAV. First we'll set up a new application to handle our transform. We need something that's good at displaying XML files – so I decided to use Internet Explorer.

Select Administration > IT Administration > General Setup > Launch Applications. The Send-to Programs form is displayed showing two programs: Excel.exe and Winword.exe.

Create a new record and enter IEXPLORE.EXE as the executable and Internet Explorer as the name. Press Esc to close the window.

Now I want to import my new template and tell NAV to use Internet Explorer to launch the resulting XML file.

Select Administration > IT Administration > General Setup > Manage Stylesheets. The Manage Style Sheets form is displayed showing all style sheets that have been set up for the database (oops – maybe that should have been set up as data per company since your templates are likely to contain company logos – as Microsoft would say: "this is a reseller opportunity" meaning: "oops we stuffed it up – you fix it"). In the standard CRONUS company this will mean a default stylesheet for Word and Excel (common to all forms) and a few form-specific templates (Customer Card letter template, Vendor Card letter, etc.)

Before you can create a new template, you need to select either the Style sheets common to all forms or Style sheets for this form only radio button against the Show label. For this example we want to be able to export from any form so click the Style sheets common to all forms radio button.

Maybe you are thinking you can now create a new record in the list of stylesheets – but that would be too easy. Instead, you should select the Import option from the Functions menu button. The system will display the Import Style Sheet form. Use the assist edit button to open the "Copy All.xslt" you saved earlier or type the path and file name into the Style Sheet field. Enter "Copy All to IE" into the Name field. Use the look-up button to select Internet Explorer as the Send-to Program. Click the OK button to import the stylesheet. You should now see a new line in the Manage Style Sheets form and, if all has gone well, we will now be able to test it out.

Open the Customer Card and click the Send Options button. The Program Selection form is displayed. Select our new Internet Explorer program. The Style Sheet field is showing the first style sheet that can be used for the data we are exporting. If it is not the one you want, click on the field and use the look-up button to select a different style sheet – in this example you should have only one stylesheet set up for the Internet Explorer program. Click the Send button.

If all has gone well, you should now see Internet Explorer with a new xml file displaying the information from the Customer card. Take a look at our xml file. You'll notice that the first part just gives us a hierarchy of all the controls on the form starting with Form, then Tab Control, then Tab Page, then a mixture of labels and text boxes. After the controls, we see some other sections for Customer, WorkDate, Salutation, CompanyInfo, etc. This suggests to me that there is something funny going on. How does NAV know that these things are important? We'll look at how you can create your own custom content to the XML file in a later posting.

To wrap things up, let's take a look at a simple WordprocessingML file. In my next post on this topic, I'll look at how we can create an XSLT to make a Word document in a format of our design.

WordprocessingML is a neat language that allows you to serialise an entire Word document as an XML file. The majority of the file contains tags that start with "w:". The text is typically built up from paragraphs (w:p), runs (w:r) and text (w:t) elements.

Open up Notepad and enter this text:

<?xml version="1.0"?>
<?mso-application progid="Word.Document"?>
<w:wordDocument xmlns:w="http://schemas.microsoft.com/office/word/2003/wordml">
<w:body>
<w:p>
<w:r>
<w:t>WordML Rocks!</w:t>
</w:r>
</w:p>
</w:body>
</w:wordDocument>

Now save the file as "WordML Rocks.xml". Even though the file extension is xml, the file is displayed with a WordML icon. This is because of the progid="Word.Document" property. Explorer is looking inside the xml file to determine how to display the icon.

Double click the file and Word will launch and display a document with a single line of text.

That's all for now. If you want to earn bonus points, you could take a look at the XSLT templates used in the CRONUS company and the XML files they produce.

Help - I Accidentally Updated My Navision Database!

2007-05-09T09:28:00.001+12:00

I spotted a great posting on the Navision News Group today.

Dynamics NAV has this nasty habit of stamping the version number of the client program against the database. So the scenario goes something like this:

You have a 4.0 SP1 Client and you (and all your co-workers) are accessing your database quite happily
You install a 4.0 SP2 or SP3 client just to take a look at the exciting new features and fire-up the new client
The client looks at your ZUP file and finds the server and database details you last used and tries to open the database
The client puts up a message asking you if you want to upgrade your database to a new version
You don't read the message and click Yes because that is what all users do - it's easier than thinking
The client puts up a second message saying "I know you didn't read the first message, because no-one ever does, but are you really sure you want to upgrade the database?"
You don't read the message and click Yes again
The client lets out a deep sigh and proceeds to update your database
You get to use the new client on the database and everything is hunky-dory.

Meanwhile...

Your co-worker with their 4.0 SP1 client tries to open the database and they see a message: "This database has been upgraded to a later version by someone who does not read warning messages, you must find that person and taunt them."

I may have changed the wording on some of these messages slightly - but you get the gist.

What I didn't know was that, according to the Ian C who posted the response, if you are using a SQL database, all is not lost. There is a dirty hack - which I must say I have not tried out - in which you can edit the databaseversionno field on the $ndo$dbproperty table. I would suggest creating a new database with the correct client just to see what the version number is - in Ian's example he talks about changing the value from 60 to 40.

I actually tried to use this today (15th September 2007) and it sort of worked. I tried to change a 4.0 SP3 back to a 4.0 no service pack. When I changed the value of the databaseversionno field to 30 the database would still only open with a 4.0 SP1. Maybe I did something wrong. What I did do is make a note of the version number used for a number of different versions:

3.10A = 13

3.60 = 14

3.70 = 17

4.0 = 30

4.0 SP1 = 40

4.0 SP3 = 60

5.0 = 80

5.0 (Build 25653 known as Update 3) = 82

5.0 SP1 = 95

Dynamics NAV 5.0 - Send to Application Feature - Part 1

2007-05-03T17:27:00.000+12:00

There are four new buttons on the toolbar for Microsoft Dynamics NAV 5.0. This posting starts to look at the three buttons used for sending data to other programs.

You’ll find the relevant help topic under Welcome to Dynamics NAV Help > Using Microsoft Dynamics NAV > Sending Data to Other Programs. This tells us that we can export data from NAV to other programs such as Microsoft Office Word or Excel. You can also export data to any other program that can handle an XML file.

To see this in action, use the CRONUS demo company for version 5.0 and open a customer card (Sales & Marketing > Order Processing > Customers); I am using customer account 10000 for The Cannon Group PLC. Now click on the Send to Microsoft Office Word button (or use the Ctrl+W shortcut key).

Word springs into life with a new document (called “10000 The Cannon Group PLC – Customer Card.xml”), stored in your temporary file path.

The sample document is a letter formatted to contain a CRONUS company logo and address details, Customer Address Details, Today’s Date, Salutation, etc. There is a place for you to type your letter contents.

Close Word and return to Dynamics NAV. Now hit the Send to Microsoft Office Excel button (you could use the Ctrl+E shortcut).

This time Excel opens and we are looking at another xml file. There is one Excel sheet within the workbook for each of the tab pages on the NAV form. Each sheet contains a title, the caption for the tab and the labels and data values for each of the fields on the tab page.

From Office 2003 onwards, Microsoft provided the ability to save a Word or Excel document as an XML file using their WordprocessingML or SpreadsheetML schemas. The new feature in Dynamics NAV takes advantage of this by grabbing the information on the screen and generating an XML file which it then transforms into another format by using a pre-defined XSLT (a file that describes how one XML file should be transformed into another).

You can create your own XSLT files and load them into NAV so that they can be used to make transformations. You can even specify your own applications to handle the new file, so you are not limited to using only Word or Excel.

I have two issues with this new feature. 1) Creating the XSLT file is not easy. If you have the ability to do this, you could probably have written some C/AL code to create the Word document directly. 2) Why would you use this feature? I am really struggling to think of a situation where this would be useful. If anyone has any suggestions, I would like to know.

I could only think of one possible use for this: if I wanted to write a user training document or document a test case and I wanted to list all of the fields on a form, this would be quite a neat way of doing it, but this is not a typical scenario for an end-user. Maybe I am not thinking laterally enough.

In the next post on this subject, I’ll take a look at how to set up a new XSLT so I can see what NAV is giving us to work with. Then, I’ll look at creating a Word document from scratch by making a new XSLT for WordprocessingML.

Goodnight Kiwi

2007-04-29T11:16:00.000+12:00

I was talking to Mary, my hairdresser, about life when we were kids. We didn't have so much to choose from compared to kids today.

My daughter has access to DVDs, the Internet, a 24-hour Playhouse Disney Channel and many kid's TV programmes. There are more entertainment options than there are hours in the day!

Mary told me that when she was a kid, the weekend highlight was sitting by the radio on a Sunday morning listening to a children's story broadcast. Badjelly The Witch was one story she could remember. Saturday morning was helping her Mum with the washing (feeding clothes into the wringer).

A rare treat was to stay up late enough to watch the Goodnight Kiwi - an animated short that featured a cartoon Kiwi climbing a TV tower and going to sleep. I was amazed that there was something so cool for New Zealanders at the end of the day's broadcasting (something my daughter will know nothing about - "What do you mean they used to stop broadcasting? Why?") You can see it for yourself on YouTube.

In England, our late-night treat was to listen to the BBC Orchestra playing God Save the Queen. I wonder what happened in other parts of the world when TV broadcasts finished for the day.

A Short Poem About People and Tomatoes

2007-04-28T12:26:00.000+12:00

If people were tomatoes,
Strange sights would be seen:
When you’re dead – you’d be bright red,
And when you’re born – you’re green.

Do Butterflies Have Teeth?

2007-04-28T12:13:00.000+12:00

This site is a place for me to write about Dynamics NAV. Do you know what that is? It's an ERP system that is now owned and developed by Microsoft. If you want to find out about it, go to the Dynamics NAV site, but I'll warn you - it's pretty boring.

So why do I want to blog about something that's boring? Well for starters, it's one of the few things I know anything about. I work with it, I see how it is used in businesses and I think about it way too much. I want to make the product less boring, interesting or even fun!

I have a cunning plan: I will draw people in to my site by offering little bits of advice, solutions to the odd problem, hints on new features, and possibly some ramblings on totally unrelated topics. Then, when I have enough followers, I will stage a revolution. We will march to Redmond and demand a change.

I think it would be great if people that care about Dynamics NAV tell Microsoft what they want. There are over a million Dynamics NAV users. I wonder how many of them know how to suggest product enhancements? There is a site where you can post your ideas for new product features and people can vote for your suggestion. Allegedly the product development team discuss the features with the most votes and consider them for future releases. I have tried posting suggestions here and the most votes I have had is seven. One million users. Seven votes. It is possible that my suggestions were bad but I think the system is broken. Do the Dynamics users have a voice? Do they know this site exists?

I feel the need to calm down so I will leave you with a rambling.

I was reading the story of The Very Hungry Caterpillar by Eric Carle to my daughter and, when I got to the end, I was struck by a very curious thought. I am sure most people have read this book at some point in their lives. At the end of the book, our hero builds a small house around himself, called a cocoon, and after two weeks he nibbles a small hole and then emerges - a beautiful butterfly. Now the part that I found disturbing was the "nibbles a small hole" bit. Do butterflies have teeth? Is the whole innocent, friendly insect thing a cunning disguise?

A quick trip to google tells me that, apparently, butterflies do not have teeth. But the question remains: how does the beautiful butterfly nibble his way to freedom?