Tag Archives: NAV 2009

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:

 

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.

Backup, Restore and Record Link – NAV 2009 R2

Yeah, i know, 2009 is an old one, but it still alive, despite not supported by Microsoft.

Well, we still have a customer, who run NAV 2009 R2, and colleague of mine came across interesting issue: customer reported they lost all records in Record Link table. After some investigation it turned out that they had intention to create a test company – which lead they to the problem.

So, what they did? They took a native NAV backup, then renamed existing company, then restored a backup to bring live company back. Boom! No Record Links anymore!

I am not surprised – Record Link table has ‘data per company = no’ and a primary key ‘Link ID’ .  ‘Company’ field with relation to ‘company.name’ isn’t part of key…

When you made a backup and then rename company ‘A’ to company ‘B’ system will update only ‘company‘ field in the record link table. Then when you restore original company ‘A’ backup if you include data common to all companies – system will not overwrite Record Link table – it will just skip records with same ID (and PK isn’t changed, you see!) .

So, how to solve then? The answer is simple: just a line of code:
RecordLink.Modyfyall(Company, ‘A’) 
assuming there was only one company before.

Yeah, this solve the problem, but what we can do to avoid the issue when we want a test company in old good NAV 2009 R2? My common sense tell me that there is a simple way to avoid that sort of issues.

1. Make a company A backup
2. Restore to clean DB
3. Rename restored to new name (B)
4. Take a backup
5. Restore taken backup to live DB, which will create exact copy of A company with name B.

Of course, test company B will not have a Record Links, as they had same ID, therefore not overwritten, but company still A in Record Link table.

Seems like design flaw in 2009 – but do we care about this? More interesting: it is the same Record Link table structure in latest versions. OK, we can’t copy company in NAV 2013, but can in NAV 2013 R2 and NAV 2015. I’ll investigate and report later how this behave in modern NAV.

Dynamics NAV and SQL Linked servers

Often there is requirement to get data from external sources. We all know about dataports (now xmlports), webservices, and even FILE.READ function. But sometimes there is more convenient way to get data from an external SQL server.
Since old times we do have ‘LinkedObject’ property on tables. What is it? It is just a property, which allow you to link a NAV table to SQL view in the same database. The field definitions in the view and NAV table should be the same, and NAV table name must be exactly the same as view name (but without company name). Ok, this give us a convenient way to access data on same server. What about external servers?
Quite simple: since sql 2005 we can link SQL server to another SQL server (and not only to MS SQL!).
So when we link sql server A and sql server B then we can access data from server B within server A. So we can create a vien on server A, which will read data from server B and then create an NAV linked table, attached to this view…
How do we do this?
On the server A run the following query:

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
@server = N’B’,
@srvproduct=N’SQL Server’ ;
GO

Then sort out security

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N’B’,
@locallogin = NULL ,
@useself = N’True’ ;
GO

Then test connection

SELECT name FROM [A].master.sys.databases ;
GO

Then we can do something like this:

SELECT local.name AS Logins, linked.name AS LinkLogins
FROM master.sys.server_principals AS local
LEFT JOIN [B].master.sys.server_principals AS linked
ON local.name = linked.name ;
GO

If this worked – you are able to read server B data from server A.