Monthly Archives: March 2014

The five first Design Patterns & PRS Video’s are online

Hi, my name is Mark Brummel and…

I am happy to announce that five video’s about design patterns are online now.

The first six words are how I start all the video’s. The way to make them is pretty strict.

To make it easy I’ve created a playlist on YouTube with all the video’s

These video’s are online now:

How do I Read and Understand the Posting Routines

This video explains how the general flow of data works in Microsoft Dynamics NAV and explains the difference between posting, processing and archiving. Then it explains the Test Near, Test Far, Do It, Clean Up principle and where that can be found in codeunit 80, Sales Post.

Posting Routines in Dynamics NAV

At the end of the video we look at an example posting routine written with atomic coding.

How do I Use Temporary Datasets

This is one of my favorites. This video explains how to decouple UI from table structures and how to use temporary datasets in AL code. At the end of the video we combine Queries and Temporary Datasets in a page object.

How do I Use the Transfer Custom Fields Design Pattern

This pattern is a member of the larger Hooks pattern as described by Waldo. The ideo is to establish a contract to moving fields between tables where TRANSFERFIELDS cannot be used. This pattern will make upgrading much easier.

Data Process NAV

How do I Use the Select Pattern in a Posting Routine

In Microsoft Dynamics NAV you can only send one record as a contract to a codeunit. This video describes two ways to avoid sending extra parameters using functions. One is adding extra fields to the sourcetable, the other solution is to add an attribute table, which by itsself is a Temporary Dataset

How do I Implement the Number Series Pattern

Number Series are very user friendly and easy to implement once you know the steps. This video shows that step by step. It also explains how to make Number Series cross company in a very easy way.

Have fun.

The next five will be online soon.


Tip #23 – The ‘Return’ Shortcut

Today I spent some time at a customer creating a new role center for Dynamics NAV CRM users.

One of the challenges we had was to create a My To-Do list on the Role Center that was based on the actual table. All went well until we started to test and compare to My Customers.

We could not get the double click on the lines to open the To-Do card.

So we started to ‘Find The Differences’ process. It is like playing the picture game with my kids.

Then, after trying out a few things we found this in the My Customer action properties…

Amazing isn’t it! Because return (hitting the Enter key) is the same as double clicking the line it works. [:P]

So now, let’s implement one of my favorite features.

Go to the General Ledger Entries page (20) and open the actions.

And make Navigate the default action!

So  now when I double click an entry it will Navigate.

Dear Microsoft, can you please implement this. [8-|]


The importance of keeping an open mind

How cool is this, a blog post about my blog.


There are 3 things that I learn today when I was exploring how to get things done in NAV 2013 R2:

1) New solutions often mean new opportunities

I was creating to create a new company in NAV 2013 R2. I am quite experienced with it with the Powershell tool.

There was a slight catch. The solution that I was familiar with required me to run Powershell as an network administrator. For this company, the IT department was less than helpful (or even useful) in these cases.

Lucky there are some people that have a solution that I did not consider as I was always depending on powershell. See link

2) New solutions mean new teachers

The best part of the story was while I read the link above, I did not register the simple solution as it was a simple one liner. The solution was complete but it…

View original post 337 more words

Design Patterns | Video Series

For the Readiness program on Microsoft Dynamics NAV 2013 R2 I am asked to make a series of 10 video’s on creating Vertical Solutions.


I did not need more than a split second to say yes since it matches exactly to the book I published about Application Design and my part on the Design Patterns team and Partner Ready Software.


Just my cup of tea.

PRS Logo

This video series will focus on best practices to create your own solutions on top of and integrated with Microsoft Dynamics NAV and are based on the design patterns and the methodology that was developed by PRS.

When writing this blog the first two videos are online and I just finised two more. Each week I will release two video’s, except for next week when we have our Design Paterns meeting in Vedbaek.

My (ambitous) plan is to write a small blog post about each video.

The first video’s are:

How do I Implement the Number Series Pattern (YouTube)


How do I Use the Select Pattern in a Posting Routine (YouTube)

NAV 2013 | No more Indexhinting

Editors Note : This blog post was brought forward from my previous blog.

Well, that sounds cool right? Like, never index hinting in NAV anymore after all the discussions in the past.

With NAV 2013 it is no longer possible to setup index hints using the $ndo$dbconfig table.

Does it mean it’s no longer nescesairy.

Unfortunately not.

I found out about this during an upgrade of one of my customers from NAV2009 RTC to NAV2013.

We experienced extremely slow performance in an area where NAV2009 was much faster than NAV2013. And here I promised better performance… DAMN. Stick out tongue

Off course with our perfect documentation (not) it took some time to figure out it might be that index hinting was used in NAV2009 to force SQL using an index it would not use normally.

The process is selecting shipments to be planned in a trip for a taxicompany. We use a common method to select a shipment using the user id, just like you would when applying customer and vendor ledger entries. When selected it’s an extremely selective field (only one or two record of a million have this value) but SQL does not pick it up because… because… shoot me, I don’t know why not. DAMN 2. Stick out tongue

So with index hinting no longer possible (confirmed by Microsoft, done on purpose, by redesign) I needed to be creative.

The solution is to save a pointer to a shipment in a second table called “Selected Shipment”. All shipment that need to be planned in a trip are temporarily stored there and picked up and deleted afterwards. Depending on the architecture of your transaction this could even be done in a temporary table and/or a single instance codeunit.

So recap: no more indexhinting, be carefull with upgrades and creative with solutions.

And PS: The general performance of NAV 2013 is AWESOME!!!

How Do I | Configure and Use the Background Posting Feature


You may be or even should be familiar with the How Do I video series around Microsoft Dynamics NAV which are part of the Readiness programm.

A couple of days ago I tweeted that my first video is online.


I knew Waldo, Vjeko and some others were making the video’s and I was asked to help since the orriginal planning was hard to stick to for the members of the team.

The video is about how to use the Background Posting Feature. Which is something that from my performance background is what I was familiar with and was done by some partners as a mod in the past.

I was helped by this post on the Dynamics NAV Team blog. Thanks Rikke.

You can watch the result of the video on MSDN or on YouTube.

This was my first video I made. I scripted the entire process before recording which you can hear pretty well. I would encourage everyone to try this experience. It’s weird spending a day listening to your own voice.

For those of you who are curious, the software used for the video is Camtasia.

NAV2013 | Excel Buffer

Editors Note : This article was moved from my old bolg.

The Excel Buffer table. Loved by many, unkown to most.

Here is the description on MSDN.

If you still don’t have a clue what I’m talking about, then allow me to explain. Else skip this paragraph and continue to the end of this blog post.

Excel is the most used application to use in combination with ERP but it was not always in Dynamics NAV (Navision).

The interface to Excel that we know as the Excel Buffer table was intruced in the same timeframe as Automation Control using that technology to provide the interface.

Currently the interface looks like this

The reason I call it an interface rather than a table is because that’s what it is. There is never data stored in the table on SQL Server. It is always used as a temporary table.

I’ve written small example application on Mibuso that demonstrates how this is used. With almost 7000 downloads it is by far the most popular one of my downloads on Mibuso. Probably because Google likes “Excel Buffer” and people download it who don’t have a clue about NAV.

So to create an interface with NAV the excel buffer is your friend. It allowes both reading from and writing to excel which most of us know best from the Bugetting functionality in NAV. See : How to: Import or Export a Budget (MSDN).

And now what. Now we know what to use Excel buffer is for. But the Excel buffer has it’s limmitations. Here are the ones I find missing:

  • Multiple Sheets
  • Font
  • Size
  • Backgroundcolor (preferably HTML coded)
  • Foregroundcolor
  • Hyperlinks

Now before NAV2013 you could easily add this functionality yourself. Each cell is represented by a value of the list in the interface (let’s use dotnet terminology) and by adding members to the list which you then map to the properties of the cell.

Like this:

That was possible because NAV was using the Office Automation libraries directly. However in NAV2013 they are using a wrapper dll to accomodate Office Interop.

And the wrapper DLL simply does not have the functions.

And this has been recognised by both the channel and Microsoft as an issue.

Here is a link to a discussion on Mibuso

And  here are solutions provided by the NAV Team. (one and two)

But the workarounds by the NAV team are difficult and not as neat as we had in the old days.

Now there are three different ways you can handle this.

First one is to move back to Automation Control. Simply import the Excel Buffer from NAV2009 and you’re done. But that won’t work on the service tier and is dreadfully slow.

Second one if to implement ClosedXML as hinted by Arend Jan Kauffmann in his blog.

I can really recommend this solution. The ClosedXML is a wrapper around OpenXML (see humor here, laugh please) and basically provides the same interface we had with Automation Control to DotNet. It’s extremely fast and very easy to work with.

It allowes you to use the colors and hyperlink parameters and using of extra sheets and tons and tons more.

What I did is renumber the excel buffer table to something outside the customer number range (it does not have to be in the license, it’s an interface, not a table) and implement ClosedXML.

If enough comments are left on this blog I will make it available as download on Mibuso.

But the real solution would be to have the NAV team implement the things everyone creates on projects. I think my list is a good start.

Last week I was in Vedbaek and they asked me to provide a list of the things I need. Here it is. Do you have any other request? Please leave them as a comment of mail them to me.

Thanks for reading.