base + mailmerge difficulty

users
#1

I'm trying to print out schedules for students using mailmerge and have encountered a problem that I haven't yet been able to solve. Here's what I've done so far.

I have created a simple embedded database (named "school") in base and I have three tables named Classes, Students and Schedules. Classes has three fields: ClassID (which is the index), ClassTitle and CreditHours. The Students table has StudentID (the index), LastName and FirstName. The Schedules table has PairID (the index) and StudentID and ClassID.

Students and Classes are just what you'd expect. Each record in the Schedules table links one student and one class. A schedule is the collection of all classes for a particular student. I have created a simple query for the Schedules table:

SELECT "Schedules"."StudentID" "StudentID", "Students"."LastName" "LastName", "Students"."FirstName" "FirstName", "Schedules"."ClassID" "ClassID", "Classes"."ClassTitle" "ClassTitle", "Classes"."CreditHours" "CreditHours" FROM "Schedules" "Schedules", "Students" "Students", "Classes" "Classes" WHERE "Schedules"."StudentID" = "Students"."StudentID" AND "Schedules"."ClassID" = "Classes"."ClassID" ORDER BY "StudentID", "ClassID"

All of that works just fine and I've also created a simple report that uses the query, which also works as expected.

Now what I want to do is to create a separate schedule document for each student. I'm attempting to use mailmerge to do that. I have a simple .ott file which has "Schedule for <FirstName> <LastName>" in the header and then a table in the body of the document. It has a header and about 14 rows (the maximum number of classes I expect) and then a final row which has a formula which calculates the total number of credit hours.

When I select and filter one individual student, the schedule prints just fine, but if I try to generate all of them, it doesn't work as I want. Specifically, it picks up the first student's name but then it populates the table with the next 14 classes in the query, whether or not this *particular* student is taking that class or not. Each page is filled with 14 classes until we get to the end of the list.

Each row of the table includes the three fields and then a "Next record" which is probably where my problem lies. The condition is set to "TRUE". Somewhat schematically, each line looks like:

<school.Query_Schedules.ClassID> <school.Query_Schedules.ClassTitle> <school.Query_Schedules.CreditHours> <Next record:school.Query_Schedules>

Any clues would be most welcome. I'm quite new to LibreOffice and my SQL skills are very rusty, but I'd love to learn more about both if it helps me solve this problem!

Also, attached is a small zip file with both the database and template if you'd like to try.

Thanks!

Ed

#2

The list doesn't allow attachments. Could you keep them on a xloud and link
here?

What i see is that you should prepare a query where you already have 14
results (even null) in a single record.

#3

Gabriele Ponzo wrote:

The list doesn't allow attachments. Could you keep them on a xloud and link
here?

The file I tried to attach is available here: http://www.beroset.com/school.zip

What i see is that you should prepare a query where you already have 14
results (even null) in a single record.

Yes, I think that would work but I don't know how to do that.

Ed

#4

Mail merge can not do this. For the same reasons we can not print any
invoices where many orders belong to one client.

#5

Mail merge is a Writer feature, but also Base reports rely on Writer!

So I guess for both cases it's just about arranging a proper report.

By the way I hope to have soon some spare time to do some attempts.

#6

Mail merge is a Writer feature, but also Base reports rely on Writer!

Create a report using the wizard.
Group by person (forename and last name in the expample)
The problem with the report is that you never get separated pages for
each client. Ed wants to print/export one separate list per student.

It is impossible to do because all this had been programmed without
every day use cases in mind.

It can be done without too much programming effort with the help of a
sophisticated spreadsheet template as printable report and a macro.

#7

Hey Ed,

Mail Merge requires each single line to contain all the data for that
particular document. You can not combine data from several lines onto
one mail merge document.

If you want to have 14 records per student you can either do that
manually in each table, or write some BASIC code to manage it all. You
could do it if you create a new table with a course number column
numbered 1 - 14 for each student, then fill in each record with each
students course as they register.

Warning Will Robinson! That can get very messy in a really big hurry.
Like, what will you do if your assumption that 14 classes is the maximum
that any student will take turns out to be incorrect? What do you do
when hundreds of students only sign up for one or two classes? Then you
have literally thousands of blank records taking up space and slowing
processing in your database.

What you really need is a report that will group records by student and
combine them all onto one or more report sheet(s). You can do that by
creating a separate record set, by student, and printing a report for
that student.

I'm not up to speed on LO BASIC but in VBA your code would look
something like this:

create recordset rsStudent = "SELECT Students.StudentID FROM Students;"
with rsStudent
  until rsStudent = EOF
    intStudentID = !StudentID
    create recordset rsClassReport = "SELECT "Schedules"."StudentID"
"StudentID", "Students"."LastName" "LastName", "Students"."FirstName"
"FirstName", "Schedules"."ClassID" "ClassID", "Classes"."ClassTitle"
"ClassTitle", "Classes"."CreditHours" "CreditHours" FROM "Schedules"
"Schedules", "Students" "Students", "Classes" "Classes" WHERE
"Schedules"."StudentID" = & intStudentID & AND "Schedules"."ClassID" =
"Classes"."ClassID" ORDER BY "StudentID", "ClassID"
    with rsClassReport
      if not EOF
        Call your report here. Hand the query results to the report.
      end if
    next
    end with
  next
  loop
end with

I left out a lot of details here but that's the general flow of the code
your need.

You need a dynamic query, rsClassReport, that will only pull the records
that pertain to the one student, intStudentID, that you are reporting on.

You will also need to add other columns for current/past student, passed
or failed course, course instructor, class room, class time, etc... to
make this database useful beyond a single semester.

#8

Andreas Säger wrote:

Mail merge is a Writer feature, but also Base reports rely on Writer!

Create a report using the wizard.
Group by person (forename and last name in the expample)
The problem with the report is that you never get separated pages for
each client. Ed wants to print/export one separate list per student.

You are correct! I can (and have) created just such a report but it's not what I actually need because as you correctly note, I need one separate list per student.

It is impossible to do because all this had been programmed without
every day use cases in mind.

That's a shame. While I'm inclined to address the problem myself by fixing the source code for LibreOffice, unfortunately I simply don't have that luxury of time at the moment.

It can be done without too much programming effort with the help of a
sophisticated spreadsheet template as printable report and a macro.

If I went to that much effort, I'd probably simply write everything in C++, LaTeX and MySQL and not use LibreOffice at all. What I was hoping to do instead, however, was to create a system that could be used and maintained by others in the coming years without my direct involvement. Alas, it doesn't seem that LO is quite sufficiently developed for that yet. I'll add it to my (long!) list of things to contribute fixes to when I have time.

Thanks very much to all who replied!

Ed

#9

Translating form theory in base to a writer document you would have a form that contains two sub forms.

The first sub form would be called Students and source it's data from the Students table and contain the controls FirstName and LastName
The second sub form would be called Classes and source it's data from the query Query_Schedules and have a control table with ClassId, ClassTitle and CreditHours.

I've done this after a MailMerge get 6 separate pages but unfortunately each page is for Jane Doe with her four classes. I might have done something wrong so can have another look later on in the week, someone else can always run with this in the meantime.

If this doesn't work I suggest using a macro with a cursor round the students table so in the example database perform the loop 6 times selecting each student in turn. Within the loop
perform a mail merge for each individual student. Here's a sample code for for a similar situation, it's an extract it will contain typos and other omissions as it's an extract from something similar that I'd done I also hashed out references to the progress bar - again I can have another look at this later on in the week if this problem hasn't been solved

Alex

     dim document as object
     dim dispatcher as object
     dim DatabaseContext As Object
     dim DataSource As Object
     dim oMailMerge as Object
     dim w
     Dim oCompWin as object
     Dim oScrollPane as Object

     document = ThisComponent.CurrentController.Frame
     dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

     DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
     DataSource = DatabaseContext.getByName("school")
' Conn=DataSource.getConnection("","")

REM Create a NEW status indicator
REM oBar = document.createStatusIndicator()
REM oBar.start("Started...", 3)
REM oBar.Value = 1 ' show progress bar
REM oBar.Text = "Progressing ... "
REM ProgressValue = 1
REM oBar.Value = ProgressValue

     If (Not GlobalScope.BasicLibraries.isLibraryLoaded("Tools")) Then
        GlobalScope.BasicLibraries.LoadLibrary("Tools")
     End If

     DirectoryName = DirectoryNameoutofPath(DataSource.Name, "/") & "/"

REM If Not FileExists(convertToURL(DirectoryName & "/arrears" & strDate)) Then
REM mkdir((convertToURL(DirectoryName & "/arrears" & strDate))
REM End If

     If FileExists(convertToURL(DirectoryName & "/reports/" & intStudentId & "0.odt")) Then
           kill convertToURL(DirectoryName & "/reports/" & intStudentId & "0.odt")
     End If

     oMailMerge = CreateUnoService("com.sun.star.text.MailMerge")

FOR EACH STUDENT
DO

     oMailMerge.DataSourceName = "school"
     oMailMerge.CommandType = 1
     oMailMerge.Command = "Query_Schedules"
     oMailMerge.OutputType = com.sun.star.text.MailMergeType.FILE
     oMailMerge.OutputUrl = ConvertToUrl(DirectoryName & "/reports/" )
     oMailMerge.FileNamePrefix = intStudentId
     oMailMerge.SaveAsSingleFile=True
     oMailMerge.FileNameFromColumn=False
     oMailMerge.Filter="Students.StudentId=" & intStudentId

REM oListener1 = createUNOListener("MML_ID_", "com.sun.star.text.XMailMergeListener")
REM oMailMerge.addMailMergeEventListener(oListener1)

     oMailMerge.execute(Array())

REM ProgressValue = ProgressValue + 1
REM oBar.Text ="Converting to PDF file ...."
REM oBar.Value = ProgressValue

     oMailMerge.dispose()

DONE

#10

A proper report should work. Though I seem to remember some problem about getting page throws in the right place.

Alex

#11

If that's still a problem edit the report to insert a page break before "Schedule for".

Alex

#12

If that's still a problem edit the report to insert a page break before
"Schedule for".

Alex

Been there, tried that, got the t-shirt. An example database with a
many-to-many relation, some hundreds example records and a working report
with proper page breaks would convince me that this is possible.

Meanwhile I did some experiments with mail merge, mail merge event listeners
and a self-written report generator which dumps record sets related to the
merged letter into a prepared Writer table. For me as a hobbyist programmer
this is no fun by any means. It sucks badly.

#13

--
View this message in context: http://nabble.documentfoundation.org/base-mailmerge-difficulty-tp4152762p4152936.html
Sent from the Users mailing list archive at Nabble.com.

#14

Alex McMurchy wrote:

If that's still a problem edit the report to insert a page break before
"Schedule for".

In fact, using a report turned out to be a viable, if somewhat counterintuitive way to do what I wanted. When I click the "Sorting and Grouping" icon on the Report Builder screen, there is a "Keep Together" selection which I set to "Whole Group." This keeps each schedule on a separate page -- no mail-merge required.

The only unsolved problem is that what I actually wanted is for each schedule to be a separate PDF document that would be emailed to each student (the real database has each student's email address). I imagine that I may be able to solve that with a script (thanks very much for the one you sent -- I'll study it for inspiration!)

It also took me a bit of time to figure out how to place static text on each schedule, but once I realized that the only way to do that was to use label objects, and image objects for the school logo, it was just a matter of fiddling with the placement.

Another thing I noticed was that the filter that I'd set up within the document for my mailmerge attempt was apparently still in effect for the report. It took me a while to figure out why I was only getting a single record printed. Unfortunately there does not seem to be any indication or way to turn the filter off within Report Builder. Instead, I had to go into the document, reset filtering and then return to Report Builder. On one hand, that was not expected, but on the other hand, it was a convenient way to quickly look at a sample without having to generate the entire report each time.

Thanks once again.

Ed

#15

You may be over complicating things a little bit.

Create a query table using wizard including all printable fields which must also include items requiring sorting below.
Sort by Student ID, then by Class ID

In your ODT use Student ID in header. (I have not used OTT)
Use Student ID in table. Don't forget "Next Record" in table at the end of each row.

If I recall correctly, this should cause a 'next page' based on Student ID.

Print with "Query" selected. Output to PDF generator.

Hope this helps

#16

These may help. Splits your report into separate PDFs. Works on the current document and assumes that your tables start with Group_Header and Detail. Have a look in Navigator to confirm and change thee macro below accordingly.

Sub CopyNamedTable(sName As String, oOrigDoc AS Object, oNewDoc As Object)
     Dim oTable 'Table to copy
     Dim oText 'Document's text object
     Dim oVCursor 'Current view cursor
     Dim o 'Transferable content

     oVCursor = oOrigDoc.CurrentController.getViewCursor()
     oText = oOrigDoc.getText()
     If NOT oOrigDoc.getTextTables().hasByName(sName) Then
         MsgBox "Sorry, the document does not contain table " & sName
         Exit Sub
     End If
     oTable = oOrigDoc.getTextTables().getByName(sName)
     REM Place the cursor in the start of the first cell.
     REM This is very easy!
     oOrigDoc.CurrentController.select(oTable)
     oVCursor.gotoEnd(True) 'Move to the end of the current cell.
     oVCursor.gotoEnd(True) 'Move to the end of the table.
     o = oOrigDoc.CurrentController.getTransferable()

     REM Copy Table into new Document
     oNewDoc.CurrentController.insertTransferable(o)

End Sub

Sub CopyAllTextTables ()

Dim oDoc As Object
Dim Enum As Object
Dim TextElement As Object
Dim noArgs() 'An empty array for the arguments
Dim sURL As String 'URL of the document to load
Dim aProps(0) as New com.sun.star.beans.PropertyValue
Dim strFirstName As String
Dim strLastName As String
Dim strStudentID As String

If (Not GlobalScope.BasicLibraries.isLibraryLoaded("Tools")) Then
    GlobalScope.BasicLibraries.LoadLibrary("Tools")
End If

' Create document object
oOriginalDocument = ThisComponent
sURL = oOriginalDocument.getURL()
DirectoryName = DirectoryNameoutofPath(sURL, "/")
aProps(0).Name="FilterName"
aProps(0).Value = "writer_pdf_Export"

' Create enumeration object
Enum = oOriginalDocument.Text.createEnumeration
' loop over all text elements

While Enum.hasMoreElements
   TextElement = Enum.nextElement

     If TextElement.supportsService("com.sun.star.text.TextTable") Then
           REM MsgBox "The current block contains a table called - " & TextElement.TableName
           strTableName=TextElement.TableName
           if (Mid(strTableName,1,5) = "Group") Then
               REM Create a new swriter Document
               oTable = get_table_by_name(oOriginalDocument, strTableName)

             REM #############################################
             REM The row/column positions may be different in your document
             REM The filename should also include StudentID to cater for when
             REM the concatenation of LastName & FirstName is not unique
             REM #############################################

               strFirstName=oTable.getCellByPosition(2,4).getString()
               strLastName=oTable.getCellByPosition(6,3).getString()
               strStudentID = ""
               REM strStudentID=oTable.getCellByPosition(?,?).getString()

             REM ###########################################

             sURL = "private:factory/swriter"
             oNewDocument = StarDesktop.LoadComponentFromUrl(sURL, "_blank", 0, noArgs())
CopyNamedTable(strTableName,oOriginalDocument,oNewDocument)
           Else
CopyNamedTable(strTableName,oOriginalDocument,oNewDocument)
             sURL = converttourl(DirectoryName & "/" & strLastName & strFirstName & strStudentID & ".pdf")
                oNewDocument.storetoUrl(sURL,aProps())
                oNewDocument.close(true)
           End If
           If TextElement.supportsService("com.sun.star.text.Paragraph") Then
               REM Ignore Paragraphs
           End If
     End If

Wend

End Sub