You can do mail merge and hide empty lines - if you can avoid the bugs.

(whomever sorts these issues, place this whereever you might think is
approbiate, as long as it shows up as a result when people search for "mail
merge", "hidden paragraphs" etc.)

OpenOffice is a fantastic program, but every programmer will at some point to
his horror discover how users do things in a totally different way than he
thought... Nobody can crash a program like a user can! ;-)
You might have guessed that I too is a programmer, but after spending far too
many hours trying to get mail merge to work correctly, I decided to write this
little guide – a guide that establishes what the excellent programmers have done
that actually do work if you do as â€they thinkâ€, but also point out the pitfalls
that should be corrected in future versions.
By placing it here I hope other users of OpenOffice, who have been stuck like
myself, will discover how it can be gotten to work right now, untill the
different bugs are corrected.

Ok the situation is that I want envelopes or letters printed with mail merge,
that contains addresses like this:

FirstName MiddleName LastName
CorpName
Address1
Address2
Address3
ZIP City
Country

You might put the fields in a different order in your country (I am from
Denmark) but the situation is the same: Some people have an address that only
fills 3 lines, others have a corporation name, a C/O address, a local province
name, a different country name or whatever. So I want to print the lines nicely
together with no blank lines in between.
While Lotus WordPro and MS Word both detects that a line contains only one
field, and then automatically remove the whole line and linefeed if the field is
empty, in OpenOffice you have to do specify this manually. This is done by
inserting a special field called a â€hidden paragraphâ€, which is very well
described in the help file – if it were not for all the bugs that will make it
silently fail, with the result that you don’t understand why lines are always
shown or always hidden no matter what you do.

OK – the main bug is, that any kind of special signs will make it fail. Spaces,
hyphens and everything else than simple letters and numbers must be avoided
everywhere and I do mean everywhere (except possibly the name of the directory
where the file is placed). I should probably also say: use only English letters,
since I guess that our additional letters in danish and other languages will
give the same problems.

To avoid other possible filter bugs that there might or might not be, I always
export my address list from whatever piece of software they come in, so that I
can open them in a simple OpenOffice spreadsheet.
I then go through the top row of the spreadsheet which should contain the field
names of this single database table of addresses. I make sure that each field
name only uses letters and numbers and no other signs or spaces. And I make sure
the sheet name also only uses letters and numbers.
Then I write it as an OpenOffice spreadsheet document  (or OpenDocument
spreadsheet) and again in naming the file of the spreadsheet I make sure only to
use letters and numbers and nothing else.

Good. Now in a new OpenOffice text document write your letter (or set up your
envelope) that you want the addresses merged into.
To connect to the database (by which I mean the spreadsheet we just did) you do
it differently depending on your version of OpenOffice:
1.1.4: Choose “Toolsâ€->â€Data sourcesâ€, press “New datasource†and give it a name
(remember only letters and numbers) and specify the spreadsheet it should use.
1.9.93: Choose “Editâ€->â€Exchange databaseâ€, press “Browse†and select the
spreadsheet. Press the “+†symbol so you can see the sheet you want, and press
“Defineâ€.

You can then select “Viewâ€->â€Data sources†and again in the explorer to the left
open up to the sheet in the spreadsheet you want. There is buttons for filtering
the data that works excellently, and there is buttons for previewing the data
(if you have selected a whole row and not just a single field), but again you
can not rely on the “Hidden paragraphs†to work with this preview – another bug.
Often it will ‘think’ that is on the row that was last printed, no matter what
you try to do with the preview.

To add a mail merge field to your document, simply drag the field into your
document (hold the mouse button down on the name of the field in the dase source
browser).

Next step is to make sure lines are hidden correctly.
First make sure your configuration is correct: In “Toolsâ€->â€Options†under
“OpenOffice Writerâ€->â€Formatting aids†there is a list of checkboxes that
control what is always displayed. Make sure that there is no check mark next to
“Fields: Hidden text†and “Fields: Hidden paragraphs†- otherwise you will
always see/print hidden text no matter what you try! (I don’t know why these
options are there or why they are enabled by default. I guess the programmer of
that part did not use mail merge ;-)
For use while you design your mail merge document you find back on the main menu
two simple points: “Viewâ€->â€Field names†and “Viewâ€->â€Hidden paragraphsâ€.
If you have a check mark on the “Hidden paragraphs†then you can see/print them
even though they should have been hidden. This is of cause normally off.
If you have a check mark on the “Field names†then you can see all the special
fields in your document, including the precise name of each database.table.field
pair that you earlier dragged and dropped from your data source browser into
your document.
Now for each line that you want hidden if there is no text in a database field,
choose “Insertâ€->â€Fieldsâ€->â€Otherâ€, select the tab “Functions†and highlight the
one called “Hidden paragraphâ€. There is a text box where you have to enter a
condition, and here you type the database.table.field name EXACTLY as you can
see OpenOffice displays them with “Field names†selected. BUT before the name
you place a “!†or a “NOT†(the later you have to translate into your own
language if you use a localized version) AND A SPACE!!!
An example condition based on a spreadsheet called “MyMailing.sxc†containing a
sheet named “sheet1†with a field called “Address2â€:
! MyMailing.sheet1.Address2

Now as I said before: Any spaces (except the one after the “!†sign) and any
other special signs (like “-â€) will mean that it does not work, and the program
will think that you have written some fixed text as a condition instead of a
database field.
Add more “Hidden paragraphs†as you need them, then save your document in one of
the OpenOffice native formats and select “Print†- which will give you the
option to print all the rows,  or just specific numbers of rows (very nice if
the printer jams and you loose the print job – you just browse through the list
and find the last that was printed OK, and print from the row number to the end.)

After happily having done a successful mail merge, you can even re-use the
document next time you want to mail merge to a different spreadsheet. Just
choose another database & table with “Editâ€->â€Exchange databaseâ€, and it will
(at least in OpenOffice 1.9.93) correct all your database fields to point to
this new one, if you just have used the same field names in both databases.
It will even change the condition in the “Hidden paragraphs†to point to the new
database and table (sheet) names!
Just remember to check for spaces, “-†and other funny stuff before you do it,
because it will happily change the “Hidden paragraph†condition into one with
some of the illegal signs in it, but it is not always able to change it back. In
that case, with “Field names†visible, double click on the “Hidden paragraphâ€
marker to go in and correct it.

One day when you are annoyed with all the old connections to databases (if you
do a lot of different mail merges), here is where you remove them again,
depending on which version you use:
1.1.4: Choose “Toolsâ€->â€Data sourcesâ€, right click on the name of the data
source, and select delete.
1.9.93: Choose “Toolsâ€->â€Optionsâ€, Select “OpenOffice Baseâ€->â€Databases†and you
can highlight them and press the delete button.

I hope this will help other people who were stuck like myself.
And I hope this will only be a temporary work-around, until some programmer
tackles the nightmare of strange signs in conditions. 

Hey, an idea: how about something as simple as defining “<†and “>†to mean
“what is inside here is either a database.table.field name or a variable, and if
it cannot be found then open a dialogue box and complain with an errorâ€. Just
such a simple error message as “field not found†would have saved me more that
48 hours trying to figure out what was wrong!!!

Another idea that falls under enhancements: All database related functions
should be in one menu, where one can add, change and delete connections. And/or
the Data sources browser should have buttons and right-click menus to add,
change and remove connections. It would be the obvious place to have them there,
instead of having to look through all the menus and options to find where it is
hidden!
Wizards are nice, but they always follow the “thinking†of the person who
designed them, and users are so very different that they will never cover
everybody’s needs. But by grouping all related functions in a menu, it will be a
lot easier for a user to adjust and use just the things he or she needs.

ML
Allan Kindberg
allan@kindbergs.dk