Page 1 of 1

Weed out unwanted routes from EDDM List

Posted: Wed Apr 10, 2019 2:31 pm
by Eric
Building an EDDM List can be a pain in the :shock: when you have to build the list from a large group of ZIP Codes and large amount of routes in the list. If you're building an EDDM list of more than two ZIP Codes and more than a dozen routes, or in other words if you're spending more than 10 minutes in the search editor searching for the ZIP Codes and routes you want to keep out of the EDDM List you built and you think to yourself, "there must be a better way", there is. You'll run this command on your EDDM List, which will delete all ZIP/Route combos that are not in the list of those you want to mail.
com_db = GETFILE('dbf', 'EDDM Routes Table:', 'Open', 0, 'Open')
USE ("&com_db") IN 0 ALIAS "zipsroutes"
cDbf=DBF()
cAlias=ALIAS()

SET SAFETY OFF
SELECT("zipsroutes")

***CLEAN zipsandroutes***
*Replace all crrt with SUBSTR(zip,7),zip with LEFT(zip,5)
*DELETE FOR EMPTY(zip)
*PACK

***This section marks the routes we want to keep in each ZIP***
INDEX ON LEFT(zip,5)+ALLT(crrt) TAG mytag
SELECT(cAlias)
REPLACE x with "X" for SEEK(LEFT(zip,5)+ALLT(crrt),"zipsroutes")

***This section deletes the un-marked routes***
DELETE FOR EMPTY(x)
PACK
How to use it: Look at your Excel file or text file of the ZIP's and routes you need to mail. Note the beginning ZIP and the ending ZIP in that range.
Build your EDDM list in AccuZIP6 by entering this range (such as 15212-25019). Keep that list open.
Now do a FIle > Open List. Open the text or excel file containing your ZIP Codes and routes. Assign the field named ZIP to the 5 digit ZIP Codes. Assign CRRT to the carrier routes. Import this list.

If you need to add a leading zero to the ZIP Code, run this command:
Replace zip with PADL(ALLT(zip,5,"0") FOR LEN(ALLT(zip))<5 AND !EMPTY(zip)
Delete blank records with this command:
DELETE FOR EMPTY(zip)
PACK
With the list cleaned, do a File > Save Database As, and name it zipsroutes.dbf. Put it someplace where you'll be able to find it, like your desktop. Close that database, so only the EDDM list is currently open.
Run the command above. The command will have you browse to select the database of ZIP Codes and routes that you want to keep. Once you've selected this, the command will compare the EDDM list and your ZIP/Carrier Route list, mark the matches, then delete and pack anything that is not marked.

Now you're ready to presort!