Hi
I am trying to use Libre Writer to mail merge from a query in Libre base -
The query must be a parameter query where the letter is loaded up in writer, the letter connects to the database query and allows a date to be entered - it then produced mailed merged letters based on the results of the query.
Can anyone help or find an alternative soltuion.
It can be done in MS Office but we don’t have that
Haven’t tried it, but a parameter inside the query like :date (could be it must written like 2022-03-31) doesn’t work?
I don’t use parameterized queries. Will fill all the “parameters” in a single-row-table called “Filter”. So I could set up a query to get the single value from this table “Filter”.
Hello,
Yes, as noted by @RobertG, a parameter query works. I used :myDate (date is a keyword) and the format for input is lenient. Both YYYY-MM-DD and MM/DD/YY worked for me as did some others.
The parameter query works fine and I have already tested it when I enter a date. I need the user to enter a date of their choice hence the need for parameter.
I need to MAIL MERGE the parameter query created in Libre Base, with Libre Writer to create a letter with the results of the query so that when I open the letter from scratch I am prompted to enter a date, the date will search the query and put the results onto the letter.
when I try a regular (non parameter query) with the mail merge and writer, it works. Just doesn’t work with the parameter query.
Any help appreciated
@Mani_Singh
It does work if the fields used are based upon the query. The process here is that the prompt for the date parameter only occurs when you select Print.
Since many years we use mail merge (routing slips actually) based on a query like
SELECT "this", "that", "more", "Date" FROM "some_table" WHERE "Date" = :Please_Enter_Date
The user prints the form letter, enters the date and the routing slips for that day fall out of the printer.
Please describe how you try to use MailMerge. I have tried to press “Print” an the dialog for input a data will appear. It will show the result in the MailMerge dialog and I could choose if I want to print in File or Printer.
Hi
Sorry for the late reply.
I have created a parameter query in Base using the wizard but here is the SQL for it:
SELECT * FROM “tbl_cyberprogress” WHERE “Date Enquiry Received” = :enterdate
As I need this to be mail merged to Libre Office Writer I have then gone in and started a merge by connecting to the data source (database). I have chosen the parameter query as the data source
When I drag in the fields into writer from my query it doesn’t change the field place holders to the other names in the query result.
Can I send this to someone to look at?
Simply hit Ctrl+P in order to print. You will be asked if you want to print a form letter. Say yes. You will be aksed for the parameter. A dialog pops up with the query result on top and some output options. Let it print to a single file and have a look at the result. All place holders will be replaced by their actual database content. If your document has one page and your result set has 10 results, the resulting document should have 10 pages.
Use the upload button on the toolbar when editing a message.
I have attached the DB I have created and the letter I have tried to merge with. When I do view source and add the data fields from the query, refersh and enter the date nothing appears on the print out
@Mani_Singh
First had to fix your query:
SELECT * FROM "tbl_HHHHHHH" WHERE "Date Enquiry Recieved" = :enterdate
Then re-entered the correct fields in the Writer document for:
<Title><Date Enquiry Recieved><Client Contact First Name>
Then selected print which brought up message to print form letter. Answered Yes. Then the dialog to enter a date. Valid entry was shown and print worked.
.
Edit:
I hope the data in the Base file is fictitious.
Hi
Yes its dummy data.
Can you please send me your version if you don’t mind?
HHHHHHH.odb (46.5 KB)
mailmerge_COPY.odt (15.1 KB)
.
In the queries, qry_date_entered is the corrected one and qry_date_entered2 is the original.
.
If you register the Base file as HHHHHHH all should work without modification.
It worked thank you so much.
Can you just remind me what I did wrong in my file apart from the naming,
I can see that the date format in the database is different, does this matter as I had it as dd/mm/yyyy but yours is dd/mm/yy
Thank you again
Date format depends upon locale.
.
As for the query, you changed the Base table name but not in the query. Also the query used:
`Date Enquiry Received`
where the table had it spelled
`Date Enquiry Recieved`Open a spreadsheet.
Enter 13/1/2000 into any cell.
If you get the same string 13/1/2000, then you have entered an invalid date. The date is only in your head. The cell content is a text with 10 characters.
If you see 13/01/00 you have entered something that is recognized as a date and since you did not apply any format to the cell, the date is automatically displayed in some default format which is dd/mm/yy.
How entered text, pasted text, imported text is interpreted depends on Tools>Options>LanguageSetting>Languages>Locale.
You literally renamed every single object in that database including the database itself. Nothing refers to anything.
The fields in your mail merge refer to a query named “qrynew” in a database registered as CyberProgress,
After registering your db as “CyberProgress”, I find no “qrynew” in that database. The existing query refers to a table name that does not exist in that database and to a column name that does not exist in the only table (due to a typo “Recieved”).
Alternatively, keep all the funny names, change the query to:
SELECT * FROM "tbl_HHHHHHH" WHERE "Date Enquiry Recieved" = :enterdate
Open your mail merge document and call menu:Edit>Exchange database… Point to the correct query in the correct registered database.