All roads lead to Sweden with Microsoft Dynamics 365 Business Central

I’m a poor judge of character. That is… according to my wife.

She is probably right. If you psyco analyse me, I am too eager to please people. Like a labrador.

Continue reading

Posted in Dynamics NAV | Leave a comment

ShowMyCode – Politics & Microsoft Dynamics 365 Business Central

Before I continue where I left of with the previous post (I’ve prepared all the parts) I like to say a few words.

Continue reading

Posted in BusinessCentral, Dynamics NAV | 5 Comments

Is Microsoft Dynamics 365 Business Central the Navision Road to the Future?

Yes! The short answer is yes and if you don’t care about details you can close the browser window or delete the email.

I’ll elaborate on the Yes, but there is also a “Yes, but not if…” that I will share with you.

Continue reading

Posted in BusinessCentral, Dynamics NAV | 1 Comment

Managing Extensions in Microsoft Business Central

Two days after the news about the new product name for NAV (Navision) has leaked I am already getting used to it and actually starting to like it. I just hope the poor Marketing boy or girl who was responsible for the name is not paid per changed word.

So let’s get back to business. Many companies are upgrading to NAV 2018 or have already completed this task. The reason for this is simple, NAV 2018 is a mandatory step if you want to move forward with your investment and if you want to grow into Microsoft Dynamics 365 Business Central.

Microsoft is currently working very hard to move their NAV C/AL code to AL and move us to Visual Studio Code as a development platform. Extensions are a great concept and in my opinion the only way forward even though roumors have reached me that in On-Prem versions of Business Central you can still make raw source code modifications. I would strongly advise against doing that.

Challenges with Extensions

I’ve created quite a few extensions. In fact, I would be surprised if you can find anyone in our community that has created more than I did which are actually running live either on prem or via App Source.

If you are lucky you can start from a blank slate and this post does not apply to you but most of us, almost everyone has legacy IP to move forward. If this is the case you want to keep your extensions more or less in sync with your C/AL objects.

Object Numbers

This is where object numbering becomes a challenge. If you look at the Microsoft C/AL objects you can see patterns in the object numbers, but you can also see anomalies. For example: Relationship Management starts at object ID 5050, Employee Management at 5200 etc.

The General Journal Template is table 81 and the Journal Line is table 82. The General Journal Batch is number 232. Table 83 is the Item Journal Line.

Reason is simple. Legacy. Journal Batches were introduced a number of releases after the initial Journal design is created. Later journals like Warehouse have adjacent numbering.

Why is this important with Extensions? Well. I can guarantee you that very soon you will loose overview of which object ID’s have been used.

Encapsulation & Modularity

Another challenge that you will have to solve is breaking up your existing code into smaller modules. You will want to build up your extensions in layers with dependencies.

This is rediculouly complex as we have never faced this challenge before. Let me give you an example.

You may think that the lowest level of modularity is in Setup tables and Master Data. This might be true, but what about the flowfields in your Master Data that point to Entry tables? Are you moving them away to Table Extensions higher up in your layers? Are your setup tables separated per module? Or did some developer who left your company years ago made a mistake by putting values in wrong tables?

Running Objects

Another challenge is to be able to quickly run a page or a codeunit. This is easy today but not so easy from VS Code. Microsoft is shoving the Web Client down our throat but in reality this client is far from ready for prime time use. 99.9% of all users work with the Windows Client.


It took me some time to figure out how to do versioning with my Extensions. Yes, we have native GIT integration but that does not solve the problem of understanding which version of code is running in which database in your DTAP environment. You will want to version your extensions each time you want to move something forward but most versions will never make production.

The Solution

I am very fortunate to call Michael Nielsen my colleague and friend. As most of you know he invented the C/AL programming language and came up with the idea of C/Side and the Object Designer.

Object Designer is basically an Excel Sheet that is helping you manage your solution. It works so well that I’ve seen partners manage over 10.000 objects. Try that in Visual Studio Code.

Michael decided to create an Object Designer for Extensions and I have the luxury of testing and helping to make some improvements.

You can try it out yourself and download it from the ForNAV website. It’s 100% free and customizable. You may find some neat tricks in there.

We decided to use Page ID 1000 which is free in NAV 2018 but also available to execute in End User licenses. This allows everyone to start using this today.

As soon as Microsoft supports DotNet Interop in Extensions we will ship a version as an Extension so you can manage it in a proper way.

View the AL Source Code

Microsoft decided with Extensions to allow partners to hide their source code from being debugged. This is extemely anoying as you constantly have to contact your partner to do troubleshooting.

Michael found a way to go around this and reveal the code and re-create the extension as a debug version. You still need a partner license to publish this to the server though.


Enjoy this little tool as I am sure you will find it useful.


Posted in Dynamics NAV | 3 Comments

Refactoring with ForNAV – Standard Reports with Clean Code

Having a big mouth is easy. Everyone can go on social media and tell whats wrong in the world. But did you actually try to change it? Or are you just talking about it?

Dynamics NAV is full of legacy code and even for senior developers who have been using it for a long time it’s hard to understand so every bit of cleanup and moving back to simple and easy to understand code is more than welcome.

One year ago Michael Nielsen asked me if I wanted to create a new set of reports that would replace the standard NAV reports for those in our community who use the ForNAV product. The reports had to be easy to understand and maintain, also for end users who don’t have a huge amount of experience with C/AL and our data model.

Oh, and by the way, “please start with the document reports” is what he asked.

Cool. Now what…

I’ve been working with Dynamics NAV since it was called Navision Financials. I started as an end-user with no experience in C/AL and no understanding of the data model. So all I have to do is move myself back two decades. What was so easy about Navision back then that made me able to make the move to software development.


This image shows the Sales Invoice back in those days. What made it simple?

  1. You were able to start designing the report from the User Interface
  2. You can add fields without first adding them to the dataset
  3. We had transheaders, transfooters, running totals etc. without code
  4. If you changed the sales invoice you could save the report as credit memo and proforma invoice


This image shows the Sales Invoice in NAV 2018 using Report Builder. I don’t think I have to explain why I think this is not a step forwards.

The Result

Let’s say the four things I mentioned earlier are the design goals. Let’s make it a little more challenging.

  1. It should be possible for users to make relatively challenging changes without needing a special license.
  2. Most (small) companies send reports as PDF. They should look stunning, better than the printed version.
  3. In the USA they have Sales Tax, not VAT. Those customers should also have an optimized experience and not be confused with wrong terminology.
  4. Avoid code cloning wherever possible.
  5. No changes to standard NAV code.


This image shows the result. I hope you agree that it looks clean and not terrifying to start with if you are not a developer with a university degree.

Let’ go through the design step-by-step to see if all the design goals are achieved.

1. Start From the User Interface

Ok, this is unfair, not my achievement, but you can start the designer from every ForNAV report directly from the Windows Client. I got this for free.

Start Designer

2. Add Fields without changing the Data Set

Another thing I got for free. In ForNAV you ONLY need to add the record to the dataset, not the individual fields. You can grab any field, any caption and even related tables WITHOUT doing anything in C/Side.

Let’s look at the Header.



Note that the addresses are one field. No need to do any coding, ForNAV automagically generates the address for you and uses the country code to decide how to format the postcode and country

Customer Caption & Payment Terms Caption

Do you also find these NAV terms annoying when you send documents to your customers? Do your customers understand what a “Bill-to Customer No.” means? Or “Payment Terms Code”? It would be so much nicer if this shows “Customer” and “Payment Terms”.


ForNAV allows you to print the caption of the NAV table and translates it if NAV has the translation. In NAV2018 that is 22 languages! For FREE!


This is a header in Dutch, and even the Payment Terms are translated if the translation table is used. All of this without writing a single line of code. ForNAV automatically detects the metadata and handles translation for you.

The detection is done using the Design Patterns in NAV. If your ISV solution applies these best practices you get this for free to!

3. Transheaders/Transfooters

If you ask anyone about their top three issues with RDLC, missing transheaders and transfooters is almost always mentioned. It makes you look like an idiot if you tell your customers that making that work requires you to count lines and even then it may go wrong. You spend hours coding.

Running Totals

With ForNAV running totals works like you expect but you get more! The “continued” caption is translated in all 22 NAV languages for free.

4. Change 1 report, change them all

So you finished creating the invoice layout your customer wanted. Everything prints on the correct position. Great! Congratulations. O yeah, by the way, this is also how the credit memo & order confirmation should look, and is it possible to create a pro-forma invoice in NAV?

We’ve all been there and in classic Navision there was this great trick. Export your report to a text file, use find and replace on the correct strings and voila, you had a working copy. Alternatively one could also use a compare tool because all the control id’s, function names and sections were called the same.

With RDLC this has become a challenge so to say and how nice would it be to have this back.

ForNAV allows two ways to make sure your reports are the same.

1. Master Sections

With Master Sections you can tell your report to use the layout for a specific section from another report. The controls that have identical names will be inherited and controls that are not present (the Sales Shipment does not have all the fields the Sales Invoice has) are removed.


2. File, Save As, Done

Ok, I’ll be honest, this was Michael’s idea, not mine. Look at the name of the DataItems in our ForNAV reports. The Header tables are called, eh, well, Header and the Line, this is getting boring, are called Line.


The benefit is that you can change the Data Source from one table to another and if they are “transferfields” siblings it just works. Even easier than in classic.

So Cool…

Right? This is where you should be enthousiastic enough to go to and download the designer and the report pack to have a look. Go ahead, we won’t monitor you and it’s free. All you need is a Microsoft Training License and you can try the reports in the training numbering range or as a partner just use 70.000 or something like that.

You can even see what I am currently working on as we allow you to download the beta version of unreleased work.


But there is more…

If you are not tired of reading and you want to know more about how we made the standard reports even better than Navision Classic, please continue

1. Make Changes as an End User

We want users to be able to change their own reports, even if they don’t have a development license.

To do that you can create your own report layout using the standard NAV options.

Custom Layout

Note that we don’t change the Microsoft standard code so you need to choose RDLC and NAV will tell you the report is open in Report Builder. This is not true (fortunately) since we are working with ForNAV.

Inside ForNAV you can do everything, you can even write code and add variables without any aditional license.


There are four triggers where you can add code, and you can also inherit code from a master report which reduces code cloning.


The coding experience is not fantastic but we are working on that in a next version.

Inside ForNAV we use a combination of AL and JavaScript. Javascript ECMAScript 5.x is supported. This also allows you to create classes.


2. Stunning PDF Reports

You want to be proud of your investments. If you spend a lof of money on Dynamics NAV you want to get that compliment of your customer that says WOW. Your order confirmation looks awesome.

We spent a lot of time debating about fonts, fontsize and where to put which data.

InvoiceWe use Segoe IU 8pt except for the legal conditions.

What makes the report stand out is the PDF watermark and the way we print the Company Information.

The Company Information is printed using JavaScript and it only shows the fields which the customer has used in their NAV system.

This makes the layout looks very clean.


With JavaScript you can do stuff in one statement that would require complex coding in AL.

3. What about the USA?

Dynamics NAV is born in Denmark and started with VAT in mind. Sales Tax was added later on and many of the Sales Tax features use the same fields and tables as VAT.

The first thing a NAV developer in the USA learns is “Yeah I know, it’s called VAT but it is showing Sales Tax”.

This may feel like not being a first class citizen.

We created a special report for Sales Tax that has specific Sales Tax captions and functionality. This makes it so much easier for users from the USA to understand their layout and avoids unnecessary questions.

4. Avoid Code Cloning

My favorite one. Try to avoid duplicate code.

Code duplication is a nightmare for maintaining code and NAV is full of it. The reason for that is very simple. The first versions of Navision had a very simple IDE. There was no “go-to definition” and all of the business logic was written in one function.

In the 1990ies Navision introduced Temporary Tables to solve that but it never really took of. Until today things like VAT calculation is copied into every table and every report that needs it.


This image illustrates code cloning. It’s taken from the Sales Invoice report in NAV 2018. The VAT buffer is populated and printed using an Integer dataitem.


Here you can see how I tried to improve the experience.

Instead of using the Integer table I directly use the VATAmountLine as a dataitem using the Temporary property that Microsoft introduced in NAV 2015.

NOTE: Because of this you cannot use our report pack in NAV 2013 or NAV 2013R2 unless you do a platform upgrade to at least NAV 2015. (I’m sorry for that)

The calculation is done in a codeunit. Let’s have a look at how this is done.

Variants, RecRef, Reflection and Classes

I don’t think you ever have to change this code, but just in case. Here is how I calculate the VAT.


Step 1 – Move the table to a RecRef variable.

This uses a Variant so you can call this codeunit with any table that has a Document/Line pattern with VAT fields

Step 2 – Make Sure you are not writing to the database

We don’t want printing the report to be slow, so I am checking if you call the API with a temporary table

Step 3 – Read the Lines

The API is called from the Header record and we use reflection to check if there are lines and if the lines table has the fields we need. The fields are used from an arguments table that I use as a class

Step 4 – Populate the VAT Amount Lines

I move the fields I found in the lines table via the arguments table to the VAT Amount Lines table.

The real black magic happens in the arguments table.

Update No. Printed and grab No. of Copies

Examples that are easier to understand are the codeunits that update the No. Printed fields and grab No. of Copies from the Customer table.


5. No Changes to Standard NAV Code

Last item on the list. We did not change any standard NAV code. You can import our objects and run them side by side with the standard NAV reports.

The first time you run a ForNAV report we will automatically launch the setup wizard


Or if you import our objects without using them we remind you next time you print a report.


The wizard will guide you through the process and at the end it will ask you to replace the reports in the Sales & Purchase report selection.




Posted in Dynamics NAV | 1 Comment

NAV2018 – Upgrade Issue with Sync-NAVTenant

Ok, so today I had another issue in my NAV 2018 upgrade that made my blood pressure go up. I kept getting a weird error in the Sync-NAVTenant PowerShell command. I was afraid that this would be a showstopper and I had to report the issue and wait for CU1 or CUx.

What did I do…

As in any upgrade I had opened the NAV2017 database in a NAV2018 C/Side, deleted everything but tables and imported my merged objects. Compile with Schema Sync Later and then you should be ready to apply the schema changes.

First of all, be aware that this takes a while. It moves a lot of data around in this step. My database is 150GB and it ran for about half an hour. We have relatively good HP Lefthand SAN boxes.

This is the error that I kept getting

Sync-NAVTenant : The following SQL error was unexpected:
Incorrect syntax near 'Unit'.
At line:1 char:1

And this is the SQL Statement that gave the error. I found it in the Windows Event Log


SELECT @StatisticsToDrop = COALESCE(@StatisticsToDrop +', ', '') + '[Clean Company$G_L Entry].'+

FROM sys.stats AS s

INNER JOIN sys.stats_columns AS sc ON (s.stats_id = sc.stats_id AND sc.object_id = s.object_id)

INNER JOIN sys.all_columns AS c ON (c.column_id = sc.column_id AND c.object_id = s.object_id)

WHERE s.object_id = OBJECT_ID('Clean Company$G_L Entry')

AND ( = 'Business Unit Code' OR = 'Gen_ Bus_ Posting Group' OR = 'Gen_ Prod_ Posting Group' OR = 'No_ Series' OR = 'Tax Group Code' OR = 'VAT Bus_ Posting Group' OR = 'VAT Prod_ Posting Group' )


SET @StatisticsToDrop = CONCAT('drop statistics ', @StatisticsToDrop)

EXECUTE sp_executesql @StatisticsToDrop


The Solution…

The message is indicating that it was trying to delete statistics which failed. So my idea was to do that before the upgrade myself, hoping that then NAV would skip it. And it did.

The script is one that I stole from my good friend Jorg Stryk a long time ago. You can find it here but you should make a small change.

This is the modified script. I hope this may help someone else running into this issue.

set statistics io off

set nocount off

declare @id int, @name varchar(128), @statement nvarchar(1000)

declare stat_cur cursor fast_forward for

select [id], [name] from sysindexes

where (indexproperty([id], [name], N’IsStatistics’) = 1)

and (isnull(objectproperty([id], N’IsUserTable’),0) = 1)

order by object_name([id])

open stat_cur

fetch next from stat_cur into @id, @name

while @@fetch_status = 0 begin

set @statement = ‘DROP STATISTICS [‘ + object_name(@id) + ‘].[‘ + @name + ‘]’

begin transaction

print @statement

exec sp_executesql @statement

commit transaction

fetch next from stat_cur into @id, @name


close stat_cur

deallocate stat_cur


Posted in Dynamics NAV | 1 Comment

Step 4 – Convert C/AL to AL with Visual Studio Code

The ForNAV standard reports are in public preview. We have just released CTP2 and now we want to start testing them with Visual Studio Code. In order to do that we have to convert them to AL objects.

Microsoft is kind enough to provide tooling which you can find on the June update for the developers preview.

The tool is a small .exe file you can find in the same folder as your finsql.exe and called txt2al.exe. It is documented on MSDN.

The tool takes a NAV txt object but it has to be exported using the MS-DOS command prompt using a special option.

Because it is a lot to write down I’ve actually recorded a 30 minutes video and posted it on YouTube. This video shows the commands, shows the differences in beyond compare and tells you how to make the tool run, test and put it on GitHub using a folder structure that makes sense. At least in my opinion.

Here is the video

And here is the text from my Convert.Bat file

finsql.exe Command=ExportToNewSyntax, File="D:\CAL\fornav.txt", Database="Demo Database NAV (10-0)", ServerName=.\NAVDEMO, filter="ID=70000..79999", Logfile=D:\Log_ExportFile.txt


txt2al --source=D:\CAL\ --target=D:\AL\ --rename --extensionStartID 70070000



Posted in Dynamics NAV | 1 Comment

Tip #58 | Run Extension Objects

One of the quirks of working with extensions is that you cannot run an object from the object designer. This is true for V1 and V2.

With V2 you can start an object (page) after deploy but this only works once and only in the WebClient.

If you just quickly want to check our a page or codeunit in the Windows client you can write a codeunit against an object that does not exist.

An example is the TowersOfHanoi app that Microsoft ships as example. This does not have a page extension to execute itself.

Works all the time.

Want to learn more about extensions? Contact me today!

Posted in Dynamics NAV, Events and Extensions, Tips and Tricks | 3 Comments

Step 3 – Wizards | ForNAV App Building

Wizard pages are working on a revival from being almost forgotten. They were first introduced as form objects in Navision 3.0 as part of CRM. I instantly fell in love and started to create them for my own add-on.

Wizard pages have a few great advantages to normal pages and let’s go over them.

  1. Save Button – Even though it is not called “Save” the Wizard page is one of the few options in NAV you have to populate data, validate it and cancel the operation without a hassle.
  2. Overview – Essentially Wizard pages have fast tabs just like Card pages but the tabs are only displayed one at a time giving a clearer overview
  3. Validation – Wizard pages allow the programmer to clearly validate the contents of each fast tab before continuing to the next fast tab. Much more precise than normal database validation since with a wizard you can asume, program, for a really specifical order of entry.
  4. Explanation – On each page (tab) of the wizard you can write half a bible explaining to the user what to do. Be careful not to over do it since people these days are not used to sit down and read text anymore.

Continue reading

Posted in Design Patterns, Dynamics NAV, ForNAV | 1 Comment

Step 2 – Notifications | ForNAV App Building

I’m building an App to work with ForNAV. If you haven’t you should read earlier posts first or watch the YouTube channel.

New Blog Series | Building the ForNAV App

Step 1 – Renumbering | ForNAV App Building


The next step I will blog about is how to make notifications work for you.

Continue reading

Posted in Dynamics NAV, ForNAV | Leave a comment