Details about this thread
  • State Verified Answer
  • Replies 5 replies
  • Answers 1 answer
  • Subscribers 292 subscribers
  • Views 1138 views
  • Users 0 members are here
Related content from around SugarClub

Adding the Name of the County in Account records based on the Postal Code?

Bud Hartley

I had an idea that adding the County Name for each Account record would be helpful when we split a Sales Territory by County.  We are in an On-Demand environment so adding a table and doing a join isn't an option.

I was thinking that maybe a simple Business Rule that looks at the Postal Code and Returns the County Name would do the trick, but I'm really not excited about the challenge of keying the line item for each Postal Code - As you probably know, there are more than 40,000 of them in the USA (not counting the four digit extensions) :-)

I don't see a way to import the values into a business rule.  I created the rule with three entries and exported it, but I don't see a way to edit the PBR file to add the codes effectively and import it.

Writing an Action element in a Process would also be very tedious and the execution of that action would be VERY slow.

Are there any other suggestions?

  • +1

    I am not sure that zip code is enough to determine the county.

    There are zip codes that cross county lines and even state lines.
    For example13849 NY is both Delaware and Otsego counties., and 97635 can be in California or Oregon.
    And even if you could make such a list, maintenance would require significant effort and a collaboration with the postal service, since zip codes change over time.

    You could check out services that do address validation to see if they go to that granularity.
    I know that products like Loqate.com, for example, do physical address cleanup and validation internationally as well as autocompletion for web forms, they might be able to tell you the county from a full street address. They advertise an established relationship with postal services across the world to maintain their data current, as zip codes, postal distribution centers, street names etc get added/changed, and they have APIs to make it all work with various data entry points.
    Maybe such a service is worth the cost in your case?

    FrancescaS

  • 0

    I think you may want to consider a service to lookup addresses via API.
    There are a couple over on Outfitters here for you to review:

    AddressHelper by Synolia
    https://bit.ly/3uuRnmH

    Avalara Plugin by Veon consulting
    https://bit.ly/3v5Y91j 

    Hope one of those will be suitable for you.

    .

    .

    CRM Business Consultant

  • 0 in reply to Francesca Shiekh

    WOW Francesca! This has been an education for me, and I have some great fodder for a Trivia contest.  In the early days of DB2 we were looking at third normal form the postal code equated to a post office location, and since a post office didn't move often, it was redundant to store the city, state, and postal code.  Of course, there are postal codes that are for a single floor of a high-rise building.  I was very surprised to read about postal code 97635 @ https://www.onlyinyourstate.com/oregon/new-pine-creek-or/. Great trivia!

    We don't do a lot of business in New Pine Creek (none actually) :-). My go-to source for Postal Code to County has been https://www.zip-codes.com/state/or.asp which gives me the County and City for each code in the State.  I have a URL field in each account record to Google Maps http://maps.google.com/?q={billing_address_street},{billing_address_city},{billing_address_state},{billing_address_postalcode} so the users can see the location (and see if the postal code is incorrect) and I've been looking for a way to "automate" the Team Assignments where we have sales territories that aren't the entire state.

    I'm going to think about this more in the coming days.  Thanks for the education on postal codes!

    P.S. "Welcome to the town of New Pine Creek! Depending on where you're standing, you might be in Oregon...or you might be in California.  A mistake by a surveyor way back in the 1800s meant that both states can claim this town. It's said that the surveyor was a heavy drinker, which might explain how he managed to create a fairly wavy state line that varies from the 42nd parallel by as much as a half-mile. (https://www.onlyinyourstate.com/oregon/new-pine-creek-or/) 

    P.P.S. All 97xxx postal codes are Oregon addresses :-)

    Bud Hartley | Cape Foulwind, NZ (and Oregon, USA)

  • 0 in reply to Vincent Amari

    Thanks Vincent.  I've looked at commercial services (and APIs) but we're in an on-demand environment and aren't doing any "special" modifications.

    Bud Hartley | Cape Foulwind, NZ (and Oregon, USA)

  • 0 in reply to Bud Hartley

    hi Bud, both those work with Sugar On-Demand, they don't require special mods just install of the plugin which is standard method for everything on-demand :-) 

    .

    CRM Business Consultant