Inserting City and State Automatically based upon Zip Code



I saw an internal question a couple of weeks ago about how do I have CRM auto fill in City and State when I insert the zip code. After noodling on this for a while, there are a couple of different approaches. I would highly recommend you look at the Group1 Data Quality Adapter as it does that and much more. But what if ALL you want is just the City and State with no other bells and whistles. Is this possible? The easy answer is yes.


So to make this happen, I first went on a search of an web services that return data to you in XML that we could parse and insert into CRM. After looking around, the one that seemed to jump out at me is the one from the USPS. After playing with it for a while, I gave up since their licenses is fairly restrictive. (If you are not doing it for USPS shipping and cannot provide them a URL to show them what you are doing, it is a $3500 a year package you need to buy. Or at least that is what they told me.)


In looking at a totally unrelated to CRM blog post, I ran across the Yahoo Maps GeoCoding Web Service. It has a VERY liberal policy of 5,000 hits per day and no username or password are required. In playing with it, it looks like it will do everything you would want around addresses. My only wish is that Local.Live.com had this same API I could use. (And yes I went poking around and asked there first.)


So the inspiration for this came from Arash’s post on Multicurrency. After playing with his script for about an hour and realizing that I am really pretty dumb, I emailed Jason and Aaron over at Invoke Systems.


Wouldn’t you know Jason had something handy? Here is his link. He goes a little more in-depth than I want. (VERY Simple for me, insert a zip code and get city and state. Everything else is icing… ) This should be placed on the OnChange for the Zip / Postal Code Field. I have not tried it with any international addresses, yet.


Thanks Jason!


So here is my copy of Invoke’s code with it JUST for City and State


var sUrl = “http://api.local.yahoo.com/MapsService/V1/geocode?appid=MicrosoftCRM“;


sUrl += “&zip=” + crmForm.address1_postalcode.DataValue;


var oXmlHTTP = new ActiveXObject(“Msxml2.XMLHTTP”);


oXmlHTTP.Open(“GET”, sUrl, false);


oXmlHTTP.Send();


var oXmlDoc = oXmlHTTP.responseXML;


crmForm.address1_line1.DataValue = oXmlDoc.selectSingleNode(“ResultSet/Result/Address”).text


    crmForm.address1_city.DataValue = oXmlDoc.selectSingleNode(“ResultSet/Result/City”).text


    crmForm.address1_stateorprovince.DataValue = oXmlDoc.selectSingleNode(“ResultSet/Result/State”).text


    crmForm.address1_country.DataValue = oXmlDoc.selectSingleNode(“ResultSet/Result/Country”).text

Comments (22)

  1. ak says:

    Ben

    The USPS web service looks good but you’re right, they’re absolutely pains in the you-know-what and $3,500 is a ridiculous fee for small businesses.

    I recommend the UPS Online Tools (http://www.ups.com/content/us/en/bussol/offering/technology/automated_shipping/online_tools.html). It is also totally free, you can create an account and receive an ID online, and its API is totally XML driven and very easy to use. You can very easily write a piece of code that prompts for zip code and that returns city/state, takes only one page of code.

  2. Jim Glass says:

    Ben, that was an excellent suggestion. I wonder who that masked man was? AK isn’t much to go on.

  3. Visitor says:

    Ben, I looked into your code however I have a question on how would you intergrate this code In microsoft Navision.

    We usually have OCX controls where we send out info and receive info back, however I am not sure how I can call your code within Navision. Does anyone have any idea ??

    ALso

    var oXmlHTTP  where OxmlHttp is defined as an ActiveX object, however Navision does not a datatype of ActiveX. I believe this is a great idea, however I want to be able to intergrate this Microsoft Navision.

    THanks

  4. Ben Vollmer says:

    AK has made several very good comments. 🙂 There are probably another 1/2 dozen ways to skin this cat. 🙂 This was just a way that I found and Jason coded. 🙂

    As for Navision, I have no idea. I can barely spell ERP, let alone code in it… You may want to check with tech support, they should be able to give you some pointers. 🙂

  5. Scott says:

    Where do you put this, in the onchange?

    Just wanted to confirm

  6. Ben Vollmer says:

    Yes. Or OnSave. Your choice.

  7. Scott says:

    Any idea if you delete the value out of the ZIP why it defaults to KNULL, ID?

  8. Ben Vollmer says:

    Scott,

    I would probably insert an IF statement above the code if you think you will do this allot. This code is meant to be insperational, but you may want to play with it a bit to get it working in your enviroment.

    Thanks

    Ben

  9. Chuck says:

    Is there nay way for the returned data to be formatted back to initial caps rather than all caps?


  10. Hello JScript friends,

    I am back from vacation and back again to enjoy you with some interesting…

  11. Mike says:

    I cannot get this to work.  I am new at scripting in CRM, and have placed this on the zip field on an onchange event, and every time I try it on the form I get the error "permission denied" (from the preview page) or "unknown name" on the actual page.

    CRM 3.0

    There was an error with this field’s customized event.

    Field: address1_postalcode

    Event: onchange

    Error: Unknown name

    Anyone have some suggestions?

  12. James says:

    I too am getting the same error.  Any thoughts?

    James

  13. James says:

    Mike,

    Enable Cross site scripting in IE.

  14. Paul Croubalian says:

    Any idea how to make this accept Canadian postal codes, or, if not, how to trap the error so as to do nothing?

  15. Marc says:

    Were you able to resolve this? If not, I might be able to offer a helpful tip, though not a full-blown solution.

    Marc

  16. phumlo says:

    hi Marc , How would i make this to accept South african postal codes

  17. ilan says:

    Is there a way to convert this code to raw  PHP to be able to be used on web-sites?

  18. david withers says:

    has anyone got this to work in crm 4.0?

    I’m getting the permission error.  and cross fram scripting is only an option for IFRAMES

  19. John says:

    Has anyone gotten this to work?  I’ve enabled cross-site scripting but am still getting the permission error.  Not sure what else to do at this point.

  20. Aaron says:

    Still having issues with "permissions denied".  Any suggestions if has enable cross site scripting.  Possibly not compatable with IE?