NAV System table 'Date' strange filtering

Roelof
Roelof Member Posts: 377
in NAV Three Tier
We recently did a test upgrade from NAV2015 to NAV2017 and doing some testing of custom functionality. We noticed a strange behavior of the NAV System table 'Date'. Apparently the table does not filter correct on the boxes of the customer, while this behavior is not occurring on our test-server.
Here is a brief explanation of the issue:

Code:
UseOrderDate := 041417D;
Date.SETRANGE("Period Type",Date."Period Type"::Date);
Date.SETRANGE("Period Name",'Friday');
Date.SETFILTER("Period Start",'>=%1',UseOrderDate);
IF Date.FINDFIRST THEN
MESSAGE('Date = %1',Date."Period Start")
ELSE
MESSAGE('Date not found.');

Results:
- On our server the result is correct, as:
'Date = 04/14/2017.

- On the customer server (we tried a few different boxes, but same result):
'Date = 01/03/0001'.

According to the Customer's IT person and SQL specialist this behavior is not related to any SQL server setting.
The only difference is that we use 'SQL-2012' as our test server while our customer is using 'SQL-2016'.
I would assume that SQl-2016 is compatible with NAV2017. (build: 10.0.14199).

Any ideas?

Roelof de Jonghttp://www.wye.com

Comments

  • Juhl
    Juhl Member Posts: 724
    Localization???
    Follow me on my blog juhl.blog
  • vytjak
    vytjak Member Posts: 36
    Yes, as Juhl pointed out - the issue might be localization. The code sets the Period name filter as 'Friday'. If the regional setting is is set to not an English speaking country, chances are that the period name 'Friday' does not exist.
    Vytenis Jakas
    B3 Technologies - Making Technology Serve the People
  • Kishorm
    Kishorm Member Posts: 921
    Try restarting the NST to see if this fixes the problem. I have encountered a very strange bug which crops up very occasionally (i.e. not often) which results in similar behaviour. This isn't limited to 2017 though, it's been happening through various versions of 2016 too.

    If you can consistently replicate the issue then I'm sure Microsoft would be interested in hearing from you so it would be very worthwhile raising a support issue with them.
  • Kishorm
    Kishorm Member Posts: 921
    vytjak wrote: »
    Yes, as Juhl pointed out - the issue might be localization. The code sets the Period name filter as 'Friday'. If the regional setting is is set to not an English speaking country, chances are that the period name 'Friday' does not exist.

    If this was a localisation issue and 'Friday' was not a valid "Period Name" then you would expect no records to be found and therefore the 'Date not found' message to be shown.
  • NavNab
    NavNab Member Posts: 181
    Hi Roelof,

    I suggest you to test if the code below will help you:

    // UseOrderDate := 041417D; // help the code to understand it is 2017
    UseOrderDate := 04142017D;
    Date.SETRANGE("Period Type",Date."Period Type"::Date);
    // Date.SETRANGE("Period Name",'Friday'); // use "Period No." instead
    Date.SETRANGE("Period No.", 5);
    Date.SETFILTER("Period Start",'>=%1',UseOrderDate);
    IF Date.FINDFIRST THEN
    MESSAGE('Date = %1',Date."Period Start")
    ELSE
    MESSAGE('Date not found.');

Categories