Mailing: many issues

[josemoya]

Hi. I’m experiencing many different issues with mail merge.

I’m using mail merge in order to make individual paper documents that contain the password of each user. (There are reasons that make me use paper documents instead of email).

Master document and “address list” (i.e. user data) are stored in a webdav server, so they NEVER reach the local computer harddrive.

I started working with a remote database, but then I found I had to use a local database, or else the database will become “read-only”, making impossible to add queries.

In the process I had tested the different mail merge capabilities of different versions between 6.x and 7.3. I can agree there are Mail Merge Issues Between Versions, like this topic states:

Mail Merge Issue Between Versions

Hi, I work in many places (home, workplace, co-workers’ place). We use libreoffice for our doc needs. I was used to MS mail merge and was applying the same kind of work in LibreOffice. Turns out, you can but with some getting used to a different approach as MS. But the bottom line is you can. I was doing forms. I encounter some difficulty when I tried to update a mail merge document on a different version of LibreOffice. I originally created the file on a version 5. I uploaded this file…

FIRST - WRITER-RELATED

• In some parts of the “Mail merge assistant”, it is difficult to select a DATABASE QUERY (instead of a DATABASE TABLE or a SPREADSHEET TABLE) as source.
• You can filter rows using the “address book source” at the mail merge dialog, but you can’t if you use the “address book source” button on the button bar.
• In the filter dialog, the LIKE clauses do not seem to work. They won’t use neither “*” nor “%” as wildcard. EDIT: Maybe it works, but It is not updating the preview acordingly. It seems to work in the database view.
• Field assignments seem to work for Auto-Address Block only. It would be nicer if you could assign small, one word names for your spreadsheet names, and then use them all over the mail merge.
• In the database view you can filter the rows you see, but it won’t not affect the mail merge. This is really weird.
• Most time, selecting “edit database” from Write will crash the system.
• Selecting “edit database” in Writer, then refreshing / rebuilding tables from Base will NOT refresh the addressbook rows in Writer.
• To end this list, I will say something positive. The “replace table” tool is fantastic and saved many time.

SECOND - BASE-RELATED

• Base 7.3 / 7.2 can’t open remote documents. Exception are remote shares, but they are opened read-only.
• Sometimes Base 7.2 throws some exception when reading remote Calc spreadsheets. This happened to me with a spreadsheet that was edited with 6.0, then 7.0, then 7.2, then 7.3, then 7.2 again.
• When you copy a query, the query is not copied. I.E. The easiest way to make multiple similar SQL queries should be copying the query, then modifying it, but you have to enter SQL edit mode, copy the SQL, then create a new query in SQL mode.
• You can’t query on queries. This makes Base very poor compared to Access.

[Villeroy]

@josemoya

You can’t query on queries. This makes Base very poor compared to Access.

Access is a database development suite. Base is a tiny addition to this office suite with a footprint of 20 or 30 MB.

Everything depends on the actual database. A connection to a spreadsheet interpretes rectangles of cells as tables. A connection to some database server gives multi-user access, nested queries.

@josemoya

In some parts of the “Mail merge assistant”, it is difficult to select a DATABASE QUERY (instead of a DATABASE TABLE or a SPREADSHEET TABLE) as source.

Open the data source window (Ctrl+Shift+F4), select the query in the left pane and drag column headers from the right pane into your document.

Of all possible data sources, spreadsheets are the worst choice. I use a spreadsheet when I have to copy data from various sources into an improvised, one-off data source. Yes, spreadsheets do not update properly until you saved them to disk and restarted the office suite. Better use dBase files. For multi-user access you need to set up a database server.

[josemoya]

Thanks for the info, @Villeroy

I’m stuck to spreadsheets. The data must not go outside the server, and the server is a file server, not a mail server.

This said, a nice thing about libreoffice base is that it does not cache linked spreadsheets, as access does.

This is the reason why I am using an entire Libreoffice approach, instead of using ODBC + access + JDBC.

ALSO:
I can’t drag columns from the database, since the register number is took from the “address book”, not from the database.
This seems to be the reason I can’t use the database view to filter results

[Villeroy]

Once upon a time I used multiple Base clients with a stand-alone HSQL database on a Windows file server (mapped drive letter). The first client had read-write access, subsequent clients had read-only access which was OK for us.
The initital draft was an embedded HSQLDB, then an external HSQLDB on file server and finally I set up a little HSQL server for this database. HSQL is a small Java program that can be started with a batch script. Unfortunately, Windows can not run Java programs as services. A most minimal MySQL setup would be far better than the best possible spreadsheet solution.
For mail merge I use the database of our business management software which produces old style dBase files.

[josemoya]

Hi, Villeroy.

Making a basic macro to build a dBase file from a spreadsheet should be trivial (well, I have to dig out the index file format, but the dBase file format was so simple I read dBase files from Qbasic). So I might try that approach.

The problem is, if I put the file as a Dbase ODBC connection, should I keep the network share open? I close all shares each time I close session (net use /persistent:no).

In the other hand, I could put filters in the spreadsheet itself, export to different CSV files and merge from there.

[Wanderer]

@josemoya

Making a basic macro to build a dBase

You can just directly SaveAs as dBase-formatted tables. The Information on Type can be set in Header row.
.

@josemoya

The problem is, if I put the file as a Dbase ODBC connection,

Never used odbc for this, as you can directly “connect” to the folder containing the .dbf-files as tables.
.

@josemoya

The data must not go outside the server, and the server is a file server

Another option (besides dBase) could be Sqlite, where you also have one file, containing the darabase.

[RobertG]

Too many different problems, so I will take this one:

@josemoya

You can’t query on queries.

Doing this very often with internal HSQLDB database. It won’t work with Calc sheets. Calc sheets only allow one source, and this has to be a table.

@josemoya

When you copy a query, the query is not copied.

Same: This is working well here. Don’t know how to get no copy. It also works here with Calc sheets.

Base is a tool, which could connect to external databases with JDBC and ODBC. So please use a database server to connect to, if you want external connection. Connections to Calc are always read only, so you could only query one table. This is the same behavior with Calc on local systems.

[Hrbrgr]

In addition to @RobertG :

Mail Merge in Writer

[josemoya]

Hi, @RobertG.

Apart from copy-pasting the query, did you try to edit it afterwards?

With libreoffice 7.0 / 7.1 / 7.2, when editing the SQL of a query you just copied and pasted, the inner SQL is empty.

Maybe it works in newer versions?

[josemoya]

Hi, @Hrbrgr

Thanks for directing me to a tutorial. As you see from my question, I know how to add an address book, I know how to select fields (thus it does not work unless you are writing a real letter with an address block, instead of a prefilled form), and I know how to print or edit.

What a pity the tutorial does not cover more deep topics, such as inserting field codes inside the text using CTRL-F9. I think I should edit the tutorial to add them.

[RobertG]

@josemoya

Apart from copy-pasting the query, did you try to edit it afterwards?

Have done this very often. Tested it now for you (Base connected to Calc - right?) with LO 7.3.6.2, LO 7.2.5.1 and LO 7.1.5.2. Could edit the query in SQL or through GUI. Have installed many version of LO here for testing, because I’m author of German Base Handbuch. Have never heard about a query, which couldn’t be copied, inserted and edited afterwords.

[josemoya]

Hi, @RobertG.
Since you tell me you never had the query-copy problem, I think maybe that was a side effect of the read-only base. I’m unsure because the base queries and tables were not grayed out, but, as you said, my setup with remote spreadsheets was very unstable.

I’m updating all my systems to LO 7.3 latest and it is working better now all systems have same version.