[Solved] How to mail merge from multiple tables

Discuss the database features

[Solved] How to mail merge from multiple tables

Postby Ron19855 » Tue Sep 03, 2019 6:27 pm

Hello,

I'm looking to form reports that include data from both my client and invoice tables. However, it appears that natively you can only pull data from one data source at a time when using mail merge. I've looked into the oracle report builder plugin but this hasn't solved my issue since I'm only looking to use one row of information at a time in my reports and I want to use a writer document as a template. If anyone has any idea as to how I can accomplish using multiple data sources at once for a mail merge they would be much appreciated.

Thanks
Last edited by Ron19855 on Fri Sep 13, 2019 9:46 pm, edited 1 time in total.
OpenOffice 4.1.6 - Windows 7 Ultimate
Ron19855
 
Posts: 6
Joined: Thu Dec 27, 2018 11:25 pm

Re: How to mail merge from multiple tables

Postby Villeroy » Tue Sep 03, 2019 6:57 pm

If you were using a true database, you could merge tables. Since your database document might be connected to some (spreadsheet?) file, there are limitations.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27574
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to mail merge from multiple tables

Postby longi » Wed Sep 04, 2019 12:17 am

Well, it seems to me that you want something different than mail merge, because you are searching reports.
You can use the old report builder to get something like the example I made (is really simple but effective). However you can see this code in order to connect a writer document as template with a database: (you need to have the template in the same folder that the database, see the other example)
Code: Select all   Expand viewCollapse view
Sub MakeDoc(event)   
    'Referencia al formulario
    form = event.Source.Model.Parent
    'Nombre de la plantilla
    doc_name = event.Source.Model.Tag
    'Ruta de esta base de datos
   path = form.ActiveConnection.Parent.DatabaseDocument.URL
   'Ruta a la plantilla
   path_template = replace_name(path, doc_name)   
    'Abrimos la plantilla
    doc = open_doc(path_template)
     'Reemplazamos los valores
     replace_fields(doc, form)
End Sub


Sub replace_fields(doc, form)
    sd = doc.createSearchDescriptor()
    sd.SearchCaseSensitive = False
    sd.SearchRegularExpression = False

    'Los campos del formulario
    fields = form.Columns.getElementNames()
     For i = LBound(fields) To UBound(fields)
        sd.setSearchString("%" + fields(i) + "%")
        found = doc.findAll(sd)
        If found.Count > 0 Then
         For f = 0 To found.Count - 1
            t = found.getByIndex(f)
            t.setString(form.getString(i+1))
         Next f
        End If
     Next i

End Sub


Function replace_name(path, doc_name) As String
   tmp = Split(path, "/")
   tmp(UBound(tmp)) = doc_name
   replace_name = Join(tmp, "/")
End Function


Function open_doc(path) As Object
Dim opt(0) As New "com.sun.star.beans.PropertyValue"
Dim doc As Object

     opt(0).Name = "AsTemplate"
     opt(0).Value = True

     open_doc = StarDesktop.loadComponentFromURL(path, "_blank", 0, opt())   
End Function

The example 'PruebaForo' seems to be much, much better, and we can adapt it in order to get data from a query instead from a form, which can be made from several tables.

The 'Anexo' document want to be an explanation about how to use the old Report Builder to use a report as a template as a writer document.

Bye!
Attachments
PruebaForo.7z
(18.23 KiB) Downloaded 19 times
PruebaTextoCompletada(1).7z
(22.45 KiB) Downloaded 26 times
Anexo.7z
(51.47 KiB) Downloaded 18 times
OpenOffice 4.1.5 on Windows 10
LibreOffice 5.1 on Windows 7
LibreOffice 6.0.1 on Windows10
longi
 
Posts: 108
Joined: Mon Jul 15, 2013 5:04 pm

Re: How to mail merge from multiple tables

Postby Villeroy » Wed Sep 04, 2019 3:06 pm

Merging 2 tables in the same database:
Code: Select all   Expand viewCollapse view
SELECT "Name", "Address", "ZIP", "City" FROM "Table A"
UNION SELECT "Contact", "Addr", "Postal Code", "Town" FROM "Table B"

returns 4 columns from 2 tables with different column names but simlar data types. This requires a true database such as HSQL. It won't work with a file based database (sheet, csv, dBase).

If you want to re-use the same Writer template with completely different data sources a set of simple queries can help.
In Writer a mail merge field is specified by 3 names: SourceName.TableName.ColumnName where TableName could be the name of a query as well.
You can switch to a different data source via Writer menu:Edit>Exchange Database, however this only switches the source name and the table name but not the column names. Column names are not always the same and they are not always as easily changeable as in a spreadsheet file.
If your Writer template uses column names "Name", "Address", "ZIP", "City" but the table in the next data source uses column names "Contact", "Addr", "Postal Code", "Town" you can easily create a query wich applies the wanted column names as alias names and save this simple query under some name:
Code: Select all   Expand viewCollapse view
SELECT "Contact" AS "Name", "Addr" AS "Address", "Postal Code" AS "ZIP", "Town"AS "City" FROM "Table B"

In the query designer you would click together the wanted columns and write the alias names below the column names. This simple renaming of columns works with any type of database.

Now you can use Writer menu:Edit>Exchange Database... and switch the form letter from Database_A.Table_X to Database_B.Query_X with the same column names.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27574
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to mail merge from multiple tables

Postby Ron19855 » Fri Sep 13, 2019 9:46 pm

Thank you for all the assistance. I ended up just creating a query to combine to two tables since they were already joined. It was a simpler solution then expected but you gave me the idea. Thanks!
OpenOffice 4.1.6 - Windows 7 Ultimate
Ron19855
 
Posts: 6
Joined: Thu Dec 27, 2018 11:25 pm


Return to Base

Who is online

Users browsing this forum: No registered users and 6 guests