Jaa

Inserting City and State Automatically based upon Zip Code

  • Artikkeli

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 = "https://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

  • Anonymous
    June 22, 2006
    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.

  • Anonymous
    June 22, 2006
    Ben, that was an excellent suggestion. I wonder who that masked man was? AK isn't much to go on.

  • Anonymous
    June 22, 2006
    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

  • Anonymous
    June 22, 2006
    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. :-)

  • Anonymous
    June 23, 2006
    Where do you put this, in the onchange?

    Just wanted to confirm

  • Anonymous
    June 23, 2006
    Yes. Or OnSave. Your choice.

  • Anonymous
    June 23, 2006
    Any idea if you delete the value out of the ZIP why it defaults to KNULL, ID?

  • Anonymous
    June 23, 2006
    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

  • Anonymous
    June 29, 2006
    Is there nay way for the returned data to be formatted back to initial caps rather than all caps?

  • Anonymous
    July 16, 2006

    Hello JScript friends,
    I am back from vacation and back again to enjoy you with some interesting...

  • Anonymous
    August 15, 2006
    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?

  • Anonymous
    August 18, 2006
    I too am getting the same error.  Any thoughts?

    James

  • Anonymous
    August 21, 2006
    Mike,

    Enable Cross site scripting in IE.

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

  • Anonymous
    March 26, 2008
    Were you able to resolve this? If not, I might be able to offer a helpful tip, though not a full-blown solution. Marc

  • Anonymous
    May 21, 2008
    hi Marc , How would i make this to accept South african postal codes

  • Anonymous
    May 21, 2008
    Is there a way to convert this code to raw  PHP to be able to be used on web-sites?

  • Anonymous
    August 03, 2009
    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

  • Anonymous
    March 01, 2010
    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.

  • Anonymous
    March 11, 2010
    Still having issues with "permissions denied".  Any suggestions if has enable cross site scripting.  Possibly not compatable with IE?

  • Anonymous
    May 07, 2010
    For those of you having problems, please come check out my new blog for the updated code. http://businessnone.com/demo-tools/update-ndash-inserting-city-and-state-automatically-into-microsoft-dynamics-crm/