Geocode Addresses in Dynamics CRM

• Download demo project - 24.2 Kb
• Download source - 9.2 Kb

Introduction

In my previous article on How to Use Geocoding Web Services to Determine the Congressional District for an Address, the code can be used to geocode addresses in Dynamics CRM.  This article will show you how to connect to Dynamics CRM, customize and query the Customer Address entity, and update CRM data with a console application.

 

Background

Current Dynamics CRM users with System Administrator or System Customizer privileges should be able to implement the changes and execute the code described in this article.  Unfortunately, if you do not have access or cannot be granted sufficient access, I do not know of any virtual labs with Dynamics CRM and Microsoft is no longer offering free trials of CRM Online.

CRM Online currently offers a Geocode feature but it does not include the Congressional District information.

 

Dynamics CRM

By default, the Customer Address entity in Dynamics CRM contain the fields for Latitude and Longitude so if you want to create new fields for Congressional District information, you can created fields that resemble these:

• Congressional District Name (Single Line of Text, 200 maximum length)
• Congressional District Number (Single Line of Text, 100 maximum length)
• Session of Congress (Single Line of Text, 100 maximum length)

 

CRM Publisher

Customizations to Dynamics CRM should be contained in a Solution and preferably with an appropriate Publisher.  Note: the Prefix used in this demo is "xrmatic".  If the Congressional District fields are not used, be sure to omit them from the source code.

 

Using the code

All of the source code mentioned in the article has been included in the downloadable materials.  The code has been tested on Dynamics CRM 2011, 2013, 2015, 2016 and CRM Online.  The demo project was built and tested using Visual Studio 2015 SP3 but there is no reason why the source code should not work in earlier versions of Visual Studio and .NET 4.5.

The following NuGet packages were used in the demo project.  Note: Please be aware of the .NET Framework dependencies for your version of Dynamics CRM SDK.

• Json.NET (v.9.0.1)
• Microsoft Dynamics CRM 2013 SDK Core Assemblies (v6.0.4)
• Microsoft Dynamics CRM 2013 SDK Extensions (v6.0.4)

References to the CrmConnectors and GeocodeConsole need to be added in Visual Studio.

Source Code

Geocode.cs

Two additional properties were added to store the Congressional District Number and the Session of Congress (e.g. 115).

// Congressional District Number
public string CongressionalDistrictNumber { get; set; }
// Session of Congress
public int SessionOfCongress { get; set; }

Census.cs

If the additional properties are added to the Geocode class, the GeocodeAddress() method should to be updated as well.

public Geocode<Census> GeocodeAddress(string street, string city = "", string state = "", string zip = "")
{
    Census json = null;
    var geocode = new Geocodee<Census>();

    HttpResponseMessage response = geocode.GetResponse(this.apiUrl, this.GetUrlParameters(street, city, state, zip));
    using (response)
    {
        if (response.IsSuccessStatusCode)
        {
            var data = response.Content.ReadAsStringAsync().Result;
            json = JsonConvert.DeserializeObject<Census>(data);

            if (json != null && json.result.addressMatches.Length > 0)
            {
                geocode.Latitude = json.result.addressMatches[0].coordinates.y;
                geocode.Longitude = json.result.addressMatches[0].coordinates.x;
                geocode.CongressionalDistrictName = json.result.addressMatches[0].geographies.congressionalDistricts[0].NAME;
                        
                // Congressional District Number
                geocode.CongressionalDistrictNumber = json.result.addressMatches[0].geographies.congressionalDistricts[0].BASENAME;
                // Session of Congress
                geocode.SessionOfCongress = json.result.addressMatches[0].geographies.congressionalDistricts[0].CDSESSN;
            }
        }
        return geocode;
    }
}

Geocodio.cs

Similar changes to the GeocodeAddress() method need to be made in the Geocodio class.

public Geocode<Geocodio> GeocodeAddress(string address)
{
    Geocodio json = null;
    var geocode = new Geocode<Geocodio>();

    HttpResponseMessage response = geocode.GetResponse(this.apiUrl, this.GetUrlParameters(address));

    using (response)
    {
        if (response.IsSuccessStatusCode)
        {
            var data = response.Content.ReadAsStringAsync().Result;

            json = JsonConvert.DeserializeObject<Geocodio>(data);
            if (json != null)
            {
                geocode.Latitude = json.results[0].location.lat;
                geocode.Longitude = json.results[0].location.lng;
                geocode.CongressionalDistrictName = json.results[0].fields.congressional_district.name;

                // Congressional District Number
                geocode.CongressionalDistrictNumber = json.results[0].fields.congressional_district.district_number;
                // Session of Congress
                geocode.SessionOfCongress = int.Parse(json.results[0].fields.congressional_district.congress_number);
            }
        }
        return geocode;
    }
}

LatLon.cs

Similar changes to the GeocodeAddress() method need to be made in the LatLon class.

public Geocode<LatLon> GeocodeAddress(string address)
{
    LatLon json = null;
    var geocode = new Geocode<LatLon>();

    HttpResponseMessage response = geocode.GetResponse(this.apiUrl, this.GetUrlParameters(address));

    using (response)
    {
        if (response.IsSuccessStatusCode)
        {
            var data = response.Content.ReadAsStringAsync().Result;

            json = JsonConvert.DeserializeObject<LatLon>(data);
            if (json != null)
            {
                geocode.Latitude = json.lat;
                geocode.Longitude = json.lon;
                geocode.CongressionalDistrictName = json.congressional_district.name;

                // Congressional District Number
                geocode.CongressionalDistrictNumber = json.congressional_district.district_number.ToString();
                // Session of Congress
                geocode.SessionOfCongress = int.Parse(json.congressional_district.congress_number);
            }
        }
        return geocode;
    }
}

Program.cs

The main program connects to the Dynamics CRM instance with methods provided by the CRM SDK.  In the demo project provided for this article, I used a separate project named CrmConnectors to handle CRM connections to retrieve the OrganizationService object.  See the section on CrmConnectors for more details.

The GetCrmAddresses() method to returns an EntityCollection containing CRM Addresses to geocode.  The loop through the EntityCollection executes the GeocoodeAddress() method.  Finally, the address Entity fields are updated in Dynamics CRM.

using GeocodeConsole;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using System;

namespace GeocodeCrmAddressesConsole
{
    class Program
    {
        static void Main(string[] args)
        {
            // Connect to CRM
            var conn = new CrmConnectors.Crm.Connect();

            // Get CRM Organization Service (On-Premise)
            IOrganizationService service = conn.Service(CrmConnectors.Environment.OnPrem);

            // Get CRM Addresses
            EntityCollection addressEC = GetCrmAddresses(service);

            // Census Bureau
            var census = new Geocode<Census>();
            census.Service = new Census();

            foreach (Entity address in addressEC.Entities)
            {
                Console.WriteLine(address.GetAttributeValue<string>("line1") + " " +
                    address.GetAttributeValue<string>("line2") + " " +
                    address.GetAttributeValue<string>("city") + " " +
                    address.GetAttributeValue<string>("county") + " " +
                    address.GetAttributeValue<string>("stateorprovince") + " " +
                    address.GetAttributeValue<string>("postalcode") + " " +
                    address.GetAttributeValue<string>("country")
                    );

                // Geocode Address
                var geocodeCensus = census.Service.GeocodeAddress(
                    address.GetAttributeValue<string>("line1") + " " + address.GetAttributeValue<string>("line2"),
                    address.GetAttributeValue<string>("city"),
                    address.GetAttributeValue<string>("stateorprovince"),
                    address.GetAttributeValue<string>("postalcode")
                    );

                if (geocodeCensus != null)
                {
                    Console.WriteLine(geocodeCensus.Latitude + " : " +
                        geocodeCensus.Longitude + " = " +
                        geocodeCensus.CongressionalDistrictName
                        );

                    // Update address Entity fields
                    address["latitude"] = geocodeCensus.Latitude;
                    address["longitude"] = geocodeCensus.Longitude;
                    //--- Custom fields for Congressional District ---//
                    address["xrmatic_congressionaldistrictname"] = geocodeCensus.CongressionalDistrictName;
                    address["xrmatic_congressionaldistrictnumber"] = geocodeCensus.CongressionalDistrictNumber;
                    address["xrmatic_sessionofcongress"] = Int32.Parse(geocodeCensus.SessionOfCongress.ToString()).ToString();

                    // Update Customer Address (Late Bound)
                    service.Update(address);
                }
            }
        }

        internal static ColumnSet GetColumnSet()
        {
            return new ColumnSet(
                "line1",
                "line2",
                "city",
                "stateorprovince",
                "postalcode",
                "country"
                );
        }

        internal static EntityCollection GetCrmAddresses(IOrganizationService service)
        {
            QueryExpression addressQE = new QueryExpression();
            addressQE.EntityName = "customeraddress";
            // Get the list of Customer Address fields (only needed for Console output)
            addressQE.ColumnSet = GetColumnSet();

            FilterExpression addressFE = new FilterExpression
            {
                FilterOperator = LogicalOperator.And,
                Filters =
                    {
                        new FilterExpression {
                            FilterOperator = LogicalOperator.And,
                            Conditions = {
                                new ConditionExpression("line1", ConditionOperator.NotNull),
                                new ConditionExpression("city", ConditionOperator.NotNull),
                                new ConditionExpression("postalcode", ConditionOperator.NotNull),
                                new ConditionExpression("latitude", ConditionOperator.Null),
                                new ConditionExpression("longitude", ConditionOperator.Null)
                            }
                        },
                        new FilterExpression {
                            FilterOperator = LogicalOperator.Or,
                            Conditions = {
                                //--- Custom fields for Congressional District ---//
                                new ConditionExpression("xrmatic_congressionaldistrictname", ConditionOperator.Null),
                                new ConditionExpression("xrmatic_sessionofcongress", ConditionOperator.BeginsWith, "114"),
                                new ConditionExpression("country", ConditionOperator.Equal, "U.S."),
                                new ConditionExpression("country", ConditionOperator.Equal, "U.S.A."),
                                new ConditionExpression("country", ConditionOperator.Equal, "US"),
                                new ConditionExpression("country", ConditionOperator.Equal, "USA"),
                                new ConditionExpression("country", ConditionOperator.Equal, "United States"),
                                new ConditionExpression("country", ConditionOperator.Equal, "United States of America"),
                                new ConditionExpression("createdon", ConditionOperator.OlderThanXMonths, 3),
                                new ConditionExpression("modifiedon", ConditionOperator.OlderThanXMonths, 3)
                            }
                        }
                    }
            };

            addressQE.Criteria = addressFE;
            // Prevent record locking
            addressQE.NoLock = true;
            addressQE.Distinct = false;

            // Limit the number of results (Testing)
            addressQE.TopCount = 3;
            
            EntityCollection addressEC = service.RetrieveMultiple(addressQE);

            return addressEC;
        }
    }
}

Alternatively, the EntityCollection in the GetCrmAddresses() method can be written with a FetchXml string.  The FetchXml string can be exported from an Advanced Find search in Dynamics CRM.

Note: Before you copy and paste the exported version of FetchXml from Dynamics CRM, be sure to find/replace the double quotes with single quotes.  Also, don't forget to add the "no-lock='true'" attribute to the root <fetch> node and adjust the "count='3'" attribute to limit the results during testing.

internal static EntityCollection GetCrmAddresses(IOrganizationService service)
{
    string fetchXml = @"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false' no-lock='true' count='3'>
    <entity name='customeraddress'>
    <attribute name='line1' />
    <attribute name='city' />
    <attribute name='postalcode' />
    <attribute name='customeraddressid' />
    <attribute name='stateorprovince' />
    <attribute name='xrmatic_sessionofcongress' />
    <attribute name='modifiedon' />
    <attribute name='longitude' />
    <attribute name='latitude' />
    <attribute name='createdon' />
    <attribute name='county' />
    <attribute name='country' />
    <!--//--- Custom fields for Congressional District ---//-->
    <attribute name='xrmatic_congressionaldistrictnumber' />
    <attribute name='xrmatic_congressionaldistrictname' />
    <order attribute='stateorprovince' descending='false' />
    <order attribute='city' descending='false' />
    <filter type='and'>
    <filter type='and'>
    <filter type='and'>
    <condition attribute='line1' operator='not-null' />
    <condition attribute='city' operator='not-null' />
    <condition attribute='postalcode' operator='not-null' />
    <condition attribute='latitude' operator='null' />
    <condition attribute='longitude' operator='null' />
    </filter>
    <filter type='or'>
    <!--//--- Custom fields for Congressional District ---//-->
    <condition attribute='xrmatic_congressionaldistrictname' operator='null' />
    <condition attribute='xrmatic_sessionofcongress' operator='like' value='114%' />
    <condition attribute='country' operator='eq' value='U.S.' />
    <condition attribute='country' operator='eq' value='U.S.A.' />
    <condition attribute='country' operator='eq' value='US' />
    <condition attribute='country' operator='eq' value='USA' />
    <condition attribute='country' operator='eq' value='United States' />
    <condition attribute='country' operator='eq' value='United States of America' />
    <condition attribute='createdon' operator='olderthan-x-months' value='3' />
    <condition attribute='modifiedon' operator='olderthan-x-months' value='3' />
    </filter>
    </filter>
    </filter>
    </entity>
    </fetch>";

    EntityCollection addressEC = service.RetrieveMultiple(new FetchExpression(fetchXml));

    return addressEC;
}

The QueryExpression in the GetCrmAddresses() method can also be written as such:

internal static EntityCollection GetCrmAddresses(IOrganizationService service)
{
    QueryExpression addressQE = new QueryExpression();

    addressQE.EntityName = "customeraddress";
    // Get the list of Customer Address fields (only needed for Console output)
    addressQE.ColumnSet = GetColumnSet();

    FilterExpression addressFE = new FilterExpression();
    addressFE.FilterOperator = LogicalOperator.And;

    // Child FilterExpression 1
    FilterExpression addressFE1 = new FilterExpression();
    addressFE1.FilterOperator = LogicalOperator.And;

    // line1
    ConditionExpression addressCE1 = new ConditionExpression();
    addressCE1.AttributeName = "line1";
    addressCE1.Operator = ConditionOperator.NotNull;
    addressFE1.Conditions.Add(addressCE1);

    // city
    ConditionExpression addressCE2 = new ConditionExpression();
    addressCE2.AttributeName = "city";
    addressCE2.Operator = ConditionOperator.NotNull;
    addressFE1.Conditions.Add(addressCE2);

    // postalcode
    ConditionExpression addressCE3 = new ConditionExpression();
    addressCE3.AttributeName = "postalcode";
    addressCE3.Operator = ConditionOperator.NotNull;
    addressFE1.Conditions.Add(addressCE3);

    // latitude
    ConditionExpression addressCE4 = new ConditionExpression();
    addressCE4.AttributeName = "latitude";
    addressCE4.Operator = ConditionOperator.Null;
    addressFE1.Conditions.Add(addressCE1);

    // longitude
    ConditionExpression addressCE5 = new ConditionExpression();
    addressCE5.AttributeName = "longitude";
    addressCE5.Operator = ConditionOperator.Null;
    addressFE1.Conditions.Add(addressCE5);

    // Child FilterExpression 2
    FilterExpression addressFE2 = new FilterExpression();
    addressFE2.FilterOperator = LogicalOperator.Or;

    // xrmatic_congressionaldistrictname -- Custom Field 
    ConditionExpression addressCE6 = new ConditionExpression();
    addressCE6.AttributeName = "xrmatic_congressionaldistrictname";
    addressCE6.Operator = ConditionOperator.Null;
    addressFE2.Conditions.Add(addressCE6);

    // xrmatic_sessionofcongress -- Custom Field
    ConditionExpression addressCE7 = new ConditionExpression();
    addressCE7.AttributeName = "xrmatic_sessionofcongress";
    addressCE7.Operator = ConditionOperator.BeginsWith;
    addressCE7.Values.Add("114");
    addressFE2.Conditions.Add(addressCE7);

    // country
    ConditionExpression addressCE8 = new ConditionExpression();
    addressCE8.AttributeName = "country";
    addressCE8.Operator = ConditionOperator.Equal;
    addressCE8.Values.Add("U.S.");
    addressFE2.Conditions.Add(addressCE8);

    // country
    ConditionExpression addressCE9 = new ConditionExpression();
    addressCE9.AttributeName = "country";
    addressCE9.Operator = ConditionOperator.Equal;
    addressCE9.Values.Add("U.S.A.");
    addressFE2.Conditions.Add(addressCE9);

    // country
    ConditionExpression addressCE10 = new ConditionExpression();
    addressCE10.AttributeName = "country";
    addressCE10.Operator = ConditionOperator.Equal;
    addressCE10.Values.Add("US");
    addressFE2.Conditions.Add(addressCE10);

    // country
    ConditionExpression addressCE11 = new ConditionExpression();
    addressCE11.AttributeName = "country";
    addressCE11.Operator = ConditionOperator.Equal;
    addressCE11.Values.Add("USA");
    addressFE2.Conditions.Add(addressCE11);

    // country
    ConditionExpression addressCE12 = new ConditionExpression();
    addressCE12.AttributeName = "country";
    addressCE12.Operator = ConditionOperator.Equal;
    addressCE12.Values.Add("United States");
    addressFE2.Conditions.Add(addressCE12);

    // country
    ConditionExpression addressCE13 = new ConditionExpression();
    addressCE13.AttributeName = "country";
    addressCE13.Operator = ConditionOperator.Equal;
    addressCE13.Values.Add("United States of America");
    addressFE2.Conditions.Add(addressCE13);

    // createdon
    ConditionExpression addressCE14 = new ConditionExpression();
    addressCE14.AttributeName = "createdon";
    addressCE14.Operator = ConditionOperator.OlderThanXMonths;
    addressCE14.Values.Add(3);
    addressFE2.Conditions.Add(addressCE14);

    // modifiedon
    ConditionExpression addressCE15 = new ConditionExpression();
    addressCE15.AttributeName = "modifiedon";
    addressCE15.Operator = ConditionOperator.OlderThanXMonths;
    addressCE15.Values.Add(3);
    addressFE2.Conditions.Add(addressCE15);

    // Add child filters
    addressFE.AddFilter(addressFE1);
    addressFE.AddFilter(addressFE2);

    addressQE.Criteria = addressFE;
    // Prevent record locking
    addressQE.NoLock = true;
    addressQE.Distinct = false;

    EntityCollection addressEC = service.RetrieveMultiple(addressQE);

    return addressEC;
}

 

CrmConnectors

There are detailed instructions on MSDN, numerous blogs and walk throughs on how to connect to Dynamics CRM.  For this article, the classes defined in the CrmConnectors namespace are for demonstration purposes only.  There are certainly better methods to secure the service account credentials but this project can get you started.

Config.cs

The Config class contains the connection strings for each CRM instance.  For added security, the credentials could be stored in app.config instead of being hard-coded in the ConnectionString() method.

namespace CrmConnectors
{
    public enum Environment
    {
        CrmOnline,
        OnPrem
    }

    public class Config
    {
        public string ConnectionString(Environment environment)
        {
            string connectionString = "";

            // CRM Connection String - CRM Online
            if (environment == Environment.CrmOnline)
                connectionString = @"Url=https://XXXXXX.crm.dynamics.com; Username=XXXXXX@XXXXXX.onmicrosoft.com; Password=XXXXXX;";
                
            // CRM Connection String - On-Premise CRM
            if (environment == Environment.OnPrem)
                connectionString = @"Url=https://crm/XXXXXX/XRMServices/2011/Organization.svc; Domain=XXXXXX; Username=XXXXXX; Password=XXXXXX; authtype=AD";
            
            return connectionString;
        }
    }
}

Connect.cs

using Microsoft.Xrm.Client;
using Microsoft.Xrm.Client.Services;
using Microsoft.Xrm.Sdk;

namespace CrmConnectors.Crm
{
    public class Connect
    {

        public IOrganizationService Service(Environment environment)
        {
            // Get Connection String
            var config = new Config();
            string connectionString = config.ConnectionString(environment);

            return Service(connectionString);
        }

        public IOrganizationService Service(string connectionString)
        {
            // Connect to CRM
            var conn = CrmConnection.Parse(connectionString);

            IOrganizationService service = new OrganizationService(conn);

            return service;
        }
    }
}

 

Now What?

In the next article on this topic, the final step is to create a Custom Workflow Activity in Dynamics CRM to geocode new and updated addresses.

 

History

• 26-Nov-2016: First draft
• 26-Dec-2016: Updated Source and Demo Project