All around NAV dev and test
Chew, it has been quite some time we met here. Feels like learning to blog again. If you have been waiting for me all that time I make a deep bow and apologize for that. It has been a very busy 8 month period starting last December bringing our NAV 2009 R2 classic environment eventually to NAV 2016 last July. And after that ... a well deserved vacation in our little house down south in France. I guess it never felt more like that: time to take a leave and enjoy live in a total different way. And we sure did.
And now back on the job again. But with one major change: we're on 2016 now. Finally. FINALLY! Over three years ago I joined Van Dijk Educatie to get their Dynamics NAV installation to the latest version, NAV 2013 R2 at that time, but due to higher operational priorities, and to my dismay, it was postponed a couple of times. But we made it. Just before our high season started, end of July, and to the satisfaction of all our colleagues. Go Live on Monday July 4th went smoothly. We had been on it for all those 8 months, getting from NAV 2009 R2 classic to NAV 2009 R2 RTC in February and then straight to NAV 2016 in July. I must say it was quite an effort from dev and test with great involvement from all. And yes, we have a nice number of lessons learned to share with you. Hope I will find time enough to get that done, here, so stay tuned.
Well, let's take the bull by the horns, as we say over here; let's start immediately. With an issue that had been bugging our operation ever since I went out to enjoy may vacation. Pure coincidence of course.
I was informed that our colleagues were frequently experiencing deadlocks on the Warehouse Request table (5765), which sounded unfamiliar to me. Looking in the history of our 2009 installation TAB5765 was hardly ever an issue, but I noticed we had some customization on this object from way back in 2012, fixing ... deadlock issues!
Apparently TAB5765 standard primary key (PK) was sub optimal:
Type,Location Code,Source Type,Source Subtype,Source No.
The selectivity of the first PK field Type, with only 2 values, was clearly to low, where as the selectivity of the last PK field, Source No., was very high. For this reason, by means of the SQLIndex property of the PK, the SQL index was changed to:
Source No.,Location Code,Source Type,Source Subtype,Type
It indeed did solve the performance issue at that time.
The PK wasn't changed by MS and we had kept the SQLIndex property customization on the PK. But clearly, monitoring the deadlocks it showed that it was using the PK.
However, looking at the SQL implementation of the PK, I could not believe my eyes:
Do you see what I mean? It's the standard PK structure:
Not the one defined by the SQLIndex property:
This is somewhat scary. Is SQLIndex not working anymore?
Technically it could have been a candidate for the Clean Team as there is no need for this property anymore since we're on SQL only since NAV 2013. But ... migrating old code that uses SQLIndex to 2013 or beyond should however implement the key as defined by SQLIndex.
Some more detailed investigation learned me the following:
I have tested this for NAV 2013 R2 and NAV 2016, but probably also applies to NAV 2013 and 2016.
Why C/SIDE has changed this way I have no clue, but it scares me somewhat.
Mr. MS could you tell us why? If not please revert to the old behavior.
Indeed as David points out the non-optimal PK should have been fixed by MS and even more, the app code also, as there are a number of GET calls that have to be restructured to reflect the updated PK structure. Here is the list of objects that are affected:
Not long after this post I reported the issue to Microsoft and they acknowledged the bug on the SQLIndex property and informed me that it will be fixed in the next Cumulative Update.
Regarding the non-optimal PK: this is still under investigation and apparently has lower priority as this has potential more impact on all installations out there.
Apparently we keep on running into this issue once in a while. While developing you run your objects from the Objects Designer and with each single execution a new Windows Client session is being fired. Using your valuable time and frustrating your debug session as the previous one is not valid anymore and you have to start a new one. If I recall this right it first happened when NAV 2013RTM and R2 were installed side-by-side and we got a fix for that from MS.
In the meanwhile I didn't get bugged by this until a couple of weeks ago after I created a new Azure machine for NAV 2016 which contained CU 3. As I was busy preparing various courses and needed my time for that I didn't allow myself to fix the issue, somewhat annoying my students, with every other object I launched from the development environment. Triggered by this old post showing up in my dynamicsuser RSS-feed subscription (another annoying phenomenon), and having some time today, I decided to get this thing solved for my NAV 2016 CU 3 installation.
The old post was too old for my issue, but from this I got to others. Through NAV RTC is opening for each run of object I eventually bumped on this one that helped me out fast and easy. Thanx Chris et al.
To prevent myself from having to search again next time this small blog post.
Since NAV 2013 we are more and more getting the full potential out of SQL Server.
In NAV 2009 (and before) it was, for example, unimaginable to setup "your own" sorting for your data retrieval as SETCURRENTKEY only allowed you to use the keys defined on the table you were going to query. Or not possible for users to sort their lists by any column, like they had been used to in, for example, Excel long time.
So now we get, more and more, the power of SQL directly accessible.
Now with NAV 2016 another step is made with SETASCENDING even though it only seems to be applicable on data processed by code (didn't get it working on data displayed in a page, temporary or not).
The next code example shows what that we can sort on individual columns (in this case Currency Code and Name).
OnRun() ShowSetAscending(TRUE); ShowSetAscending(FALSE)
LOCAL ShowSetAscending(IsAscending : Boolean) MESSAGE('Ascending = %1',FORMAT(IsAscending)); WITH Customer DO BEGIN SETCURRENTKEY("Currency Code",Name,Contact); SETFILTER("Currency Code",'SEK|USD|ZAR'); SETASCENDING("Currency Code",IsAscending); SETASCENDING(Name,NOT IsAscending); IF FINDSET THEN REPEAT MESSAGE( 'Customer %1\ %2\ %3', "No.", Name, "Currency Code"); UNTIL NEXT = 0; END
The field SETASCENDING is applied to, should be part of the sorting as defined by the current active SETCURRENTKEY. If not a runtime error will be thrown:
Cannot call SetAscending on field <field name> because it is not part of the current sorting.
Coming from a C/C++ world into NAV, ages ago, it was somewhat dismaying to feel the various constraints of the system. And true, to also feel the power of it, not in the least because of these constraints, IMHO. But hell, isn't this always the case when changing? I mean, when changing, whatever. Habits. Tools. On-premise to cloud. Your partner. Or we could be still in a honeymoon state of mind and find out the constraints later.
Well, long story short. One thing I was very surprised by was the BREAK statement in NAV. Probably I am wrong, but my head kept on saying: any programming language has a BREAK statement allowing to break out of the current loop (or switch) construction and continue just after it. And yes, NAV did have (and still has) a BREAK statement, but only within the Dataport, Report and XMLport objects.
And now, finally, after all these years [emotional sob], with NAV 2016, BREAK has become what-I-always-wanted-it-to-be, allowing me "... to break out of the current loop (or switch) construction and continue just after it."
Yesss!
And contrary to what the help topic title (still) seems to suggest BREAK Function (Report, XMLport), it is valid in any object now. Just try for example the following in a codeunit:
Window.OPEN('i = #1###'); FOR i :=1 TO 10 DO BEGIN Window.UPDATE(1,i); SLEEP(500); IF i = 6 THEN BREAK; END; Window.CLOSE
It works just fine.
Reading the help topic in detail don't let yourself be mislead:
no BREXIT, i.e. BREAK <> EXIT
Because contrary to what the remark in the topic says (notice my red coloring):
BREAK causes the current trigger to end. When used inside a loop, such as a WHILE-DO or REPEAT-UNTIL construction, BREAK interrupts the loop and causes the current trigger to end.
Nope, BREAK will just interrupt the loop and "continue just after it". To cause the current trigger to end we already have EXIT don't we?
When you program a BREAK outside a loop the compiler will throw an error:
BREAK statement can only appear inside a loop (FOREACH, FOR, WHILE, REPEAT).
One of the major additions to NAV 2016 is Workflow. Both technical and functional more than interesting to take a deep-dive in as me and my friend Arend-Jan Kauffmann did to prepare a What's New in 2016 workshop. It makes use of the new platform feature Eventing (technical) and has been applied to replace the Document Approval feature (functional).
If you haven't been on this subject yet, come and visit or book our workshops or inform yourself using the various resources that have been popping up since October, like for example:
Or the various documents on the Microsoft Dynamics Learning Portal.
But as with many new things not all parts of the story are being told. The various resources will help you to create new workflows (functionally) and how to add new workflow events and responses (technically), but do not tell, for example, anything about creating new workflow templates. Clearly, with CRONUS we get a number of readily setup templates, but how do you create a new template? How to accomplish this (1) manually or (2) by code as part of your add-on?
Basically there is not much of a difference between a workflow template or a regular workflow as both types are stored in the same table (1501). The sole discriminating factor is the Template field. Turning it on changes a regular workflow into a workflow template, and vice versa. But ... there is no way of getting this done through the user interface. No page to create, open and modified a workflow template. No page that allows to check mark the Template field. Check out the Workflow Templates page.
You can view a template or create a new workflow based on an existing template. But ... no way to create a new template.
So we have a new workflow on the verge of being transformed to a template which we do by running the workflow table (1501) from the Development Environment.
Tip: make sure that the Description field also has a meaningful value as this is used to list a workflow template.
Workflow templates are created through codeunit 2 (Company-Initialize) calling the function InitWorkflow in codeunit 1502 (Workflow Setup):
InitWorkflow in turn calls the local function InsertWorkflowTemplates, where I would typically hook in for my own workflow template:
Enough example functions available to write my own InsertMyWorkflowTemplate function.
Do I need to write my code here? What about that other nice new feature Eventing, I mentioned above? Is there now Integration Event available in the InsertMyWorkflowTemplate function that I can subscribe to?
I mean, it has been provided in codeunit 1520 (Workflow Event Handling) and 1521 (Workflow Response Handling) to allow me, without any footprint in these 2 codeunits, to write my own codeunits to create new workflow events and responses. Hasn't this been done for workflow templates? Nope, I am sorry.
So I will need to put code in codeunit 1502 to get this done. Yep, no way around it for now.
But ... I have asked MS to create an integration event OnInsertWorkflowTemplates in codeunit 1502 for this very reason. Wanna be help on this? Go there and give your vote. Thanx!