Operation Database Storm

by Don A. Hoyt

II Alive Volume 1 Number 0
January/February 1993

Even if thunder rumbles from your database reports thanks to their massive accumulations of accurate facts, your readers may respond with gaping yawns. After all, some people sleep better in a storm.

It’s lightning that does the trick. To put lightning into your reports, don’t create a new Tables format. There’s a better way! AppleWorks’ greatest strength is its integration- the ability of the word processor, database, and spreadsheet modules to work together and share data. The most overlooked way of sharing data between modules is the word processor’s mail merge feature. That’s a shame, because the word processor lets you arrange your data any way you like. With mail merge, the word processor becomes a custom report generator that’s built right into AppleWorks. Now that’s lightning!

Your careful computations will fl ash through in varied print sizes, some entries printed in plain type and some in boldface, some with category names and some without, and some underlined – in other words, all the excitement of a word processor document in a database report.

As the Planning Director for a small city, one of my duties is warning property owners of “nuisance code” violations like overgrown lots. My truly awesome power derives from a supply of fi rst-class postage stamps, a simple twenty-three category AppleWorks database, and several mail merge letters (with a little help from the City Council). The Mayor demands frequent updates; it is here that I truly want lightning.

GRASS ROOTS MAIL MERGE

I’ll use my GRASSFILES database to demonstrate how I electrify my reports, but any one of your own will do. Boot up to AppleWorks 2.0 or later. (Versions below 2.0 don’t have a mail merge function.) After loading the file, zoom in with open apple-Z (OA-Z) to your first record. Dump it from your screen to your printer with OA-H. Now you have a complete list of your category titles from which to work.

The report format shown in Figure 1 is typical. To get it I created a Tables report (OAP, 2, Rtrn) called MASTER. I used OA-Right and OA-Left to adjust my column widths, and I used the OA-> and OA-< keys to position the categories across the page. Even at seventeen characters per inch (OA-0, CI, Rtm, 17, Rtrn) only ten of my twenty-three categories fit in the report. Worse, even with my ImageWriter U in high quality print mode, the report is, well . . . typical.

One way to include more categories is to create a Labels report. A Labels report makes more noise but, alas, generates even less light because it’s difficult to compare one record to the next.

Instead, create a word processor document as your report and merge the data into it. But wait – isn’t that even worse? Won’t you end up with only one record on each page? Oh ye of little faith!

KNOCK THEIR SOCKS OFF

Escape to the main menu, select “Add Files”, select “Make a new file: for the word processor”, then select “from scratch” (Esc, I, Rtrn, 3, Rtrn, Rtrn). Let’s call the new document SOCKS, because it is your socks which are going to get knocked off. (If you’ re wearing shoes, you may want to remove them. Sock-knocking can wreak havoc on unsuspecting footwear.)

On the first two lines, enter your report titles, followed by a long line of equals signs. After these lines, enter only the print command for seventeen characters per inch (OA-1 , OA-0, CI, Rtrn, 17, Rtrn, Esc) which gives you the widest screen display while you work. Now you can begin entering titles and special formatting features to jazz up your report.

One thing to keep in mind while working on your report is that print options like bold (Ctrl-B) and underline (Ctrl-L) appear as little markers on the screen. These markers take up space on the screen, but not on paper. Keep this in mind when trying to line up columns or writing all the way to the edge of the page.

Not every category you in tend to merge into the document needs to be labeled in the word processor. For example, I have four categories of owner information that I grouped together under one heading; and, since most of my categori es are date fi elds, I c reated a “CASE FILE ACTION DATES” subheading so the word “date” can be omitted e lsewhe re without confusion.

Before you can enter the database categories into your document, you must put some information from the database onto the Clipboard. Return now to your database (OA-Q, 1, Rtrn). Create a new Tables format named SOCKS like your WP document (OA-P, 2, Rtrn, SOCKS, Rtrn) and print it to the clipboard (OAP, 5, Rtrn, Space, Esc, Esc). You must use a tables format. Forget about layout specifications, since your mail merged word processor document will handle all the formatting. You may want to add some record selection criteria later, but for now let’s send it all.

Back now to the SOCKS word processor document (OA-Q, 2, Rtrn). Position the cursor after your first data title, which in my report is “Case Number” and insert the proper category (OA-0, MM, Rtrn). Your screen will display the categories in the database you just printed to the clipboard. Choose a category from this list by scrolling with the Up or Down arrow and pressing return. When AppleWorks prompts you for a Yes or No answer, choose Yes (Y, Rtrn, Esc).

Continue this process for the rest of the categories you wish to insert into your custom report. When you print this word processor document, data from the selected categories will appear “lightning-fast” in your document. One copy of the document will be printed for each record in the database.

There are only a couple of things to be careful of. First, when you insert a category using MM, it may wrap around on the screen because at this point you are looking at category titles, not category contents- the content, when printed, may be much shorter than its title on the screen.

Second, Apple Works 2.0 and 2.1 have no true Tab function. So if data merged into the left side of the page varies in length, the positions of everything to its right will change as well. To overcome this second problem, try always to place category contents of equal length like dates on the left or devote a whole line to the one item. Better yet, upgrade to Apple Works 3.0.

LIGHTNING STRIKES TWICE

To put the flash in your report, insert the print commands shown in Figure 2 at the beginning of your document (OA-0 , etc.). Note that by widening your left and right margins, even though it might not help on the screen, some longer items won’t wrap around unexpectedly on the page.

Don’ t forget to set the “Accepts top of page commands” option for your printer to No. If you’ve printed labels before, you’ve probably already done this. If not, escape to the main menu, select “Other activities”, and choose “Specify information about your printer” (Esc, 5, Rtrn, 7, Rtrn, 4, Rtrn). (AppleWorks 3.0 requires you to select “Select standard settings for AppleWorks” from the “Other activities” menu, then choose “Specify information about your printer.”) Option 2 on this menu, when selected, will reverse the setting from Yes to No. If you don’t change this setting, only one record per page will be printed no matter how your document is formatted. (You can leave this item set to No even for your regular print jobs with no ill effects. In fact, I recommend it.)

Now back to your word processing document (Esc, Esc, Esc, Esc, etc.) and print (OA-P, Rtrn, Rtrn, Rtrn, Rtrn). You now have a tworecord- per-page detail report (see Figure 3), with lots of flash and with every category included, suitable for ring binding and proud display. Before I print the data to the clipboard, I go back to the database file and sort on “CASE#” (OA-A, 1, Rtrn, Esc), so that the detail report is printed in that order.

DRIFT DODGERS

Just as everyone experiences the blahs now and then, they also occasionally experience “drift.” No, “drift” is not some kind of middle-age crisis; it’s caused by trying to print weird-sized documents on ordinary II” paper. If the number records you’re printing on the page don’t fill a page completely, the records won’ t land in the right places on pages after the first. The key to printing multiple records per page with mail merge is knowing how to eliminate drift. It involves a little math (sorry). Printing one record on each page does not cause drift. And as you’ve already seen, printing two records on a page does not result in drift because it’s easy to divide an II” tall page into halves. Since the printer divides each inch into six or eight lines, the halfinch doesn’t cause a problem.

Printing eleven 1″ tall records on each page works equally well. Simply set your paper length at 1″ with no top or bottom margins, limiting your lines per record to 6 (set line spacing LI=6) or 8 (set LI=8). Any other number of records will cause drift because they don’t divide into II evenly. If you need a report with five records on each printed page, you can make it work by minimizing drift. Here’s how. Set your paper length to 2.1 with no top or bottom margins and limit your report to 12 or 14 lines at 6 or 8 lines per inch, respectively. Why 2.1″? Because 11 divided by 5=2.2; but 2.2 translates on your printer into 2 & 2/5 or 2 & 2/6, not 2 & 2/10. It looks like tenths in your print commands; but for ImageWriter at least, it is either eights or sixths.

With your page length set at 2.1 (actually 2 & 1/8), your documents will drift 3 lines every page. To correct it, simply press the select button on your printer once and issue three line feeds for each printed page. (This is easiest if you include a Pause Here, PH, command at the end of the word processing docume nt. Hit the space bar after each record and remember to advance the paper every third record.) Don’t bother wrestling with any other formats than I, 2, II, or (if necessary) 5 records per page. There is simply too much drift.

BOUND FOR GLORY

Most of the time a quick “tables format” report suits my needs well enough, but when the boss will be looking at my reports, I like them to rumble modestly and flash with lightning. You can exercise your creativity, communicate your information, and impress the boss all at once by taking just a bit of extra time to use the mail merge function for your otherwise dull database reports.

About the Author