Integration to Virtual Earth – Part 3 (out of 4)

If you haven’t read Part 1 and Part 2, you should do so before continuing here. In Part 1 we saw how to add geographical information to your customer table and how to connect to the Virtual Earth geocode web service, and in part 2 we created a simple web site showing a map and adding pushpins.

Now we want to add an action to the customer card, showing us the location of the customer and other customers in the surrounding area.

Parameters to the website

The web site we create in part 2 takes 3 optional parameters: Latitude, Longitude and Zoom

If you try to type in

http://localhost/mysite/default.htm?latitude=0&longitude=0&zoom=6

you will get something like this:

image

Which is location 0,0 and a zoom factor of 5.

Now the only thing we need to do in NAV is to create an action, which launches this web site with the location set to the coordinates of the customer and set the zoom factor to e.g. 10. The web site will then connect back to web services and place push pins where the customers are and that part already works.

Adding the action

In the customer card open the site actions and add an action called View Area Map:

image

Switch to code view and add the following line to the action:

HYPERLINK(‘http://localhost/mysite/default.htm?latitude=’+FORMAT(Latitude,0,2)+’&longitude=’+FORMAT(Longitude,0,2)+’&zoom=10’);

FORMAT(xxx,0,2) ensures that the latitude and longitude are in the correct format and when you launch the action on the Cannon Group you will get:

image

A nice map of the surroundings and if we hover over the customers we can see more information about the customer.

I did not upload the binaries for this to any site – since it really is just adding this one line of code to a new action.

Part 4 of the Virtual Earth Integration will have to wait a little. It uses SP1 features and we are not allowed to blog about SP1 features until the Statement of Direction is published and that should be the case end of March 2009 – so stay tuned for the round-up of Virtual Earth Integration beginning of April 2009.

 

Enjoy

 

Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV

Integration to Virtual Earth – Part 2 (out of 4)

If you haven’t read Part 1, you should do so before continuing here. In Part 1 we saw how to add geographical information to your customer table and how to connect to the Virtual Earth geocode web service. Now we want to put this information to play.

First of all, we all know Virtual Earth (if not – try it at http://maps.live.com). Less commonly known is it, that the map control used in maps.live.com actually is available for public usage in other web pages.

The Virtual Earth Interactive SDK

If you navigate to http://dev.live.com/virtualearth/sdk/ you will get a menu in the left side looking like this:

image

and a map looking like this:

image

(depending on where in the world you are)

Try looking at the different tabs and you can see the source code for getting a map like this – and you can select other menu items.

Try clicking at the menu item for “Show a specific map”. The map now shows the space needle in Seattle and if you click the source code tab you will see the code for creating that map:

<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”&gt;
<html>
<head>
<title></title>
<meta http-equiv=”Content-Type” content=”text/html; charset=utf-8″>
http://dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6.2

var map = null;
function GetMap()
{
map = new VEMap(‘myMap’);
map.LoadMap(new VELatLong(47.6, -122.33), 10 ,’h’ ,false);
}  

</head>
<body onload=”GetMap();”>

</body>
</html>

So – HTML and Javascript again. A few things which are important to know is that this line:

http://dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6.2

Imports the mapcontrol library from the Virtual Earth Web Site and allows you to use all the controls and functions exposed by this library (like a C# reference and using statement).


var map = null;
function GetMap()
{
map = new VEMap(‘myMap’);
map.LoadMap(new VELatLong(47.6, -122.33), 10 ,’h’ ,false);
}  

Is the actual code – for manipulating the map. 47.6, –122.33 should be latitude and longitude for the Space Needle, 10 is the Zoom level, ‘h’ is the style (h==hybrid) if you click the reference tab on the dev sdk window you can find the documentation to LoadMap and other stuff.

Turns out that the Space Needle is not exactly in that position – the right position would be 47.620564, -122.349577 – anyway you get the picture,

The instantiation of the Map also tells you where on the web site, the map should be placed (‘myMap’) which references a

area in the body:

 

 

This

area specifies the location, flow definition and the size of the control.

The last thing we need to know is how our function (GetMap) in Javascript is executed – and that happens as an event handler to the onload event on the body tag.

 

Note that Javascript is executed on the Client and there is no serverside code in this example.

Knowing that we can communicate with NAV Web Services from Javascript (as explained in this post) we should be able to create a site, which loads a map and displays pushpins for all our customers – more about this in a second…

Deployment

Knowing that there are a million different ways to deploy web sites I will not go into detail about how this should be done – but only explain how it can be done (aka what I did on my laptop).

What I did was to install IIS (Internet Information Server) on my laptop. After this I fired up Visual Studio, selected File –> New –> Web Site and specified that I wanted to create an Empty Web Site called http://localhost/mysite

In the empty web site I right clicked on the project and selected add new item, selected HTML page and called it default.htm:

image

C# / Visual Basic choice really didn’t matter as I wasn’t going to make any server side code.

In this default.htm I replaced the content with the following HTML

http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd&#8221;>

/fonta%20href=

var map = null;
function GetMap()
{
map = new VEMap(‘myMap’);
map.LoadMap(new VELatLong(47.620564, -122.349577), 16 ,’h’ ,false);
}

</body>
</html>

and when I open the site in my browser i get:

image

Voila, the Space Needle.

So far so good and if you succeeded in showing this map in a browser from your own Web Site – we know that all the basics are in place.

What’s needed in NAV

Nothing comes for free – and just because we added the latitude and longitude to the customer table doesn’t mean that we can query on them.

We need a function in NAV, exposed as a Web Service, which we can call and ask for all customers in a rectangle of the Earth. Now sceptics might say that there are no such thing as a rectangle of the earth – but programming towards the Virtual Earth API – there is. For returning the customers through Web Services I have created a XML port:

image

and the two lines of Code behind this

Customer – Export::OnAfterGetRecord()
ref.GETTABLE(“<Customer>”);
_Bookmark := FORMAT(ref.RECORDID,0,10);

for making sure that the bookmark is correctly formatted.

The function that we want to expose as a webservice looks like this:

GetCustomersWithin(latitude1 : Decimal;latitude2 : Decimal;longitude1 : Decimal;longitude2 : Decimal;VAR result : XMLport CustomerLocation)
customers.SETRANGE(Latitude, latitude1, latitude2);
customers.SETRANGE(Longitude, longitude1, longitude2);
result.SETTABLEVIEW(customers);

So this is the reason why it is good to remember to have a key on the latitude and longitude fields in the customer table.

I just added this function to the NavMaps codeunit and made sure that the other function (from post 1) is private – meaning that this function is the only function exposed and you need to expose the NavMaps codeunit as a web service in the Web Service table

image

I called it Maps – and this is used in the code below.

Combining things

In the menu you can find code for how to add shapes (pushpins and other things). You can also find code for how to subscribe to events and we are going to do this for two of the events from the mapcontrol (onendzoom and onendpan) as they change the current viewing rectangle of the control.

<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>
<html>
<head>
<title></title>
<meta http-equiv=”Content-Type” content=”text/html; charset=utf-8″ />
/fonta%20href=

var map = null;
var defaultURL = “http://localhost:7047/DynamicsNAV/WS/CRONUS_International_Ltd/&#8221;;
    var XMLPortResultNS = ‘urn:microsoft-dynamics-nav/xmlports/customerlocation’;
var XMLPortResultNode = ‘Customer’;
var resultSet;

    // Event handler for endzoom event
function EndZoomHandler(e) {
UpdatePushPins();
}

    // Event handler for endpan event
function EndPanHandler(e) {
UpdatePushPins();
}

    // Initialize the map
function GetMap() {
map = new VEMap(‘myMap’);

        // center and zoom are passable as parameters
var latitude = parseFloat(queryString(“latitude”, “0”));
var longitude = parseFloat(queryString(“longitude”, “0”));
var zoom = parseInt(queryString(“zoom”, “2”));

        // use normal dashboard
map.SetDashboardSize(VEDashboardSize.Normal);
// load the map
var position = new VELatLong(latitude, longitude);
map.LoadMap(position, zoom, ‘r’, false);
// hook events
map.AttachEvent(“onendzoom”, EndZoomHandler);
map.AttachEvent(“onendpan”, EndPanHandler);
// Place pushpins
UpdatePushPins();
}

    // Update all pushpins on the map
function UpdatePushPins() {
map.DeleteAllShapes();
// Get the view rectangle
var botlft = map.PixelToLatLong(new VEPixel(1, myMap.offsetHeight-1));
var toprgt = map.PixelToLatLong(new VEPixel(myMap.offsetWidth-1, 1));

        // Get customers within rectangle
GetCustomersWithin(botlft.Latitude, toprgt.Latitude, botlft.Longitude, toprgt.Longitude);
if (resultSet != null) {
i = 0;
while (i                 var shape = new VEShape(VEShapeType.Pushpin, new VELatLong(resultSet[i].childNodes[2].text, resultSet[i].childNodes[3].text));
shape.SetTitle(resultSet[i].childNodes[0].text + ” ” + resultSet[i].childNodes[1].text);
shape.SetDescription(”

” +

” +

” +

” +

Contact ” + resultSet[i].childNodes[5].text + “
Phone ” + resultSet[i].childNodes[6].text + “ Sales ” + resultSet[i].childNodes[7].text + “ Profit ” + resultSet[i].childNodes[8].text + “ Open Customer Page

“);
map.AddShape(shape);
i++;
}
}
}
// Helper function for querying parameters to the site
function queryString(parameter, defaultvalue) {
var loc = location.search.substring(1, location.search.length);
var param_value = false;
var params = loc.split(“&”);
for (i = 0; i             param_name = params[i].substring(0, params[i].indexOf(‘=’));
if (param_name == parameter) {
param_value = params[i].substring(params[i].indexOf(‘=’) + 1)
}
}
if (param_value) {
return param_value;
}
else {
return defaultvalue;
}
}

    // Get Base URL
function GetBaseURL() {
return defaultURL;
}

    // Get Customers within specified rectangle by connecting to NAV WebService
function GetCustomersWithin(latitude1, latitude2, longitude1, longitude2) {
resultSet = null;
try {
// Instantiate XMLHTTP object
xmlhttp = new ActiveXObject(“Msxml2.XMLHTTP.6.0”);
xmlhttp.open(“POST”, GetBaseURL() + “Codeunit/Maps”, false, null, null);
xmlhttp.setRequestHeader(“Content-Type”, “text/xml; charset=utf-8”);
xmlhttp.setRequestHeader(“SOAPAction”, “GetCustomersWithin”);

            // Setup event handler when readystate changes
xmlhttp.onreadystatechange = function() {
// Inline function for handling response
if ((xmlhttp.readyState == 4) && (xmlhttp.Status == 200)) {
var xmldoc = xmlhttp.ResponseXML;
xmldoc.setProperty(‘SelectionLanguage’, ‘XPath’);
xmldoc.setProperty(‘SelectionNamespaces’, ‘xmlns:tns=”‘ + XMLPortResultNS + ‘”‘);
resultSet = xmldoc.selectNodes(‘//tns:’ + XMLPortResultNode);
}
}
// Send request
xmlhttp.Send(‘http://schemas.xmlsoap.org/soap/envelope/&#8221;
>’ + latitude1 + ” + latitude2 + ” + longitude1 + ” + longitude2 + ”);
}
catch (e) {
alert(e.message);
}
}


</head>
<body onload=”GetMap();” style=”margin:0; width:100%; height:100%; overflow: hidden”>

</body>
</html>

I will let the code speak for itself and direct attention to some of my other Javascript posts and/or information generally available on Javascript on the Internet.

image

The defaultURL variable in the start should be set to the URL of your web services listener and if you want to have more fields when you hover over a customer on the map, you will need to add these fields to the customerLocation XML port and add them to the SetDescription call in the code.

I have only tried this with the demo data – and of course you could imagine that if you have a very high number of customers the pushpin placement routine would be too slow. If this is the case, we can either limit the rectangle (if it spans too much – then ignore) or we can check the size of the returned and only place pushings for the firs xx number.

Epilog

When I said nothing comes for free, it wasn’t entirely true – this blog post is free, the usage of the information in this post is free include code snippets etc. – so some things are free:-)

I realize that this was a LOT of non-NAV code – but I do think it opens up some very compelling scenarios for integration when we combine the strength of different products using Web Services.

As always the NAV objects and the default.htm from above is available for download here.

In step 3 I will show how we can add an action to the Customer Card for showing the area map for a given customer – and you probably already know how to do this, if you have followed this post carefully.

 

Enjoy

 

Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV

Integration to Virtual Earth – Part 1 (out of 4)

Disclaimer

Please note that this is an example on how to do geocoding and Virtual Earth integration. There are probably a lot of different ways to do the same, and this might not work in all areas of the world. I do however think that there is enough information in the post that people can make it work anywhere and the good thing about the samples and demos I post in my blog is, that they are free to be used as partners and customers see fit. My samples doesn’t come with any warranty and if installed at customer site, the partner and/or customer takes full responsibility.

Following this sample also does not release you from following any license rules of the products used in the blog (note that I don’t know these rules)

Mappoint or Virtual Earth

As you probably know there already is an integration to Mappoint in NAV 2009. This integration makes it possible to open a map for a given customer (or create route description on how to get there). The way it works is, that when you request an online map, a URL is created which will open a map centered on the requested customer. It is a one way integration – meaning that we can see a map and/or calculate routes.

But… – it is a one way integration. Wouldn’t it be cool if we could request NAV for all customers in a range of 10 miles from another customer – display all customers on a map and have information included directly on Virtual Earth with phone numbers, orders and other things.

That is what this is all about…

But in order to do that, we need more information on our customers than just an address, a city and a country, as this information is hard to query. How would NAV know that Coventry is near Birmingham – if we don’t tell it.

The idea is off course to add geocode information to all customers in our customer table.

We can do this the hard way (typing them in), the other “easy” way (create an automation object which does the trick for you).

Latitude and Longitude

I am not (and I wouldn’t be capable of) trying to describe in details what Latitude and Longitude is – if you want this information you should visit

http://en.wikipedia.org/wiki/Latitude

and

http://en.wikipedia.org/wiki/Longitude

Not that it necessarily helps a lot, but there you have it.

A simpler explanation can be found on http://www.worldatlas.com, which is also, where this image is from

image

Click the image to take you directly to the description.

In this map, coordinates are described as degrees, minutes and seconds + a direction in which this is from the center (N, S, E, W).

There are different ways to write a latitude and a longitude – in my samples I will be using decimal values, where Latitude is the distance from equator (positive values are on the northern hemisphere and negative values are on the southern) and Longitude is the distance from the prime meridian (positive values are going east and negative values are going west). This is the way Microsoft Virtual Earth uses latitude and longitude in the API.

Underneath you will find a map with a pushpin in 0,0.

image

Another location (well known to people in the Seattle area) is Latitude = 47.6 and Longitude = -122.33, which on the map would look like:

image

Yes – the Space Needle.

I think this is sufficient understanding to get going.

Preparing your customer table

First of all we need to create two fields in the customer table, which will hold the geocode information of the customer.

image

Set the Decimalplaces for both fields to 6:8 and remember to create a key, including the two fields (else your searches into the customer table will be slow)

image

You also need to add the fields to the Customer Task Page, in order to be able to edit the values manually if necessary.

Virtual Earth Web Services

In order to use the Microsoft Virtual Earth Web Services you need an account. I do not know the details about license terms etc., but you can visit

https://mappoint-css.live.com/MwsSignup

for signing up and/or read about the terms. I do know that an evaluation developer license is free – so you can sign up for getting one of these – knowing of course that you probably cannot use this for your production data – please contact maplic@microsoft.com for more information on this topic.

Having signed up for a developer account you will get an account ID and you will set a password which you will be using in the application working with the Virtual Earth Web Services. This account ID and Password is used in your application when connecting to Web Services and you manage your account and/or password at

https://mappoint-css.live.com/CscV3

You will also find a site in which you can type in your Account ID and password to test whether it is working.

On these sites there are a number of links to Mappoint Web Services – this is where the confusion started for me…

I wrote some code towards Mappoint and I quickly ran into problems having to specify a map source (which identifies the continent in which I needed to do geocoding). I really didn’t want this, as this would require setup tables and stuff like that in my app. After doing some research I found out that Virtual Earth exposes Web Services to the Internet, which are different from Mappoint (I do not have any idea why). A description of the Virtual Earth Web Services API can be found here:

http://msdn.microsoft.com/en-us/library/cc980922.aspx

and a description of the geocode service can be found here:

http://msdn.microsoft.com/en-us/library/cc966817.aspx

and yes, your newly assigned account and password for Mappoint services also works for Virtual Earth Web Services.

The way it works is, that when connecting to Virtual Earth Web Services you need to supply a valid security token. This token is something you request from a different web service and when requesting this token, you specify the number of minutes the token should be valid (Time-To-Live).

Confused?

Creating a COM automation object for geocoding addresses

If you think you have the grasp around the basics of the Virtual Earth Web Services – let’s get going…

First of all – fire up your Visual Studio 2008 SP1 and create a new Class Library (I called mine NavMaps).

Add the CLSCompliant(true) to the AssemblyInfo.cs file (I usually to this after the ComVisible(false) line).

// Setting ComVisible to false makes the types in this assembly not visible
// to COM components.  If you need to access a type in this assembly from
// COM, set the ComVisible attribute to true on that type.
[assembly: ComVisible(false)]
[assembly: CLSCompliant(true)]

After this you need to sign the assembly. Do this by opening properties of project, go to the Signing TAB,  check the “Sign the assembly” checkbox and select new – type in a filename and password protect the key file if you want to (I usually don’t).

Next thing is to create the COM interface and Class – the interface we want is:

[ComVisible(true)]
[Guid(“B1F26FE7-0EA0-4883-BD6A-0398F8D2B139”), InterfaceType(ComInterfaceType.InterfaceIsDual)]
public interface INAVGeoCode
{
string GetLocation(string query, int confidence, ref double latitude, ref double longitude);
}

For the implementation we need a Service Reference to:

http://staging.dev.virtualearth.net/webservices/v1/geocodeservice/geocodeservice.svc?wsdl

called

GeocodeService

Note that the configuration of this Service Reference will be written in app.config – I will touch upon this later.

The implementation could be:

[ComVisible(true)]
[Guid(“9090DF4C-FB24-4a4b-9E49-3924353A6040”), ClassInterface(ClassInterfaceType.None)]
public class NAVGeoCode : INAVGeoCode
{
private string token = null;
private DateTime tokenExpires = DateTime.Now;

    /// <summary>
/// Geocode an address and return latitude and longitude
/// Low confidence is used for geocoding demo data – where the addresses really doesn’t exist:-)
/// </summary>
/// <param name=”query”>Address in the format: Address, City, Country</param>
/// <param name=”confidence”>0 is low, 1 is medium and 2 is high confidence</param>
/// <param name=”latitude”>returns the latitude of the address</param>
/// <param name=”longitude”>returns the longitude of the address</param>
/// <returns>Error message if something went wrong</returns>
public string GetLocation(string query, int confidence, ref double latitude, ref double longitude)
{
try
{
// Get a Virtual Earth token before making a request
string err = GetToken(ref this.token, ref this.tokenExpires);
if (!string.IsNullOrEmpty(err))
return err;

            GeocodeService.GeocodeRequest geocodeRequest = new GeocodeService.GeocodeRequest();

            // Set the credentials using a valid Virtual Earth token
geocodeRequest.Credentials = new GeocodeService.Credentials();
geocodeRequest.Credentials.Token = token;

            // Set the full address query
geocodeRequest.Query = query;
// Set the options to only return high confidence results
GeocodeService.ConfidenceFilter[] filters = new GeocodeService.ConfidenceFilter[1];
filters[0] = new GeocodeService.ConfidenceFilter();
switch (confidence)
{
case 0:
filters[0].MinimumConfidence = GeocodeService.Confidence.Low;
break;
case 1:
filters[0].MinimumConfidence = GeocodeService.Confidence.Medium;
break;
case 2:
filters[0].MinimumConfidence = GeocodeService.Confidence.High;
break;
default:
return “Wrong value for confidence parameter”;
}

            GeocodeService.GeocodeOptions geocodeOptions = new GeocodeService.GeocodeOptions();
geocodeOptions.Filters = filters;

            geocodeRequest.Options = geocodeOptions;

            // Make the geocode request
GeocodeService.IGeocodeService geocodeService = new ChannelFactory<GeocodeService.IGeocodeService>(new BasicHttpBinding(), new EndpointAddress(“http://staging.dev.virtualearth.net/webservices/v1/geocodeservice/GeocodeService.svc&#8221;)).CreateChannel();
GeocodeService.GeocodeResponse geocodeResponse = geocodeService.Geocode(geocodeRequest);

            if (geocodeResponse.Results.Length == 0 || geocodeResponse.Results[0].Locations.Length == 0)
{
return “No locations found”;
}
latitude = geocodeResponse.Results[0].Locations[0].Latitude;
longitude = geocodeResponse.Results[0].Locations[0].Longitude;

            return “”;
}
catch (Exception ex)
{
return ex.Message;
}
}
}

Before we add the last function – GetToken – I would like to draw attention to the line:

GeocodeService.IGeocodeService geocodeService = new ChannelFactory<GeocodeService.IGeocodeService>(new BasicHttpBinding(), new EndpointAddress(“http://staging.dev.virtualearth.net/webservices/v1/geocodeservice/GeocodeService.svc&#8221;)).CreateChannel();

This isn’t normally the way you would instantiate the service class. In fact normally you would see:

GeocodeService.GeocodeServiceClient geocodeService = new GeocodeService.GeocodeServiceClient();

which is simpler, looks nicer and does the same thing – so why bother?

Which configuration file to use?

The primary reason is to avoid using the configuration file. The standard way of instantiating the Service Client is looking for a number of settings in the appSettings section in the config file – and you wouldn’t think that should be a problem – but it is. The problem is that it uses the application configuration file – NOT the DLL config file, and I couldn’t find any way to make it read the DLL config file for these settings.

So if my NavMaps.dll should be accessible from the classic client, I would have to create a finsql.exe.config with the right configuration. Microsoft.Dynamics.Nav.Client.exe.config would be the configuration file for the Roletailored Client (if we are running the automation client side) and I would have to find a way to merge the config settings into the service tier configuration if we are running the automation server side.

So, to avoid all that crap (and severe deployment problems), I instantiate my WCF Client manually through code – meaning that no app.config is necessary.

Requesting a Virtual Earth Security Token

First you need to add a Web Reference to

https://staging.common.virtualearth.net/find-30/common.asmx?wsdl

called

TokenWebReference

The code for the GetToken could look like this:

/// <summary>
/// Check validity of existing security token and request a new
/// Security Token for Microsoft Virtual Earth Web Services if necessary
/// </summary>
/// <param name=”token”>Security token</param>
/// <param name=”tokenExpires”>Timestamp for when the token expires</param>
/// <returns>null if we have a valid token or an error string if not</returns>
private string GetToken(ref string token, ref DateTime tokenExpires)
{
if (string.IsNullOrEmpty(token) || DateTime.Now.CompareTo(tokenExpires) >= 0)
{
// Set Virtual Earth Platform Developer Account credentials to access the Token Service
TokenWebReference.CommonService commonService = new TokenWebReference.CommonService();
commonService.Credentials = new System.Net.NetworkCredential(“<your account ID>”, “<your password>”);

        // Set the token specification properties
TokenWebReference.TokenSpecification tokenSpec = new TokenWebReference.TokenSpecification();
IPAddress[] localIPs = Dns.GetHostAddresses(Dns.GetHostName());
foreach (IPAddress IP in localIPs)
{
if (IP.AddressFamily == System.Net.Sockets.AddressFamily.InterNetwork)
{
tokenSpec.ClientIPAddress = IP.ToString();
break;
}
}
// Token is valid an hour and 10 minutes
tokenSpec.TokenValidityDurationMinutes = 70;

        // Get a token
try
{
// Get token
token = commonService.GetClientToken(tokenSpec);
// Renew token in 1 hour
tokenExpires = DateTime.Now.AddHours(1);
}
catch (Exception ex)
{
return ex.Message;
}
}
return null;
}

Note that I am giving the token a TTL for 70 minutes – but renew after 60 – that way I shouldn’t have to deal with tokens being expired.

<your account ID> should be replaced by your account ID and <your password> should be replaced with your password.

Using the geocode automation object

Having build the assembly we need to put it to play.

After building the assembly we need to place it in the “right” folder – but what is the right folder?

It seems like we have 4 options

  1. In the Classic folder
  2. In the RoleTailored Client folder
  3. In the Service Tier folder
  4. Create a Common folder (next to the Classic, RoleTailored and Service Tier folders) and put it there

Let me start by excluding the obvious choice, the Service Tier folder. The reason is, that if you have multiple Service Tiers, they will all share the same COM automation object and you don’t really know which directory the assembly is read from (the one where you did the last regasm). You could accidently delete the Service Tier in which the registered assembly is located and thus end up having denial of service.

RoleTailored Client folder also seems wrong – there is absolutely no reason for running this object Client side – it should be on the Service Tier and there might not be a RoleTailored Client on the Service Tier. The same is case with the Classic folder – so I decided to create a Common folder and put the DLL there. Another reason for selecting the Common folder is, that you can install it on the Client (for being able to run it in the Classic Client) in a similar way as on the server.

Your situation might be different and you might select a different option.

After copying the DLL to the Common folder, we need to register the DLL and make it available as a COM automation object.

C:\Windows\Microsoft.NET\Framework\v2.0.50727\regasm NAVMaps.dll /codebase /tlb

Is the command to launch.

Creating a codeunit for geocoding your customers

What I normally do in situations like this is, to create a codeunit, which initializes itself when you Run it.

In this case Running the codeunit should run through all customers and geocode them.

OnRun()
IF cust.FIND(‘-‘) THEN
BEGIN
REPEAT
err := UpdateLatitudeAndLongitude(cust);
IF (err <> ”) THEN
BEGIN
IF NOT CONFIRM(‘Customer ‘+cust.”No.”+ ‘ – Error: ‘+err + ‘ – Continue?’) THEN EXIT;
END;
UNTIL cust.NEXT = 0;
END;

Whether or not you want an error here or not is kind of your own decision.

The function, that does the job looks like:

UpdateLatitudeAndLongitude(VAR cust : Record Customer) error : Text[1024]
CREATE(NavMaps, TRUE, FALSE);
country.GET(cust.”Country/Region Code”);
query := cust.Address+’, ‘+cust.”Address 2″+’, ‘+cust.City+’, ‘+’, ‘+cust.”Post Code”+’, ‘+country.Name;
error := NavMaps.GetLocation(query, 2, cust.Latitude, cust.Longitude);
IF (error = ”) THEN
BEGIN
cust.MODIFY();
END ELSE
BEGIN
query := cust.City + ‘, ‘ + country.Name;
error := NavMaps.GetLocation(query, 0, cust.Latitude, cust.Longitude);
IF (error = ”) THEN
BEGIN
cust.MODIFY();
END;
END;

Local variables looks like this

image

Now we can discuss whether this is the right way to go around it – problem for me is, that the demodata are not real addresses – meaning that the street names are fine, city names are fine – but the street and number doesn’t exist in the city.

So – what I do here is to start building a query with Address, City, Post code, Country Name – and tell Virtual Earth to make a High confidence search – this will return the location of correct addresses. In the demo database, this returns absolutely nothing. Next thing is to just give me the location of the city in the country – whether you want to do this for your customer table is kind of your own decision, but it works for the demo data.

BTW – i tried this with addresses in Denmark (my former) and the US (my current) – and the format seems to suit Virtual Earth in these countries, but I didn’t try a lot of other addresses. The advantage of using the query is really to give Virtual Earth the freedom to interpret and look at the address – and it does a pretty good job. If anybody experiences problems with this in certain countries – please let me know (with a suggestion as to what should be changed) and I will include this.

Can’t make it work?

If (for some reason) this code doesn’t work for you – but you really want to get on with the next two parts of this walkthrough, I have included a table here with Latitudes and Longitudes for all the customers in the W1 database.

Copy the entire table into a clean excel spreadsheet and use Edit In Excel to copy the data from the two columns to the two columns in your customer table in one go and save it back to NAV. Note that the Latitude and Longitude is only available in Excel if you

  1. Have added the fields to the customer card
  2. Updated the Web Reference in the Edit In Excel project to the customer card after doing step 1 (if you already had Edit In Excel running)

If you don’t have the Edit In Excel – you can find it here http://blogs.msdn.com/freddyk/archive/tags/Excel/default.aspx

 

No Name Latitude Longitude
01121212 Spotsmeyer’s Furnishings 25.72898470 -80.23741968
01445544 Progressive Home Furnishings 41.86673200 -87.70100800
01454545 New Concepts Furniture 33.77397700 -84.38731800
01905893 Candoxy Canada Inc. 48.38170029 -89.24547985
01905899 Elkhorn Airport 49.97715327 -101.23461867
01905902 London Candoxy Storage Campus 42.98689485 -81.24621458
10000 The Cannon Group PLC 52.47865520 -1.90859489
20000 Selangorian Ltd. 52.42190337 -1.53778508
20309920 Metatorad Malaysia Sdn Bhd 3.08329985 101.64999984
20312912 Highlights Electronics Sdn Bhd 3.15021023 101.71284467
20339921 TraxTonic Sdn Bhd 1.54907294 110.34416981
21233572 Somadis 34.01504517 -6.83272026
21245278 Maronegoce 33.60242486 -7.61274353
21252947 ElectroMAROC 33.91666643 -6.91666670
27090917 Zanlan Corp. -26.35520540 27.40158677
27321782 Karoo Supermarkets -29.11835074 26.22492447
27489991 Durbandit Fruit Exporters -29.83637005 30.94218850
30000 John Haddock Insurance Co. 53.47962007 -2.24880964
31505050 Woonboulevard Kuitenbrouwer 52.14019530 6.19148992
31669966 Meersen Meubelen 51.98542312 5.90462968
31987987 Candoxy Nederland BV 52.37311967 4.89319481
32124578 Nieuwe Zandpoort NV 51.17786638 4.83266278
32656565 Antarcticopy 51.22171506 4.39739518
32789456 Lovaina Contractors 50.88170014 4.71750006
33000019 Francematic 48.81669022 1.94925517
33002984 Parmentier Boutique 48.85692470 2.34120972
33022842 Livre Importants 48.90484169 2.81284869
34010100 Libros S.A. 41.38566770 2.16993861
34010199 Corporación Beta 39.43432257 -0.38737597
34010602 Helguera industrial 40.41576270 -3.70385108
35122112 Bilabankinn 64.11111474 -21.90939903
35451236 Gagn &Gaman 64.92900006 -18.96200001
35963852 Heimilisprydi 64.13533777 -21.89521417
38128456 MEMA Ljubljana d.o.o. 46.05124690 14.50306222
38546552 EXPORTLES d.o.o. 46.05124690 14.50306222
38632147 Centromerkur d.o.o. 46.55813813 15.65098330
40000 Deerfield Graphics Company 51.86390832 -2.24978395
41231215 Sonnmatt Design 47.42380030 8.55140001
41497647 Pilatus AG 47.05957649 8.30785449
41597832 Möbel Scherrer AG 47.69385177 8.63503763
42147258 BYT-KOMPLET s.r.o. 49.03722882 17.81008579
42258258 J &V v.o.s. 48.98017220 17.21433230
42369147 PLECHKONSTRUKT a.s. 48.85557219 16.05438888
43687129 Designstudio Gmunden 47.91861087 13.79814081
43852147 Michael Feit – Möbelhaus 47.58900024 14.13999975
43871144 Möbel Siegfried 48.16780201 16.35428691
44171511 Zuni Home Crafts Ltd. 52.49931332 -2.13111123
44180220 Afrifield Corporation 51.27380021 0.52508533
44756404 London Light Company 52.20986970 0.11156514
45282828 Candoxy Kontor A/S 56.15704469 10.20700961
45282829 Carl Anthony 56.15704469 10.20700961
45779977 Ravel Møbler 55.31117991 10.79238497
45979797 Lauritzen Kontormøbler A/S 57.03462996 9.92748722
46251425 Marsholm Karmstol 56.67225949 12.85753034
46525241 Konberg Tapet AB 57.78593438 14.22523925
46897889 Englunds Kontorsmöbler AB 58.59301685 16.17726378
47523687 Slubrevik Senger AS 59.85794865 10.47694914
47563218 Klubben 59.91503946 10.56067966
47586954 Sjøboden 71.07307523 24.70469333
49525252 Beef House 51.21562980 6.77605525
49633663 Autohaus Mielberg KG 53.55334528 9.99244496
49858585 Hotel Pferdesee 50.04764177 8.57851513
50000 Guildford Water Department 51.23708010 -0.57051592
60000 Blanemark Hifi Shop 51.51777074 -0.15552994
61000 Fairway Sound 51.50632493 -0.12714475
62000 The Device Shop 51.50632493 -0.12714475
IC1020 Cronus Cardoxy Sales 56.10199973 9.55599993
IC1030 Cronus Cardoxy Procurement 53.55334528 9.99244496

Note that some of the customers have the same latitude, longitude – this is due to the demo data.

Next steps

Ok admitted – that was a long post – but hopefully it was helpful (and hopefully you can make it work)

As usual – you can download the objects and the Visual Studio solution – and please do remember that this is NOT a solution that is installable and will work all over – you might have issues or problems and I would think the best place to post questions is mibuso (where my answer will get read by more people).

In the next post I will create a website, which connects to NAV Web Services to get customer location information – and in the third post I will show how to create an action in the customer List and Card to open up an area map centered around a specific customer.

So… – after step 2 you will see this

image

Stay tuned!

The forth post in this series is a surprise – and will require NAV 2009 SP1 in order to work – so stay tuned for this one…

 

Enjoy and good luck

 

Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV

The Customer MAP demo

My newest demo was shown at the NAV Partner Keynote and also on the NAV Customer General session.

I also did a very short walkthrough of the demo at the NAV06 concurrent session at Convergence – and I promised a number of people that I would add a walkthrough of how the demo is done on my blog.

This post is only an appetizer – the real stuff is going to be out here over the next couple of days – and will be a 3 step walkthrough.

  1. How to geocode the customers in your customer database using a Microsoft Virtual Earth Web Service
  2. How to use this geocode information from an intranet application using Microsoft Virtual Earth
  3. Adding an action to the Customer page to view other customers in the area

A screenshot of the demo can be seen here

image

and basically what happens is that I added a Latitude and a Longitude field to the Customer table – and created a small automation object, which can geocode adresses. Having this information in the customer table enables a lot of cool scenarios – the above one is just the first simple one, that springs into mind.

Beside the geocoding the above solution requires a Web Service codeunit to be exposed and a small intranet application.

The Web Service codeunit contains a function that returns all the customers in a rectangle of the world (given by the bottom left and the top right latlong coordinates) – and the small intranet application just calls this webservice every time you zoom or pan the map.

Stay tuned

Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV