User-Modifiable Reports and Letters

by Tom Cone, Jr.

The Problem:

Users of the Runtime Engine want to be able to modify the text which appears in reports and letters. They want to do this directly in Alpha Five, without bothering to export data to their word processing package. The problem becomes: How to provide a mechanism by which the user may modify and store different blocks of text which can then be printed automatically in reports and letters?

Of course it is possible to embed blocks of text in the layout of your report at design time. However, this text cannot be changed by someone with the Runtime Engine. Whenever the text must be changed they have to come back to you for the revision. What the users want is the ability to modify the text which is printed in the report without having to revise the layout of the report at all.

Ah hah! Let' s use Memo fields in the layout, you think. Alpha Five supports Memo fields which are great for storing blocks of text. You can even store formatted RTF text within them. Even better, the users can modify the text in the Memo field without modifying the report layout. So let's add a Memo field to our database table, and get busy filling in the text. Sound like just the ticket, right?

Nope! Updating the Memo field text in each record to be included in the report becomes a huge obstacle. If you want to put the same block of text on each page of a letter or report, you quickly realize that to do this you have to store the text in the Memo field of each record that will be included in the report. There's no easy way to do this. Furthermore, if you want to have different blocks of text for other reports or letters, each chunk of text would have to also be included in a separate memo field in each of your records. You face the prospect of updating all records in the table, since you don't know which of them will be needed at print time. It gets tedious fast. Frankly, it's not workable.

What you need is a separate table that contains all your text, organized in separate chunks or blocks. Then you need a way to pull a specific block of text from that table into your current report at print time. This would be similar to doing a mail-merge operation with a word processor. Your Alpha Five layout becomes the 'template', and the separate text block table becomes your merge data file. Your users could modify the text directly, without disturbing the report or letter design. Can this be done in Alpha Five?

I've wondered about this off and on several times in the last year. Until recently I've concluded that it can't be done. This always bothered me because Alpha Five seems to always have just the right set of tools or functions to solve any problem I've come across. Why not this one?

Now, happily, I've come up with a solution. This article describes it and furnishes a working example.

Assume for the moment that you've created a table called 'TxtBlocs' that is intended to contain the different chunks of text you plan to use in your letters and reports. Its structure might look like this:

Notice that it contains a character field (Textblockname) where you could store an identifying name for each chunk of text. The text itself would be saved in the Textblock field, which as you can see is an RTF memo field.

Here is how one of the records in that table might appear:

Now let us suppose that you have another table which contains a name and address list which you wish to use in your report or letter. Let's call this table 'Names'. It might look like this:

The structure of the 'Names' table seems quite ordinary, except for the field, 'Blocname'. This field will be used to hold the name of the text block we wish to insert in our report at print time.

A sample record in the 'Names' table might look like this:

Notice that the Blocname field is blank.

If our report is going to be based on the 'Names' table, then the memo field in the 'TxtBlocs' table is inaccessible to us. If we base our report on the TxtBlocs table, then the name and address information in 'Names' is inaccessible, but the memo field will print just fine. How do we easily combine them?

Why not create a set called 'Namebloc', in which the 'Names' table is parent and the 'Txtblocs' table is the child, linked one-to-one? We could use the Textblockname field in the child table to link to the Blocname field in the parent and, voilà, values from any field in either table (including the memo field) will be accessible to our report.

Here's what the set design would look like:

The linking field in the 'Txtblocs' table will be the Textblockname field, so we have to build an index on it. (Don't forget this step.)

Now we design our report and base it on the set. A simple layout might look like this:

Notice a few things about the layout. I use a rich text object for the name and address information. And I use an ordinary field object for the block of text. 'TextBlock' is the rich text memo field in my 'TxtBlocs' table. I just drop it on the report. One other thing, that hashed line running across the bottom of the layout is a page break object, used to force each record to a new page. (This is how I do my letters, since I want each to begin at the top of a new page.)

Some of you may be wondering about this approach about now because you know that if we ran the report the memo field text will not appear because the key field in the 'Names' table ('Blocname') is still empty. This means there are no composite records available to the report. After all, none of the child records are 'linked' to any of the parent records.

But this is easily corrected.

What if the button which calls the report does a little housekeeping first? Suppose it stuffs the name of the desired text block into the key field in the parent table before calling the report? If we could do this, then a link would be established between the values in the parent table record and the text in the memo field of the child table.

Here's a script that will stuff the key field value in each record of the parent table, and then preview the report:

This script can be put on a button on a form. It can exist as a global script, as shown here. If you strike the :report.preview() statement it could even be put in the OnPrintInit event of the report itself.

Petty nifty isn't it? What's interesting to me is that the keyfield, 'Blocname' can be filled with the names of different blocks of text and used over and over to print different reports or letters. Each time the keyfield changes it establishes a link to a different record in the child table. So each time the keyfield changes a link is created to a different block of text.

To implement this in a runtime setting the designer would create all the blocks of text in separate records of the child table ('Txtblocs.dbf'), and would design all the report layouts. The user would be permitted to edit the text in any block, but would not be permitted to create new blocks of text.

Is it possible to use this approach and just print a single letter or report?

Yes, just by changing the script which does the housekeeping work as the report is called. Here's an example:

Recap:

By deliberately creating a 'broken' set, one without any active links between the parent table and the child table, you can use the keyfield in the link between parent and child as a sort of pointer to the desired block of text in the child table. With a 1:1 link the contents of the memo field in the child table become available to your report. Using simple scripts which manipulate the keyfield in your parent table you can determine which block of text in the child table will appear in your report or letter.

In the normal course of database work we're accustomed to entering unique values in the keyfield of our parent tables. Alpha Five then invisibly incorporates the same values in keyfields of related child tables. The approach outlined in this article works in reverse. By establishing the text block names as key fields in the child table from the outset, we link them up to the desired records in the parent table at print time by stuffing the desired block name in records of the parent table, and then use the resulting composite records in our report.

The examples discussed are contained the downloadable zip file.

8/26/00

Don't forget, we need your feedback to make this site better!