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:
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:
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).
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.
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”:
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!
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:
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.
In order to access Nav via the Development Environment, the answer is quite simple… Just use the Database Server Authentication:
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 For 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 On 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:
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:
The screenshots below shows the setup of these two steps: Run Report
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:
In due course I will do some further investigation into ETL Tools. Watch this space!
In 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.
These 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.
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.
Directly 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);
Using .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:
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:
I’m a believer of VPN for remote workers. It’s really useful for workers to be able to remotely connect to Microsoft Dynamics Nav as if working in the office. Although there is the web client, nothing beats the full client for usability.
I’ve used OpenVPN for a few projects in the past. I do like OpenVPN as it’s very powerful and also is extremely secure due the advanced encryption used. The downside is that it needs a separate client installing on the PC and it can be quite fiddly to set up. Most importantly, OpenVPN works an absolute dream with Dynamics NAV.
For a recent project, instead of using OpenVPN, I implemented L2TP/IPSec with fallback to PPTP. Although not as secure as OpenVPN, L2TP/IPSec is still pretty secure and is good for the majority of uses. The only issue I’ve had with this is accessing Nav through the VPN in Windows 8.1.
Nav was throwing the following error:
The Service Principle Name (Delegation) configuration has been set incorrectly.
Server connect URL: "net.tcp://{servername}:7046/DynamicsNAV71/Service".
SPN Identity:"DynamicsNAV/{servername}:7046"
A call to SSPI failed, see inner exception.
Additionally sometimes the following error was being thrown:
The program could not create a connection to the server.
Do you want to try again?
Very useful…!
After a lot of research and trial and error, I was able to identify that this issue is caused by the Username/Password combination used for VPN. Once the VPN connects, the credentials for the VPN was being used for the Kerberos authentication. As the VPN credentials where not those in Active Directory, this was obviously causing the issue.
In order to fix this issue, luckily a simple change to a file is required. Unfortunately this cannot be amended in the GUI.
To find this file, first browse to the following location:
%appdata%\Microsoft\Network\Connections\Pbk
Right click on the file “rasphone.pbk”, open with Notepad and look for the following line:
UseRasCredentials=1
This will be just below the name of the VPN connection in square brackets. For example if your VPN is called “Test”, your looking for “[Test]”.
This line needs amending to the following:
UseRasCredentials=0
After saving the file and connecting to the VPN again, Microsoft Dynamics Nav was working correctly.
