Performing a mailmerge

Overview

A mailmerge converts a template document into an expanded document. The expansion is created by replicating the template one time for each record in a data set. A template can include one or more loop sections (which are replicated for each record) as well as text before and after the loop (which only appears once in the result).

Use the mailmerge feature for data reporting for print or web publication, to print form letters, or to get data into a word processor for some other use. By reporting to external text files, you leverage the full features of a word processor in your reports, which can't be done with the built-in report building block. For example, you can create columns, or reformat the report to different paper sizes, or specify the page breaks manually. By exporting to an HTML document, you can create reports suitable for web publishing.

The general mailmerge styles are:

• Simple: In this case, the template has no special formatting, so the results flow one after the other.

• Form letters: In this case, the template should begin with a page-break, so that each letter starts on a new page.

• Tabular reports: In this case the template consists of a table with two rows - a header row and a detail row. Only the detail row is in the loop section.

Axisbase supports these formats:

• plain text

• html or xml - for use with web reporting

• RTF - To use mailmerge with formatted text documents, save the template from your word processor in RTF format. Axisbase cannot read files saved in Word format.

Basic steps

To perform a mailmerge,

1. Create a template document which specifies where record data will be inserted. Save the template as plain text, RTF format, or HTML format.

2. Create a bulk operation in Axisbase. The data source of the bulk operation defines the records you want to insert into the template document. Create a process step in the bulk operation to perform the mailmerge, where the "read file" (the template) and the "write file" (the result) are specified.

3. Run the process step.

4. Use a word processor to open the resulting document. (or a browser if you are using HTML format). From there you can print it or save it as required.

Creating the template

It's easiest to understand how to format a template by seeing an example:

$loop MARS.inventory$
Item: #MARS.inventory.name#
Pieces on hand: #MARS.inventory.qty#
(To reorder, call #MARS.inventory.supplier# at #MARS.inventory.phone#)
$endloop$

In many word processors' mailmerge tools, you use a wizard and insert special mailmerge fields. With Axisbase mailmerge, you just type the actual characters as shown. To insert a field from the data set, use the full name surrounded by # characters. The full name is usually the Axis ID, type name, and property name serparated by periods.

The $loop construct must be followed by a space and the table name as defined by the data subset, and a final $ character. Only the section between $loop...$ and $endloop$ are replicated.

Mailmerge Tips

• It's important to close the template and result files in your word processor before running the mailmerge in Axisbase.

• If your results have too many or too few blank lines between repititions, experiment with where you place $loop and $endloop$. If the $loop construct is on a line by itself, this causes a blank line.

• Axisbase requires you to store external files; be consistent about where you save the files and what you name them.

• If using RTF format, and the merge is failing to insert data, or the formatting is inconsistent - particularly if the first repetition has different formats from the others - then experiment with the placement of $loop. It's helpful to understand that the RTF format saves sequences of characters interspersed with formatting codes. It is important to type the $loop construct before the formatting of the first line of your loop begins, so that those codes are part of the loop. The simplest way to deal with issues like these are to remove all formatting (in Word, Ctrl-A then Ctrl-Space), then carefully apply formatting to the document without formatting the $loop..$ and $endloop$ constructs.
  
• Axisbase will use its internal numeric data formats for mailmerges when you might expect to see a friendlier format. For example, if you have a record type "order" with a customer field, then Axisbase normally displays the customer name. But in fact it stores a number (a key) identifying the customer with the order, and this key is what gets exported in a mailmerge. If you see very large negative numbers in your output, this is what is happening. To fix this, you must set up a data subset as the source for the bulk operation, and in the data subset, join in the data you want exported. For example, suppose you are using a data subset that is based on all records of type MARS.order. MARS.order.customer is a key (a number), but you want to export "MARS.customer.name" for the customer identified by that key. In the data subset, press Add Table, and choose All Records of type MARS.customer. Add a Join Tables step to the order table. For the Left Side and Right Side, you will choose two columns that have the same value. For the Left side, choose the column MARS.order.customer, and for the right side choose the column MARS.customer.key. Now the column MARS.customer.name will be available to use in the mailmerge.

Master/Detail data (nested loops)

Axisbase mailmerges can process master/detail data relationships, which is not found in most mailmerge tools. For example, suppose you want to send order confirmation letters to customers who ordered in the last week, and include the line items that they ordered in the letter. In this case, your data subset would define two tables linked in a master/detail relationship: MARS.order and MARS.lineitem. The template would look something like this:

$loop MARS.order$
Thank you for ordering the following items:
$loop MARS.lineitem$#MARS.lineitem.qty# #MARS.lineitem.description#
$endloop$
If you have questions about the order, please call us.
$endloop$

Axisbase will replicate the outer loop section for each order; within each replication of the outer loop, it will replicate the inner loop section for each line item in the order.

Independent loops

There are cases where you might want multiple independent loops in a mailmerge.

Example 1: Updating a web page containing several lists of company statistics. You may have an internal company web page that lists daily or weekly sales by region, per product, or all open orders, or some other lists. You can design a data subset that includes multiple independent tables. In the mailmerge template, you can specify multiple independent $loop sections with any amount of fixed text before, between or after the loops. By running the single mailmerge, you can update all the lists on the web page at once.

Example 2: Nested independent loops. In less common circumstances, you may want to repeat the entire contents of a table within each section of an outer loop. For example, if you want to generate weekly letters to job seekers, and include in the letters the list of currently open jobs, then you may set up two unrelated record types: job seekers and open positions. You would then create a data subset with all records of both types, and a template that includes a loop of the open positions nested in a loop of the job seekers.