** 2 page regular / 1572 words **

Mighty Mail Merge

Michael High ventures beyond generating junk mail to discover more Papyrus power...

The few paragraphs devoted to Mail Merge, buried in the Menus section of the Papyrus manual, hardly grip the imagination. They seem to be all about serial letters, which most of us know as junk mail. I doubt many Papyrus users have bothered to play with this functionality. Yet most of us use at least one database program. They are great for keeping addresses, cataloguing CD's, videos and the like. Yet few of them can produce more than single column, plain text reports. Mail merging is the key to linking the information processing power of the database to the presentation power of Papyrus.

There are several ways in which databases can send information to Papyrus. Address v2.20e, which was included on the AC#8 Reader Disk, is a particularly fine database that uses just about every export format available. So I have used this to give examples of suitable techniques for differing purposes.

The humble address label

The Post Office are not impressed by fancy fonts. They just want a clear address label. All we want Papyrus to do is fit the addresses onto the rows and columns of our sheet of labels. So boot up your Atari with Address loaded as a desktop accessory (or parallel running application under a multitasking OS). Fire up Papyrus and open your label template, which you have saved with a clear monospace font as the normal text style. Now call up Address and select the records you wish to print. We are going to use the XAcc protocol to move the data to Papyrus. Set up the Import/Export filter as shown in figure 1.

** XACCFORM.IMG here **
** Caption **
Figure 1
** /Caption **

For those not familiar with Address, the important setting to note is the %f at the end of the Data format. As shown, Address will remove any blank lines in the address to keep it all together. Typically the County field will be blank for city addresses. To make sure each address starts on a new label, the %f command sends the form feed character (ASCII 0Ch) which Papyrus reads as a page break. This in turn starts a new label.

The XAcc protocol behaves like automated cut and paste. Open Address's XAcc Transfer Option window to select Papyrus (see figure 2) and click on Output. You will then see your perfectly set out labels appear in Papyrus. It is as near to multitasking single-TOS gets - and it is very satisfying.

** XACCOPT.IMG here **
** Caption **
Figure 2
** /Caption **

Note that in this method, the address lines have to be correctly ordered in the database. Also, Papyrus has to give the same paragraph and text styles to the whole address. We need to use the mail merge technique to cut and shuffle data or apply special formatting.

The delimited file

Each address in our database is a record, and each record has many fields. One such field would be for the postcode. To successfully mail merge, we need the database to create a file in such a way that Papyrus can tell one record from another and separate out the fields within each record. This is called a delimited file. Papyrus can cope with a variety of file formats, but the easiest to work with are tab-delimited and comma separated. These two formats have one line for each record. Tab delimited files separate each field with a tab character (ASCII 09h). This is fine for Papyrus, since it treats both numbers and text as text. The files often have the TXT file extension.

Some database and spreadsheets prefer to export files as comma separated values, giving the files a CSV file extension. As well as placing a comma (ASCII 2Ch) between each field, text data is enclosed in quotes. This is because databases and spreadsheets handle numbers and text in different ways. Both tab-delimited and comma-separated files can be edited or even created in text editors such as Everest, Edith or Papyrus in ASCII mode.

Earlier versions of Papyrus could load a header file. This is in the same format as the delimited file, and has one record where the data consists of meaningful field names. This function was removed in Papyrus v5. This is a pity since many databases such as Address, cannot export a header line with the data. However, all versions of Papyrus can be instructed to read the first line of data as a header. All you have to do then is create a record in your database where the data consists of field names. The trick is to give the key search field a name such as -Company. The minus character will ensure that it is the first record of any sorted list.

** DUMMYREC.IMG here **

Mail merging is fun!

Mail merging works by substituting the field text from the database for the field reference in the master document. The text picks up the paragraph and text style that was applied to the field reference, and the resulting new "merge document" is automatically re-paginated. The field references can be cut, pasted and moved round the master document, just like any other text. So rather than moving to and fro between the document and the Field ordering dialog,  simply add all the fields in one place. Then move them as required.

The beauty of mail merging is that the fields can be exported from your database in any sequence. You are free to place as many or as few of them as you like in your document. You can even put the same field in the document more than once. This means that having set up your database to export all the fields, the delimited file can be used in a variety of ways. The only thing that this file controls is the order in which the records are processed by Papyrus.

I do not intend to go through the mechanics of setting up a mail merge document in Papyrus. This is well covered in pages 163 to 167 of the manual. But I would like to suggest a few creative ways in which it can be used.

Junk mail is churned out with the Generate a new page for each record option checked. Turn it off, and some interesting possibilities are opened up for database reporting.

The Address Book

The Address database can only print plain, single column reports. How do we get a formatted double column listing on Filofax paper that does not split addresses across page and column breaks? Answer...

** UL **
* Select and export the addresses you need from Address to the delimited file, not forgetting to include the dummy record with the field names at the top of the list. Create a Papyrus template with suitable headers and footers. Why not add a cut line graphic while you're at it?
* Insert the field names in the desired order and apply formatting. Add a blank line, perhaps with an underscore line to separate the records.
* Use the Text... Group lines command to group all bar the last blank line.

** ADDTEMPL.IMG here **

* Save this new template. All your merge settings are saved, including the file name and path to your delimited file. So next time an updated list is required, just open the template and...
Click on Create document. Print and be amazed.

** MAILMERG.IMG here **
** /UL **

The formatted report

Papyrus will happily merge into tables, provided there are no lines before or after the table in the template. It is helpful to turn on the Spaces, tabs, return screen display option when setting this up. As the data is merged in, the rows of the table automatically adjust their height to fit the text as it wraps into the cells. If the effect is not quite right, simply discard the merge document, adjust the table in the template and then merge again. Once merged, the table can have headings added, or the table can be cut and pasted into another document which already has the headers and footers setup.

What about blank fields?

Not everyone has a fax machine. So how do we avoid padding out the merged address listing with empty lines? The answer is to do some simple post-merge editing. By having the word "Fax: " in front of the fax number field, we simply use the Edit... Search and Replace command to search for "Fax: [CR]" and replace this with nothing. This is another dialog that is more powerful than it first looks.

** SEARCH.IMG here **

Next time...

Hopefully the HTML and other goodies of Papyrus 7 will be available in English for review.


** boxout **
Feedback

Please send your feedback and/or suggestions on disk in ASCII or Papyrus format, to the address in the masthead on page 4 or by get in touch by email:

** BC on **
Email: high@nortelnetworks.com
** /BC **
** /boxout **

** Boxout **
Notes for Address users

CSV files are easier to set up. A typical filter's data format would be:

** NP **
"%VOR","%NAM",""NA1","%NA2","%STR",""STA","%LAN","%PLZ%N
** /NP **

However tab separated can also be created using the %t character. The equivalent data format would be:

** NP **
%VOR%t%NAM%t"NA1%t%NA2%t%STR%t"STA%t%LAN%t%PLZ%N
** /NP **

Place the cursor in the Replace %t with field, hold down the [Alternate] key and enter 9 on the numeric key pad. This generates a tab character (ASCII 09h) which appears as a clock symbol on the screen.
** /boxout **