=====================================================================
Mail merge using the string replacement function
=====================================================================
Product: R:BASE Version: 4.5 Plus! or Higher
=====================================================================
Area: Reports Catalog: Forms, Reports, Labels
=====================================================================
Use the new string replacement function, SRPL, to do mail merge
letters in R:BASE. You won't need to export data to your word
processor to make letters that say things like, "Congratulations -YOUR
NAME HERE- you are a finalist ...". The name, or other data, can now
be inserted correctly into the middle of the text with no extra spaces
around it. You can replace whole words only (flag=1) or a character
string even if it appears in the middle of a word (flag=0)
A mail merge letter is one that prints a letter with database
information inserted into the letter at appropriate places. The letter
is printed for each name or piece of data in a table. You can now
easily produce this type of letter in an R:BASE report. The two new
features that are used are the line end character and the SRPL string
replacement function. The Concomp database is used as an example.
Create a table, mergeletters, to hold the text of the letter. In the
simplest case, the mergeletters table has two columns: letter_id and
Letter_text. Letter_id is an INTEGER column used to identify the
particular letter, and Letter_text is a NOTE column that contains the
text of the letter.
Here is the actual text entered in the letter_text column for our
example. Where we want to substitute a name from the database in the
text of the letter we put in a keyword, in this example [NAME]. The
keyword is then used later in a report expression with the SRPL
function.
Consolidated Computers has a policy of rewarding exceptional
employees. The management looks for employees who work hard and are
willing to go the extra mile. Employees who show leadership and do
their jobs with skill and enthusiasm deserve to be rewarded. That is
why you, [NAME], are receiving this letter. In recognition of the fine
work you have done for our company, this letter is to inform you of
your participation in the employee bonus program. __The Consolidated
Computers' employee bonus program consists of two options. You have a
choice of a bonus check or stock options in our corporation... etc.
Make use of Reports automatic word wrapping feature and print the text
as a single paragraph, or when you enter the data, use the new LINEEND
character to place "soft" carriage returns in the data. This allows
the note field to print in two paragraphs. The default LINEEND
character is the "_"(ASCII character 254). To make a line between
paragraphs you need two line end characters. Don't put a space after
the second one unless you want to indent the next paragraph.
The report is pretty simple. It is based on the employee table, and
uses just two variables for the letter text. They are:
1: NOTE: H1: vletter = letter_text IN mergeletters WHERE letter_id = 1
2: TEXT: H1: vmerge_letter = (SRPL(.vletter,'[NAME]',empfname,1))
The first variable looks up the letter text from the mergeletters
table. Use a literal value, in this case 1, or a variable to identify
the letter text to find. The vmerge_letter variable searches the
vletter variable(which is holding your letter text) and replaces the
string [NAME] wherever it finds it with empfname (the employee's first
name). The flag is 1 so only whole word matches are replaced. You'll
probably want to define other variables to concatenate first and last
name and city, state and zip for the letter address.
One letter is printed per page. Define a breakpoint on employee ID and
say Yes to Form feed after header. Then locate all the fields on break
header, H1, lines. You won't have any sections on the report except
for the break header section.
Locate Vmerge_letter as a wrapped field on one break header line with
the width (margins) that you think looks best for the letter. When the
report is printed, the text has the employee's name inserted and the
line end character creates a paragraph break in the output.
Locate the name and address fields as desired for the letter heading.
Define additional variables as needed. Locate #DATE, the system date
variable, and use a picture format to display it with the month
spelled out. Place the salutation and closing on break header 1 lines
as well. Use the new report expression evaluation time feature to
evaluate all the expressions at the break header only.
You now have a mail merge letter that can be printed without having to
export data. The letter might look like this. Note how the different
length names are placed in the letter text with no extra spaces.
December 03, 1993
Peter Coffin
4105 29th Ave N.E.
Duvall, WA 98004
Dear Peter,
Consolidated Computers has a policy of rewarding exceptional
employees. The management looks for employees who work hard and
are willing to go the extra mile. Employees who show leadership
and do their jobs with skill and enthusiasm deserve to be
rewarded. That is why you, Peter, are receiving this letter. In
recognition of the fine work you have done for our company, this
letter is to inform you of your eligibility for participation in
the employee bonus program.
The Consolidated Computers' employee bonus program consists of
two options: a bonus check or stock options. ... etc.
Sincerely,
John Smith
President
December 03, 1993
Darnell Williams
8806 88th Street
Seattle, WA 98103
Dear Darnell,
Consolidated Computers has a policy of rewarding exceptional
employees. The management looks for employees who work hard and
are willing to go the extra mile. Employees who show leadership
and do their jobs with skill and enthusiasm deserve to be
rewarded. That is why you, Darnell, are receiving this letter. In
recognition of the fine work you have done for our company, this
letter is to inform you of your eligibility for participation in
the employee bonus program.
The Consolidated Computers' employee bonus program consists of
two options: a bonus check or stock options. ... etc.
Sincerely,
John Smith
President
R:BASE Exchange Technical Journal
=================================
This technical information comes from Microrim's award winning
technical journal, the R:BASE Exchange. The R:BASE Exchange provides
R:BASE users with usage tips, programming techniques and solutions to
common problems - information that can help you get even more out of
R:BASE. Available on a yearly subscription basis, or free with
Premium Support. For more information, call 1-800-628-6990.