Why work if you can get a computer to do the job for you?
Recently, when I needed to send a form letter to 133 different businesses, I thought of NICAR data guru Richard Mullins, who teaches this ethic. I could sit and type 133 different addresses on top of 133 different copies of the same form letter - or I could get a computer to do it for me. I decided to let the computer do the job.
Here's what I had available: a FoxPro database that included, among other things, the names and addresses of the businesses that I wanted to contact.
I knew I could write a scanloop in FoxPro to pull out the addresses. And I found out that I could use the TEXT/ENDTEXT function to insert a text block - in this case, my form letter - after each scanned address. Later, I found I could get FoxPro to use the same database to print out mailing labels. What would have taken me a couple of hours, FoxPro did in minutes. (Incidentally, you could accomplish this same task with Microsoft Word's Mail Merge without writing a FoxPro program).
To create the FoxPro program, which I called "letters," in the command window, I typed:
modify command letters
In the .prg, or program, window that appeared, I wrote the following program (an explanation follows):
CLEAR CLEAR ALL CLOSE ALL SET CENTURY ON SET TALK OFF SET STATUS BAR OFF USE C:\business. files = VI " SCAN files = alltrim (str (business.id)) SET ALTERNATE TO "c:\' + files SET ALTERNATE ON ?alltrim (cmonth (date + + alltrim (str (day (date + + alltrim (str (year (date )
?name ?address ?alltrim "+state +" "+zip ?" "
TEXT
To Whom It May Concern:
This is where I typed my letter.
ENDTEXT CLOSE ALTERNATE ENDSCAN SET TALK ON SET STATUS BAR ON
Sincerely, Jo Craven
My database, business.dbf, had 133 records. This program uses an alias called "files" to create 133 different text files, each containing the form letter topped with the different business addresses.
To make this work, I needed each record to have a unique ID of no more than eight characters. My data did not have such a field, so I created one using the recno() function: I modified the database by adding a field called "id" and told FoxPro to:
replace all id with recno
This numbered each record, providing the necessary unique ID. Here is the program again, including explanations of its components:
CLEAR CLEAR ALL CLOSE ALL These three lines clear the slate, making sure that no other files are open when the program starts. They are standard in any FoxPro program. SET CENTURY ON This sets the dateline that starts each letter. SET TALK OFF SET STATUS BAR OFF These turn off FoxPro's bells and whistles in order to make the program run faster.
USE C:\business.dbf
This tells the computer which database to use - the one with the business names and addresses files =
This reveals that an alias called "files" will be used. It is defined and plugged into the program later. This line can be omitted; including it is a matter of form. SCAN This begins the scanloop. In FoxPro, a scanloop tells the computer to look at every record in a database and perform whatever functions are specified within the scanloop - in this case, setting up separate text files, typing in the various names and addresses from the database, and inserting the text block written within the scanloop. files = alltrim (business.id) This defines the alias "files." It says that, for this program, "files" equals "id," which is the field in business.dbf containing the unique IDs. "Alltrim" makes sure blank spaces don't confuse the machine. And "str" tells the computer to consider the numeric, unique IDs as character data. SET ALTERNATE TO "c:\" + files SET ALTERNATE ON This is where the individual text files are created. "Set Alternate To" tells the computer that everything it does within this scanloop should be copied to another file on the c:\ drive. "Files" has already been defined as "id," so for every unique ID, a new text file will be created on the c:\ drive. "Set Alternate On" tells the computer to act on the "Set Alternate To" command. "Set Alternate To" won't work without "Set Alternate On." ?alltrim (cmonth (date + + alltrim (str (day (date ( ) ) + + alltrim (str (year (date This makes the date. If your computer's date and time are set correctly, this command will produce the current date. The question mark (?) tells FoxPro to type the information. ?" " This produces a blank line between the date and the address. ?name ?address ?alltrim (city) "+state "+zip ?" " This tells FoxPro to pull out the information in the fields "name," "address," "city," "state," and "zip," inserting the comma where appropriate, and spacing between the state and ZIP code.
(Remember, we already told the computer which database to use - business.dbf) The last line leaves a blank space between the address and the letter below.
TEXT To Whom It May Concern:
This is where I typed my letter. Your letter can be as long, or short, as you like.
Sincerely, Jo Craven
ENDTEXT
TEXT tells FoxPro a text block is coming and should be inserted in the scanloop after the address. Anything can be typed into the text block. A tip: Use carriage returns after each line to ensure proper line breaks. ENDTEXT must be typed at the end of the text block.
CLOSE ALTERNATE
Remember, FoxPro was typing everything from the scanloop into a text file named after the unique ID. This closes that file, so that when the scanloop begins again, FoxPro will name the next alternate file after the next unique ID and thus create the different text files. (When I ran this program, I ended up with 133 text files with names called 1.txt, 2.txt, on through 133.txt)
ENDSCAN
This tells FoxPro the scan is done, and should be started anew on the next record in the database, until there are no more records left. SET TALK ON SET STATUS BAR ON These turn the bells and whistles back on. (Remember, I turned them off in the beginning to make the program run faster.)
After typing this program into the .prg window, to save and exit, type ctrl W. To run the program, in the FoxPro command window, type:
do letters
Making mailing labels for envelopes is even easier because FoxPro offers a wizard that will draw the addresses from the same database. From the Run pulldown menu, choose Wizard and then "labels" and follow the steps. Tip: Standard mailing labels are size 5160.
Jo Craven can be reached at (573) 882-0684, or send e-mail to jo@nicar.org