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.
Due 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.
Once 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:
Here’s the complete task within Taskcentre:
This 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