On Harleys, mad gods and Dynamics NAV

I could have named this blog post Living with extensions in a hybrid Dynamics NAV database. The title I went with is far cooler though. And since I write this stuff mostly for my own enjoyment you get stuck with the cool title.

You see, a little over a year ago I traded in my trusty old bike for a Harley Davidson. It is an awesome bike. Riding it feels like having the mad god of Milwaukee steel shred the fabric of reality and smash you face first through the black hole thus created. I love it and until I can buy the flying steam train out of Back to the Future it is my preferred mode of transport.

It did create a bit of a problem though. I had wrenched on my old bike for years. I knew it inside out, and my thirty year old metric spanners worked perfectly well on it.

Harley Davidson though does not do metric though. Metric tools lack something undefined. They are perhaps too refined for something as cool as a Harley. So I had to get a whole new set of inch sized tools. And that is not the only difference. There is a whole different planet of aftermarket parts for Harleys. I had to find out where to get the stuff I needed to do a simple oil change. It was infuriating.

But I guess that is change. More than a year later I still can’t guess if I need a 1/2 or 9/16th inch wrench but mostly I am happy on planet Harley.

And that brings us back to Dynamics NAV. Or Business Central or whatever you want to call it. Our environment has changed. We have had a major change in the way we work and we are still trying to make sense of it. Sometimes it feels as if the not so mad god of Danish sensibility has left us to an uncertain world of extensions, API’s and all sorts of other things that we can’t make head or tail of.

Where I work (a Dynamics NAV end user) we decided to upgrade to Dynamics NAV 2018 and start working with extensions. And, as I learned to live with Imperial wrenches I am also learning to live with AL and Visual Studio Code. Mostly I like it. Not as much as C# but definitely more than C/AL. The biggest problems we experience are due to the fact that we are in an in between period. We are forever generating symbols because we still need to develop in C/Side. We are creating events in custom code in standard objects because we need to move on and develop new things. I still have doubts about how easy the upgrades will be when Microsoft decides to change the events we use. But upgrading will be easier, no doubt about that.

So basically what I want to say with this blog is this. Cheer up. Change is hard. It is inevitable but it sucks. It creates great opportunities and even bigger pains in the backside. But from where I stand we are going to a good place.

With extensions you can do everything you should do. The rest you should leave alone even in C/Side.

How does that good place look? Dynamics NAV (or Business Central if you must) systems with as little modification as we can get away with. Just customized enough to give our customers or employers a competitive edge. Connected to great server based web applications through API’s. Using machine learning to help our users make sense of the masses of data we create. And think of Azure functions. Great ways of decentralizing business logic and ensuring similar execution across several platforms.

So the future looks good. As long as we develop ourselves to be something more than Navision developers. Change is hard. But, as 15 months of riding a Harley Davidson taught me, change is also good.

And so I hoist my glass to the mad god of change. May we live in interesting times.

Before you move on there is one thing I want to point out. I am independent. I have zero desire to ever become a MVP. It is a great award and I am grateful to all MVP’s for sharing their knowledge but it is not for me. There is no advertising here because I value my independence much more than the €4 per month WordPress charges me for removing the ads. The only reason I write this is because I enjoy writing it. If anyone disagrees with my opinions then that is fine. This is my view.

Absorb what is useful. Discard what is not. Add what is uniquely your own. Bruce Lee


Options and the agony of choice

Choice is agony. How do you discard a whole world of possibility and settle for a single option?

The other hard thing about options is how to program the buggers in AL… After finding precisely zero results on Google (I’m sure there is something to be found, I just lack the patience to find it) I decided to share my findings here.

To add a field of type option

Options field

And to use an option as a variable:

Options var

So there you go, the agony of choice now lies with your users. Where it should be.


The cat, the blowfly, and the Japanese archer

The other day I was witness to something awesome. A blowfly had just entered my room and I was feeling mildly annoyed. From out of nowhere my cat came, jumped and snatched the fly from the air. My sense of awe at this feat of poetry in motion was only slightly marred by the crunching sound of said blowfly in the jaws of my cute and adorable apex predator.

It brought to mind a lecture by Alan Watts that I had been listening to a while earlier. In it he mentioned that the objective of the Zen archer is to release the arrow without thinking. The big hangup here of course is how do you purposely do something not on purpose. How do you think about not thinking?

It is like being told to be spontaneous, the ghastly results of which can be viewed on national television with jarring regularity. Anyway, for a blog supposedly about programming and other worldly ventures things have started getting far too woo-woo.

So lets bring this back to programming. Serious stuff where thinking is the norm. When we are stuck we programmers think our way out of it. Or we use Google but that is just asking their AI to think about our problem. Voracious application of our intellect will solve all our problems for us. And when we get really stuck we use the scientific method to think ourselves out of any hole.

So when stuck on a particularly difficult piece of code this is what I try. I think, I Google and I try to concentrate. And the result is a headache, time wasted and no result. I want to think up something new, I need an idea!

So let’s explore this. Alan Watts tells us that thinking hard is like straining to see an object in the distance. You frantically pull at muscles around the eyes, squinting like a madman. And it has no effect whatsoever, the eyes work best when you don’t try. Just look, your eyes will work fine. So how does it work? How do we get ideas, solve problems?

“The ultimate test is always your serenity. If you don’t have this when you start and maintain it while you’re working you’re likely to build your personal problems right into the machine itself.” Robert Pirsig.

Whether it is programming, fixing a motorcycle, or releasing an arrow, the secret is not thinking about it. And you can’t do that just like you can’t lift yourself up by your bootstraps. And if you can’t do it you might as well stop trying. And then magic happens. You give up and all of a sudden, at the weirdest time, lightning strikes. You jump from your bath running naked through the streets shouting eureka.

So cheer up. Have a coffee, have a bath. There are methods for getting out of your own way and doing more creative work. But they only work if you don’t try them.

Meanwhile my cat is lying in the sun looking smug. When she lies she lies. When she walks she walks. And when there is a blowfly she crunches.

Thanks for reading this far. If you liked this you might want to explore these sources. They helped me and were the inspiration for this blog post.

For more on Alan Watts you might try listening to this
Try reading Zen and the Art of Motorcycle Maintenance by Robert Pirsig

For some tips on getting out of your own way try listening to Josh Waitskin on the Tim Ferriss podcast
And, of course, Eureka!
Photo by Zoltan Tasi on Unsplash

Upgrade from Dynamics NAV 2016 to 2018

Last weekend it was time for me to brew up a strong coffee, clear my schedule and upgrade our Dynamics NAV database from 2016 to 2018. This was a big operation for me and many colleagues as we also upgraded to SQL2017 and Windows server 2016.

The good thing about using a new server environment was that I got to do a trial run with the production conversion. Invaluable it turned out as we found some issues that we had not had converting our Acceptance database. The mail culprit here was an old SQL index that played silly buggers on our item table. I had run into a similar issue before on another database. Removing the index fixed it.

For the Dynamics NAV upgrade I used the steps Microsoft advised in their article https://docs.microsoft.com/en-us/dynamics-nav/upgrading-the-data. I found some issues though so I amended this list for my personal use. With success, I upgraded our ~800Gb database in about 4 hours with zero issues.

For the Powershell lovers among you, sorry, no fancy scripts. I work for an end-user and I don’t mind executing the steps by hand as we only upgrade once a year.

For your information, here is the full list with citations for the sources of my fixes. As always, if you have questions or improvements let me know. Enjoy and happy migrating!

Before the upgrade:

  1. Code merge. Use the scripts Waldo made
  2. Install Dynamics NAV 2018
  3. Create upgrade FOB. Don’t forget upgrade objects


  1. Backup database
    1. Auto create statistics off in SQL Database.
    2. Database recovery on simple
    3. Shrink database and files
  2. Uninstall OMA http://www.msdynamics.de/download/file.php?id=5665&mode=view http://www.msdynamics.de/viewtopic.php?f=68&t=31064
  3. Open the database with the 2016 object designer and run a 2016 service tier for the database.
  4. Unlock all objects
  5. Build all application objects https://docs.microsoft.com/en-us/dynamics-nav/how-to–build-server-application-objects
  6. Sync database schema https://docs.microsoft.com/en-us/dynamics-nav/how-to–synchronize-the-tenant-database-with-the-application-database
  7. 2018 Dev license in the database
  8. Stop all service tiers
  9. Delete all objects except tables
  10. In SQL:
    1. Clear Statistics, https://markbrummel.blog/2017/12/14/nav2018-upgrade-issue-with-sync-navtenant/
    2. Clear SQL tables before upgrade, delete entries in tables [Debugger Breakpoint] and [Server Instance]. Delete all locks as somehow deleting them in the object designer is not enough. See the sql script below.
    3. Check for open sessions with sp_who. Kill them if necessary.
  11. Open NAV2018 Object designer as Administrator https://forum.mibuso.com/discussion/69166/sync-error-invalid-column-name-base-hash
  12. Open the database in NAV 2018 object designer. Convert the database when prompted.
  13. Load FOB
  14. Compile all objects with met Schema Sync on later, when using rtm then clear C:\Users\<USER>\AppData\Local\Temp\Microsoft Dynamics op DY-RAS2 after compile. This is a bug in compiling reports that is fixed in CU1.
  15. Check version of new objects and make sure the upgrade objects are in the database.
  16. Connect a NAV2018 service tier
  17. Open Administration Shell as administrator on the service tier
  18. Schema sync “Sync-NAVTenant –ServerInstance <ServerInstanceName>”
  19. Data upgrade “Start-NavDataUpgrade <ServerInstanceName> -Force”
    1. Check progress with Get-NavDataUpgrade -Progress
  20. Delete the objects that need to be deleted. There are a few objects in NAV2016 that are not used in NAV2018. For us these were (might not be the same for you, due diligence advised!):
    1. Table 1305
    2. Table 1510
    3. Table 470
    4. Table 5150
    5. Table 5181
    6. Table 5371
    7. Table 5372
    8. Table 824
    9. Table 825
    10. Table 826
    11. Table 827
    12. Table 828
    13. Table 829
    14. Table 830
    15. Table 8640
    16. Table 9192
  21. Delete upgrade objects
  22. Register Clientcontrol add ins
  23. Install Extensions
  24. Update Dynamics NAV web client config
  25. SQL
    1. Turn on auto create statistics
    2. Datarecovery on full (or your preferred setting)
    3. Backup
  26. Load customer licence
  27. Do a happy dance

And here is the sql script I used to clear the necessary tables:

DELETE FROM [database].[dbo].[Server Instance]
DELETE FROM [database].[dbo].[Debugger Breakpoint]

use [database]
UPDATE [Object] SET [Locked] = 0, [Locked By] = '';

Dutch Dynamics Community presentation

It seems fitting that my first blogpost is about my first Extension.

I gave a talk about the Data Exchange Framework in Dynamics NAV on the 10th of january 2018. The focus of this talk was a business case from my employer, The Learning Network.

I would like to share my slides, the setup and the extension I developed during this presentation. The slides are in Dutch. The important bits are the images. Should you want a translation please ask.

The basic premise of my extension is that whenever a record is inserted into one of the transitory tables of the Data Exchange Frameork something can be done with the events that the insert generates. In my case I use a simple table extension on the OnAfterInsert trigger of table 1214, the Intermediate Data Import, and use that event to add records for data that the Data Exchange Framework needs to create purchase documents.

The presentation: Presentatie

The extension is on GitHub

The setup for the Data Exchange Framework is here: Setup and sample xml

More about the Dutch Dynamics Community