Dynamics C5 2014 | Welcome MiniApp

Dynamics C5 2014 | Welcome MiniApp

Ok, I guess it is officially announced now as you can read in Erik’s post.


Although it is hard to find english content and many people probably have questions, let me share what I know about C5 2014 and NAV 2013R2.

Most annoucements of C5 2014 are danish as you can see if you google it. That’s simply because it has always been a danish local product. Like we have exact in the Netherlands and Quickbooks in the USA.

With this release Microsoft has decided to merge the products C5 and NAV. That meaning C5 will still be called C5 but uses the NAV core to run on.

One of the questions Erik rises in his post is what functionality Microsoft will offer with C5. Although I cannot firmly answer that question I can make a good guess.

Usability has always been an issue with Dynamics NAV or Navision. The product is 80-95 procent finished and Microsoft expects partners to add the missing 5-20 percent. (Some add more).

So if you want to merge a product like C5 which is usable out of the box, you cannot use NAV without making it more usable. To Microsoft has the issues of usability of the base products that many partners have had for the last years.

If you have a chance to download NAV 2013 R2 (many of you will have that chance soon when it is released) you should find new objects called “Mini *Something*” and a new Role Center called Small Business.

This role center provides access to the “Mini App” which is a simplified way of using NAV for small businesses.

As for myself, I have been using this featurepack for my own accounting in the last six months and it is great.

Let me show you some examples

1. Creating Customers and Vendors

When I create a new Sales Invoice, I do not have to select an Invoice number, the system generates it for me. And when I select a customer that does not exist, it asks me if I want to create this customer

If I say yes, it asks me what sort of customer I want to create

This is just a small example

2. Statistical Information

When opening the Customer Card You can directly access information about the customer with visual support

Now this post can be a mile long if I start describing all the functionalities in the Mini App but I hope you get the message.

I am extremely exited about this new feature pack and I hope that this will be launched outside of Denmark soon.

Until now the features have been shipped with NAV 2013 R2 CTP builds so I expect that also all NAV partners can benefit from this investment.

Design Patterns

This new way of creating features in Dynamics NAV (C5) requires a new way of thinking. New Design Patterns.

In Antwerp on NAVTechDays there will be a session about the new design patterns that will show best practices on how to program in NAV (C5) to realize a new user expecience.

Good luck to all the C5 partners and welcome to our community!

NAV2013R2 | Interactive Sorting

NAV2013R2 | Interactive Sorting

NAV2013 R2 is about to ship and it contains a bunch of cool new features but one of them I am so entousiastic about I will share it first.

We can, from this release forward, sort on each column in listpages!

Yes, isn’t it amazing? And it works in dropdown lists too!

You do not need to add keys to the table to enable this feature


This means that also setcurrentkey can be used without actually defining the key!


This means that if a user sorts on a column on the Item Ledger Entry table without filters and SQL Indexes they can completely clutter your system. Be aware of that!

Other than that, awesome job Microsoft! Thanks.


TIP #35 | Using Queries in Pages & Reports

TIP #35 | Using Queries in Pages & Reports

This post is an extention of the post I did yesterday about Queries and performance.

As I said Queries deserve more attention. Unfortunately this is another post about what Queries cannot be used for, but with a workaround.

Whenever you want to make a report or page (or form in the old days) show data from two or more tables in one view you run into a challenge. Even though reports allow complex datasets they are easier to design if your dataset is single layer.

Technically it has always been possible to link a table object in NAV to a SQL Server view allowing you to join tables on SQL Server and have a single view in NAV. I never bloged about this cause I’m not a huge fan of this but more information can be found here on MSDN.

The first question that I can remember being in a session at MDCC about queries few years ago after the applause was: “Can we use this as a source for a page or a report?”. The answer was, “That would be great but is not within the scope of the current project”. And today after the release (soon) of R2 this is still the case, it is out of scope and my guess is it will be for a while.

The single and most important reason queries are here is to avoid the loopy-loopy code patterns.

But what if we want to use the query as a source for pages or reports. Well, that solution was presented during that same session at MDCC within minutes: “Then you use the integer table”.

And yes, that would be the solution. Or at least one solution, there is a second more elegant solution.

Let’s discuss the integer solution first.

An example of using the integer table for reporting has always been report 111 Customer – Top 10 List. This report allows you to show the top X customers by sales in your system. X defines the number of loops over the integer table.

As you might (or should) know the integer table is a system table (2000000026) that has been there forever allowing you to build repeat code Patterns. A similar table is the Date table (2000000007).

Writing this I realise that these system tables deserve their own blog article too. Stick out tongue

So let’s start with a cool query that we will use in our page.

The query combines sales information from the Value entries per customer. Now how do we show this in a page.

First step is to create a new listpage with the wizard based on the Integer table like this:

When we are in the page designer the next step is to add the query as a global variable and add a counter variable. Then we add all the query fields to the page and write some code as displayed

You can immediately see that this is not a real nice solution cause we loop at least twice trough the query. This is to get the number of rows to filter on the integer table.

When we run this page on Cronus we see this result:

And yes, we are happy and dancing.


Try page up and down a little now..


Now you might have a solution but I could not find one. And there are more issues with this way of showing a query in a page, we cannot filter, we cannot sort.

So what is the solution?


Quite a few years ago I have blogged about this property. And the power still counts.

But Mark, are you saying we should create a table now? Yes you should. But, that is an extra object! I know but it is free. Tables that are only used temporary do not need to be in a customers license.

So let’s continue.

First we create the table, with Entry No. as primairy key

Remember this table is free of charge, no license required. We only use it as buffer.

Step 2 is to create a new page on this table and toggle SourceTableTemporary to Yes.

And we add a very small amount of code

And this code we run from the OnOpenPage trigger and voila: this is the result:

All the gizmo’s work like filtering and the new interactive sort on all columns that is introduced in NAV 2013 R2

Hope this post was not to long.


Advertisement: I do Tips & Tricks workshops and What’s new training! Send me an email if you are interested or fill out this contact form!

Table vs. Query | Performance Battle

Table vs. Query | Performance Battle

NAV2013 introduced the Query object. Frankly I think that this new object type deserves more attention from bloggers. We have been nagging Microsoft for years to deliver this and now it is there and what happens? Quietness.

I have at least two blogs around Queries. This one is the first. It is an important one because I want to blowup a myth even before it get’s the chance of becoming one.

Queries have two advantages when it comes to reading performance that tables don’t have. First you can limit the amount of data you retrieve from the server by defining only the specific columns you need and second you can join multiple tables into one query saving noumerous roundtrips to the server with our good old loopy-loopy code pattern.

But Queries can’t do what Tables can, they cannot write to the database.

And there is more that Queries cannot do. Let me explain with an example.

In my example I’ve created a realy simple query that contains a few fields from the Item Ledger Entry table

I use this Query in a Codeunit along with the Table variable itsself.

This is a very stupid piece of code that reads the same Item Ledger Entry four times. Twice from the table and twice with the query.

And yes, we get four messageboxes with the Document No.

So what is the clue?

Let’s look at what NAV Sends to SQL Server with Profiler…

Please note that NAV only issues three SQL Statements, not four. And I can tell you a Little secret, if you run this again, it will only issue two.

The two statements that are the same are the Query statements. True, they are more lightweight than the Table Query but unless you have a covering index it will read the entire record in SQL anyway.


Another thing that NAV2013 introduces is Service Tier caching. All the users on the same service tier share the same reading cache. So if I read customer 10000 and another user wants this data it is not read from SQL Server but from cache. NAV Cache.

Unfortunately this only works for Tables, not for Queries.

So remember this when you design your solution. If you read the same data over and over again, a Query might be overkill.

If you want my opinion you should only use queries when joining multiple tables. Never ever use queries in a single table scenario. The only thing you do is adding an extra layer of complexity to your solution and add extra objectcosts for your customer.

Thanks Waldo for the tip!

Directions EMEA 2013 | “In” Office 365 and “On” Azure

Back home for a day after Directions EMEA I am stil Baffled from what happened in Vienna. Even though I warned everyone in my previous post.

As my PRS “partner in crime” Gary already posted last monday Microsoft has announced a new strategy for Microsoft Dynamics NAV.

“In” Office 365 and “On” Azure

So what does that mean in real life? Remember back in school if we wanted to know what something meant we broke a sentence down into smaller pieces. Let’s start with the first piece.

“In” Office 365

So what does that mean. The previous versions of NAV were already capable of interfacing with Office 365 so the new thing is not in this part of the sentence.

The new thing is in the smallest word. “In”.

Until this release, whenever we had an interface with a product, NAV was always “leading”. Meaning usually a user started from Dynamics NAV, pushed a button and Word, Excel or Outlook would open and do something.

With this release, office is leading. So if you log into Office 365 you would get Dynamics NAV as part of the subscription. One could even call it Dynamics365. You don’t even notice that it is NAV. Every part of NAV is integrated. You can see charts and lists as part of your Office 365 experience.

From a repeatable perspective this is a huge thing. There are way many office users than NAV users. Microsoft claims 1 billion office users vs. almost 1.x00.000 NAV seats. This means that only 0.1% of all office users have NAV. Imagine that those office users would have NAV as part of their suite just by clicking on the option. Even if we move to 1% it would mean an explosion of NAV seats.

We will be busy.


We can’t be that busy, we don’t have enough people, not enough consultants, not enough developers! Panic!!

This is where the repeatable story comes in, and this is where Azure kicks in!

“On” Azure

As with Office 365 the news in this sentence is not Azure. We could already run NAV2013FP1 on Azure, we can even run NAV2009 on Azure infrastructure services. Hell, it’s “only” a virtual machine.

Well, that is not true and there is news.

Azure is not “just” a virtual machine. I’ll come back to that later.

What is new in this sentence is not in this sentence. That makes it more difficult to explain.

Multi Tenacy

New in NAV2013R2 is that it is a true Multi Tenant environment. Remember that I asked last year, what happend to Azure? Truth is that I found out what happend but won’t share this online. (nah nah…). Let’s focus on the fact that it’s there today.

What does Multi Tenant mean in combination with Microsoft Dynamics NAV.

It means: “Running multiple independant legal entities on the same codebase on the same server”. So let’s say I am a lawyer, no let’s say I am a freelance consultant and I need a solution to make sales invoices, register my expenses, declare VAT and do bank reconciliation. That is not rocket science, even for someone like me. Asuming there are 100.000nds other freelance consultants out there that have the same needs, why not share the same software. This is now possible with Microsoft Dynamics NAV 2013R2.

With a simple powershell command it is possible to spin off a new tenant on a codebase and start working with a few setup questions like companyname, VAT number etc.

And it get’s better, it is integrated with Office 365! Wow!


This is al extremely cool and sounds very simple to do, but how are we going to execute on this. Who is going to do this? Are the Dynamics partners in the current ecosystem going to change their models from single implementations with customisations to serve me as a small customer with a monthly subscription?

That I guess is the real reason why I am baffled after attending Directions EMEA 2013.

The software is ready for it, Microsoft is ready for it, the customers are asking for it. But who is going to deliver it?


I promised to come back to azure infrastructure services. Yes, if you use it it is a Virtual Machine, but in my opinion there are two things that differantiate Azure from any other hosted VM solution.

1. Portal Experience

If you never worked with the Azure Portal I can only urge you and recommend to try. I’ve been working with servers for 30 years of my life (and I am only 36 years old) and I have never, ever experienced seting up a new machine being this simple. Never. Just a few clicks and you are up and running.

I have two machines running in Azure and my current bill is under 3 euro’s per day! Is that cheap or what! Truth is here that I need to admit that MVP’s get 150 euro/month for free, but hey, don’t tell anyone please.

2. Powershell

Azure supports a full powershell experience. You can create new machines and install software all from powershell commands.

And now what?

Well it is one of the last summerdays in The Netherlands, 25 degrees still outside at 9pm. I am going to sit outside with my wife and enjoy.

What wil you do?