Category Archives: Uncategorized

Why Microsoft should add a “Surrogate Key” to Microsoft Dynamics NAV

If you have meet me at a conference last year I am sure we have discussed my favorite topic: “Surrogate Keys”

Why is this so important to me?

The key message we have heard the last few years at every conference has been repeatable solutions, but if you want to create repeatable solutions it all starts with repeatable code.

So what has that to do with Surrogate Keys you might ask?

Well Surrogate Keys are one way (and until today one of the best I have found) to create functionality that is completely generic and reusable. Back a few years before I had even formalized my thoughts about this I called it “LEGO Programming” (okay I am Danish and love LEGO. Deal with it J) because I wanted to create code similar to these wonderful building blocks that can be placed anywhere in a construction and they just work.

LEGO is just one of these wonderful toys that you can be very creative with.

Why should my code be any different?

Well back to why Microsoft should look at this as a standard part of the platform:

  1. They want us to create generic solutions, so give us generic tools to do it with. Yes we can use things like RecordID, but a surrogate key is much more flexible and doesn’t depend on any record data. It also doesn’t suffer from renaming issues…
  2. My implementation has some faults. If you would use the INIT function it gets cleared out which is should not unless you actually insert a new record. This is standardly done twice in Table 37 and 39 as an example. We can work around it quite easy, but we should not have to do so. A proper implemented Surrogate Key in the platform would resolve that issue.
  3. Too many places in the application things have been done locally while using global wording causing things that I find highly regrettable. Take the table 5062 “Attachment”. Just based on the word you would assume it would work anywhere in the application and not just in a small local area of the functionality. Why should attachments be limited to one record when it could be so much more?
  4. A generic platform implementation of this would save the community a lot of work of adding Surrogate keys to all tables where it is needed.
  5. It is so simple to do compared to the benefit it would bring the community.

Just to be clear I am not pointing at anybody with my examples. They were developed in another age and great at the time. All I am trying to say is that we need to change gears.

It is time to start thinking generically and hold ourselves to a higher standard.

Copy/Move Data placed in Surrogate Key Based tables

An example of how to do things very generically when using the Surrogate Keys Design Pattern is when you need to copy or move data attached to one record to another record.

Looking at classic design you often see different functions created to copy or move data in Subsidiary Tables between different master records. Take an area like Comments (My favorite example) where we have 29 different implementations of it (at least if I can count correctly). Let’s say I would like to copy or move the comments between any of these 29 implementations to any other of them. Now with 29 tables in play I would need to create a function from the first table and to all the other 28 tables. Than do it again for Table 2, 3, … & 29.

Yes that is a lot of functions and not exactly a small task.

So what is the difference if I had used a Surrogate Key and created only one generic implementation of Comments?

Well my copy function could look like this:

Because I am always using the same record to store my Subsidiary Table Data it is very easy to copy from one Master Record to another.

To make is even more generic and because I am lazy by nature I also added this function.

So now I can copy between 2 Records, 2 RecordRefs or a combination of a Record and a RecordRef without knowing anything about them.

This last function relies on a few other functions in the same codeunit.

These 2 functions in turn relies on my RecordRefLibrary Codeunit and the function

And my SurrogateKey Management Codeunit with this function.

Yes I use a fixed Field No for all my Surrogate Keys and no it is not 59999, but I had to create an example for this blog.

Before you ask why I use a fixed number I better address it.

There are 3 reasons for it:

  1. It is the easiest
  2. Once (Not when or if) Microsoft gets around to make their own version of a surrogate key in NAV it would make the most sense they did something similar
  3. It makes (at least that is my hope) for an easy and generic upgrade script once this finds its way to the standard platform

Let’s get back to the real issue here. Basically in a few lines of code we have resolved all our coding needs for copying comments between any master records and it doesn’t matter how many Master Tables comments are added to in the future. This will always work.

Creating repeatable implementations starts with creating repeatable code.



Find your Table ID

Using many hours programming with my favorite Design Pattern “Surrogate Keys” I often need to know simple things that can be a little hard to find at times.

One of these is to know the Table ID of any record I might be handling at any given time.

All the tables that use the surrogate key pattern use a primary key based on “Table ID”, a “Surrogate Key Reference” & maybe a “Line No.” or “Code” depending on what the table is hosting of data. In other words I often need to know the Table ID to filter correctly based on the record I am calling from.

I handle this with the following Code:

The ReturnTableID function takes a Variant as Parameter and calls Variant2RecRef to get it transformed to a Record Ref no matter if a Record or a Record Ref was passed to it. After that the function Returns the RecRef.Number which is the Table ID of whatever was passed to it.

This way I can call the first function with any Record or RecordRef and get the Table ID returned.

This creates a completely generic function that always returns your Table ID.

This is a fixed part of my RecordRef Library Codeunit and now hopefully of yours too.

Social Listening

While on MSDN I came across some new Help Content for Dynamics NAV 2015 talking about Social Listening.

Now while Social Listening might not be on everybody’s “must have list” it is still a great way to show the power of Dynamics NAV and the application stack from Microsoft.

I look very much forward to see what these new topics mean for the product.

Have a look here:


I was made aware by Vjeko that there is an “How Do I” YouTube video on the topic too…

Mixing concepts – code vs. translations

In my little miniseries here of things we can do better I would like to put focus on another conceptual issue that we really need to deal with too.

Code and Translations are 2 very different things and they should never be mixed. Okay I said it and maybe I will get a little verbal beating for doing so, however it doesn’t make it less true.

The way we deal with translations today by putting them into the compiled objects has its roots a very long time back in the application. Long before Dynamics NAV had a service tier. Long before we had even considered SQL as a database platform and maybe it is time to rethink that again. I am not saying that they did it wrong when they created translations the way the work today, but I am simply saying that the times has changed and so should the product.

Purely form a conceptual point of view Code and Translations should never be mixed. It is simply a bad practice.

Now we can technically already do this as we can host the translations on the service tier and not include them in the code, however this is not the standard way and unless you work with a country that needs this, we hardly ever see this in any install.

You can read all about how this is done on MSDN here:

Now at this point Microsoft doesn’t recommend this unless needed because it is slightly slower than using the translations when compiled into the objects. So you have been warned… J

In a side note when it comes to this discussion: “Why do we handle translations the way we do?”

Take a word like “Customer”. The word is translated many times because it exist in many locations across the code, but why do it that way? This is not a very efficient way of handling the translations and it would be far more efficient if we stored this translation once and pointed to it many times based on need. This would also greatly help when a given translation needs to be changed by a partner for a given vertical as I am sure many of you have tried.

I want to see our great product become even better than today. We need to constantly improve to stay in the lead. Removing these simple inefficiencies, so partners and developers can concentrate on building great products, will surely benefit us all.

Fixed text format

If you are working with NAV and Source Code Management you will know how easy it is to get a text file exported in the wrong format. The formatting of dates and numbers is done differently from country to country and even inside some countries causing issues with your source code management if everybody in your team don’t make sure to export with the agreed regional settings.

What makes it even worse is if this code finds its way into your source code as you will most likely not be able to do your build from it. Because the dates and numbers now are formatted wrongly your import of the text files fails or even worse imports them incorrectly.

In any case that is not what you would want to see happen and as an avid Source Code Management advocate it bugs me.

The solution would be that we had a fixed format in the export/import that would be independent of your regional settings. What if text export always, without exception, exported in one format and imported in the same format. Would that cause problems? I don’t think many would have issues with that.

In other words a good suggestion to improve Source code management adoption would be to create exactly this.

Just saying 🙂

Codeunit 1 Merge Conflict Issue

If you have ever merged Codeunit 1 you will have noticed that you always get a merge conflict in the functions ApplicationVersion and ApplicationBuild at least if you have added your own versioning which most vertical solutions would have done.

Standard the 2 functions look like this depending on what Cumulative Update you are on

And typically I would add my own function call (Hook) inside the EXIT to call and add my version or build number.

The result of this is of course that in the next merge I have changed this line and so has Microsoft giving me a conflict, which I will get every single time I try to implement a Cumulative Update. Very annoying to say the least and with no good work around.

So what would the solution be?

Well if Microsoft would add a standard hook here to an empty function returning nothing we as partners would be able to add our hooks in this function and avoid the conflict.

Now that resolves the major part of the problem as long as we as partners also remember to add our code nicely here, so we don’t start getting conflicts when using each other’s add-ons.