Pass data to a Writer document

Discuss the database features
Post Reply
1gatomontes
Posts: 20
Joined: Sun May 14, 2017 3:48 am

Pass data to a Writer document

Post by 1gatomontes »

Goal: In Base, pass data to a Writer document, without using macros.

How: By clicking a button in a form (of the database), which has "Action" selected to open document, and linked to the specific Writer document.

Writer document: Has placeholders for the data to be passed, such as name, email, phone number, etc. Ex: Volunteer_Database.volunteer_table.volunteer_name

Issue: When clicking on the button, the Writer document opens up, but displays the name of the variable instead of its value. Ex: instead of displaying "Albert", it displays "<volunteer_name>".

Thank you for your suggestions.
OpenOffice 4.1.2
Windows 10 (v.1607)
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Pass data to a Writer document

Post by FJCC »

For this problem
Issue: When clicking on the button, the Writer document opens up, but displays the name of the variable instead of its value. Ex: instead of displaying "Albert", it displays "<volunteer_name>".
I think you have to remove the selection from the menu item View -> Field Names.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Pass data to a Writer document

Post by Villeroy »

FJCC wrote:For this problem
Issue: When clicking on the button, the Writer document opens up, but displays the name of the variable instead of its value. Ex: instead of displaying "Albert", it displays "<volunteer_name>".
I think you have to remove the selection from the menu item View -> Field Names.
menu:View>Field Names toggles between displaying the field name and the meaning of the field; in this case something like <volunteer_name> and Database_Name.Table_Name.volunteer_name. It's just a view thing for all kinds of text fields. A mail merge place holder stands for "all row values from this particular column". Many people want a mail merge based on one particular row, for instance pick one client somewhere and send a letter to him.

1) Without any preparation, you can select one record in the data source window (click the grey row header), then the "Data to fields" button and print the document as is (not as form letter).

2) You can also start printing a form letter but limit the output to one selected record on the form letter dialog. Search the record, select it and choose "Selected records only".

Instead of a table, use a parameter query such as:

Code: Select all

SELECT "FieldName1" AS "Forname",  "FieldName2" AS "Surname", "FieldName3" AS "ZIP", "FieldName4" AS "City", "FieldName5" AS "Country"
FROM "One table or joined Table(s)"
WHERE "Surname" = :Surname
Ideally the WHERE clause should include as few matching records as possible.

Code: Select all

WHERE "Surname" = :Surname AND "Forname" = :Forame AND "day_of_birth" = :Birthday
or one particular record by a unique key:

Code: Select all

WHERE "ID" = :Enter_Client_No
Every time you call a parameter query one way or the other (e.g. triggered by a form letter), a little dialog pops up where the user enters the filter criteria.
When you start the mail merge and enter the parameter value(s), you either get a single record or a limited set of records where it should be easy to pick the right one by means of methods 1) or 2) above.

############################################

The above methods simply filter form letter records by extra user input. You could copy the record ID from your database form and paste it into the little parameter dialog.

There is no bult-in mechanism to pass any form values to documents. I think, most people would start writing macros when confronted with this limitation.

However, this type of problem can be solved with an additional print table where a subform to your existing form enters the row-ID (primary key) of a record to be printed. The query which serves as a data source to your form letter picks the latest record that has been added to this table. As an additional benefit the table keeps track of items that have been printed already.

This is a possible definition of a table "PrintLog" with a one-to-one relation to your persons table:

Code: Select all

CREATE TABLE "PrintLog" ("Person_ID" INTEGER PRIMARY KEY, "TS" TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "B" BOOLEAN DEFAULT FALSE, FOREIGN KEY ("Person_ID") REFERENCES "Persons" ("ID"))
Replace "Persons" and "ID" with your table name and the name of the primary key.
A subform shows something if the selected main form record has a record in PrintLog. It appears blank on a new record if there is no matching record.
The boolean "B" field is required to add a new record to print log. You have to manually add something to a form before you can save the modified form. So click on the B check box and push the save button.

A possible query to select the person record with the latest print log entry:

Code: Select all

SELECT "Persons".* 
FROM "Persons" JOIN "PrintLog" ON "Persons"."ID"="PrintLog"."Person_ID"
WHERE "PrintLog"."TS"=(SELECT MAX("TS") FROM "PrintLog")
This requires that you actually print the document immediately after adding the record to the PrintLog subform and before adding any other record.

You could also use the dummy "B" field to store the print status (Null=Ignore, True=To be printed, False=Printed). and then select all the persons having a True entry in the PrintLog and print them in one go. But then you need to reset the status of the Bs to False. A simple macro could trigger UPDATE "PrintLog" SET "B"=False WHERE "B"=True in order to reset all "to-be-printed" records to status "printed"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
1gatomontes
Posts: 20
Joined: Sun May 14, 2017 3:48 am

Re: Pass data to a Writer document

Post by 1gatomontes »

This module of the project has been pushed down the priority list, but I just wanted to let you know that your help is appreciated and I'll be coming back to your reply on this post in due time. Thanks Villeroy!
OpenOffice 4.1.2
Windows 10 (v.1607)
Post Reply