Tag Archives: Nav

Business Central / Dynamics NAV DateFormula SQL

The DateFormula DataType in BC / NAV allows calculates to date to be stored in Business Central. For example “5D” = “5 Days from date X”

These are not easily available in SQL due to how Microsoft stores the data.

Using this, I’ve created the following Function to convert BC/NAV’s DateFormula using SQL. This function allows you to pass in the date, plus the dateformula. This then returns the value using similar function to what is used in Business Central / Dynamics NAV.

CREATE FUNCTION dbo.CalcDate (@date date, @datefilter nvarchar(32))
RETURNS date

BEGIN

SET @datefilter = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@datefilter,  CHAR(1), 'C'), CHAR(2), 'D'),CHAR(3), 'WD'),CHAR(4), 'W'),CHAR(5), 'M'), CHAR('6'), 'Q'), CHAR('7'), 'Y')


declare @returneddate date = null;

declare @cleanedfilter nvarchar(35) = '';

SET @cleanedfilter = STUFF(REPLACE(REPLACE(CASE
  WHEN LEFT(@datefilter, 1) NOT IN ('+', '-') THEN '+'
  ELSE ''
END + @datefilter
, '+', '|+'
)
, '-', '|-'
)
, 1, 1, ''
) + '|||';


declare @p1 nvarchar(10) = '';
declare @p2 nvarchar(10) = '';
declare @p3 nvarchar(10) = '';


SELECT
  @p1 = LEFT(@cleanedfilter, CHARINDEX('|', @cleanedfilter, 0) - 1)
 ,@p2 = SUBSTRING(@cleanedfilter
  , CHARINDEX('|', @cleanedfilter, 0) + 1
  , (CHARINDEX('|', @cleanedfilter, CHARINDEX('|', @cleanedfilter, 0) + 1) - 1) - (CHARINDEX('|', @cleanedfilter, 0))
  )
 ,@p3 = REPLACE(SUBSTRING(@cleanedfilter
  , CHARINDEX('|', @cleanedfilter, CHARINDEX('|', @cleanedfilter, 0) + 1) + 1
  , 999
  )
  , '|'
  , ''
  );

--Calculate Value

SET @returneddate = (SELECT
    CAST(v3.retp3 AS DATE) 
  FROM (SELECT
      @p1 AS p1
     ,@p2 AS p2
     ,@p3 AS p3) p
  OUTER APPLY (VALUES (
  CASE
    WHEN SUBSTRING(p1, 2, 1) = 'C'                -- <Prefix><Unit>
    THEN CASE SUBSTRING(p1, 3, 2)
        WHEN 'D' THEN @date
        WHEN 'WD' THEN @date
        WHEN 'W' THEN DATEADD(WEEK, DATEDIFF(WEEK, 0, @date) +
          CASE
            WHEN LEFT(p1, 1) = '+' THEN 1
            ELSE 0
          END, 0)
        WHEN 'M' THEN CASE
            WHEN LEFT(p1, 1) = '+' THEN eomonth(@date)
            ELSE DATEADD(DAY, 1, eomonth(@date, -1))
          END
        WHEN 'Q' THEN DATEADD(DAY, CASE
            WHEN LEFT(p1, 1) = '+' THEN -1
            ELSE 0
          END, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @date) +
          CASE
            WHEN LEFT(p1, 1) = '+' THEN 1
            ELSE 0
          END, 0))
        WHEN 'Y' THEN DATEADD(YEAR, DATEDIFF(YEAR, 0, @date) +
          CASE
            WHEN LEFT(p1, 1) = '+' THEN 1
            ELSE 0
          END, 0)
        ELSE ''
      END
    WHEN ISNUMERIC(SUBSTRING(p1, 2, 1)) = 1       -- <Number><Unit>
    THEN CASE
        WHEN RIGHT(p1, 2) = 'WD' THEN DATEADD(DAY, CAST(REPLACE(p1, 'WD', '') AS INT), @date)
        ELSE CASE RIGHT(p1, 1)
            WHEN 'D' THEN DATEADD(DAY, CAST(REPLACE(p1, 'D', '') AS INT), @date)
            WHEN 'W' THEN DATEADD(WEEK, CAST(REPLACE(p1, 'W', '') AS INT), @date)
            WHEN 'M' THEN DATEADD(MONTH, CAST(REPLACE(p1, 'M', '') AS INT), @date)
            WHEN 'Q' THEN DATEADD(QUARTER, CAST(REPLACE(p1, 'Q', '') AS INT), @date)
            WHEN 'Y' THEN DATEADD(YEAR, CAST(REPLACE(p1, 'Y', '') AS INT), @date)
          END
      END
    WHEN ISNUMERIC(SUBSTRING(p1, 2, 1)) = 0       -- <Unit><Number>
    THEN CASE
        WHEN SUBSTRING(p1, 2, 2) = 'WD' THEN DATEADD(DAY, RIGHT(p1, 1) - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, @date) -
          CASE
            WHEN LEFT(p1, 1) = '-' THEN 1
            ELSE 0
          END, 0))
        ELSE CASE SUBSTRING(p1, 2, 1)
            WHEN 'D' THEN DATEADD(DAY, ABS(CAST(REPLACE(p1, 'D', '') AS INT)) - 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @date) +
              CASE
                WHEN ABS(CAST(REPLACE(p1, 'D', '') AS INT)) < DAY(@date) THEN 1
                ELSE 0
              END +
              CASE
                WHEN SIGN(CAST(REPLACE(p1, 'D', '') AS INT)) = -1 THEN -1
                ELSE 0
              END, 0))
            WHEN 'W' THEN DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(WEEK, ABS(CAST(REPLACE(p1, 'W', '') AS INT)) - 1, datefromparts(YEAR(@date) +
              CASE
                WHEN ABS(CAST(REPLACE(p1, 'W', '') AS INT)) <= DATEPART(WEEK, @date) THEN 1
                ELSE 0
              END +
              CASE
                WHEN SIGN(CAST(REPLACE(p1, 'W', '') AS INT)) = -1 THEN -1
                ELSE 0
              END, 1, 1))), 0)
            WHEN 'M' THEN datefromparts(YEAR(@date) +
              CASE
                WHEN ABS(CAST(REPLACE(p1, 'M', '') AS INT)) <= MONTH(@date) THEN 1
                ELSE 0
              END +
              CASE
                WHEN SIGN(CAST(REPLACE(p1, 'M', '') AS INT)) = -1 THEN -1
                ELSE 0
              END, ABS(CAST(REPLACE(p1, 'M', '') AS INT)), 1)
            WHEN 'Q' THEN datefromparts(YEAR(@date) +
              CASE
                WHEN ABS(CAST(REPLACE(p1, 'Q', '') AS INT)) <= DATEPART(QUARTER, @date) THEN 1
                ELSE 0
              END +
              CASE
                WHEN SIGN(CAST(REPLACE(p1, 'Q', '') AS INT)) = -1 THEN -1
                ELSE 0
              END, ((ABS(CAST(REPLACE(p1, 'Q', '') AS INT)) - 1) * 3) + 1, 1)
            WHEN 'Y' THEN datefromparts(ABS(CAST(REPLACE(p1, 'Y', '') AS INT)), 1, 1)
          END
      END
    ELSE ''
  END
  )
  ) AS v1 (retp1)
  OUTER APPLY (VALUES (
  CASE RIGHT(p2, 1)
    WHEN 'D' THEN DATEADD(DAY, CAST(REPLACE(REPLACE(p2, 'W', ''), 'D', '') AS INT), retp1)
    WHEN 'W' THEN DATEADD(DAY, CAST(REPLACE(p2, 'W', '') AS INT) * 7, retp1)
    WHEN 'M' THEN DATEADD(MONTH, CAST(REPLACE(p2, 'M', '') AS INT), retp1)
    WHEN 'Q' THEN DATEADD(QUARTER, CAST(REPLACE(p2, 'Q', '') AS INT), retp1)
    WHEN 'Y' THEN DATEADD(YEAR, CAST(REPLACE(p2, 'Y', '') AS INT), retp1)
    ELSE retp1
  END
  )
  ) AS v2 (retp2)
  OUTER APPLY (VALUES (
  CASE RIGHT(p3, 1)
    WHEN 'D' THEN DATEADD(DAY, CAST(REPLACE(REPLACE(p3, 'W', ''), 'D', '') AS INT), retp2)
    WHEN 'W' THEN DATEADD(DAY, CAST(REPLACE(p3, 'W', '') AS INT) * 7, retp2)
    WHEN 'M' THEN DATEADD(MONTH, CAST(REPLACE(p3, 'M', '') AS INT), retp2)
    WHEN 'Q' THEN DATEADD(QUARTER, CAST(REPLACE(p3, 'Q', '') AS INT), retp2)
    WHEN 'Y' THEN DATEADD(YEAR, CAST(REPLACE(p3, 'Y', '') AS INT), retp2)
    ELSE retp2
  END
  )
  ) AS v3 (retp3));

return
  @returneddate


END


GO

This function can be used as follows (today is the 9th January).

select dbo.calcdate(getdate(),'-6D')

Microsoft Dynamics NAV – View All Active Sessions

With Microsoft Dynamics Nav, there are various ways of viewing all the active sessions within the system. The easiest of which is the “Sessions” page within the software itself:

sessions-menu-option

sessions-window

The downside of this, is that it only shows active sessions on the tier which the user is connected. This is ok for solutions where the system has a single tier, however it is common to have multiple tiers in order to spread load. Checking this way is time consuming as you need to check multiple tiers in order to get a session count.

However using SQL can be an easy way to get the number of active sessions:

SELECT [User ID]
	,[Server Instance Name]
	,[Server Computer Name]
	,[Database Name]
	,[Client Computer Name]
	,[Login Datetime]
FROM [dbo].[Active Session]

It is worth nothing that Nav tidies up this table automatically, but in some cases it may be incorrect. For example if a middle tier crashes out, it could be left with orphaned records until the tier starts up again (or another tier prunes the records down).

SQL Remove Trailing Decimal Places

Within a recent project, I’ve been extracting data from Microsoft Dynamics NAV using Taskcentre.

Within NAV, decimals are stored as decimal(38,20), complete will all 20 decimal places..!

Dynamics NAV 20 Decimal Places

The following SQL query is very handy at removing the un-required decimal places (but keeping the accuracy of the data)

SELECT CONVERT(DOUBLE PRECISION, 1.99990000000000000000)
	,CONVERT(DOUBLE PRECISION, 2.00000000000000000000)
	,CONVERT(DOUBLE PRECISION, 3.00005077000000000000)

The results of the above query is:

1.9999
2
3.00005077

SQL Reduce Number of Decimal Places

Microsoft Dynamics Nav – Either the caller does not have the required permission or the specified path is read-only – Error

Today on Microsoft Dynamics Nav 2013R2, I kept getting the following error, which stumped me for a bit…

Either the caller does not have the required permission or the specified path is read-only.

After much investigation, I found a solution to the problem, delete the following folder and restart the service.

C:ProgramDataMicrosoftMicrosoft Dynamics NAV71Server{Nav Instance Name}usersdefault

Upon restarting the service, I was able to connect back to Nav without receiving the error.

Dynamics NAV RTC – Page Slow to Load – Flowfields

With Microsoft Dynamics Nav, it is possible to add a “FlowField” to a table. This flow field is then calculates an aggregate of an underlying table.

Typical uses are to perform the following calculations:

  • Sum
  • Average
  • Min
  • Max
  • Count

Additionally, the following non-aggregate functions are available:

  • Lookup
  • Exists

Typical values which can be calculated via flow fields include:

  • Inventory
  • Quantity on Sales Order
  • Quantity on Purchase Order

With some versions of Microsoft Dynamics Nav, opening a page can be extremely slow. If you compare it to an earlier version, say 2009 or 2013, the speed of the earlier version can be a lot quicker.

This is due to the FlowFields on the page. With some versions of Nav, the FlowFields are calculated when you open the page, which can be quite time consuming, especially where there are lots of records. Even if the FlowField is not visible on the page! On earlier versions of Nav, if you hide the field, the FlowField is not calculated, so the issue is not present.

Therefore if you have a page with a flowfield which is not used, instead of hiding it from the page, delete it completely.

Further information of this issue is available here on the Mibuso forum.

Windows 10 – Blurry Fonts at High Resolutions Due to DPI Scaling

With high resolutions in Windows 10, windows will automatically adjust the DPI of the fonts in order to allow you to see these on the screen.

In most cases, this works, however in some specific applications, it can make the fonts all “blurry” and give you a headache when using the machine.

For example, running the Microsoft Dynamics Nav 2013 R2 Development Environment on a high resolution screen, with DPI scaling set at 250% looks as follows. (Click the image to view full size)

Windows 10 - Display Scaling Enabled

A fix for this is to enable the option “Disable display scaling on high DPI settings” within compatibility mode. This is accessed by right clicking on the shortcut and choosing “Properties”:

Windows 10 - Compatibility Settings

Once ticked and you run the application again, the scaling issue for the text is fixed. Unfortunately in this case, it makes some icons smaller (as you can tell by comparing the screenshots), however it is more usable and does not give you a headache!

Windows 10 - Display Scaling Disabled

Access Microsoft Dynamics Nav 2013 R2 From Windows 10, Not on Domain

Edit: The following updated blog post has a better method for achieving this: Run Programs as a Domain user from a none Domain account.

With Microsoft Dynamics Nav 2013R2, Microsoft recommend using a “Pro” version of windows which is registered on an Active Directory domain.

If you are using a “Home” version of windows, when you try to access Nav via the RTC (Role Tailored Client), you get the following error:

You do not have access to Microsoft Dynamics NAV.
Verify that you have been set up as a valid user in Microsoft Dynamics NAV.

Unfortunately “Home” versions of Windows cannot be registered on a domain (Only Pro and above can be). This error occurs because Nav is trying to use the local credentials from the machine across the domain, which will obviously fail.

In order to solve this issue, edit the following file:

%appdata%\Microsoft\Microsoft Dynamics NAV\71\ClientUserSettings.config

Amend the following line:

<add key="ClientServicesCredentialType" value="Windows" />

To the following:

<add key="ClientServicesCredentialType" value="UserName" />

This then will prompt the following credential prompt – once the correct credentials have been provided, you will be able to access Nav from your “Home” edition of windows.

Prompting for Credentials

In order to access Nav via the Development Environment, the answer is quite simple… Just use the Database Server Authentication:

Development Environment Authentication

Automating Reports from Microsoft Dynamics Nav using ETL Tools

Recently I’ve been looking into a piece of software called ETL-Tools – Advanced ETL Processor Enterprise.

This software is designed to perform ETL operations, similar in which to Taskcentre does.

The software is quite well priced, with a single user licence being between $340 and $690 (£216 and £440). This is a purchase cost and includes a years worth of support. Annual maintenance is 20%, but is optional. A site licence is also available which allows unlimited users.

In this blog post, I’m going to cover creating an automated task to generate a PDF document with the sales totals for company, split by salesperson code. The report is designed to run daily and is sent to the sales manager in order to view the progress.

This is using the Nav demonstration database – Cronus, with Nav 2015, running on Server 2012.

Creating Connections
ETL Tools Nav ConnectionFor this task, we’re going to need 2 connections as follows:

  • MS SQL Database – Connection to the Nav Database
  • SMTP – In order to send the email

After opening Advanced ETL Processor, it’s a simple task of right clicking on the blank connections and choosing New. In the screenshot on the right, I’ve shown the Nav connection.

Creating The Report
ETL Tools Report WizardOn right clicking to create the report a wizard is shown. This wizard takes you through a few steps in order to create the basis of the report:

  • Source Connection (In this case, it’s the connection we’ve created earlier)
  • SQL Query
  • Fields to add to the report
  • Groupings
  • Report Orientation
  • Report Layout (Rows or Columns)
  • Theme

After completing the wizard, this opens up in design view for further editing and design:
ETL Tools Report Designer

For this task, I’ve used all default options, plus the following SQL:

Select
   [CRONUS UK Ltd_$Sales Header].[Salesperson Code],
   Sum([CRONUS UK Ltd_$Sales Line].Amount) AS Sum_Amount
From
   [CRONUS UK Ltd_$Sales Header] Inner Join
   [CRONUS UK Ltd_$Sales Line] On [CRONUS UK Ltd_$Sales Header].[Document Type] =
   [CRONUS UK Ltd_$Sales Line].[Document Type] And
   [CRONUS UK Ltd_$Sales Header].No_ =
   [CRONUS UK Ltd_$Sales Line].[Document No_]
Where
   [CRONUS UK Ltd_$Sales Header].[Document Type] = 1
Group By
   [CRONUS UK Ltd_$Sales Header].[Salesperson Code]

Create Package
After creating the report, we need to create a package to perform the following steps:

  • Run Report
  • Save PDF to Disk
  • Send Email with PDF Attachment

Creating the package is very simple, right click and choose add. We need to drag two “Blocks” onto the planner, “Report” and “Send Email” and join these up together:
ETL Tools Package Design

The screenshots below shows the setup of these two steps:
Run Report
ETL Tools Package Design - Run Report

Send Email
ETL Tools Package Design - Send Email

Running Task
The task can be run by either opening up the Package, right clicking and choosing “Run Package” (F3). Alternatively, under the schedule tab, it can be scheduled to run automatically.

In this example, I’ve set the email to be run manually and sent to my email address. Example in the screenshot below:
ETL Tools Emailed Report

In due course I will do some further investigation into ETL Tools. Watch this space!

Microsoft Dynamics Nav 2013 R2 Action Image Library

Nav 2015 Action ImagesIn Nav, it’s possible to change the icon on a menu option, in order to make it clearer to the user. These are called “Action Images” in Nav 2009 or “Icon Collection” in Nav 2013 onwards. In the screenshot to the right I have highlighted an example of such an image.

Nav Action Images Object DesignerThese icons can be changed in “Object Designer” by a Microsoft Dynamics Nav Developer, within the properties for the action.

Depending on the version of Nav, it is either a free text entry, or a lookup. But regardless of the version of Nav, unfortunately there’s no image preview. Hopefully this will be included in a future version.

Luckily, the full library of options available are available on the Microsoft’s website:

Unfortunately for Nav 2013, these are split across multiple pages. This post is a lift of all the images from the site above, but all on a single page to make it nice and easy to view and select the perfect icon for your page.

For up to date images, I recommend viewing the Microsoft articles above.

0-9A

B

CA-CH

CH-CO

CO-CU

D

E

F

G

H

I

J

K

L

M

N

O

PA-PR

PR-PU

Q

RA-RE

RE-RU

SA-SH

SH-SY

T

U

V

W

X

Y

Z

Stripping String to Alphanumeric in Microsoft Dynamics Nav using Regex

Within Microsoft Dynamics NAV, there are a few times which an input string needs stripping back to alphanumeric characters.

For example:

  • Unique Identifiers
  • Shipping Tracking Numbers
  • Vehicle Registration Numbers
  • etc

This can be done directly in C/AL, or also using .NET.

Strip String to AlphaNumeric CALDirectly in C/AL:
Directly in CAL, you need to create a function as follows:
Name: StrinStringToAlphaNum
Accepts: Text - 250
Returns Text - 250
Local Variables:

  • tempString - Text
  • i - Integer


The code required is:

 IF STRLEN(StringToStrip)=0 THEN
   EXIT(StringToStrip);

 FOR i:= 1 TO STRLEN(StringToStrip) DO
   BEGIN
    IF StringToStrip[i]IN ['a'..'z','A'..'Z','0'..'9'] THEN
      BEGIN
          tempString:=tempString+FORMAT(StringToStrip[i]);
      END;
   END;
  EXIT(tempString);

Strip String to AlphaNumeric DOTNETUsing .NET:
Directly in .NET needs less code, but is only suitable for Nav 2009 onwards (NAV 2009, 2009 R2, 2013, 2013 R2 and 2015).

Local Variables:

  • RegEx - DotNet - System.Text.RegularExpressions.Regex.'System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'

The code required is:

"Output String" := RegEx.Replace("Input String",'[^A-Za-z0-9]','');

Comparison
The Regex method is less code, but only works on newer versions of NAV. Whereas the C/AL only method works on older versions. (If you use the “classic client”, it’s an old version and doesn’t support .NET)

I’ve not tested both methods for speed, but both are really quick and not noticeable to the user.

Regex could be used for other things too, such as providing validation, classic examples being:

  • E-Mail Address
  • Phone Numbers
  • Website Addresses
  • etc

The Regular Expression Library is a good starting point for these regular expressions.

Maybe at some point I’ll provide some details on how to do these.