NAV 2016 – Page 2 – Gunnar's Blog

February 2, 2017

Updates to my Object Renumbering Tool

Back in the end of 2014 I published a renumbering tool for NAV objects. Using DotNet I was able to increase the renumbering speed for text object files dramatically.

Since then I have been asked if I could upgrade it to work with IDs and Field Numbers.

Now I have.

What’s more, it is also on GitHub.

The Process functions are the following;

Read Object Lines – Creates renumbering lines base on the objects in the selected object file.
Suggest IDs – Suggest new object numbers in the range from 50.000 based on the available objects in the current license.
Read from Excel – Reads object renumbering lines from Excel Sheet created with the Write to Excel process.
Write to Excel – Writes current renumbering lines to a new Excel Sheet to me managed within Excel and reread into the renumbering lines.
Renumber Using Lines – Prompts for a file to read and for a new file to save with renumbered objects based on the rules in the renumbering lines.
Renumber Using Controls – Prompts for a file to read and for a new file to save with renumbered objects based on the rules in the control IDs setup.

I have done some fixes to the renumbering function and have added support for the EventSubscriber.

Go to GitHub to download Page and Table 50000, try this out and submit improvements.

When I am processing an object file I have it open in my text editor. When I see something to renumber I update the control ranges and execute the renumbering process, reading and writing to the same object file. My editor will reload the file and I can see the results immediately.

January 24, 2017

NAV Http Web Request

In my post about Json and Rest web services I showed how to use the standard Codeunit no. 1297 for web service communication.

Today I was asked to do this in NAV 2015. I must admit, I forgot that this Codeunit was not available in NAV 2015.

So I made one.

This one has identical functionality to the one delivered with NAV 2016. To catch and handle the errors I use the NAV Web Request Add-in that I created and published here on my blog.

Now I can easily move that Json code down to NAV 2015.

Download here –> COD1297-NAV2015

December 1, 2016

Using REST/Json web services from NAV

One of my most popular blog entry is the one about Json. I have also had some questions outside this website about this topic.

This week I got a task. We need to communicate with a payment service that uses REST web services and Json file format.

I got a document describing the service. Some methods use GET and some use POST. Here is how I did this.

In the heart of it all I use Codeunit 1297, “Http Web Request Mgt.”.

Every time we talk to this POS API we send an Access Token. If we don’t have the token in memory (single instance Codeunit), we need to get a new one. That is what the above code does.

The ParameterMgt Codeunit is what I want to focus on. You can see that I start by inserting my “Authorization Key” into the RequestBodyBlob. As usual, I use the TempBlob.Blob to get and set my unstructured data.

The interesting part here is that I use an XMLPort to create the data I need to post to the Api.

A simple one in this example, but nothing says it can’t be complex. Then I convert the Xml to Json with a single function.

The last TRUE variable means the the Document Element will be skipped and the Json will look like it is supposed to.

The REST service response is Json.

And to read the Json response we take a look at the GetAccessToken function.

Here I start by converting from Json to Xml.

And make sure my Document Element name is “posApi”.

And I have the result.

As you can see from the documentation some of the Json data is more complex. This method will work nevertheless.

For more complex date I always create tables that matches the Json structure. These table I use temporary through the whole process so the don’t need to be licensed tables. Here is an example where this XMLPORT

will read this Json

I suggest that with our current NAV this is the easiest way to handle REST web services and Json.

November 28, 2016

NAVTechDays 2016

Now, ten days after a succesful NAV TechDays 2016, it is time to write a short recap.

As usual I met with a lot of old friends and managed to gain new ones. Had a great time. I hosted a workshops on extending the Data Exchange Framework on Thuesday and Wednesday. It looks like I am the only one in my MVP group to have dug deep into that functionality. Huge thanks to the people who attended. I always learn a lot from you guys.

On Thursday morning I had my first NAV TechDays session. I talked about migrating to events. I had a blast!

Luc has the video on Mibuso, so if you missed it or just want to relive the moment, go ahead and watch or download.

You can also watch the slides right here. I have notes on most of my slides to better explain what I was thinking.

On Friday morning Soren presented Source Control. There he talked about an initiative that Kamil and me started – to make Source Control available for every NAV developer. Soren jumped on board and we can expect more to join. On that note I have created my GitHub account where everyone can download the stuff that I publish.

I have published the presentations and objects to GitHub for everyone to play with. What you download and do with it is on your own responsibility.

p.s. the NAV TechDays photo album is right here to browse through.

April 20, 2016

Using the new FilterPage in NAV 2016

I was a little surprised to not find any information online on the new FilterPage type in Dynamics NAV 2016.

As a part of the new Workflow feature Microsoft built a new generic feature to ask the user for a filter on any record.

Pressing the Assist-Edit button will open the Dynamic Filter Page.

This view is the same view a NAV users is familiar with when starting reports and batches.

Now to show how to use this new feature. The best way to show is usually with an example.

Go to the Chart of Accounts. Then from the ribbon select G/L Balance by Dimension. Select a setup similar to the screenshot below and press Show Matrix on the ribbon.

Now you are in a page where you can’t filter anything. You will see all G/L Accounts within the G/L Account Filter selected earlier and all Accounting Periods in columns according to the Matix Options. Yes, you have all the normal filter options on the page but none of them work.

So lets see how to use the Dynamic FilterPage to give the user a better experience of this feature.

The first challenge; I want a single month comparison in the columns. Lets compare amounts for January by year.

To do this we need to make a few modifications to Page 408.

Add the global text variable PeriodTableView.

When the user changes what to show as columns we need to make sure that the PeriodTableView is empty.

When the column captions are generated the new PeriodTableView should be used.

Same changes needs to be applied to the NextRec function.

Two new functions needs to be added to ask the user for the filter.

And finally, get these functions available for the user.

The result is that the user can now press the Assist-Edit button and enter a filter for every column option.

To attain our goal, lets filter on the month we want to see.

And the result Matrix looks like this.

We could add a filter page to the Matrix Page to be able to filter on the G/L Accounts using the same methods and we could add a functionality to add filter on the lines similar to what we did for the columns, but I am not going though that now.

The modified Page 408 is attached. Good luck.

Page408

March 31, 2016

Presenting the Data Exchange Framework

Earlier this month Arend-Jan contacted me about being a presenter on Dutch Dynamics Community NAV Event for March 2016. Of course I was honored and after a moments thought I accepted.

The following presentation was repeated two times for close to 140 people in total. I had a great time and am thankful for the opportunity.

I promised to share one Codeunit – that general mapping Codeunit – and here it is: Codeunit 60000.

If you download the presentation you can also read my notes. They should help you better understand the whole story.

March 26, 2016

Data Exchange Framework enhancements

I have made several enhancements to the Data Exchange Framework in NAV 2016. Previously I wrote about text or a comma separated file import and about access to a local data file when creating a xml/json structure. Both those enhancements are included in the objects attached to this post.

I have needed to handle JSON files where the actual data is not in the node value but in the node name. To support this I added a boolean field to the Data Exchange Column Definition table (1223).

To support this I added the same field to the Data Exchange Field Mapping table (1225) as a flow field, calculating the value from the definition table.

Codeunit 1203 is used to import XML and JSON files into the Data Exchange Field table. I made two changes to the InsertColumn. First, I added a parameter that accepts the node name and if the above switch is set to true I insert the node name into the column value instead of the node value. The other change is that instead of finding only the first Data Exchange Column Definition I loop through all of them. This allows me to create more than one definition based of the same XML node and therefore import into multiple columns from the same node.

To enable this scenario in the Currency Exchange Update Service I made changes to the Data Exchange Field Mapping Buffer table (1265) and the Data Exchange Setup Subform page. More on that later.

A JSON file without a single root element will cause an error in the standard code. Inspired my Nikola Kukrika on NAVTechDays 2015 I made changes to Codeunit 1237 utilizing the TryFunction to solve this problem. To top this of I made a simple change to the Currency Exchange Rate Service Card page (1651) and table (1650) to allow JSON file type.

Having completed these changes I can now use a web service that delivers JSON in my Currency Exchange Rate Service.

Also inspired by Microsoft I added a transformation type to the Transformation Rule table (1237) to convert the Unix Timestamp (seconds since January 1st 1970 UTC) to a date.

All objects and deltas are attached

DEF-Enhancements

March 17, 2016

Asynchronous web services in NAV

Asynchronous methods are very useful for web services. To be able to start a process with one method and then check the status with another opens a lot of possibilities.

There are two known patterns in NAV that support asynchronous methods; one is to use the STARTSESSION function to execute the business logic in another thread, the other is to create a job queue entry and let NAS handle the task.

I was talking to a client this morning and suggested using the asynchronous web services for his tasks. I decided to write some code and test the functionality to see if my theory was working.

The first thing I need is a table for the requests. This table contains the identification for the queue, the status and other details.

A single web service method is used to create a new queue entry.

I like to use the new TryFunction for my web services. Note that when the queue has been inserted I fire an event with the newly created queue entry. This means that I can extend this web service with events without having to modify the web service signature.

Each process needs a dedicated Codeunit. That Codeunit is built on top of the queue record and thereby using the parameter table pattern.

Note that this Codeunit is also using the TryFunction and taking care of the rollback if that function fails.

The process Codeunit catches the integration event and checks the process code before starting then selected task.

So, if I ask the web service to start a job called StartAsyncAdjustCostItemEntriesProcess, NAV will start a new session to execute the adjust cost for item entries. The process that creates the queue and starts a new session is very quick so the response from the web service is almost instant.

Then we wait for a moment and ask the web service about the status for the newly created queue.

Again using the TryFunction to make sure that my web service will have a proper response to the query.

It is quite easy to extend this module by catching the OnNewQueueInserted event for each process code you build support for.

A sample C# code that I used to test this looks like this

As you can imagine we can put anything into the ProcessData variable. By using Base64 encoding anything can be converted to a text variable.

I hope this will turn on some lights and you will be able to use this in your daily work.

Attached is a zip file with the NAV objects and the C# project.

NAVAsyncWebService

March 15, 2016

NAVUG European Congress

This conference, being held 9-10 May 2016 in Stuttgart, Germany, is a user-driven event dedicated to helping Dynamics NAV users maximize the return on their Dynamics investment. The programme schedule currently include the following sessions and more:

Why the Cloud? Options and Considerations for Full or Hybrid Hosting
Approaches to Managing Your Historical Data
Best Practices for Dynamics NAV Administration and Security
Power BI in Action
Global vs. Local: Localizations and Cultural Aspects

Experienced Dynamics users and partners will lead instructional sessions, showcases, and How2’s, focusing on past and current versions of Dynamics NAV. As an attendee you will:

Broaden your Dynamics NAV product knowledge through hands-on training sessions, prepared by knowledgeable Dynamics NAV end users and partners.
Improve your Dynamics NAV competency to be more efficient in the workplace.
Learn more about the Dynamics NAV version you are currently using; as well as newer versions if you are looking to upgrade.
Network with other Dynamics NAV peers who use the product on a daily basis.

The conference will be held at the International Congress Centre in Stuttgart, Germany on 9-10 May 2016. To learn more about the conference or to register today, please visit www.navugcongress.com.

March 9, 2016

My SQL Server 2014 gets flooded and stops responding

All right, I must confess; my SQL Server is not installed on a super computer. Also, it is not installed and configured by a SQL Server MVP nor by Alain Krikilion (picture from NAV TechDays 2013).

The Hyper-V machine running my SQL Server is using two processors and 12GB of memory. Operating system is Windows Server 2012 R2 x64 and SQL Server version 2014.

I have two NAV 2016 CU5 instances on two servers running a multi tenant application. Every time I started the second instance everything froze. Even the SQL Server stopped responding. I am not going to pretend that I understand the issue, but still, I found a solution and wanted to share that with you.

We have a property for the NAV Service called “Max Concurrent Calls”. This is, by default, set to 40. I found out that if I lowered this value to 10 on the second instance I was able to start it. That is a workaround, not a solution. What if I want to start the third service instance, do I then need to lower that even more or update the configuration for all the previously running instances?

So I turned my attention to my SQL Server. On MSDN Microsoft states:

This topic describes how to set the user connections server configuration option in SQL Server 2014 by using SQL Server Management Studio or Transact-SQL. The user connections option specifies the maximum number of simultaneous user connections that are allowed on an instance of SQL Server. The actual number of user connections allowed also depends on the version of SQL Server that you are using, and also the limits of your application or applications and hardware. SQL Server allows a maximum of 32,767 user connections. Because user connections is a dynamic (self-configuring) option, SQL Server adjusts the maximum number of user connections automatically as needed, up to the maximum value allowable. For example, if only 10 users are logged in, 10 user connection objects are allocated. In most cases, you do not have to change the value for this option. The default is 0, which means that the maximum (32,767) user connections are allowed.

This page also shows an example on how to configure this setting. In that example the property is set to 325. So, I tried that, returning the “Max Concurrent Calls” back to the default value for all NAV Services. Happy times, everything is running smooth.

So how can two NAV Servers that have “Max Concurrent Calls” set to 40 flood a SQL server that supports 325 concurrent user connections? I even looked at the network connections by executing

netstat -nao | find “1433” > connections.txt

in command prompt and looking at the output. The total number of connections was less than 50! I think I must leave this to Microsoft or my friend Alain to explain this.

So it was clear; the Next, Next, Finish methodology failed me this time. I needed to get my hands dirty and fix the SQL Server configuration.

First, I looked at the “Max Degree of Parallelism” property. The default value is zero but is should be set to “No. of CPUs” – 1.

Then updated the connections property.

I restarted the SQL Server Service to apply changes.

I updated one more property and below is the SQL Query I used to update these properties.

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE ;
GO
EXEC sp_configure 'max degree of parallelism', 1 ;
EXEC sp_configure 'user connections', 512 ;
EXEC sp_configure 'max worker threads', 2048 ;
GO
RECONFIGURE;
GO

Why this turns up in NAV 2016 and not in previous NAV versions, what changed, I don’t know. Perhaps someone out there has the answer.

If you get stuck in a problem like this, see if you have NAS services running on both instances. That seemed to be my problem.