Suppressing Blank Address2 Fields in OpenOffice.org Labels, Envelopes, or Other Documents, for Mail Merges

Here it is -- suppressing a blank Address2 field in your mail merges. It's not extremely simple, but it's reasonably straightforward and it works.

Here's the situation we're addressing. Sometimes your addresses have two lines for the address part, sometimes they don't.

Bob Jones
101 Main
Suite 55
Boulder, CO 80022

Marion Silverman
888 105th Ave
Broomfield, CO 82211

But you have to put in the <Address2> field for everybody, since it's a mail merge. The setup has to be the same.

<Firstname> <Lastname>
<Address1>
<Address2>
<City>, <State>, <Zip>

But with this approach, your addresses look like this.

Bob Jones
101 Main
Suite 55
Boulder, CO 80022

Marion Silverman
888 105th Ave

Broomfield, CO 82211

Ick. How do you suppress that second Address2 line and the corresponding carriage return if there's no content for a particular record, for that Addres2 field?

Select the Address2 field in your mail merge document, choose Insert > Section, and create a conditionally hidden section with this formula.

databasename.tablename.fieldname EQ ""

Here are the details, using an example of labels.

1. Create the labels for mail merge as usual. File > New > Labels, select your database and tables, insert the fields, etc.

2. Choose the Synchronize Contents checkbox.

3. Click New Document.

4. Here are the labels.

5. Turn on nonprinting characters if they're not on already.

6. Select the first soft return, shown selected.

7. Press Return or Enter to replace it with a hard return.

8. Repeat, to make them all hard returns.

9. Click Synchronize to update the other labels to be the same.

10. Select the Address2 field.

11. Choose Insert > Section.

12. Name the section Suppress. Select the Hide checkbox and type the following condition. The screen shot shows the syntax.

Syntax
databasename.tablename.fieldname EQ ""    (the last part is two double quotes together)

Example
databasewithtwoaddresslines.Table1.Address2 EQ ""

NOTE: if you are using the Thunderbird address book as a data source, you need to use square brackets if the field name includes a space (i. e.: [Address 2]=="") to hide the second line of the address if it the Address 2 field is blank.)  I would suggest in general avoiding field, table, or database names with spaces.

http://www.oooforum.org/forum/viewtopic.phtml?t=43528&highlight=

Click the screen shot to see it bigger. It shows the syntax, not an actual example.

13. Click Insert.

14. Click Synchronize.

15. Now preview the data or print the data and you'll see that it prints correctly.

15. If you need to change the section, select it in the first address and choose Format > Section. Select the one named Suppress for the master label and make changes, then click OK. Click Synchronize again in the labels.

---

---

Comments

[David]

I've followed the guide but no joy. Still blank address lines however I try it.

[Solveig Haugland]

Hi David,

I'm sorry it didn't work. Try again in a different document; sometimes it didn't work for me the first time but it always worked eventually.

[Monty]

Love your blog!

Any idea on how you would do a mail merge when you have documents 2-up or 4-up on a page? For instance, if you're printing postcards you may have 4 to a page... but how do you keep [FIRST NAME] from printing the same person's name on all four cards?

Thanks!

[Paul]

I eventually got this working with some difficulty.

I'm using a mysql datasource and found that in the hide expression I had to use

datasource.database.table.column EQ ""

moreover I found that my datasource name oo-email caused the process to fail because it included the '-' character. Changing the name to ooemail meant I had

ooemail.emailmarketing.advertisers.address2 EQ ""

this finally worked but it took a couple of hours to figure out.

Amazing software open office. Would be nice if there was some documentation for such an important feature.

[Anon]

Instructions for Ooo 2.30 (& published 12-15-2007)
This .pdf file has the "Suppress" instructions as different than above:

http://documentation.openoffice.org/manuals/oooauthors2/0211WG-UsingMailMerge.pdf

Along with "hard paragraph breaks] like above,
around page 19, as you will read, and you must to follow instructions too long to put here, the instructions say to use this, in a different place:

![Database.Table.Field]

and it works... I suggest you ignor this article and reference the .pdf file above for a quick way to suppress a field/empty line in an envelope.

[Slybynight]

Got this working eventually - nice fix but dont forget
Its CASE SENSITIVE!!!! so if your data source is XmasList.Sheet1.Add2
xmaslist.sheet1.add2 wont work! - took me a while to figure that out!

[Solveig]

Good point about the case sensitivity, I'll add a note in the blog.

Solveig

[Solveig]

Thanks for the tip about the alternate instructions, I'll check'em out.

Solveig

[David Sarnowski]

Do you know if you can suppress more than one line at a time. I have suppressed Address2 successfully, but I have another field I want to suppress if empty as well. Address2 goes fine, but when I try the second, it just disappears completely regardless of value.

Cheers,
Dave

[Solveig]

Hi David,

You'd have to set up everything again for everything you want to suppress -- the conditional statement, etc. But yes, you can suppress anything conditionally using the same approach.