How to change Writer form letter database connection

users
#1

How does one go about changing the database that a form letter connects to?

I have this problem in several "form letters". I want to connect to a different database than the one they were created against for the simple reason that I have a different set of data that I want to print against.

If this is a simple spreadsheet connection then I can simply connect the database to a different spreadsheet. However I have a some cases where I need to use queries with joins and/or sub-queries, which precludes the use of a spreadsheet connection.

While the obvious answer is to use a single database and use a query to only select the current data, this is not ideal in my case. I prefer to keep the data sets entirely separate so I can archive them by event along with a lot of other event-specific files.

#2

Hi :slight_smile:
Is it F4 to bring up a "connected databases" toolbar?
Regards from
Tom :slight_smile:

#3

No. I think you're thinking about Ctrl-Shft-F4 which brings up the the Data Sources toolbar.

Actually, it's started working now for some reason. The error message I was getting has gone away. Weird.

#4

Spoke too soon. The data source changes but not the fields. Unless I change the fields in the document (re-insert them from the current data source), they show up as blank. This is NOT what I want. I want the fields to move to the new data source.

In fact, I can't think of a reason for the current behaviour. Why should switching data sources do that instead of the more sensible behaviour of picking up the data from fields with the same name in the new data source?

#5

Hi Gary,

In fact, I can't think of a reason for the current behaviour. Why should
switching data sources do that instead of the more sensible behaviour of
picking up the data from fields with the same name in the new data source?

Being cynical, no doubt because that would actually be a useful and
intuitive behaviour, at least insofar as the field names and field data
types are the same, and LO is still in many aspects not very intuitive
for the user, especially with stuff like database mailmerge.

What you are looking for could be made the subject of a bug request for
enhancement. From my point of view as a QAer, I'd find that a valid
request (if it hasn't already been made - I haven't checked).

Note that the behaviour with regard to mailmerge documents changed
recently-ish from that originally present in previous iterations of LO.
Previously, the Writer document which included field references only
linked via a reference to the datasource. Nowadays, the Writer document
includes the datasource at least where that datasource is a spreadsheet,
text or CSV file (or at least, that is my understanding of it). This
might be why the change you effected didn't show up until you re-opened
the document. There is still some kind of inconsisten weirdness in there
in some cases with regard to older Writer documents that were made
before the change to integrate the datasource, but I haven't managed to
nail it down yet. Perhaps someone else has.

Alex

#6

Perhaps they were thinking of allowing links to multiple data sources (I'm not sure how that would work)? Otherwise I can't see any justification to include the data source in the document.

From decades of experience with form letters, I know that the office staff typically use a (frequently manually) generated list to drive the mail merge rather than a database query. It's often a spreadsheet and the staff know how to title the columns so it works. The spreadsheets are always given unique names so including the name, let alone the path, would be counterproductive.

LibreOffice lets you register databases so I have a nice list to choose from that I can give meaningful names - like "2015results", "2016results", etc. for lists that I may access frequently. For most merges even registering the database would be a useless chore since they are frequently one-time lists.

Anyway, just using the field names and assuming the users know what they are doing strikes me as the more reasonable approach. I'll file a "feature request" to that effect.

Thanks.

#7

Are the field names identical from original to newer data?

Are you creating new/updated queries?

I'm always switching to new single source databases from boilerplate Writer and haven't had a problem. I have not tried multiple sources though.

Also beware that while the fields may update to the new data, <next

has to be applied manually. (Seems to be true in most versions

of LibreOffice).

#8

I'm using a Base database (pasted the data in from a spreadsheet so I could run queries that were apparently too complex for simple conditional text). The field names haven't changed. If they did, then I would expect that I'd have to update the merge document.

When I View | Field Names (ctrl-F9), I not only see the database name for both the old database (in the fields that I haven't updated manually) and the new database (in the fields that I did re-insert to this year's database) but also the name of the query that was used.

This is absurd. The form letter should just contain the name of the fields and let the user select which database and/or query they wants to use. As it stands, I am apparently required to not only stick with the same database but also keep the same query name.

Even the menu item (Field Names) seems to be poorly thought out. Toggling it switches the view from just the Field Names to the full database, query, Field Names. In both cases I see Field Names. It probably should be named "Query Path" - although as I have stated, the document shouldn't even use that information.

#9

Hi Gary,

This is absurd. The form letter should just contain the name of the
fields and let the user select which database and/or query they wants to
use. As it stands, I am apparently required to not only stick with the
same database but also keep the same query name.

Whilst I would agreed with you, and it has been one of my pet hates for
a long time, the behaviour you are experiencing is inherited from
StarOffice and survived all the way through Sun's and Oracle's
stewardships of the OpenOffice.org project, so it is no real surprise
that it is still there in LibreOffice. The "field name placeholder", as
it is inserted in the document, is systematically linked to the complete
path of the datasource. I do have some vague recollection however that
is was easier to unlink and relink a data source in the way you want in
OpenOffice.org 1.x (and indeed in StarOffice before that). This was
changed in OpenOffice.org 2.x when the "new" Database module was
developed (to include embedded hsqldb). The links entry in the data
source configurator disappeared, never to be seen again.

Alex

#10

In my circumstance, I am copying over an old database to a new one and changing the name. Then I add new data to the new database. After that I update the query to access the new data only.

I copy over to a new database as a way to remove old records (data) and keep Base from losing it when it has to many records. No data lost and old data is stored away.

In every case, in Writer, from the menu <Edit><Exchange Database>, I select the new database and select the new query. All fields update appropriately except <new record> which I do manually.

As I understand it, if you are not changing database or fields. You should be able to just update your query to select the records you want.

Hope this helps.

#11

Thanks Paul. You're right, that does work. However it begs the question of why bother to bring up the database selection dialogue in the initial Mail Merge panel - especially given that it doesn't actually work?

IMHO the Edit | Exchange Database menu item is a travesty that should be removed. Instead the database selection dialogue in the Mail Merge panel should be used at run time to select the database/query rather than just the specific records.

I will further note that having to go through arcane gyrations to change the query is also nuts. A common use case to have multiple different queries that can be associated with a specific form letter. Each query would be given a meaningful name and the secretary / office administrator who is responsible for the getting the letters out selects the appropriate one.

Again, this functionality appears to be present in the Mail Merge panel but doesn't work.

Or to put it in more direct terms, the database/query selection should be part of the mail merge operation, not part of the document definition. The document should contain simply the database field name.