Re: [libreoffice-users] base + mailmerge difficulty
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 On 6/26/2015 12:05 PM, 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+unsubscr...@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
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 REMoBar = document.createStatusIndicator() REMoBar.start(Started..., 3) REMoBar.Value = 1 ' show progress bar REMoBar.Text = Progressing ... REMProgressValue = 1 REMoBar.Value = ProgressValue If (Not GlobalScope.BasicLibraries.isLibraryLoaded(Tools)) Then GlobalScope.BasicLibraries.LoadLibrary(Tools) End If DirectoryName = DirectoryNameoutofPath(DataSource.Name, /) / REMIf Not FileExists(convertToURL(DirectoryName /arrears strDate)) Then REMmkdir((convertToURL(DirectoryName /arrears strDate)) REMEnd 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 REMoListener1 = createUNOListener(MML_ID_, com.sun.star.text.XMailMergeListener) REMoMailMerge.addMailMergeEventListener(oListener1) oMailMerge.execute(Array()) REMProgressValue = ProgressValue + 1 REMoBar.Text =Converting to PDF file REMoBar.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
Re: [libreoffice-users] base + mailmerge difficulty
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. Il 26/giu/2015 18:06, Ed Beroset bero...@mindspring.com ha scritto: 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+unsubscr...@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 -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] base + mailmerge difficulty
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 -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] base + mailmerge difficulty
On 06/27/15 09:17, Ed Beroset wrote: 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 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. -- _ °v° /(_)\ ^ ^ Mark LaPierre Registered Linux user No #267004 https://linuxcounter.net/ -- To unsubscribe e-mail to: users+unsubscr...@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
