NAV and Json

Json is a common data format, but in NAV it’s support isn’t huge. Especially when it comes to parsing Json. Of course one can use Newtonsoft dll, wrap it in c/side code and parse the contents. Still a bit too complex and not very straightforward.

But what if we just deserialize or serialize Json to XML and XML to Json? XML support in NAV is good, and anyone know how to handle it using xmlports or .Net XmlDomDocument. Second is preferable as xpath is way more flexible than xmlports.

Honestly, the code below isn’t mine, I’ve googled it an all credits goes to  .

This is very useful bit of code indeed.

The only issue I have with this – is the fact that this one will not work in extensions until MS add .Net support to extensions if this will ever happens. We still need an external dll too.

Just wondering if anyone built a json parser in pure C/AL? Or maybe MS will just include it into future NAV?

RDLC reports – nightmare and solution to setdata/getdata

Well, I thought RDLC reports will go at some point, but even for NAV 2018 they are still here. Apparently they may survive longer than I will.

There is one particular thing about RDLC reports I always hated. It is the method how do you send data to header. This horrible getdata/setdata functions. The main problem with them is that data can only be accessed by index number. Like that

I always hated this meaningless getdata indexes. To get what do they mean – it is a nightmare, especially if one is not report author. Even on my own reports after day or two I got no idea what this index mean and to what dataset filed it actually refer. Let alone the fact that they all displayed as ‘ “Exp” ‘ in designer.

I can’t help with second, it is a question to VS team, but I think I can help with first.

Imagine one can refer to a dataset item from header by dataset item name, like that

That would save hours and hours both on report design and on any rework.

Actually, this is fairy simple to achieve. I am sort of surprised MS haven’t done this since 2009, nearly 10 years! Considering MS probably started development of RDLC reports for NAV prior to 2009, 10 years mark is well passed.

So let’s list the contras of standard getdata/setdata:

  1. On setdata expression the order of fields does matter. Add something to beginning of expression or in the middle – and that’s it – everything screwed.
  2. On getdata the index reference is 100% meaningless, one have to refer back to setdata to figure out to what filed the index refer every time.

Then let’s see the pros of new approach:

  1. on setdata function the order of fields does not matter anymore. Need to add a field – add it on first line, with zero scrolling.
  2. On getdata – forget the horrible indexes, just refer your field by a dataset item name as string.

Sounds good?

So then let’s lee how this can be achieved.

RDLC reports code is just VB script, nothing more. Despite I am not expert of VB by any means I managed to create a new getdata function, see below:

I know this code is ugly and untidy, I already see how this can be improved, but I can’t be bothered. It does the job after all.

So this is our new getdata, not much more complex than MS one, isn’t it?

What about setdata then?

And expression itself…

I think expression above don’t need to be explained per se, if one ever done a RDLC report… Obviously everything need to be converted to string on this setdata expression, if you deal with a bitmap (or other binary, but what binary apart of bitmap we may have on report?) – use Convert, if anything else – just do CSTR.

That’s pretty much it. Yes, I t is a tad bit more complex, comparing to standard setdata. But so much easier to use after all.

If you like this – use this. If there is anyone from MS NAV department  – feel free to take this, re-do to MS standards and use for new versions of NAV – there is no copyright, I am happy to donate this little bit to MS…

With all roughness of functions above I do believe they are better that what we had to deal with for last 10 years.

 

Handling a datetime in NAV with super accuracy

So, as I have promised – some neater code for exact datetime handling in NAV.

The same concept, as described in the earlier post. I am going to use text string to store formatted and sortable datetime value. The only difference – I’m employing a power of .NET to do so. .NET enables me to utilise culture, and most importantly CultureInfo.InvariantCulture. Why would I do so? Simply because initial root of issue was the fact that I was in need to accept by NAV a very accurate datetime from external sources. I can hardly imagine a situation when I would need exact datetime in NAV. Dealing with data from other app which are much more accurate in terms of time handling comparing to NAV perhaps is only case when we need to deal with milliseconds.

So, then the goal is to store data within NAV SQL database with high level of accuracy and same time to allow external system to read and write this data.

And this is simple: two new functions – FormatDT  and EvaluateDT – and this is pretty much it.

The first one, FormatDT simply accept datetime value. I am assuming my datetime come from webservice, therefore always UTC. So I just apply to it CultureInfo.InvariantCulture and convert it to string. Therefore, it saved in SQL DB in UTC, but as string. Same as NAV handle dates (yes, dates are always UTC in NAV SQL DB and only formatted to local time on UI). So then in DB we have an UTC date string formatted as sortable datetime. Like this one: ‘2017-08-31T19:58:29.2780000Z’

FormatDT(DateTimeString : DateTime) : Text
DotNetDateTime := DateTimeString;
EXIT(DotNetDateTime.ToString('o', CultureInfo.InvariantCulture));

The next task then is to evaluate it back to datetime to present it on my SOAP service.

The advantage of .NET here is the fact that TryParse method will inherit a parent culture. So, on SOAP service it will always be UTC, as NAV SOAP services have invariant culture.

On NAV UI (windows client, web client, phone, tablet, etc..) this function will inherit the client culture, which is in turn inherited from local machine. Pretty, isn’t it?

EvaluateDT(DateTimeText : Text) : DateTime
DotNetDateTime := DotNetDateTime.DateTime(0);
DotNetDateTime.TryParse(DateTimeText, DotNetDateTime);
EXIT(DotNetDateTime);

So conclusion then:

  1. Datetime stored as text string allowing us a microseconds accuracy, micro, not even milliseconds and stored in SQL DB in UTC, same way as standard NAV would store dates/times in UTC.
  2. Datetime evaluated for UI or SOAP service same way as standard NAV would do.

Job done.

A test objects here:

 

Some notes on MS SQL timestamps in Dynamics NAV 2016.

My data have a timestamp. A datetime datatype. And it is vital to me as business process dictates so some logic decisions must be made based on record datetime.  What I have noticed is that all my datetimes ends with 0 or 3 or 7 milliseconds. Like 01/01/2017 20:54:253. Or 01/01/2017 20:54:250. Or 01/01/2017 20:54:257. But never 01/01/2017 20:54:254.

Quite strange, as I have assumed the tick is a millisecond. Which is true in NAV – the smallest tick is a millisecond (1/1000 second) indeed. So why then always 0 3 or 7 in the database? I quickly created a nav code like this:

OnRun()
MyDatetime := CURRENTDATETIME;
FOR i := 1 TO 1000 DO BEGIN
  CLEAR(MyTable);
  MyTable."Create Date" := MyDatetime + i;
  MyTable.INSERT;
END;

What would you expect of this code? I was expecting 1000 records created with one tick datetime difference. I’m using CLEAR(MyTable); as my table has autoincrement primary key, so MyTable.INIT would not work as INIT does not clearing the primary key value, so INSERT will fail then as we let SQL server to decide primary key.

And indeed, I had 1000 records. And all of them ends with 0, 3 or 7. Mystery? There must be no mystery in IT. So I amended the code then to dump data to simple text file same time as I am inserting to table.

MyFile.TEXTMODE := TRUE;
MyFile.WRITEMODE := TRUE;
MyFile.CREATE('c:\temp\test.txt');
MyDatetime := CURRENTDATETIME;
FOR i := 1 TO 1000 DO BEGIN
  CLEAR(MyTable);
  MyTable."Create Date" := MyDatetime + i;
  MyTable.INSERT;
  MyFile.WRITE(FORMAT(MyTable."Create Date", 0, '<Day,2>/<Month,2>/<Year4> <Hours24,2>:<Minutes,2>:<Seconds,2><Second dec.><Comma,.>'));
END;
MyFile.CLOSE;

And results were truly surprising.

Text File SQL table
30/08/2017 11:58:29.271 30/08/2017 11:58:29.270
30/08/2017 11:58:29.272 30/08/2017 11:58:29.273
30/08/2017 11:58:29.273 30/08/2017 11:58:29.273
30/08/2017 11:58:29.274 30/08/2017 11:58:29.273
30/08/2017 11:58:29.275 30/08/2017 11:58:29.277
30/08/2017 11:58:29.276 30/08/2017 11:58:29.277
30/08/2017 11:58:29.277 30/08/2017 11:58:29.277
30/08/2017 11:58:29.278 30/08/2017 11:58:29.277
30/08/2017 11:58:29.279 30/08/2017 11:58:29.280
30/08/2017 11:58:29.280 30/08/2017 11:58:29.280

 

As you can see – in the file it is one tick 1/1000 of second, as expected. But in SQL the tick is about 1/300 second and rounded to nearest. Thank you, MS, for your infinite wisdom!

I can prove the point – https://stackoverflow.com/questions/715432/why-is-sql-server-losing-a-millisecond

Of course, there is a SQL datatype datetime2 with precision of 100 nanoseconds. But it is not available in NAV.

So, then conclusion: never use NAV datetime if you want one tick precision. It is not here. Yes, NAV can handle 1/1000 of a second, but SQL cannot!

To prove the point, I have added two new fields to my table: Create Date and Create Time, with NAV datatypes Date and Time respectively. And slightly amended the code

MyDatetime := CURRENTDATETIME;
FOR i := 1 TO 1000 DO BEGIN
  CLEAR(MyTable);
  MyDatetime += 1;
  MyTable."Create DateTime" := MyDatetime;
  MyTable."Create Date" := DT2DATE(MyDatetime);
  MyTable."Create Time" := DT2TIME(MyDatetime);
  MyTable.INSERT;
END;

I somehow was under impression that on SQL end NAV Time datatype handled differently. But it is not. On SQL end NAV Time = SQL datatype DateTime. Which is rounded to 1/300 of a second.

So, there is no way in NAV 2016, 2017 or any other version of NAV to record a time with precision of one tick, I mean 1/1000 second. No way.

Well, there is. A text string of length 23 and some stupid weird code to format datetime received from external sources (where it is high-precision) to string, and back to datetime when our external source read nav data via webservice. I know, it is ugly. But I cannot see better way.

DatetimeToString(MyDatetime : DateTime) : Text
IF MyDatetime = 0DT THEN
  MyDatetime := CREATEDATETIME(01011753D, 000000T);
String := FORMAT(MyDatetime, 0, '<Day,2>/<Month,2>/<Year4> <Hours24,2>:<Minutes,2>:<Seconds,2><Second dec.><Comma,.>');
IF STRLEN(String) = 19 THEN
  String := String + '.';
IF STRLEN(String) < 23 THEN
  String := PADSTR(String, 23, '0');
EXIT(String);
StringToDatetime(MyDatetimeAsString : Text) : DateTime
EVALUATE(Day, COPYSTR(MyDatetimeAsString, 1, 2));
EVALUATE(Month, COPYSTR(MyDatetimeAsString, 4, 2));
EVALUATE(Year, COPYSTR(MyDatetimeAsString, 7, 4));
EVALUATE(MyTime, COPYSTR(MyDatetimeAsString, 12, 12));
MyDate := DMY2DATE(Day, Month, Year);
MyDatetime := CREATEDATETIME(MyDate, MyTime);
EXIT(MyDatetime);

And there is another issue – the datetime stored in NAV always stored in UTC and then formatted to local time on NAV UI. On webservice it is UTC though. So, then all this handling needs to be implemented in your code when you do format datetime to string and vice versa, or to be precise, when you display this data in NAV.

I will cover this in separate post though.

UPDATE: I actually see a better way of managing this. Still using strings, but utilizing the power of .NET – so cleaner and faster code then. New post will follow on this.

Dynamics NAV 2015 and Windows 10

//UPDATE: MS promised to fix everything in October hotfix! http://blogs.msdn.com/b/nav/archive/2015/08/20/windows-10-and-dynamics-nav.aspx

Also I need to add that if you install NAV on windows 8.1 and then upgrade to windows 10 – everything works, including web server components and help server. Fails on fresh install, though!

//UPDATE END

So, as you can see it works! Windows 10 build 10162 and NAV2015 build 8.0.37874.0.

NAV2015onWindows10

Yeah, NAV2015 windows client and server can be installed and run on Windows 10. But, unfortunately not everything works. I am struggling to make live Help Server and Web Server for NAV 2015, it seems to me that MS have done something to IIS in Windows 10, so it is not compatible to NAV 2015 anymore. The error I am getting is the following:

Microsoft Dynamics NAV Build 37874
Error Report
Setup Components
The component was rolled back.
Microsoft IIS URL Rewrite Module
Microsoft IIS URL Rewrite Module (64-bit)
Fatal error during installation.

Microsoft IIS URL Rewrite Module Error

No idea what it is, tried to google, but not a big success.

Anyways, I’ll report this to MS and we will see what will happens in future builds.

 

 

Timestamp and NAV 2015

Doing an integration work I’ve created a new table. I’ve decided to keep filed names as they come in inbound XML file, just to keep things simple. Ok, it is against the MS naming conventions, but no people will look into this intermediate table, so i decide to go this way.

And know what, I’ve get trapped almost immediately. My inbound XML file had a field named ‘timestamp’.

Guess what I got?

This one:

timestamp

Ok, it is clear that any SQL table (and NAV tables are SQL tables) already have column named ‘timestamp’

So, stick to MS naming conventions and you won’t be in trouble :).

Record Link and Notification in NAV 2013R2.

Yeah, no posts for a long time. The reason is simple. I am currently working for a customer on 2009SP1, classic. There is nothing to post about…

But, earlier this year I have had a customer request, when customer wanted some notifications to be created, triggered by NAS. Ok, sound simple, say the condition met and all we need is to insert a text into ‘Note’ field of a ‘Record Link’ table.

There is even a standard code to do so (can be found in the codeunit 454 ‘Job Queue – Send Notification’, local function ‘SetText’. What can be easier?

All coding has been done in little time, matter of hours, but initial testing revealed that the notification text is truncated. Even more, some messages were all right, but some badly truncated…

I’ve tried everything. But, the answer was simple – wrong build. I was on 7.1.36366.0. I’ve upgraded my installation to 7.1.40076.0 – and problem gone. All my truncated notes now looks just fine.

Another reason to keep everything updated!

 

NAV 2013, NAV 2015 RDLC Reports – Font size issue.

As you probably know, it is fairly easy to use HTML formatting on RDLC report controls. Sometimes it is quite useful, especially for things like footers or so, when you need to put something like company details in strictly formatted way, using different fonts and colors for single line of text, like: ‘NAV Workshop LTD Registered address: XXXXXXXX, ZZZZZZZZZ, Warwickshire, United Kingdom’, Company No: 900000′.  Of course, this can be accomplished by creating a separate text box for each formatting, but it is a bit of overkill. HTML formatting like

<span style="font-size: 8pt;"><span style="color: #003366;"><strong>NAV Workshop LTD</strong></span> Registered address</span>: <span style="font-size: 8pt;"><strong>XXXXXXXX, ZZZZZZZZZ, Warwickshire</strong>,</span> <span style="font-size: 8pt;">United Kingdom, Company No:<strong> 900000. </strong></span>

will easily do a trick.

Where the issue then? In code example above there is no issues, but if you use something like

<span style="font-size: Medium"><span style="color: #003366;"><strong>NAV Workshop LTD</strong></span>

– then you re in trouble. Although it is fine to use relative font-size on HTML, but just don’t do it on RDLC reports HTML, as it will be rendered differently on different machines. Just use strict font-size.

Payment Terms, Payment Method and Payment Discounts

In Dynamics NAV 2015 (and in older versions too) we do have a functionality to define payment terms and payment methods. The difference between two is that Payment Terms define WHEN we want money in, and Payment Method define HOW we want money in.
Pretty simple, and it is hard to find a NAV Consultant, who is unfamiliar with this.

But, I’ve managed to find a flaw in this well-known and reliable stuff. Say you define a Payment Terms of 1M(8D) which will mean, that we want money in in 1 month, but will grant an payment discount if customer pay within 8 days. Fine till this point – quite realistic scenario. Then we define a number of payment methods, including CASH, and for cash we put a Bal. Account. This is fine too – when you post an invoice system will automatically post an balancing entry, indicating that payment is received. Exactly what you expect when you take a cash and put it to your cashbox :).

But what if customer’s payment terms defaulted to earlier mentioned 1M(8D)? Well, here we got an issue. The system will automatically grant a payment discount, as you’ve just posted a payment transaction with same date as on your invoice. So in result we will have a payment discount granted, and in all our ledgers the payment amount will be reduced by discount amount, and system will say no single word about this until you navigate on posted document and check you ledgers…

In my opinion this behaviour is entirely wrong for cash based transactions. Well, I know, the payment discounts isn’t a very popular stuff, but as long it is here it worth to check if you customer got payment discount, and just disable it if you going to take cash.