How To Add Zip Code Lookup Functionality

Many customer relationship applications provide the users an automated process to populate City & State after the user enters a Zip Code, but CRM sadly does not. If your business needs this functionality the following tutorial was written with you in mind.

Using the following methods, we’ll add a new entity in CRM, populate it with zip code, city & state info and then we’ll show you how to make CRM fill in a Contact or Lead’s City & State after a user enters a zip code.

Step 1 – Create the Custom Entity

  • Name the Entity “Zip Code”
  • Choose “Organization” as the Ownership type
  • Unselect Notes and Activities (do we need an activity for a zip code?)
  • Choose the Areas to display the entity. (I chose none but you may want to display it initially for any troubleshooting)

Click over to the Primary Attribute tab.

  • Make the display name & schema name “zipcode”.
  • Change the max length to 20. This should be plenty for most of us.

Your setup should look like these screen shots:

General Tab

Primary Attributes

Now we need to add the additional attributes.

  • Create an attribute for City (nvarchar, 100 max)
  • Create an attribute for State (nvarchar, 10 max)
  • Publish your work

Here are the 3 attributes we’ll be using:

All Attributes

Step 2 – Acquire the Zip Codes.

You can purchase the zip codes for the United States and its territories for about $20 from a number of companies on the web. They will send you a .csv file which will be about 10,000 rows & 3 columns (zip, city, state).
Getting the data imported into the CRM database requires a 3rd party tool such as C360’s import tool, or another database import tool that would probably be unsupported by Microsoft. We used the C360 tool.

Step 3 – Customize your Contact and Lead forms.

Now that the data is ready, we’ll enter an onChange event for the zip code field on our form. Go to Settings->Customization->Customize Entities .
Assuming your forms are using the default “address1_postalcode” and you setup your Zip Code entity exactly as shown earlier, you can copy & paste the following code into the onChange window: (troubleshooting help follows the code)

*******************************************************

var Zipentry = crmForm.all.address1_postalcode.DataValue;

var xml= “” +
“<?xml version=”1.0″ encoding=”utf-8″?>” +
“<soap:Envelope xmlns:soap=”http://schemas.xmlsoap.org/soap/envelope/” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=”http://www.w3.org/2001/XMLSchema”>” +
” <soap:Body>” +
” <query xmlns:q1=”http://schemas.microsoft.com/crm/2006/Query” xsi:type=”q1:QueryExpression” xmlns=”http://schemas.microsoft.com/crm/2006/WebServices”>” +
” <q1:EntityName>new_zipcode</q1:EntityName>” +
” <q1:ColumnSet xsi:type=”q1:ColumnSet”>” +
” <q1:Attributes>” +
” <q1:Attribute>new_city</q1:Attribute>” +
” <q1:Attribute>new_state</q1:Attribute>” +
” </q1:Attributes>” +
” </q1:ColumnSet>” +
” <q1:Distinct>false</q1:Distinct>” +
” <q1:Criteria>” +
” <q1:FilterOperator>And</q1:FilterOperator>” +
” <q1:Conditions>” +
” <q1:Condition>” +
” <q1:AttributeName>new_zipcode</q1:AttributeName>” +
” <q1:Operator>Equal</q1:Operator>” +
” <q1:Values>” +
” <q1:Value xsi:type=”xsd:string”>” + Zipentry + “</q1:Value>” +
” </q1:Values>” +
” </q1:Condition>” +
” </q1:Conditions>” +
” </q1:Criteria>” +
” </query>” +
” </soap:Body>” +
“</soap:Envelope>” +
“”;
var xmlHttpRequest = new ActiveXObject(“Msxml2.XMLHTTP”);
xmlHttpRequest.Open(“POST”, “/mscrmservices/2006/CrmService.asmx”, false);
xmlHttpRequest.setRequestHeader(“SOAPAction”,”http://schemas.microsoft.com/crm/2006/WebServices/RetrieveMultiple”);
xmlHttpRequest.setRequestHeader(“Content-Type”, “text/xml; charset=utf-8”);
xmlHttpRequest.setRequestHeader(“Content-Length”, xml.length);
xmlHttpRequest.send(xml);

// Remove the // in front of the next line to see the code that is being sent
//alert(xml);

var rtnddata= xmlHttpRequest.responseXML;

// Remove the // in front of the next line to see the code that is returned
//alert(rtnddata.xml);

var data;
if (rtnddata.selectSingleNode(“//BusinessEntity”) != null)
{
data = rtnddata.selectSingleNode(“//BusinessEntity”);

var City ;
if (data.selectSingleNode(“new_city”).text != null)
{
City = data.selectSingleNode(“new_city”).text;
crmForm.all.address1_city.DataValue = City;
}
var State ;
if (data.selectSingleNode(“new_state”).text != null)
{
State = data.selectSingleNode(“new_state”).text;
crmForm.all.address1_stateorprovince.DataValue = State;
}
}

else {
crmForm.all.address1_stateorprovince.DataValue = “”;
crmForm.all.address1_city.DataValue = “”;
}

*******************************************************

Make sure you enable the event:

Event Detail Properties

Test your work by using the Preview-> Create Form. Enter a valid zip code in your postal code/zip code field, tab out of the field and the city & state should populate immediately.

Now we need to give users the right to use the data. In your Security Roles settings, go to the Custom Entities tab and give Read access to the Zip Code entity we created, to each Role you need to adjust.

** Troubleshooting the Code **

Any troubles you may have should be easy to diagnose. We’ll discuss the changeable parameters one by one:

Line 1 – “var Zipentry = crmForm.all.address1_postalcode.DataValue;”

The code is setting up Zipentry for use later by reading what zip code the user entered.
Is your postal code or zip code field on the form the default, address1_postalcode? Check the schema name and adjust the code as needed.

Line 12 “” new_zipcode” ”

Here we define the CRM entity we are querying.
Is your zip code entity named new_zipcode? Adjust as needed.
(technically we are querying the View in the database, not the database itself)

Line 15 &16 – “” ” new_state” + “”

The query is asking for the City and State (new_city, new_state) data that match our zip code entry.
Make sure your attribute names are the same or adjust the code as necessary

Line 24 “”new_zipcode””

We will compare the users zip code entry to the database here, using new_zipcode. This requires that you entered “zipcode” as the primary attribute name during the creation of your zip code entity. Adjust as needed.

Line 27 – ” ” ” + Zipentry + “” + +

Now we use the zip code data that our users entered, and query the database with that entry. Refer back to Line 1 if you still need to adjust.

Line 45“// Remove the // in front of the next line to see the code that is being sent”

Just as stated, if you remove the two slashes from the next line, //alert(xml), you will get a pop-up window showing you the body of the query you are sending to the database. Use this option for education as well as troubleshooting.

windows-internet-explorer-1.bmp

The user entered zip code 94538

Line 50 “// Remove the // in front of the next line to see the code that is returned”

As above, but this pop-up window will show you what the database returned in response to our query. Use this option for education as well as troubleshooting.

microsoft-internet-explorer-2.bmp

A good response, giving us Fremont and CA.

Lines 53 – 56

The code is making sure there is good data from the query before continuing. Nothing you should adjust here.

Now the code populates the form with city & state.

Lines 58 – 70

If you’ve had to make any adjustments previously, you will have to do the same here. The code assumes the city and state fields have the schema names:
address1_city
address1_stateorprovince

and that your new zip code entity uses the names described earlier:
new_city
new_state

Adjust the code as needed.

Lines 72 – 75
else {
crmForm.all.address1_stateorprovince.DataValue = “”;
crmForm.all.address1_city.DataValue = “”;
}

This code sets the city and state to nothing if a user enters an unknown zip code. This is optional and you can remove it if you like.

And we’re done….Enjoy!