How to change Writer form letter database connection

classic Classic list List threaded Threaded
11 messages Options
Gary Dale Gary Dale
Reply | Threaded
Open this post in threaded view
|

How to change Writer form letter database connection

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.


--
To unsubscribe e-mail to: [hidden email]
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
TomD TomD
Reply | Threaded
Open this post in threaded view
|

Re: How to change Writer form letter database connection

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

On 11 October 2016 at 17:00, Gary Dale <[hidden email]> wrote:

> 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.
>
>
> --
> To unsubscribe e-mail to: [hidden email]
> Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-uns
> ubscribe/
> 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: [hidden email]
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
Gary Dale Gary Dale
Reply | Threaded
Open this post in threaded view
|

Re: How to change Writer form letter database connection

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.


On 11/10/16 12:22 PM, Tom Davies wrote:

> Hi :)
> Is it F4 to bring up a "connected databases" toolbar?
> Regards from
> Tom :)
>
> On 11 October 2016 at 17:00, Gary Dale <[hidden email]> wrote:
>
>> 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.
>>
>>
>> --
>> To unsubscribe e-mail to: [hidden email]
>> Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-uns
>> ubscribe/
>> 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: [hidden email]
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
Gary Dale Gary Dale
Reply | Threaded
Open this post in threaded view
|

Re: How to change Writer form letter database connection

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?


On 11/10/16 01:34 PM, Gary Dale wrote:

> 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.
>
>
> On 11/10/16 12:22 PM, Tom Davies wrote:
>> Hi :)
>> Is it F4 to bring up a "connected databases" toolbar?
>> Regards from
>> Tom :)
>>
>> On 11 October 2016 at 17:00, Gary Dale <[hidden email]> wrote:
>>
>>> 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.
>>>
>>>
>>> --
>>> To unsubscribe e-mail to: [hidden email]
>>> Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-uns
>>> ubscribe/
>>> 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: [hidden email]
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
Alex Thurgood Alex Thurgood
Reply | Threaded
Open this post in threaded view
|

Re: How to change Writer form letter database connection

Le 11/10/2016 à 19:45, Gary Dale a écrit :

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






--
To unsubscribe e-mail to: [hidden email]
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
Gary Dale Gary Dale
Reply | Threaded
Open this post in threaded view
|

Re: How to change Writer form letter database connection



On 12/10/16 05:33 AM, Alexander Thurgood wrote:

> Le 11/10/2016 à 19:45, Gary Dale a écrit :
>
> 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.
>

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.

--
To unsubscribe e-mail to: [hidden email]
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
Paul D. Mirowsky Paul D. Mirowsky
Reply | Threaded
Open this post in threaded view
|

Re: How to change Writer form letter database connection

In reply to this post by Gary Dale
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
record> has to be applied manually.  (Seems to be true in most versions
of LibreOffice).



On 10/11/2016 1:45 PM, Gary Dale wrote:

> 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?
>
>
> On 11/10/16 01:34 PM, Gary Dale wrote:
>> 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.
>>
>>
>> On 11/10/16 12:22 PM, Tom Davies wrote:
>>> Hi :)
>>> Is it F4 to bring up a "connected databases" toolbar?
>>> Regards from
>>> Tom :)
>>>
>>> On 11 October 2016 at 17:00, Gary Dale <[hidden email]> wrote:
>>>
>>>> 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.
>>>>
>>>>
>>>> --
>>>> To unsubscribe e-mail to: [hidden email]
>>>> Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-uns
>>>> ubscribe/
>>>> 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: [hidden email]
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
Gary Dale Gary Dale
Reply | Threaded
Open this post in threaded view
|

Re: How to change Writer form letter database connection

On 12/10/16 11:00 AM, Paul D. Mirowsky wrote:

> 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
> record> has to be applied manually.  (Seems to be true in most
> versions of LibreOffice).
>
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.

--
To unsubscribe e-mail to: [hidden email]
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
Alex Thurgood Alex Thurgood
Reply | Threaded
Open this post in threaded view
|

Re: How to change Writer form letter database connection

Le 13/10/2016 à 22:46, Gary Dale a écrit :

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


--
To unsubscribe e-mail to: [hidden email]
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
Paul D. Mirowsky Paul D. Mirowsky
Reply | Threaded
Open this post in threaded view
|

Re: How to change Writer form letter database connection

In reply to this post by Gary Dale
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.


On 10/13/2016 4:46 PM, Gary Dale wrote:

> On 12/10/16 11:00 AM, Paul D. Mirowsky wrote:
>> 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
>> record> has to be applied manually.  (Seems to be true in most
>> versions of LibreOffice).
>>
> 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.
>


--
To unsubscribe e-mail to: [hidden email]
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
Gary Dale Gary Dale
Reply | Threaded
Open this post in threaded view
|

Re: How to change Writer form letter database connection

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.



On 14/10/16 11:20 AM, Paul D. Mirowsky wrote:

> 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.
>
>
> On 10/13/2016 4:46 PM, Gary Dale wrote:
>> On 12/10/16 11:00 AM, Paul D. Mirowsky wrote:
>>> 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
>>> record> has to be applied manually.  (Seems to be true in most
>>> versions of LibreOffice).
>>>
>> 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.
>>
>
>


--
To unsubscribe e-mail to: [hidden email]
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