Apache OpenOffice (AOO) Bugzilla – Full Text Issue Listing
|Summary:||Doing mail merge with hidden paragraphs (lines) despite bugs|
|Component:||code||Assignee:||AOO issues mailing list <issues>|
|Status:||CLOSED WONT_FIX||QA Contact:|
|Priority:||P3||CC:||cno, elish, issues|
|Issue Type:||ENHANCEMENT||Latest Confirmation in:||---|
Description kindberg 2005-04-20 03:59:39 UTC
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 email@example.com
Comment 1 michael.ruess 2005-04-20 06:42:54 UTC
MRU->HI: pls evaluate.
Comment 2 michael.ruess 2005-04-20 09:42:52 UTC
forgot to reassign...
Comment 3 h.ilter 2005-05-26 11:36:55 UTC
Reassigned to CJ
Comment 4 cno 2009-12-10 14:22:05 UTC
hi allan, If you're still there: you might be interested in issue 107476 and the discussion on dicuss@ux. Since the explanation of this (your) issue is rather long, may I suggest to add specific ideas to 107476 and close this one? Thanks, Cor
Comment 5 Edwin Sharp 2014-03-13 14:45:53 UTC
Please attach example.
Comment 6 Edwin Sharp 2014-03-26 08:44:40 UTC
No info from author.