[Solved] Keeping fields live in saved mail merge file

[RoryOF]

I'm attempting to create a mail merge document in OpenOffice 4.1.1 with several fields, which can be used over and over again to print selected records from my data base source file. I'm following all the instructions to create the form document, and when it's newly created, it prints beautifully on the selected records. However, when I save the file (either in .doc or .odt) and the re-open it to print another batch of records from the data source file, the fields are no longer 'live' and the data will not flow into the fields from the data source file. Thanks for answering again if this has been posed previously; I could not find any results in searches of the forums and topics, and none of the tutorials seem to address it. Deadline is closing in on me; would really appreciate some help. Thanks!

[Villeroy]

Hit F4, right-click your database entry and choose "Edit Database". Which type of database is indicated in the status bar of the database window?

[ColoQuery]

When I do as you suggested I get options of Tables, Queries, Forms and Reports. When I click on Tables, the table I have chosen shows as connected, and its columns come up in the initial window. I have a screenshot if that would help (pretty small; hope you can see the items!) And, at the very bottom-left corner, it says "Spreadsheet."

[Villeroy]

Spreadsheets are the worst choice for this. Saving the spreadsheet and restarting the office is the only reliable way to refresh the mail merge data from spreadsheets. Better use dBase for simple mail merge lists. Open your spreadsheet document and save it as file type "dBase (*.dbf) in a dedicated directory. Use the same file name as the sheet name in your spreadsheet. Only the active sheet will be saved. You may save similar address lists as dBase files in the same directory which gives multiple database tables in your dBase database.

Then open your database document and call menu:Edit>Database>Connection type... Switch from "Spreadsheet" to "dBase" and specify the directory where you saved the *.dbf files. Now your registered database is linked to a dBase database. The data source name, table name and field names are the same, so you don't need to edit the Writer document. dBase is always up to date and you can even edit your data in the data source window.

[ColoQuery]

This seems worth a shot, with some reservations -- the 'mail merge' I'm creating is actually a form to produce checks for accounts payable, and the spreadsheet I would like to attach at my data source is the spreadsheet where all the financials for a rather large organization are kept.

Does the dBase option retain the info across the fields which we need? And, can the entire file be used, new info added to fields, and saved in the *dbf format as a matter of daily routine?

If so, that would enormously simplify the check-writing portion of the function!

I have noticed your unfailing patience with many who have questions about OO as I've read the forum posts. You always seem to offer practical suggestions! Thank you for including me in your list of "lost ones" finding their way with your help.

[Villeroy]

Does the dBase option retain the info across the fields which we need? And, can the entire file be used, new info added to fields, and saved in the *dbf format as a matter of daily routine?

Well, it is an old established file format for database data. Yes, it works quite well with millions of records if you add some indices and you can use input forms to edit/insert new records. No more clumsy spreadsheet editing.

[ColoQuery]

Well, it's suddenly become crystal clear that I don't know nearly enough about the power of dBase and how to use it correctly! My only experiences seem to have been with so-called d-base programs created by others which do not do what they've been advertised to do, and which have been far too expensive in both money and time, for what they provide (such as the one we've just left)!

Are there tutorials you'd recommend for working in the *.dbf format in OO?

I will try your suggestion of switching save modes, and let you know the results. Thanks again ...

[ColoQuery]

I have just tried saving the spreadsheet into the *.dbf format as suggested. None of the data entered was saved. While the named fields used in the merge document retained their names it does not appear that this would be usable for our purposes. I am attaching the form document I've created, saved in *.doc, so you can see field placement. The fields were 'live' when I first drug-and-dropped them into their places, but the attachment shows the result after saving; all nicely named, but no live fields.

Opening the F4 window, as mentioned earlier, does not change the fields from placeholders to live.

Not meaning to be circular here; thank you for your suggestions, and for further thoughts.

[acknak]

If I understand what you want, you have to avoid the File > Print ... merge path. Instead, use View > Data Sources to bring up the data source panel, navigate to your data table or query, select the records you want and click the "Data to Fields" button on the panel toolbar.

From there you can print the merged document, or save it and the fields will remain intact.

This works well if your merge document has enough fields to hold the selected data; you can't get the "cycle through the document once for each data record" action that the actual File > Print merge performs.

[Villeroy]

doc does not work at all with mail merge. This is an ODF suite with a certain capability to import and export Microsoft documents. It is not supposed to be an editor for those document types. Only Microsoft can edit MS Office documents properly and Microsoft uses their own incompatible interfaces for database exchange.
Yes, you can use a mail merge document for this kind of invoice. For a single invoice do as acknak suggested, find or filter the record you are interested in, click the row selector (leading grey box in front of the record) and push the "data to fields" button. You can also start printing a serial letter and then select in the next dialog the record(s) you are interested in. Database queries provide easier ways to fetch the right records out of thousands of records.

[ColoQuery]

Oh, my goodness! We're getting there!! Only a couple of (I believe) much smaller questions left. Here's what I've done so far, at your suggestions:

I went back and resaved my merge file into the *.odt extension, and did the same for my spreadsheet. Went through the connection process to connect the new *.ods spreadsheet file to the new *.odt merge file. Re-drug each field name into the new *.odt file as the follow-up to the connections process, and resaved, being careful to retain the *.odt extension. When I opened the merge file again, THE FIELDS STAYED LIVE!! So exciting!

Acknak, I tried your suggestion of using the "Data to Fields" trick to produce a single record -- that worked like magic! I could not highlight a group of records and have it do subsequent pages for the continuing records, but for a single one, it was beautiful! My bookkeeper is going to do handsprings (and he's a big guy -- this won't be pretty!)

==> Here's the "next step" question: After testing the printing of a group of records from the *.ods spreadsheet, printed to a file, there was an extra page between each record-page printed. The page did not show up on the print preview, so I could not delete it before printing. This would cause brain damage if printing actual checks! Is there an <endpage> or <endrecord> code of some sort that I need to insert on my merge document to keep it one record per page, with no extra pages sneaking in?

Once again, thank you so very much!

[ColoQuery]

Villeroy & Acknak -- My bookkeeper and I spent today actually using the spreadsheet and merge document combination you helped me to build and launch ... and it worked so very well! We even found an elegant little way to stop the bother of the blank pages between each form document -- simply save the group of forms generated during the group record selection as a PDF document (save to file - single document; select PDF printer) and then print the document from the PDF format, using Print Options - Print Odd Pages Only. The single-form option, using your suggestion, Acknak, of "Data to Fields," works perfectly. I will close out my query, but wanted to thank you first -- and to tell others who might eventually read this that the two of you are awesome in your understanding of the challenge, and in providing workable solutions.

Thanks again for all your help!

[acknak]

Great! Glad to hear it worked out.

[ColoQuery]

How would I close or complete this topic? Or, is that for the admin to do?

[acknak]

It's nothing so formal ...

Click the "Edit" button on your first post in this topic (top of this page).

Click in the subject field and type (or paste) [Solved] at the beginning.

Click "Submit"

[ColoQuery]

Done. Thanks!