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

Hi, my name is Marije 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.


Tip #36 | Using Mark and MarkedOnly in the Role Tailored Client

I always wanted to blog about myself, but humble as I am I never got around to do that. 😉

But now I have an excuse.

When the Role Tailored Client was introduced in 2009 one of the things that is missing from the UI was the ability to Mark some records and filter on Marked Only records. This is usefull when you want a subset of your data with records that have no common value to filter on.

In the Classic Client, now Development Environment, you can find the functions here:

Mark1 Mark2

However, this feature was discontinued from the IU, but not from C/AL.

In the C/AL Symbol Menu it is still possible to Mark records and filter them.


So let’s see what happens if we use that in a Page step-by-step. We’ll use the Customer List (Page 22)

Step 1 – Add a new Action Category (Optional)

To make it easy for end users to find the options we’ll start by adding a category for the actions we are going to add later.


Note that in order to add a new Category you need to also add the default ones. In this case we will work with Category4.

Step 2 – Add Actions with the correct properties

We add three actions in the ActionItems category


And we assign a Name, Caption, Image and PromotedCategory. I also choose to make them big and promoted.


Step 3 – Add C/AL Code

We add the C/AL commands to the actions


Note that we use the reverse value to toggle the values on and off

Step 4 – Add a column indicator

To make is easy for users to see if a record is marked or not we add a new column to the page with Mark as the source expression


The Result

When we run the page you see that we can Mark records which will be indicated in the new column, filter on them and easily clear the Marks.


TIP: Copy/Paste

Since we did not use any variables or functions we can just copy/paste the actions around to any other page, as long as the ActionCategoryML does not conflict with existing values.


Tip #21 – Implementing No. Series

Editors note: This tip was brought forward from my previous blog and is still as actual as it was back then. Meanwhile the Design Patterns initiative have posted a similar walkthrough on their site.

Number series is a great feature in Navision that allows you to use all kids of numbering methods easilty and also allows the application to work the same everythere,

So how do I implement Number Series in a new table. Let’s go through that step by step. For this example we will use the Item table to copy and paste from.

Step 1 – Create the table.

A table that has number series has a minimum of two fields. A primairy key field of type code length 20. But also a second field to store the number series. This is a code field as well with length 10.

Go to the object designer and create a new Table. The first field will be created manualy like this.

Please call it No. and not something else or otherwise our copy paste actions will fail.

Secondly we will open the item table in the designer as well. Find the No. Series field and copy/paste this.

Now our table looks like this.

Step 2 – Add the code

When you implement number series in your table, there are three required pieces of code. One in the OnInsert trigger, One in the OnValidate of the No. field and a new function that is always called OnAssistedit.

We will copy and paste this code from the item table like this.

When you are done with the copy/paste action your new table should look like this.

Step 3 – The variables

When you now compile your new table you will get an error message like this.

This is because we have not yet declared the nessesairy variables. We will need two of them, the Inventory Setup table and the NoSeriesManagement codeunit. We can also copy and paste this from the item table.

So our variables are like this.

Please notice that you will still receive the same error after this.

Step 4 – Code changes

In order to have the code compile we have to change it a little bit. The code we copied is reading the inventory setup in a function but that is not nessesariy in our case. We will just read it directly.

So go to the C/AL code and do a find and replace on GetInvtSetup by InvtSetup.GET like this.

Step 5 – Save the table (Twice).

Now we need to save the table, but when we try we’ll get this error.

The reason for this is simple to explain. We have created a new field (No. Series) and started using that field in code. But because the table was not saved in between, the compiler does not know that field yet. To solve this we need to save the table without compiling.


And then compile the table from the object designer.

Step 6 – Create your own number series.

If you look closely at the code we just created you’ll see that it uses the field Item Nos. from the inventory setup table. There is a high chance that you will want to use a number series of your own so let’s create one. We will do this in the inventory setup table as well but this can also be any other setup table in Navision.

Open the Inventory Setup table in the obect designer and copy the Item Nos. field like this

And paste it as number 50000. And rename to Test Nos.

We will also add this to the Inventory Setup Form or page

Notice that we create a new (fast) tab for our field. This is best practice.

Run the form or page and populate the number series.

Step 7 – Change the C/AL code

To use this new number series we need to go back to the code in our new table and do another find and replace action. This time replace Item Nos by Test Nos.

And save the table.

Step 8 – The User Interface

Last step in implementing the Number Series functionality is adding it to the user interface. This is done via the OnAssistedit trigger. We will demonstrate both forms and pages.

Create a new form with the form wizzard or page with the page wizzard.

Now go to the code behind the No. field by focussing on the field and press F9. You will see the four triggers, OnLookup, OnValidate, OnDrilldown and OnAssistedit like this.

Our code will go into the OnAssistedit trigger of both the form and page and is copied from form 30. The Item card.

Now when we run the page or form you’ll see that it works and how easy it was to implement.



Convergence 2014 | Strategy Cleanup Time

It has been a while since I did some blogging. In Dynamics NAV we are in between releases and everything about Crete is still under NDA. However, after looking at the Convergence 2014 Keynotes yesterday I felt I needed to write down my opinion about where I think Microsoft is heading with Dynamics.

Disclaimer: This article is written primarily to clear my mind and is only my personal opinion on the market, based on publically available information.

More than a decade ago Microsoft started their ERP aspirations and acquired Great Plains and Navision and started their own CRM application. Back then everyone thought that soon after that the products would merge into one application. But they did not. Until this very day Microsoft is maintaining all products. Or not?

The Convergence event was Great Plains heritage. I visited the event twice in the US back in 2006 and 2007 and the first events in Europe around the same time. Back then Navision and Axapta were big in Europe and Great Plains dominated the US. Great Plains never really made it outside but Navision grew strong in the US competing with Great Plains. With this in mind it was not a surprise that Great Plains got a lot of attention during the keynotes back then, but Microsoft made sure all products got their share.

This has changed. Yesterday I replied on Twitter that I was disappointed about the keynotes of Convergence 2014, but this is not entirely true. The keynotes of Convergence completely shifted towards AX, CRM and a little GP. I guess I was just disappointed that NAV did not get much attention. But this can be explained, but not very easily. Let me give a try.

Yesterday I got an email from a friend with sales numbers of Microsoft Dynamics in the last 12 months. Dynamics NAV was selling better than other products and he was happy. But should he be?

Here are the figures:

Conv   2014

Conv   2013

%   change


Microsoft Dynamics AX





Microsoft Dynamics GP





Microsoft Dynamics NAV





Microsoft Dynamics SL





Microsoft Dynamics CRM










Now although Microsoft never got around merging the products, what they did do is position them strategically. By doing this they allow individual product development to focus on different target markets while avoiding internal competition. But is this really true and is it working?

I replied to the email with a quick analysis of the figures and I will share this here too.

First of all we are missing a product in this list. A product that Microsoft never really mentioned actively during the last decade but that has become quite interesting in the last year. Microsoft Dynamics C5. Let me try to explain why this product is so important from a strategic perspective.

C5 is a local product that holds a strong position in Denmark, the original market where Navision was invented and the product was inherited by Navision when they acquired Axapta and thus inherited by Microsoft. They kept supporting the legacy software quietly for 10 years until they decided to stop the product. Microsoft Dynamics C5 2014 is actually Microsoft Dynamics NAV 2013 R2 with another splash screen and limited functionality. But that’s not the only difference; even more important is the different pricing. It’s extremely cheap compared to NAV, but limited to 3 users, and only available in Denmark. For now at least.

Let’s remember this and look at the other figures. The next thing that surprised me, and also makes me wonder if the figures are 100% correct, is that SL (Solomon) is not being sold anymore. Although Microsoft supports the product officially, rumors have been that they do not maintain the product themselves, but outsourced it to an external company. Maybe this is why there are no sales data, or maybe it is safe to claim that the product can be declared deceased.

If this is true, it’s interesting to ask the question why Microsoft did not pull the same trick with SL as they did with C5. We’ll let that thought hang for now while we look at GP.

GP is an interesting figure. Reason for that is that like C5, GP is only sold in a local market. North America that is. Realizing this fact, the numbers need to be compared differently to NAV than just one-to-one. They sold, according to this information, 4000 licenses in North America alone which is an impressive figure compared to the 8000 licenses that NAV sells worldwide.

Then we have AX and CRM. Each worth 1000 adds. This seems small compared to the numbers GP and NAV write up together, but from a strategic perspective these 2000 adds are at least equally interesting to Microsoft or maybe even more.

Microsoft has been struggling with both the marketing positioning of the ERP applications and the real world interpretation of this. However it seems that this is clearing up although there is at least one conflict area that needs to be solved.

Last year Microsoft announces that AX is now targeted at the Global 2000 companies. These are large companies like Delta Airlines who was part of the keynotes yesterday. Considering that there are only 2000 Global 2000 companies, 1000 adds theoretically should cover half of that market although I expect not all of the 1000 adds are in this target area.

This positioning pushed AX up which was a big relief for a lot of Dynamics NAV partners who compete internally with AX and takes away part of the pressure these applications had internally.

While AX has been pushed up, NAV has been pushed down. Or at least have been by the marketing department of Microsoft. Also the strategic investments in the products have been implemented accordingly.

The best way to explain this would be to compare technological and functional investments. Microsoft did a huge effort in putting a lot of functionality out of the box in AX making it able to compete with SAP and Oracle while the investments in NAV have been targeted at making it high-tech from a repeatable perspective in cloud scenarios.

So from a marketing perspective this makes a very clean cut between AX and NAV, but this clean cut has yet to be adopted by the real world. And as we all know this takes time and time is something that money can’t buy.

But does the real world work like this, and where does that leave NAV and GP? Let’s try to look at that from a business perspective.

One thing the keynotes had a lot of was how customers are positioned in the current market environment. There was a demo of a guy that lost his credit card in Iceland and the so-called phone operator pretended to be friends with the customer based on CRM data. This customer based thinking is a global trend, but was aggravates me a little is that people pretend this is something new. But it’s not. It’s how people want to be consuming and how we consumed for the last 2000 civilized years except for the last decade. Globalization and computerization allowed companies to get bigger than we could have ever imagined, but this lead to consumers being treated like numbers. We all have experiences with large airline or telecom companies being held on a line when something went wrong outside their standard procedures. This was caused by companies growing outside the size where human relationships were maintainable and the trend is that the internet and social media solves this by empowering the individual voice.

This market environment is what AX and CRM serve in a brilliant way and from that perspective the keynotes were very interesting to watch. For Microsoft it is very appealing to have a strong position in that market area and therefore the 2000 adds that AX and CRM did were more important than the 12000 adds from GP and NAV. From their perspective.

NAV is positioned by Microsoft as a highly repeatable cloud product and this from that perspective extremely interesting for them. Much more than the traditional positioning that NAV used to have. Yesterday Microsoft showed this slide during the general NAV session:


The SMB market represents 90% of the total businesses and 90% of the employees and the latter are concurrent users from a Microsoft perspective.

Traditionally Dynamics NAV has a strong position in the high end SMB market. This is a segment where companies are small enough to be managed by traditionally one or more owners that had a unique view on their market requiring an ERP package that could support this. Being very easy to customize Dynamics NAV has a strong position in this part of the SMB market.

From a Microsoft perspective this market is less interesting. The reason for this is that most of the revenue at these companies are service revenues and not so much licensing. This is the reason for NAV to reinvent itself as a highly repeatable cloud solution.

However from a partner perspective the traditional NAV market is highly interesting as consultancy rates are a great revenue and customer loyalty can be extremely profitable.

This is where the friction is and where it will be highly interesting to see where the channel will be moving to and also why the investment and positioning of Dynamics C5 is so important.

It is also where GP and NAV start to be overlapping, which will be interesting to watch what will happen internally at Microsoft.

As a European NAV consultant GP was something that happened across the Atlantic. It does not exist in Europe and therefore was not interesting. However the differences between GP and NAV seems to be the same reason for the differences between the European and North American Dynamics markets and this will be a challenge for Microsoft.

During one of the meetings between Microsoft and North American partners many of them complained that the fact that there is little investment in the NAV functional area’s made it hard for them to compete with for example Epicore. I made a statement during that meeting that the responsibility of Microsoft is to deliver a solution that is 80% ready to go to market and partners should deliver the 20%. Microsoft delivers the spine and partners create the body. This is how NAV is sold in Europe. Some partners use that 20% to go vertical and even add more while other partners add horizontal features completing NAV to be used in more traditional trading scenarios. These more traditional scenario’s is where GP in North America is more successful than NAV. Out of the box it has more functionality than NAV but it is sold for the same price. However the underlying technology is not as flexible and high-tech as NAV, but that does not matter if you are not interested in doing customizations.

So my moving Dynamics NAV in a strategic area where cloud repeatability is more important than customizability Microsoft has put themselves in a highly interesting position given the fact that they use the same 80% finished application.

The combination of Twitter and online convergence allows one to almost feel present at the event and one of the tweets yesterday was highly interesting from the point of this analysis.


Microsoft typically holds pretty smart people on their payroll and it would surprise me if internally they would not have made the same analysis I just made. The question however is if adding functionality after the Crete release is not too late.

Competing products already have a strong position in the cloud and by numbers the out-of-the box GP product is outselling NAV in the North American region unless half of the NAV sales are in that area, which with the traditional European focus of NAV would surprise me.

The next two to five years will be highly interesting. From a technological perspective Dynamics NAV in the cloud is next to brilliant. The way it is integrated into .net, azure and PowerShell is next to perfect and beats any other product in the market. However it will only be successful if partners provide the 20% functionality. However, for partners the traditional high-end SMB market is way too valuable to just let go and switch to a completely different cloud based subscription model. It would be easier and make more sense if Microsoft adds the 20% themselves and make the product more cloud ready from a functional perspective. Which then would make an interesting question where that leaves GP? And where does that leave the traditional NAV market share?

Disclaimer: This article is written primarily to clear my mind and is only my personal opinion on the market, based on publically available information.