Tag Archives: Nav

Microsoft Dynamics NAV 2013 R2 and VPN

4 Replies

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.

VPN Microsoft Dynamics NavIn 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.

Plotting Customers on Map from Microsoft Dynamics Nav

Leave a Reply

In this blog post, I am going to cover plotting UK customers in a map from Microsoft Dynamics Nav using Taskcentre. In this post I’m using Nav 2013R2, with the Extended Demo Database. (This has a lot more customers and data compared to the standard demo database).

In order to plot the locations on the map, firstly we need to translate the postcodes into Latitude and Longitude. On this Website, there’s a download link to a SQL script to create and populate a table with postcodes within the UK plus the appropriate latitudes and longitudes.

SSIS Import PostcodesDue to the size of the file, I had problems running the SQL query on my Virtual Machine, so instead used an SSIS job (via the Import/Export data tool on SQL Server). Depending on the amount of RAM available on the SQL server, it should be possible to run the script directly.

SQL Query Customer Postcode Longitude LatitudeOnce the file has been imported into the Nav database, I write a SQL query to extract the customer information. Note that in the Postcode table there’s no space between the first half and the second half of the postcode. Therefore using SQL we need to remove the spaces from the postcode table as part of the join.

The join on the two tables is:

REPLACE("CRONUS EXT UK Ltd_$Customer"."Post Code",' ','') = postcodelatlng.postcode

This produces a data set as follows:
Customer Postcode Data

VBScript - Customer Location StringUsing this data, we can then create a HTML page in order to visualise the data. Firstly we need to use a VBScript step which cycles through each record from the SQL query, storing the data into a variable. This variable will later be used within Taskcentre when building up the Javascript.

VBScript - Customer Location Build HTMLAfter creating this string, this is then combined with two snippets of HTML (stored within variables for ease of editing) to produce a final, complete HTML document. This HTML document is then saved to disk and can be opened in a browser. Using the FTP tools, it could be uploaded to a website if required. The document consists of some JavaScript which uses the Google Maps Javascript API. The principle could be used for any API, such as the Bing Maps API, however personally I do prefer the Google API over Microsoft’s.

The Javascript also uses the “click” event listener. This then shows a dialog box with the customer details when the icon is clicked. This contains just the customer number and name in this example, but could contain information such as full address, contact information, sales order information etc.
Customer Map

Here’s the complete task within Taskcentre:
Customer Map - Task

Google Custom MarkersThis task is only a very basic task, but with some imagination and skill, it could be used as a basis for pretty much any use. Especially when you extend the task adding the ability to have custom markers.

Some examples could include:

  • Trend analysis
  • Deliveries for a courier
  • Customers/areas with bad debt/chargebacks

Using Taskcentre to Post HTML Tables to HipChat

2 Replies

HTML Email ExampleI use Taskcentre from Orbis Software quite a lot to send HTML emails to recipients. These usually are bog standard HTML tables with content. For example, this is a sample report based on Microsoft Dynamics Nav database. This is not the standard out of the box Cronus database, but the extended version. The standard database has very little data (about 600k rows of data), whereas the extended has closer to 2.5 million!

Email has it’s uses, however does have it’s disadvantages, such as:

  • E-Mail Ping Pong – Emails going backwards and forwards, but not getting anywhere
  • Users not replying to latest email – 20 minutes into an email conversation, someone else joins in using an earlier email
  • Too detailed – Sometimes people email a 20 line email, where a single sentence will do

More recently I’ve been trialling HipChat as an alternative to E-Mail. In a previous blog post, I covered posting error messages from Taskcentre and posting in Hipchat – Taskcentre Alert Notifications To HipChat.

Extracting Data From Nav
In order to extract information from Nav, first I need to use either ODBC or OLEDB to return the data. I prefer OLEDB as I’ve seen performance benefits over ODBC, but either is suffice.

Here’s the SQL query as generated by Taskcentre:

SELECT
	dbo."CRONUS EXT UK Ltd_$Salesperson_Purchaser".Name AS Name,
	dbo."CRONUS EXT UK Ltd_$Sales Header"."Salesperson Code" AS "Salesperson Code",
	SUM(dbo."CRONUS EXT UK Ltd_$Sales Line"."Outstanding Amount (LCY)") AS Sales,
	SUM(dbo."CRONUS EXT UK Ltd_$Sales Line"."Outstanding Amount (LCY)"-(dbo."CRONUS EXT UK Ltd_$Sales Line"."Outstanding Quantity"*dbo."CRONUS EXT UK Ltd_$Sales Line"."Unit Cost (LCY)")) AS Profit
FROM
	dbo."CRONUS EXT UK Ltd_$Sales Line",
	dbo."CRONUS EXT UK Ltd_$Sales Header",
	dbo."CRONUS EXT UK Ltd_$Salesperson_Purchaser"
WHERE
	(dbo."CRONUS EXT UK Ltd_$Sales Header"."Document Type" = 1) AND (dbo."CRONUS EXT UK Ltd_$Sales Header".No_ = dbo."CRONUS EXT UK Ltd_$Sales Line"."Document No_" AND dbo."CRONUS EXT UK Ltd_$Sales Header"."Document Type" = dbo."CRONUS EXT UK Ltd_$Sales Line"."Document Type" AND dbo."CRONUS EXT UK Ltd_$Sales Header"."Salesperson Code" = dbo."CRONUS EXT UK Ltd_$Salesperson_Purchaser".Code)
GROUP BY
	dbo."CRONUS EXT UK Ltd_$Sales Header"."Salesperson Code",dbo."CRONUS EXT UK Ltd_$Salesperson_Purchaser".Name

Building HTML for HipChat
VBScript Creating Table RowsNext we need to build the HTML for HipChat. Unfortunately the “Format as Text” tool will not work in this situation (it does not allow outputting of the created text), so we need to use VBScipt instead.

Firstly I’ve created a variable within the task called “TempHTML”. This variable will be used to store the created HTML before posting to HipChat. Then the VBScript step is created which is set to repeat for every row in the returned record set. This is done by checking the box accordingly as per the screenshot.

For clarity, here’s the code used:

' ProcessData Function
' 
Function Create_Table_Rows_Function()
Variables("TempHTML") = Variables("TempHTML") + "<TR>"
Variables("TempHTML") = Variables("TempHTML") + "<TD><B>" + ThisStep.RecordSource("Name") + "</B></TD>"
Variables("TempHTML") = Variables("TempHTML") + "<TD>£" + CStr(Round(ThisStep.RecordSource("Sales"))) + "</TD>"
Variables("TempHTML") = Variables("TempHTML") + "<TD>£" + CStr(Round(ThisStep.RecordSource("Profit"))) + "</TD>"
Variables("TempHTML") = Variables("TempHTML") + "</TR>"
End Function

For those who have HTML skills, you will notice that this does not include any <TABLE> tags, nor column headers. In order to create these, a second VBScript step is created which finishes these off:

' ProcessData Function
' 
Function Create_Headers_Function()
Variables("TempHTML") = "<B>Sales Statistics:</B><BR/><TABLE BORDER=1><TR><TD><B>Salesperson</B></TD><TD><B>Sales</B></TD><TD><B>Profit</B></TD></TR>" + Variables("TempHTML")
Variables("TempHTML") = Variables("TempHTML") + "</TABLE>"
End Function

Posting to HipChat
Posting this to HipChat is very simple, using the HipChat Send Room Notification web service. I’m not going to cover it in this post as it was covered previously, simply use the variable inside the XML Web Service Tool.

Complete Task
Here’s the complete task (I’ve still left in the HTML email example, which is outside of the scope of this blog post):
Sales Order Totals With HipChat

When the task runs, here’s the posting to HipChat:
HipChat HTML Message - Table

Borders
I’ve tried using CSS Table Formatting and even the HTML Table Border Attribute, however neither of these work, so I assume HipChat does not support borders (it does state that only basic HTML is supported and anything unsupported is stripped out). Possibly in future HipChat may support these.

Taskcentre Triggers – Helper Tasks

Leave a Reply

This post is in response to a discussion on the Taskcentre LinkedIn Group. In the discussion, I’ve suggested using a “Helper” task in order to limit the number of triggers on a database, improving performance on the SQL Database.

SQL Trigger
First to define what a SQL Trigger is. There’s a lot of information on MSDN regarding SQL Triggers. To shorten this down, basically it’s a Stored Procedure which runs every time an event happens on the table (such as INSERT/UPDATE/DELETE).

Performance Issues
Single triggers work well, but as you create more and more triggers, this can impact performance. Lets take for example, the Sales Header table on Microsoft Dynamics NAV. If you are unsure of the data structure, this table is used to store the following unposted sales documents:

  • Quote
  • Order
  • Invoice
  • Credit Memo
  • Blanket Order
  • Return Order

Imagine you had some logic in a taskcentre task which ran for each document type. This could mean that you have 6 triggers which run for every update!

Improving Performance
Helper Task - TriggerIn order to improve performance, I make a “Helper” task which runs once. Then taskcentre uses the logic to decide which tasks to run. This moves the overhead from SQL (which you want to be nice and fast) and taskcentre can chug away doing all the complex work.

As you can see from the SQL Trigger, this trigger will run the Taskcentre Task when the status changes from “Open” to “Released”. I’ve also filtered the document type to Quote/Order/Return Order too in order to prevent the taskcentre task running too often. In theory you don’t need these filters and you could handle these in taskcentre, but it depends on the business logic which you are trying to implement.

It’s worth noting that the trigger will still run for every single update. It just wont start the taskcentre task unless it matches the criteria.

Decisions in Taskcentre
Helper Task - DecisionAs you could see from the trigger, the variable “DocumentType” is set to match the Document Type in the updated record. Using the decision step as shown, the next series of tasks to run can be selected. In the example to the left, I’ve chosen the Document Type of 0 which is “Quote” (Dynamics Nav stores all “Option” data types as integers).

Below shows the completed task. Each step calls a VB Script task which in turn calls another task. (I’ll write about that at some time in the future)
Helper Task - Complete Task

Microsoft Dynamics Nav 2015

Leave a Reply

Microsoft has now released Microsoft Dynamics Nav 2015 earlier on this month. There’s a handful of new features above and beyond the features added in 2013. The tablet client looks pretty cool, removing the need to use the web client whilst out in the field. It’s optimised for touch use and available for all platforms:

If you have a valid subscription, it’s available for download on CustomerSource – Microsoft Dynamics Nav 2015 Download. You do need to get an upgraded licence to run 2015. This can be downloaded from CustomerSource (Microsoft are automatically upgrading Customer licences, or via your partner.)