[Solved] Merge multiple records onto one doc with mail merge

[flg8rgal]

This is my first attempt at using OpenOffice, and naturally I'm trying something complex right out of the gate. I'm creating a mail merge for my church to put the song number, song title and author on a copyright transfer form letter. I'd like to list all songs pertaining to each author on one letter. So here's what I've done so far: I've created a song list in Calc that I've called, oddly enough, SongList. I created a data source on that speadsheet called SongDB. I've created a form letter in Writer, and I've inserted the fields I need onto the letter. I can print this just fine, but it prints one song per letter. I've seen other posts on the forum that suggest inserting a Next Record field, but this does solve my problem. I've tried writing a SQL group clause into a query in my SongDB, but that has no effect. I know that in Microsoft Word I need to add code directly to the form letter itself to make this happen. Do I need to do the same thing here?

Here's an example of what the Calc spreadsheet looks like:
Number Title Author
200 Blue Suede Shoes James T. Kirk
201 Happy Birthday Lt. Worf
202 Hokey Pokey James T. Kirk

I'd like the mail merge output in this example to print 2 pages: one for James T. Kirk and one for Lt. Worf. The page for James T. Kirk should list both 200 Blue Suede Shoes and 202 Hokey Pokey and the page for Lt. Worf should list only 201 Happy Birthday.

Is it possible to make this happen? I'm not a C# or BASIC coder, so specific instructions including necessary code would be awesome.

Thanks so much for the help!!!
Trish

[acknak]

Greetings and welcome to the community forum!

For this purpose it may not be necessary to do a merge. Once you have the spreadsheet registered as a data source, you can open the data source panel and display the data (or query results) and drag them directly into the document, as a table or as plain text.

If you did want to use a merge, you can use the "next record" field to include results from multiple records. Unfortunately, OO Writer provides no way to simply merge "all records" into a single document.

[flg8rgal]

Thank you so much for your quick reply.

I do need to use a form letter. It looks something like this:

I, <<Author>>, blah blah blah
blah blah blah blah blah
blah blah.

Song(s):
<<SongNumber>> <<SongTitle>>

Perhaps I have not used the Next Record field correctly and that's why it didn't work. I add the Next Record field to the row under <<SongNumber>> <<SongTitle>> but only one song prints when, in this example, there should be 2 for James T. Kirk. If I add <<SongNumber>> <<SongTitle>> next to the Next Record field, it prints the next song in the list whether it is for that author or not. Do I need to change the condition on the Next Record field?

Thanks again for your help!
Trish

[acknak]

... I add the Next Record field to the row under <<SongNumber>> <<SongTitle>> but only one song prints ...

To get multiple records in the same document, you must insert enough fields to consume all the records. There's no way to have OO Writer do it; you have to insert them manually.

If you have three song records to insert, the merge document will look something like this:

• I, <<Author>>, blah blah blah
  blah blah blah blah blah
  blah blah.
  
  Song(s):
  <<SongNumber>> <<SongTitle>><<Next Record>>
  <<SongNumber>> <<SongTitle>><<Next Record>>
  <<SongNumber>> <<SongTitle>><<Next Record>>

And for multiple authors, you'll have to manually change the number of merge fields to match the number of songs used.

Somehow, I doubt that this is going to be practical.

I believe this sort of situation can be managed automatically with a database report. That requires the report creator extension but that's all I know about it.

[flg8rgal]

I'm a SQL geek, so that's more in my comfort zone anyway Thanks for the tip about the report creator extension. You've been very helpful. I appreciate it!

[Villeroy]

Have you tried menu:Insert>Fields>Other ... tab [Database], Next Record

[flg8rgal]

Hi Villeroy,

I have, but acknak is right. When I insert Next Record and put nothing after it, I still get one record per letter. If I insert Next Record and add <<Song Number >> <<Song Title>> after it, it will print 2 records per letter, whether those records belong to the same author or not. So if the user is printing 50 letters, she would have to spend a great deal of time adjusting the form letter which isn't practical. I'm looking for an easy solution. Is there a way to use the Condition field before inserting Next Record to limit the next record to the same author as the record above it? I'm not a BASIC programmer, so the things I've tried to put in that field may not have been in the correct syntax to work properly. But if I were to put SongDB.NewSongs.Author in the Condition box, what would it compare to?

I've also taken acknak's suggestion and tried writing the form letter in Base using Oracle Report Builder. If I could just figure out how to get the text in a text box to print in the Detail section instead of a 0, I would be all set. But that's a question for another forum.

[flg8rgal]

I just wanted to update this post with the solution I've configured so that any other newbie out there might save a bit of time. In a nutshell, I built a list of songs that I wanted grouped by author into a form letter. ooWriter/mail merge is not the way to go. Instead, I used ooBase and installed the Oracle Report Builder extension to handle the grouping. I did install OO 3.4.1 because I saw info that said the Base extension is not supported in versions after that. So here's what I did:

1. I created a database to connect to the ooCalc spreadsheet I used to enter the songs.
2. Then I created a report in ooBase (not using the wizard).
3. I opened the Sorting and Grouping window and I selected the field I want to group by in the first Field/Expression field. In my case, I chose Author. The Group Header is present and I chose Whole Group in the Keep Together field. This adds a group header to the report layout grid.
4. The group header section is where I put the bulk of my letter as well as the field I am grouping by within that letter. The trick to putting text into the report is to add a label field and change the label instead of using a text field. Most of my form letter was text, so I just created a bunch of label fields to put the text where I needed it.
5. In the detail section is where I put the fields that I want listed and grouped by author: song number and song title. I only added those 2 fields one time to the detail section of the report. It just so happens that in my form letter I wanted the list to appear at the bottom of the letter anyway.

When I run the report, I see exactly what I need to see: one letter per author with a list of their songs, and only their songs, at the bottom. Voila!

[acknak]

Glad it worked out for you. Thanks for posting the follow-up!