The Blog, The Books & The Workshops. In one place


This blog contains 10 years of information about Dynamics NAV . The articles are all categorised, which makes it easier for you to find the information you need.

Continue reading

Posted in Dynamics NAV, General | 4 Comments

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

DECLARE @StatisticsToDrop NVARCHAR(MAX);

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

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 (c.name = 'Business Unit Code' OR

c.name = 'Gen_ Bus_ Posting Group' OR

c.name = 'Gen_ Prod_ Posting Group' OR

c.name = 'No_ Series' OR

c.name = 'Tax Group Code' OR

c.name = 'VAT Bus_ Posting Group' OR

c.name = 'VAT Prod_ Posting Group' )

IF @@ROWCOUNT > 0 BEGIN

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

EXECUTE sp_executesql @StatisticsToDrop

END

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

end

close stat_cur

deallocate stat_cur

 

Advertisements
Posted in Dynamics NAV | Leave a comment

NAV2018 – Task vs. To-Do


This monday I was doing a merge of changes I did in NAV2017 to NAV2018. A lot of my Delta files applied without conflicts. It broke however on the Contact table. This was a bit to my surprise because this table is typically not changed by Microsoft. I remember back when I wrote my first book about NAV 2009 Application Design I had to call the module “Relationship Management”, not “CRM” because those letters were reserved for Dynamics CRM which is now called Sales because CRM is too nerdy or old fashioned.

In 2009 the thinking was also to just leave the module in for backwards compatability. Partners were strongly recommended to promote Dynamics CRM rather than implement “Relationship Mangement”.

To my surprise my merge was getting a conflict on a function called “CreateVendor2” which called into “CreateVendor” which in my database had a parameter.

When I did a where used on CreateVendor2 in Statical Prism it was not used anywhere. A boat anchor.

Vendor2

This brings back another story from the past. For quite a while Luc van Vugt has been collecting boat anchors in an effort to get Microsoft to clean up unused code. Why would they introduce a new one?

Microsoft Management did not like the fact that NAV boat anchors where blogged about and we actually had an active discussion on that during one of the NAV events in Europe. Personally I always had a strong opinion about sharing these strange phenomenons. What will happen is that people will find these functions and when less esperienced will start searching on google to find an explanation. Well, if you google on NAV2018 CreateVendor2 I bet you will end up here.

Wait, it get’s more strange. Apart from the fact that I am not a huge fan of adding an integer value to a function name to make it unique. I noticed that the variable for To-Do was renamed to Task. The table was still called To-Do.

Task

Further investigation told me that this was done everywhere

task2

And even the To-Do table now has the caption “Task” as well as the language

Task3

It’s quite a “task” to do all this, all for a name of a table in a piece of NAV that has not been touched by anyone in more than 10, maybe even 15 years.

You can understand that my imediate emotion was “WTF”. Why is this done. How can a function slip into the code which is not used and with such a weird name.

Anyway. It get’s a bit more strange.

In Dynamics NAV 2018 Microsoft also introduced a new table called User Task. This is part of the accounant portal where as a NAV user you can give your accountant access to your system. If your accountant runs Dynamics 365 they can open your system from their portal. Cool.

Task4

So what happens now if a user in NAV2018, and let’s use the webclient, searches for Task?

Task5

Right… Simplicity at its full glory.

Yeah yeah, I hear you say. Dude, report this to Microsoft. They will fix it. Well, I am not sure about this.

Do you remember this post about Navigate?

Navigate

This got changed in 2013R2 if I am not mistaken. In C/Side the section is still called “related information” which I think is still more clear. In Visual Studio Code the section is now renamed to Navigation.

When I sat down with someone from Microsoft and asked about this choice they shared with me that they investigated that people are not using the “old” navigate page and they were considering discontinuing that and on beforehand had decided to rename the tab.

I have a number of other examples where Microsoft is ambiguous about their vision with the product. Try searching in NAV on Notifications. You’ll get a handfull of options that all means something else.

Today I did a What’s new in 2018 webinar with Peik Bech-Andersen and we brainstormed a bit about this and came to the conclusion that maybe, hopefully Microsoft is trying to consolidate the tasks into one module or something like that.

What worries me most is the idea that stuff like this slips into an RTM release. Sure, a function which is not used is not going to break the test framework. Neither does a rename of a table.

A while ago Waldo already blogged that NAV has dozens, maybe even hundreds of unused functions.

Moving forward I hope that my moving everything to extensions I can leave this frustration behind. On top of that, the API will allow us to move some solutions away from Dynamics NAV into it’s own platform. These were options that we did not have in the past.

If you find other examples in NAV2018 like this I would like to be informed. I do think it is important to share these so we can all learn from it. Making mistakes makes us human and sharing sourcecode and knowledge even more.

Posted in Dynamics NAV | Leave a comment

How Do I – Move Away from NAV


NAV2018 is the best version of Navision ever! I love extensions, I love the API, the simplification with setup, integration with Flow, PowerBI. I could not be more happy.

But…

For almost as long as I remember I’ve been using NAV as a development platform. In some of my customers systems you could delete all of Microsoft objects and chances are stuff still runs.

With those systems I am running into a brick wall. Microsoft is moving NAV into a traditional ERP space where repeatability is more important than the C/Side development platform.

I am not the only one. Large NAV partners like Infoma, LS Retail and Incadea have done the same as me and probably dozens of other smaller partners that I’ve never heard of.

When I worked for SQL Perform I litterally saw several NAV databases with only codeunit 1 and a handfull of system functions. Everything else was custom.

Why…

For those of you reading my blog under 25. In 199x there was no system like Navision when it came to transaction integrity, simplicity. Back then Object Oriented Programming was a synonym for troubles and design patterns have just been scriblled down for the first time in human history. Navision solved all our problems

But now this is no longer true. Platforms like ASP.Net core and Angular are making life easier just like Navision did for us back then.

My customers are asking me to implement AI and ML. In C/AL I have to write a C# wrapper for that and in AL I have to use Azure Functions.

I have to communicate with the world out there. I can leverage the new XML and JSon AL Types but then I have to fall back to temporary tables which come as close to OOP in NAV as possible.

Meanwhile Microsoft is pushing me to use RDLC, Docker, PowerShell and other technologies which are super cool but not making life easier.

So…

I’m figuring it may be time for a change. I am only 41 years old. Not too old to be given up. I am moving my old solution from NAV into something else and I want to share this journey with you.

The options are few. The way NAV guarantees transaction integrity, flowfields and the vast amount of localizations. I don’t want to completely say goodbye.

My first step is to move the UI. My customers have been asking for proper drag-and-drop for as long as I remember. I am currently using “matrix” pages with runtime generated bitmaps for planning views. Man, I feel old and looking at what companies like Netronic do with TypeScript UI and Bootstrap I feel I am withholding my users of good stuff.

The next steps are unclear. I am keeping an eye on CDS. This may be a platform I can move to but I am open to other suggestions. It may be a valid option to create our own server application that handles the transactions. Everybody else seems to be able to do it.

Follow my journey…

You don’t have to, but if you want I am willing to share what I am learning on the way. Maybe it is a dead end road and I will stick to NAV in the end. Maybe Microsoft will pull out a big hat and by magic the Web Client works in April when we get R2. Maybe AL will be object oriented and we can generate object models based on OData definitions.

Personally I don’t think this will happen. Microsoft will continue their journey towards simplifying NAV and make it more mainstream while allowing partners to make small, nice changes using extensions. Using C/Side as a development platform will never be able to compete with ASP.Net Core. Why on earth would Microsoft want that.

Darwin Award (Website) - TV Tropes

Posted in Dynamics NAV | 5 Comments

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.

https://blogs.msdn.microsoft.com/nav/2017/06/20/nav-development-preview-june-update/

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.

https://msdn.microsoft.com/en-us/dynamics-nav/newdev-txt2al-tool

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

pause

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

pause

 

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 | Leave a comment

Testability Framework in Dynamics NAV


This afternoon (May 2nd 2017) we are hosting a webinar with NAV-Skills about the Testability Framework. Luc van Vugt will be the presenter and I will do an attempt to moderate.

The testability framework has been with us for a long time and was introduced in one of the last versions of the classic client. Even though it has been with us that long it never got widely adopted either internally by Microsoft and the partner channel.

Until now.

Continue reading

Posted in Dynamics NAV | 8 Comments

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

Notifications

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

Step 1 – Renumbering | ForNAV App Building


Ok, so as I wrote earlier this week I have been asked to build an App for ForNAV to provide with a set of “superior” reports that work optimized with the designer.

To do that I first build a prototype based on an initial interview and the prototype was approved. You can find the prototype on GitHub and run the reports in a NAV2017 database using the free download on www.ForNAV.com.

Continue reading

Posted in Dynamics NAV, ForNAV | Tagged | Leave a comment

New Blog Series | Building the ForNAV App


There is so much to blog about when you are a Dynamics NAV developer, it’s almost unreal and I can imagine that a lot of bloggers don’t know where to start.

I am in that situation and very fortunate to have found something to hold on to.

Continue reading

Posted in Dynamics NAV | Leave a comment