Note: Everything is case sensitive. Also when you type the two "" quotes, don't put a space between them. If you have spaces in your database, this won't work. Either change the database field names, or create a query based on the table, and change the field names in the query. Then base the labels on the query.
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.
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.
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.
I've followed the guide but no joy. Still blank address lines however I try it.
Posted by: David | October 11, 2007 at 07:16 AM
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.
Posted by: Solveig Haugland | October 21, 2007 at 11:34 AM
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!
Posted by: Monty | October 23, 2007 at 09:46 PM
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.
Posted by: Paul | November 04, 2007 at 08:13 PM
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.
Posted by: Anon | December 20, 2007 at 06:41 AM
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!
Posted by: Slybynight | February 28, 2008 at 08:53 AM
Good point about the case sensitivity, I'll add a note in the blog.
Solveig
Posted by: Solveig | February 28, 2008 at 09:49 AM
Thanks for the tip about the alternate instructions, I'll check'em out.
Solveig
Posted by: Solveig | February 28, 2008 at 09:50 AM
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
Posted by: David Sarnowski | April 09, 2008 at 05:22 AM
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.
Posted by: Solveig | April 09, 2008 at 05:25 AM
Can you suppress a field that has a space? Like "Address 2". I am stuck working with a file that I cannot edit field names on.
Posted by: David Sarnowski | August 06, 2008 at 04:46 AM
http://www.batterygoshop.co.uk/batterycharger/fujitsu/fujitu-19v-4.22a-80w.htm Genuine Fujitsu FMV-AC322 19V 4.22A 80W AC Adapter NEW ,
Posted by: laptop batteries | November 10, 2008 at 11:36 PM
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.
Posted by: tower defence | March 30, 2009 at 06:45 AM
I am stuck on getting the suppress instructions to work. The Synchonizing of labels is great for formatting, however I get an entire page of label with data from entry number 1 in my database. How can I get each label to populate with a new piece of data from the database. This works if I turn off synchonize, however then I can't apply the suppress function to every label.
Please help.
Posted by: KH | April 02, 2009 at 02:19 PM
How would I go about previewing the data on the labels before printing? I see you have a view there with the data previewed, but I don't know how to do that. Help please!!
Posted by: Marjorie Hall | June 10, 2009 at 08:15 PM
Hi Marjorie,
The best way is to just print to a file. Choose file > print, click yes, and instead of printing to a printer, choose File and the single option. Click OK, name the file. Then open that (plain old Writer) file, don't choose to update links if prompted, and you'll see the data.
Posted by: Solveig | June 10, 2009 at 08:29 PM
http://www.gamegoldme.com/
http://www.wowgold-powerleveling.com/
http://www.wowgold-wow.com/
http://www.wowpowerleveling.me
http://www.watchrolexshop.com
http://www.wowgold-wow.com/wow-power-leveling
http://rs-runescapegold.com/
http://www.watchrolexshop.com/wow-power-leveling/
http://www.cheap-lotrogold.com/
http://www.globalsale.me/Aion-gold-083.aspx
http://www.cheap-gamegold.org
http://www.gamegoldvip.org
http://www.globalsale.me/
Posted by: wow power leveling | July 05, 2009 at 11:37 PM
I can merge to labels into a file OK and open it but cannot format the resultant table. Cannot get the columns to highlight etc. Is this a feature of OO. Cannot find a solution in Help or on the web
Posted by: Robert Watson | January 14, 2010 at 07:26 AM