Triggering Mail Merges to Go to the Next Record in OpenOffice.org

Contributed by solveig February 25, 2007  
Tagged: OpenOffice

Ever wonder how to trigger your mail merges to go to the next record, on the same sheet of paper? This tech-recipe covers the important but simple way to make that work.

When you have information from multiple records on the same sheet of paper, in mail merges for lists, downloaded label templates, and other mail merge documents, you’ll need to do something extra if you want more than one record on each sheet of paper. The first sections of this article explains how mail merges work regarding when the next record is chosen, and why. If you’re already familiar with this, go straight to the solution in the third part of the article.

• How Mail Merges Work by Default, and Why That’s Not Always What You Want
• Why the Mail Merge Doesn’t Trigger the Next Record All the Time
• Triggering a New Record Whenever You Want It by Using the Next Record Field

How Mail Merges Work by Default, and Why That’s Not Always What You Want

A mail merge is a connection between a document and a database. The document pulls information for certain fields, like Name and Address and Birthdate, out of the database. You set up your mail merge document like this.

Figure 1: Setting up the mail merge document

The information for the first record (the first person in this case) is printed, then the second record, and so on.

Figure 2: The printed results of a mail merge

Here’s the question, though.

When you do you go the next record? What makes the mail merge document switch so that we see the information about Miranda?

In labels, it makes sense for information about the next record to show up in the next label, which is the next time that the field appears. And that’s how it works when you create labels—at least, when you create labels by choosing File > New > Labels. If you’re using different label templates, though, you get the same name and address in every label area until you go to the next page. 10 or 30 labels for the same person. The logic to trigger the next record isn’t built into those templates.

In regular documents, like a report listing the people in the department and their birthdates so you could remember to have cake on the appropriate days, then you would also want new information every time the field appearead again or every time there’s a carriage return.This is how things work when you use the report wizard, but it’s not how things work when you create a mail merge from scratch.

So the question is, when you’re using templates or your own documents that aren’t based on OpenOffice.org wizards, how do you trigger the Next Record without waiting for the next page?

Why the Mail Merge Doesn’t Trigger the Next Record All the Time

That’s because the default behavior is designed for what the programmers think is the most common use for the mail merge feature; the standard letter. If you’re creating a mail merge letter, you sometimes want the same information for one record to repeat. Sometimes your letter goes for three pages. It certainly doesn’t make sense to show information from the next record after each carriage return, or the next time the field shows up.

Here’s how you might set up a typical mail merge letter.

Figure 3: Setting up a mail merge letter

Here’s what it would look like if you triggered a new record each time you got a new carriage return, reuse of a field, or new page.

Figure 4: Unfortunate results of a mail merge that flips to the next record each time a field appears again

Here’s a summary of how a new record is triggered by default for each type of document.

Documents that work correctly and don’t need modification or which can’t be modified

• In the labels you create by choosing File > New > Labels: Each time you have a new box for another physical label, you get the next record. Ditto with envelopes. (You don’t have to worry about envelopes at all. At least, not about triggering the next record correctly.)
• In the Mail Merge Wizard under the Tools menu: with each new page. I’m not going to talk anymore about this wizard since there’s nothing to be done to change it.

Documents that sometimes need modification. You can change these, so don’t worry—it’s just a description of the default behavior, not what you’re stuck with.

• In the labels you download from WorldLabel or from other sources that aren’t integrated with the labels wizard: you don’t get a new record until the next page. (This might not be true for all templates but expect it until you see something different.)
• In any standard text document you create and then add mail merge fields to: you don’t get a new record until the next page or until the content in your text document ends, whichever comes last. If you have a three-page letter you send out to your customers, all three pages print with the fields from the first record, then you start again with those three pages for the second record, and so on.

Triggering a New Record Whenever You Want It by Using the Next Record Field

It took me a whole lot longer to explain the problem and the default behavior than it’s going to take to describe the solution.

Every time you want to trigger the next record, choose Insert > Fields > Other, Database tab. in the Type column select Next Record, and in the Database Selection list select your database and table. Click Insert.

Figure 5: Inserting the Next Record trigger

You will see either nothing appear in the document, or a very small grey speck. If you insert it by itself you’ll see it, but you won’t see it when you insert is, as you typically will, next to another field.

Figure 6: How the Next Record field looks (at its most obvious)

You don’t even need to click Insert every time. You can copy and paste that small gray field to the next line, or the next label, or, another four inches down the page—wherever you want the next record to begin.

Here are a couple ways you could use it.

Labels

In labels you create from WorldLabel templates or other templates, set up the first label normally and copy it to the second label area too. Then insert the Next Record field in front of the FirstName field in the second label area, and copy the whole label. Paste it to all the other label areas in the template. You only need to insert the Next Record field once per record; not in front of each field.

Figure 7: Using the Next Record with labels

Reports or Lists

If you don’t want to use the Reports function in the database management window, or if you just want something simpler, you could set up a report like this. Drag the first line of fields out, then press Return and copy and paste them to the second line.

Click to the left of the first field in the second line and insert the Next Record field. You only need to insert the Next Record field once per record; not in front of each field.

Now copy that second line and paste it again until you’ve filled the page.

Figure 8: Setting up a report using the Next Record

In Conclusion: Control Doesn’t Always Mean Simplicity

As we know from using LaTeX and Word, having control over exactly what you want to do doesn’t always mean it’s easy to figure out. There are two logical and very different ways for the mail merge to work. OpenOffice.org chose one, and if you want it to work the other way, well, it’s just a matter of inserting the Next Record field. On the plus side, you only need to set up your label template once, then use it again each time you print new records.

---

About the Author:Solveig Haugland has worked as an instructor, course developer, author and technical writer in the high-tech industry for 16 years. She is the author of the OpenOffice.org 2 Guidebook and blogs about OpenOffice.org at openoffice.blogs.com. To inquire about OpenOffice.org training and consulting, see her business site or email training@getopenoffice.org.