Re: [libreoffice-users] base + mailmerge difficulty

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


On 26/06/15 17:05, Ed Beroset wrote:
> 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


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted