GoldMine 9 Zip Code Refresh

I’ve been using GoldMine for CRM since 1992, starting with version 3.2 for DOS. Sometime early on, I obtained and imported a Zip Code database. Very handy:  just type in a 5-digit Zip, and GoldMine fills in the City and State.

I figure after 15+ years, maybe it’s time to refresh the Zip list so I started testing. I expected there to be changes. What I didn’t expect is that the list would go from 75,927 values down to 42,318. Why are there 33,000 fewer entries now?

How to Test

Warning Back up your database! and know how to restore it if something goes wrong. These commands will alter your data. If you’re not comfortable working in SQL Management Studio, stop now.

Caveat The changes here will NOT sync:  be prepared to re-sync your entire database (or at least the entire Lookup table).

These test were run with GoldMine 9.2.1.92. That version includes Zip Code data version 8.0.0.33.

1. Run these queries in SQL Management Studio and write down the results:

-- Count ALL Lookups
SELECT count(*) FROM LOOKUP

-- Count Zip lookups
SELECT count(*) FROM LOOKUP where FIELDNAME LIKE '|%'

-- Count odd Zip lookups (may be custom entries that you will lose when you do a global delete)
SELECT * FROM LOOKUP
where FIELDNAME LIKE '|%'
  and (len(rtrim(fieldname)) < 6 or len(rtrim(fieldname)) > 7)

2. Run this command to get a good chunk of sample data. I used “921” to get San Diego’s Zip Codes; you may want to use a different prefix. Copy the results to a text file.

SELECT FieldName, Entry FROM LOOKUP where FIELDNAME LIKE '|921%'

3. Run this command to delete the old Lookup data (did you back up your database?):

DELETE FROM LOOKUP where FIELDNAME LIKE '|%'

4. Run the commands from step 1. The counts should be 0.

5. In GoldMine, select Tools > QuickStart Wizard. Click on the postage stamp icon. Click Finish to import the current Zip Code data. (Here is where you can see the Zip Code data version number, 8.0.0.33 in this test.)

6. Run the command from step 2 and save the results in another text file.

Now, open both text files and compare the samples from the old and new databases.

How to Compare

Notepad++ is a great tool for this kind of comparison. Using its Compare plugin shows differences graphically:

The Results

I checked each difference between the old and new samples, using www.usps.com for Zip Code reference. The old list contained 96 entries; the new list has 85. Every change reflected a needed correction:

• Several Zips were duplicated with “optional” city names. For example, 92143 used to show both San Diego and San Ysidro. Now it correctly lists only the preferred name, San Diego.
• Several Zips are missing from the new list. Every one was an invalid Zip.
• 92158 was not in the old list. But it’s a valid Zip and it’s in the new one.

I can’t say the new list is perfect, but after this test, I’m convinced that even with 33,000 fewer entries, it’s an improvement over what I had. I’m going forward with deleting and re-adding the Zip data.