Bill Ryan's Other Blog

This is cross-posted from my company blog…

Microsoft’s Dynamics CRM is an amazing tool. As I learn more about it, I increasingly realize it’s an even more amazing platform.  My background has been exclusively in development and I still catch myself leaning toward performing tasks outside of CRM even though the platform has been more than capable of doing anything I’ve needed to accomplish (and in most cases, making it a whole lot easier). Because CRM’s integration story is such a good one, one way I’ve taught myself more about the product is by integrating other technologies with it to come up with cool implementations (at least I think they’re cool) that aren’t the sorts of things we typically think of when we think of CRM. Ultimately,this means that “we can’t do it” is seldom an answer you should hear with respect to CRM. If CRM doesn’t support something out of the box, you almost certainly can extend CRM to do it   So I’m planning a 5 part series (I suspect it will grow much larger but that’s all I’m promising for now) on doing things with CRM that illustrate how easy it is to extend functionality to handle several non-traditional scenarios.  Here’s the series breakdown (I’ll change the text to hyperlinks as the articles are posted):

1. CRM and Windows Live Part I
2. CRM and Windows Live Part II
3. CRM and Sharepoint
4. CRM and Twitter
5. CRM and Facebook

So without further ado…

One of the stated purposes of a CRM system is tracking relationships of various sorts. These might be customers, suppliers, leads, distributors or all of the above.  The point is, most of us use CRM to manage critical business relationships.  While telecommuting and LiveMeeting scenarios are increasingly popular, there’s still a lot of face to face contact involved in the sales process and course of operations for most companies.

Tracking addresses is one of the most basic functions we perform with CRM.  As such we have most of the information we need to figure out how to visit a client. But knowing an address is only part of the story. Having directions is often the other part. 

To get directions, we often use our vehicle navigation systems if we have them, but not everyone has a navigation system and more often than not, people turn to Virtual Earth, Google Maps, Mapquest etc to get directions.  I’m going to show you a basic implementation of getting directions from my office to a client site (or several client sites) using Windows Live and Virtual Earth.

In order to do much with mapping software, you’ll typically need Latitude and Longitude coordinates of the address.  While we normally keep addresses stored in CRM, we don’t typically keep Latitude/Longitude  in there, although I’m going to show you an easy way to get that done too.

The first thing you need to do if you haven’t already done so is register for a Live developer account. Parts of the Virtual Earth API can be accessed exclusively through web services, other parts require certain libraries. For this example, you only need the Web Services portion of the Virtual Earth API.

To avoid reinventing the wheel, please take a look at this Getting Started article on Virtual Earth which I’ll use parts of for this article.  First, add a web reference to the Geocoding service, the Routing Service and the Staging Token service. Next, create a Web reference to the .Crm Service (CrmService). For convenience, I typically make a Services class and hold each instance of my web services inside of it coupled with any authentication information that’s needed and anything that needs retrieved from configuration settings. That way it’s easy to reference them and all calls to the service are treated consistently. For the Crm Service, a typical implementation looks like this:

public staticCrmSdk.CrmServiceServiceInstance
{
  get
  {
     if (_serviceInstance == null)
     {
     _serviceInstance = new CrmService();
     _serviceInstance.Url = "http://YOUR_IP:YOUR_PORT/MSCrmServices/2007/CrmService.asmx";
     CrmAuthenticationTokensessionToken = new CrmAuthenticationToken();
     sessionToken.AuthenticationType = 0;
     sessionToken.OrganizationName = "YOUR_ORG";�
     _serviceInstance.Credentials = new NetworkCredential("USERNAME", "PASSWORD", "DOMAIN");
     // Can also use DefaultCredentials instead, which will cause the user to be prompted for their credentials
   // Simply uncomment the line below and comment out the line above where .Credentials are set      to use Default Credentials
   // _serviceInstance.Credentials = CredentialCache.DefaultCredentials;     _serviceInstance.CrmAuthenticationTokenValue = sessionToken;     }
    return_serviceInstance;
   }
}
.....Now, you'll need to create the following methods (again, for brevity I'm going to leave them out here but they are all available at the Getting Started link above 

1. SetToken()
2. CreateRoute()
3. GeocodeAddress()

I wrote the following method to simply go to the CrmService and pull back all of my existing accounts. Depending on exactly what you want to accomplish, this method will obviously change:

public static ListGetAccounts()
{
     ListCurrentAccounts = null;      //Collection that holds the name of the columms that should be retrieved
     ColumnSet columnsToInclude = new ColumnSet();
     columnsToInclude.Attributes = new String[] { "accountid", "name", "address1_line1",  "address1_city", "address1_stateorprovince", "address1_postalcode" };     //Create a query expression to retrieve the fields
     QueryExpression mainQuery = new QueryExpression();
     mainQuery.EntityName = CrmSdk.EntityName.account.ToString();
     mainQuery.ColumnSet = columnsToInclude;     //Retrieve opportunites and store in BusinessEntityCollection
     BusinessEntityCollection allAccounts = null;
     try
     {
       allAccounts = ServiceInstance.RetrieveMultiple(mainQuery);
     }
     catch (SoapException soapError)
     {
       MessageBox.Show(soapError.ToString());
     }
     catch (WebException ex) �
     {
       //Just eat it for now
       MessageBox.Show(ex.ToString());
     }
     if (allAccounts != null && allAccounts.BusinessEntities.Length > 0)
     {
       CurrentAccounts = new List();
     }
     foreach (BusinessEntity eachAccount in allAccounts.BusinessEntities)
     {
       CurrentAccounts.Add((eachAccount as account));
     }

     StringResults = CreateRoute(textInput.Text);
     labelResults.Text = Results ;


So let’s say you put in 10 different locations (10 Latitude/Longitude pairs separated by semicolons), you’d get a string of formatted steps showing you the directions on how to get to each leg. It’s amazingly easy to use.

I would posit though, that repeatedly looking up addresses’ Latitude/Longitude  is kind of wasteful in most cases (unless they change constantly which is seldom the case). Since we now have Latitude and Longitude attributes for an account, why not just store them there?   This could be accomplished a few ways. One way would be to create a Plug-in that each time an Account was Created or Updated, simply grabbed the address (if available), retrieved the Latitude/Longitude , set those two properties and saved the record. Using the Replace function I included above, your result would be something like “47.608, -122.337″   You could use a call like this to get the Latitude/Longitude values which you could in turn set the current entity’s address1_latitude and address1_longitude properties to:

StringFormattedAddress = String.Format("{0}, {1}, {2} {3}\r\n", act.address1_line1, act.address1_city,
act.address1_stateorprovince, act.address1_postalcode);

String[] LatLonGroup = GeocodeAddress(FormattedAddress).Replace("Latitude: ", String.Empty).Replace("Longitude: ", ",").Replace("\n", String.Empty).Split(',');
act.address1_latitude = new CrmFloat();
act.address1_latitude.Value = Convert.ToDouble(LatLonGroup[0]);
act.address1_longitude = new CrmFloat();
act.address1_longitude.Value = Convert.ToDouble(LatLonGroup[1]);


If you had the Plugin fire on both Create and Update, you’d catch the updates and each time the record changed, you’d go and get the Latitude/Longitude combination for those attributes and could be relatively sure things were always current.  If you had a failure (which is certainly a possibility when calling external web services) you wouldn’t want to stop the record from being created or updated though, so this approach may not be as strong in reality as it is in theory.
 

To address that, you could create a workflow that ran nightly. This workflow could go through each of your Accounts and set the corresponding properties.  The code to accomplish this would look like the following (assuming you used the GetAccounts method I illustrated earlier

SetToken();
ListCurrentAccts = GetAccounts();
foreach (account act inCurrentAccts)
{
   StringFormattedAddress = String.Format("{0}, {1}, {2} {3}\r\n", act.address1_line1, act.address1_city,
   act.address1_stateorprovince, act.address1_postalcode);
   String[] LatLonGroup = GeocodeAddress(FormattedAddress).Replace("Latitude: ",String.Empty).Replace("Longitude: ", ",").Replace("\n", String.Empty).Split(',');
   act.address1_latitude = new CrmFloat();
   act.address1_latitude.Value = Convert.ToDouble(LatLonGroup[0]);
   act.address1_longitude = new CrmFloat();
   act.address1_longitude.Value = Convert.ToDouble(LatLonGroup[1]);
   ServiceInstance.Update(act);
}


This approach isn’t perfect either.  That’s because it would update every single record and chances are, at best only a few addresses would likely have changed each day. Depending on your licensing model, it would consume tokens and in most cases that would be unnecessary. Those concerns are for another discussion, this one just focuses on how to do it.

Since we’re talking about convenience, this overall approach is still lacking. Although we now  have the  Latitude/Longitude stored in the record, the user would still have to ostensibly hit a button we put on the account form to have the directions given to them. We’d get the directions and show them in another window, or just take them to the Virtual Earth page and show that information there.  To me, that’s still kind of lame.  So what do I propose?  

I’d recommend creating an additional attribute for the account entity called DirectionsFromOffice. Once the attribute is created, you could simply add the following line of code to either of the above blocks (the one for an individual update or the one for updating each record):  

 DirectionsFromOffice = CreateRoute(FormattedAddress);
  // or
  act.DirectionsFromOffice = CreateRoute(String.Format("{0}, {1}", act.address1_latitude, act.address1_longitude));


The total distance is available in the results and can easily be extracted so you could create another field called DistanceFromOffice and using the same approach as above, set it while you’re setting everything else. This has the added benefit of giving someone a good approximation of how much distance they’d be travelling in a given day as they went about visiting customers.  The main shortcoming with the approach that stores directions in the record is that it’s likely to get obsolete (addresses might not change very often but due to traffic, construction etc the ‘best route’ might. ClearFlow is just one mechanism to check traffic that Virtual Earth offers). It also doesn’t do much for you if you had 5 clients you were visiting that day since each set of directions would show you how to get from your office to the client site, not from one client site to another.  To that end, I think there’s probably no way around having a button and another form to show the results on to make this useful for most cases.  But remember, to get directions for multiple legs, all you need to do is grab the separate the Latitude/Longitude  for each customer with a semicolon and call the CreateRoute method. If the Latitude/Longitude  are already in place, it would be trivial to get all of the values of the customers on your trip in one place, add a semicolon between them and displaying the results.  You could grab the search results on a search form for instance or use multi-select on a grid.  Each business will have different needs and out of the box, this should be a good place to start.

In the next part of this series, I’m going to walk through using a phsyical map and pushpins  You’ll be shocked how easy it is to do that once you have the above code in place. [tags]Virtual Earth, Windows Live, CRM [/tags]