%OP%TNT
%OP%JUY
%OP%WRY
%OP%AMM
%OP%DP0
%OP%HM1
%OP%FM2
%OP%BM2
%OP%LM5
%OP%HE||%H2%Pipedream Mailmerge and Report Generator%H2%||
%OP%FO|@D@||Page @P@|
%CO:A,8,72%%H2%Concept

%JR%The method of mailmerge described below allows the text of a letter to 
%JL%be written once and then discreetly addressed to many recipients. In 
%JR%addition the letter may contain within the text references which are 
%JL%once off for that recipient only. Once a set of letters has been 
%JR%prepared the report generator can be used to produce a printed report
%JR%of all the particulars with eight or ten records on one page. Also a 
%JR%one line report can be prepared with 55 records to the page. The report 
%JL%generator can be used in its own right for reporting and printing from 
a database.

%JL%This mailmerge uses a marked block when printing and hence it does not 
%JR%make any material difference whether the letter is at the top of the 
%JL%file or the bottom. A pre-existing address database can become the 
%JR%basis of the mailmerge. The database can be updated and used for 
further letters as required.

%JR%Printing is called in the normal way (%H2%<>PO%H2%) or can easily be controlled 
%JL%by a CLI file executed once all the preparatory work is complete. The 
%JR%method described has been used to print a mailing of nearly 100 
%JL%letters. A one page letter will print on a laser (page) printer at the 
%JR%rate of one every 30 seconds, a line printer can be used just as well, 
if not so quickly, and is preferable for the report generator.

%JR%The system requires that the letter, the expressions (used to run the 
%JL%system) and the address database are all present in one pipedream 
%JR%file. The CLI file is kept separate and indeed several CLI files can be 
saved and the particular one required for the job in hand executed.


%H2%Getting Started, the database

%JR%The names and addresses as well as any text to be embodied in the 
%JL%letter need to be kept in a normal Pipedream database, i.e. one line, 
%JR%one record. The left hand column is reserved for a line number. This 
%JL%starts at 1 and is incremented consecutively. This number may be used 
%JR%as a customer number but plan to have another column to hold customer 
%JL%numbers if required. The line number must be entered as an expression, 
%JR%or altered to an expression once entered as text. However, begin by 
leaving column A empty. 

%JL%The database needs as many columns as name lines, address lines, 
%JR%telephone number line and other discreet items as you wish to use plus 
%JL%column A. Add columns to the right of column F with %H2%<>EAC%H2% or insert 
%JR%columns with %H2%<>EIC%H2%. A normal name and address may require 4 to 6 lines 
%JL%and unused slots should have an exact space (%H2%<>spacebar%H2%) entered into 
%JR%them, otherwise 0.00 will be printed out in each blank line of the 
address.

%JL%The standard column width of twelve spaces is more than enough for the 
%JR%database. Reduce the width (%H2%<>W%H2%) of each column in turn to 8 and thus 
%JL%increase the number of columns visible without scrolling across. The 
%JR%amount of text to be entered into each slot will often be more than 8 
%JL%digits wide so it is essential to turn off word wrap by going to the 
%JR%options page %H2%<>O%H2% and moving down to %H4%Wrap%H4% and typing %H2%N%H2%. Narrow columns 
%JL%give more flexibility in planning the layout of the report generator. 
%JR%Whilst in the options page set %H4%Calc: Auto/Man%H4% to %H2%M%H2%. As the database 
becomes larger this will allow data to be entered more quickly.
%JR%A row of titles is very useful so start by tabbing to B1 and type
%JL%%H2%NAME%H2%. Tab to C1 and type %H2%ADD1%H2%, continue across the row and after say 
%JL%six columns for the address type %H2%PHONE%H2% or whatever other details you 
%JL%want to file in the database. Before moving the cursor off this row use
the Fix Row command (%H2%<>LFR%H2%) to fix it at the top of the screen.

%R%%H4%.......A.......B.......C.......D.......E.......F.......G.......H.......I
%H4%        NAME    ADD1    ADD2    ADD3    ADD4    1stNAME PRIZE   FOR
%H4%        J. Smith Esq.,
%H4%        J. Smith14, Hanover Street,
%H4%        J. Smith14, HanoPETERBOROUGH
%H4%        J. Smith14, HanoPETERBORPE2 3AG
%H4%        J. Smith14, HanoPETERBORPE2 3AG
%H4%        J. Smith14, HanoPETERBORPE2 3AG         John   
%H4%        J. Smith14, HanoPETERBORPE2 3AG         John    first prize
%H4%        J. Smith14, HanoPETERBORPE2 3AG         John    first prdahlias

%JR%The example above shows how a one line entry is built up. At the time 
%JL%of entry each slot will spill over those on the right but as soon as 
%JR%the slot is complete and you press %H2%TAB%H2% the cursor will move to the 
%JL%start of the next slot and the excess text will be hidden. Note that it 
%JR%is still in place and at any time you can return to the slot by using 
%JL%the %H2%TAB%H2% key or the back arrow key. Once in the desired slot all the 
%JR%normal text commands can be used to delete or correct it as required. 
%JL%The arrow keys work as normal but the text will scroll past the narrow 
%JR%window as you move around the entry. Completed entries will look like 
this:

%R%%H4%.......A.......B.......C.......D.......E.......F.......G.......H.......I
%H4%        NAME    ADD1    ADD2    ADD3    ADD4    1stNAME PRIZE   FOR
%C%%H4%        J. Smith14, HanoPETERBORPE2 3AG         John    first prdahlias
%L%%H4%        Mrs. L. 18, HighCrowlandPETERBORPE14 8QTLucy    highly csweet p

%JL%Once the list is complete enter the control numbers into column A. For 
%JR%a shortish list this is most easily done by typing %H2%1%H2% in slot A1 and 
%JL%%H2%ENTER%H2%, type %H2%2%H2% in slot A2 and repeat to the bottom of the list. Mark the 
%JR%bottom slot (%H2%<>Z%H2%) and then %H2%<>uparrow%H2%, now you will be in slot A1 and 
%JL%mark that slot, the whole block will be highlighted. Type %H2%<>ENT%H2% to 
%JR%enter the numbers as expressions, type %H2%<>LAL%H2% to left align the numbers 
%JL%and %H2%<>LDP%H2% to alter the decimal places, type %H2%0%H2% at the prompt, then 
%JR%%H2%ENTER%H2%. Now the control numbers are neatly in place and the block can be 
removed with %H2%<>Q%H2%.


%H2%Getting Started, control expressions

%JR%At this stage plan ahead and decide how many lines need to be left 
%JL%clear between the bottom of the database and the top of the letter. 
%JR%Allow for possible growth in the number of database entries. If the 
database has 40 entries then enter the control expressions on line 50.

%JR%If the database has 40 lines, and allowing 5 lines for extra entries, 
%JL%range one will be slots A1 to A45. Range 2 will be B1 to B45 for column 
%JR%B and C1 to C45 for column C etc. The %H2%key slot%H2% (in this case A50) uses 
%JL%an 'If, then, else' expression which acts in one of two ways. The 
%JR%'else' part is used to give a particular line number when starting the 
%JL%mailmerge. Normally this would be set to 1 but can be changed to any 
%JR%other number in range 1, the mailmerge will then start at that line. 
%JL%The 'then' part is used to increment the line number one at a time to 
%JR%allow you to run the mailmerge, and the 'if' part allows switching 
between the two different modes.

The expression for slot A50 is %H2%If(A49=1,A50+1,1)

The expression for slot B50 is %H2%LOOKUP($A50,$A$1$A$45,B$1B$45)

%JL%The $ sign makes the reference absolute, when used before the letter 
freezing the column and when used before the number freezing the row. 

%JR%Now use %H2%<>BRE%H2% to replicate B50 to C50 and across the rest of the row to 
%JL%the end column. At the first prompt type %H2%B50%H2% and %H2%ENTER%H2% and at the 
%JR%second prompt type %H2%C50I50%H2% and %H2%ENTER%H2%, the expression will be replicated 
all the way across to column I.

%JR%Now enter as an expression %H2%0%H2% in slot A49. Type %H2%<>A%H2% to recalculate the 
%JL%spreadsheet and the entry in row 1 of the database will be represented 
in row 50.

%L%%H4%48.....A.......B.......C.......D.......E.......F.......G.......H.......I
%H4%   0
%JL%%H4%   1    J. Smith14, HanoPETERBORPE2 3AG         John    first prdahlias

%JR%The expression line will now look very similar to line 1 of the 
%JL%database. If the word %H4%Lookup%H4% is printed across the line then slot A50 
%JR%is probably not an expression, go to A50, Type %H2%<>X%H2% and %H2%ENTER%H2% and 
%JL%recalculate again, all should now be well. Alternatively, the number in 
the slot is not within the range of numbers in range 1.


%H2%Getting Started, the letter

%JR%Firstly return to the options page and reset %H4%Wrap%H4% to %H2%Y%H2%. The letter can 
%JL%be written, or amended, and a series of @Fields used to pull the 
%JR%information out of the expression line and into the text of the letter. 
%JL%@Fields allow normal justification of the text within the letter by 
%JR%using a string of @@@'s after the slot reference. However, the extra 
%JL%@@@'s are not needed in the address because there is no text following 
%JR%the @Field. Start the letter about 10 lines below the control 
expressions.

%JR%The name for the letter is in slot B50 so the @Field is @@B50@, the 
%JL%first line of the address is in slot C50 so the @Field is @@C50@, and so 
on for all the remaining fields.

Whilst typing each line the entry will be:

%H4%@@B50@
%H4%@@C50@
%H4%@@D50@
%H4%@@E50@
%H4%@@F50@

%H4%Dear @@G50@,

%JL%%H4%I am pleased to write that your @@I50@@@@@@@@@@ won @@H50@@@@@@@@@ in the 
%H4%show yesterday.

%JR%This will print out normally and by pressing %H2%<>A%H2% for recalculation the 
next line of the database will be put into the @Fields of the letter.
The letters prepared with the examples above will look like this:

%H4%J. Smith Esq.,
%H4%14, Hanover Street,
%H4%PETERBOROUGH
%H4%PE2 3AG


%H4%Dear John,

%JL%%H4%I am pleased to write that your dahlias won first prize in the 
%H4%show yesterday.

%C%__________
%H4%Mrs. L. Jones,
%H4%18, High Street,
%H4%Crowland,
%H4%PERERBOROUGH
%H4%PE14 8QT

%H4%Dear Lucy,

%JL%%H4%I am pleased to write that your sweet peas won highly commended in the 
%H4%show yesterday.

%C%__________


%H2%Getting started, the report generator

%JL%This is simply another set of @Fields set out so as to make a neat 
%JR%report within the area of the Z88's display and positioned between 
%JR%the control expressions and the letter. An example is shown below but 
your own display should be tailored to your needs.

%H4%   2    Mrs. L. Jones,                  1stname Lucy  
%H4%        18, High Street,
%H4%        Crowland,                       prize:  highly commended
%H4%        PETERBOROUGH                    for:    sweet peas
%H4%        PE14 8QT

%JR%The words %H4%1stname%H4%, %H4% prize:%H4% and %H4%for:%H4% are typed into the desired slots 
and the other slots contain the necessary @Fields.


%H2%Printing, the letters 

%JL%Prior to printing check that the key slot is set to 1 as described 
above, the printer is ready and with paper in place.

%JR%As stated in the introduction printing is controlled by the use of a 
%JR%marked block. Mark the top and bottom of the letter with %H2%<>Z%H2% in the 
normal way, call the printer menu with %H2%<>PO%H2% and press %H2%ENTER%H2%.

%JR%Before printing the next letter recalculate the database with %H2%<>A%H2% and 
%JL%then print again when you are ready. All the letters can be printed in 
this way.

%H2%Printing, the report generator

%JR%This is printed in a similar fashion but the normal default setting in 
%JL%the options page will need altering to make a more sensible use of 
space, i.e.

%H4%       Margins: Top 0
%H4%             Header 0
%H4%             Footer 0
%H4%             Bottom 1

%JR%Headers and footers will still print so they will also need deleting. 
%JL%Mark the block of the report and print it, recalculate the database and 
%JR%print the next report. Carry on until all the reports are printed. 
%JL%Eight display sized reports will fit on one A4 page and 55 or more one 
%JR%line reports with no spaces between the lines. For this the options 
page will need altering again so that the bottom space is also 0.


%H2%Automatic Printing and Recalculation

%JL%Printing can be controlled by a CLI file so that after each letter has 
%JR%been printed the database will be recalculated and printing will then 
%JL%continue. The following CLI will print and recalculate twice and then 
%JR%stop. The file is best saved in the same directory as the Pipedream 
database.

%JR%Start in a new Pipedream file and enter the top line in slot A1, enter 
line 2 in slot A2 and so on but do not press ENTER after the last line.

~I
|[
|PO~E
.D200
|A|PO~E
.D200
|A

%JR%Save the file as %H2%2print.CLI%H2% and alter the %H4%Save plain text%H4% option to 
%H2%Yes%H2%.

%JR%Lines 1 and 2 start off the CLI, lines 3 and 4 order the first printing 
%JL%and then instructs a delay of 2 seconds to allow the print buffer to 
%JR%clear (this may not be needed at all, or may need to be increased). 
%JL%Lines 5 and 6 order a recalculation, the next printing and a delay. 
%JR%Line 7 orders a recalculation to prepare the database for the next use 
of the CLI.

%JL%To test the CLI file return to the database and check that all is ready 
%JR%to start printing, i.e. the key slot is set to 1 by entering %H2%0%H2% in slot 
%JL%A49, calculate the file and then enter %H2%1%H2% in  A49, mark the block you 
want to print and finally check that the printer is all set.

%JR%Now go directly from the database to the filer with %H2%[]F%H2%, locate the CLI 
%JL%file and (with the cursor on the title) mark it by pressing the %H2%TAB%H2% 
%JR%key, cursor down the %H4%COMMMANDS%H4%  to %H4%EXECUTE%H4% and press %H2%ENTER%H2%. Sit back 
%JL%and watch the CLI get back to your database and then printing will 
commence!

%JR%Once you are satisfied that the CLI is working correctly lines 5 and 6 
%JL%can be duplicated as lines 7 & 8, 9 & 10 etc. one less time than the 
%JR%total items that you wish to print. Lines 4 & 5 look after the first 
%JL%printing. Put line 7 in as the last line and save the file as 
%H2%8print.CLI%H2% (or whatever) as detailed above.

%JL%This system of printing is fine for letters on a Laser printer or line 
%JR%printer using continuous stationery. It is also fine for printing say 8 
%JL%reports on one page using a line printer. However, to print more than 
%JR%one report per page with a laser printer the methods detailed above 
%JL%would need altering so that there were (say) 8 expression lines and 8 
%JR%reports. Each report would have to pick up its own expression line and 
%JL%upon recalculation the expressions would have to advance 8 lines. For 
printing mark all 8 reports as a block.

%JR%Using such a method would allow the use of headers and footers which 
%JL%cannot be achieved automatically with the single report system. If 
%JL%headers and footers are required then this is probably the best way 
%JR%even when using a line printer. Note that the CLI will only need to 
contain one print and recalculate instruction for each page.


%H2%Final Tips

%JR%If required an 'If, then, else' expression can be used to test the 1st 
%JL%name for "sir" or "madame" and if found put "faithfully" after "Yours". 
%JR%If not found put "sincerely". This expression could be used in a slot 
%JR%on the expression line or in the report, and pulled from either 
location into the letter. The expression for this example  would be:

%H2%IF(G50="sir"|G50="madam","faithfully","sincerely")

%JL%The maximum number of lines that the lookup expression can handle has 
%JR%been found to be 256. This is probably quite enough for most purposes 
%JL%and in any case a database with 256 lines will be getting quite big and 
using up a lot of memory.

%JL%The use of a spelling checker to check a Pipedream database file will 
%JR%use excessive memory as both files will be suspended activities. If a 
%JL%spelling checker is available do not use it to check names and 
addresses, it will report almost every word for checking.   

%JR%However, the letter could easily be written in another file, checked 
%JL%for spelling and saved. Then, having loaded the database, the letter 
%JR%file is loaded to the main file by altering the %H4%Insert at slot%H4% option 
%JL%to %H2%Yes%H2% and giving the top left slot reference where you wish to start 
%JR%the letter. This should be at the bottom of the file, otherwise all 
sorts of layout problems will result.

%JL%A final tip is that the database should be filed to backup files (EPROM 
%JR%or tape) on a frequent basis. There is nothing worse than to loose a 
lot of work to a battery failure or fail message.

%JR%In conclusion the above method is powerful and is capable of 
%JL%development to suit individual needs. Many users of much larger 
%JR%computers would not be able to create a mailmerge without obtaining 
%JR%special software and even so might be unable to develop it to suit 
their own needs.
%CO:B,8,64%%CO:C,8,56%%CO:D,8,48%%CO:E,8,40%%CO:F,8,32%%CO:G,8,24%%CO:H,8,16%%CO:I,8,8%