OpenOffice.org Training, Tips, and Ideas

Slight bug in OpenOffice mail merge

In OpenOffice 3.2 and 3.3 mail merge printing, mail merge doesn't work if you restrict output using Range. However, restricting output using Selected Records works fine.

Here's how it works. Choose File > Print and click yes to print a form letter as usual. Then you have the option to print all records; to print selected records; or to print a range.

With the first two, everything works fine. But if you type in a range like records 1-3 to print, mailmerge just gives you blanks.

This isn't really a big issue since Selected Records works fine. But can cause problems if you don't know it and if like me you were just teaching an envelopes mail merge class. :) Luckily however I figured out the issue during break.

I

How to hide lines if a field is empty

When you do mail merges, especially those involving addresses, some people have apartments and some don't.

If you are using an Address2 field in the address block, you need to hide that line if it’s empty.

Here’s how to hide any line. Let's assume here that the database I'm using for the mail merge is called nameslist_newcustomers.odb, that the table I'm using is just called Sheet1, and the address 2 field is called, well, Address2.

Get the mail merge document ready, with all the fields brought in. Be sure that you have the Address2 field, or the field you want to hide, on a line of its own, since the whole line, i.e. everything in that paragraph, will be hidden. 

Click to the left of the Address2 field. (You can click anywhere on the line, but it’s simpler to always do it one way.)

Choose Insert > Fields > Other.

Click the Functions tab, then select Hidden Paragraph.

 

Type the following; the fieldname that might be empty is the fieldname you want to specify. This means everything on that line will be hidden if that field is empty:

Syntax:   ![databasename.tablename.fieldname]
Example:  ![nameslist_newcustomers.Sheet1.Address2]

Click Insert.

You won’t see anything appear; it will look like the picture below.

If you want to prove to yourself it’s there, click to the right of the Address2 field, press the left arrow once, and press space three times. You’ll see there are two fields, the hidden paragraph field and the regular Address2 field as shown on the right. Now press Backspace again three times so that you won’t have extra spaces in the document.

You’re all set. Now when you print  the line that the hidden paragraph field is on will show only if there is a value for that record for Address2.

Data type issues in spreadsheet-based mail merges in OpenOffice, or why your mail merge doesn't work with 3.2

Let's say you're putting together a mail merge listing the fees people in the county have to pay for getting their new cat license. You'd put together the spreadsheet data kind of like this, then create a database based on that spreadsheet.

Name    Fee

Bob      $12

Jean    $13

Your mail merge document, with the various prompts and the field names, would look kind of like this:

Name: <Name>

Fee for license: <Fee>

So far so good. But let's say that sometimes there's no fee, perhaps organizations don't need to pay for cat licenses. So your data might look like this:

Name                          Fee

Bob                             $12

Jean                            $13

Library                         N/A

BUT that will not work because mail merges as of 3.2 (possibly earlier) don't like you to mix your types of data in a column. You can have all text, like N/A, or all numbers like 12, but not both.

Here's an example of some test and screen shots I took.

Here's the spreadsheet.

Here's what is in the database, and therefore what a mail merge would have access to.

 

So: in the scenario I outlined you could:

just put in 0, or just blank, instead of N/A

OR do this, which is workable but a bit more complicated.

Name                          Fee        Applicable or Not

Bob                             $12      [leave blank] 

Jean                            $13      [leave blank] 

Library                  [leave blank]       N/A

Then for your mail merge put both the fee and the applicable or not fields next to the Fee prompt. If there's no data the field won't take up any room, and only the correct field will show.

Name: <Name>

Fee for license: <Fee><Applicable or Not>

Note: This change in 3.2 really is a proper approach to databases; having mixed types is not proper database structure. But it is probably causing a few headaches.

Free Online Seminar Thursday February 25th: Mail Merge

Ever wanted to just be guided through how to do a basic mail merge? Here's your chance! I'm going to be testing out my new online seminar tool, and I'd like your help. I'll be hosting a 2-3 hour seminar on how to do mail merges, using a spreadsheet for your data source.

• Creating the data in the proper layout
• Creating a new database based on the spreadsheet
• Creating a letter using fields from the spreadsheet
• Creating labels using fields from the spreadsheet
• Sorting and selecting the records you want to print
• If time permits: envelopes

DimDim is the tool I'm using, and it purports to support Windows, Mac, and Linux.

I'll provide a short downloadable guide to the mail merge process, as well, during the seminar as a reference.

Click on the widget to register. I can have up to 20 participants, and it's first come first served. Please register only if you can attend. It's 10 AM mountain time on Thursday February 25th.

When you register you'll receive an email with the call-in number and URL. You'll need to call in from a phone in a quiet location. The phone call is not free but should be relatively low-cost.

Thanks for helping me out with this test, those who register, and I look forward to clarifying the mail merge process.