Kelly,
I implemented a Zip Code lookup table about 4-5 years ago. 'Got it
from http://www.zipcodedownload.com. We use it to manage city/state names,
time translations between our local/server time and the 1K+ customer sites,
and distances between two sites (Zip Code table includes longitude and
latitude).
As you're imagining, it's only a simple form/table and a little
workflow. The ASCII file you're talking about (.csv format) has over 70K
records (at least the version we have does). Works fine through Remedy
Import, but if you try to open it using MS Excel, it'll truncate (at row
65,535 for 2002 version).
You'll want to consider data currency. May or may not be an issue
for you. Personally, I was surprised at how much the Zip Codes are
actually changed; quite a few are split as their populations grow. Our
version has Daylight Saving Time observance indicators, and there have been
some recent changes to which areas observe DST (in addition to the start
and end date changes which begin next year). If not a big deal, you can
implement one-time and refresh infrequently. If currency matters, you can
subscribe to receive updates (monthly, I believe). There are a few ways to
handle them - use a separate "transaction table" to import into, then
workflow to update corresponding record in the production table, or simply
initialize the production table and import fresh data.
Mike White
Office: 813-978-2192
E-mail: [EMAIL PROTECTED]
"Kelly Logan"
<[EMAIL PROTECTED] To: [email protected]
CS-NOW.COM> cc:
Sent by: "Action Subject: Re: USA City,
State lookup by zip code for Customer in Help Desk
Request System
discussion
list(ARSList)"
<[email protected]>
11/10/2006 11:52
Please respond to
arslist
SQL Server 2000 v.8
> What is your underlying database?
>
> Joe D'Souza
> Remedy Developer / Consultant,
> Shyle Networks,
> New Jersey.
>
>
>
> ----- Original Message ----
> From: Kelly Logan <[EMAIL PROTECTED]>
> To: [email protected]
> Sent: Thursday, November 9, 2006 3:55:42 PM
> Subject: USA City, State lookup by zip code for Customer in Help Desk
>
>
> Hello All,
>
> I've had a request to have the entirety of possible US (including Puerto
> Rico) zip codes available for City, State lookup in the Customer form for
> Help Desk.
>
> USPS has a ASCII file with all of them in readable format, but it looks
> monstrous (an entire CD, zipped; better than a DVD, I guess).
>
> We do not have an available outfacing web server, so a web service lookup
> is not viable.
>
> Has anyone tried this? If so, what size and type of system are you on
and
> did the amount of data cause any issues?
>
> Right now, if they decide they want to go forward with this, is to put
the
> data into a separate table and/or database and customize the Remedy code
> to pull from it. Again, any experience and/or advice on this is welcome.
>
> Thanks in advance!
>
> Kelly Logan
> Cybernetic Solutions, Inc.
> [EMAIL PROTECTED]
>
>
>
>
____________________________________________________________________________________
> Want to start your own business?
> Learn how on Yahoo! Small Business.
> http://smallbusiness.yahoo.com/r-index
>
>
_______________________________________________________________________________
> UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where
> the Answers Are"
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where
the Answers Are"
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
