Plotting Customers on Map from Microsoft Dynamics Nav

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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.