Greetings:
How to make mail merge check/uncheck check boxes in writer according to cell content 0 vs 1 ( yes vs no ) and how to chose for one of group according to cell content 0, 1, 2, 3, etc.
thanks.
edit: tried design mode check boxes, added data source, chose on/off conditions but can’t get it to behave correctly. does it need “some” SQL filter.
This is definitely not setting form control values specifically as requested, but I did test out a quick work-around and it seems to produce pretty nice results, at least for the check boxes. Since you said “cell” I’m assuming your data source is a Calc document. Hence, here “data table” = “spreadsheet tab”.
In your source data table (or in a stepping-stone data table) create a field that uses unicode checked/unchecked characters then merge against that field instead of the raw 0 or 1.
So with…
where the Check column is populated with ‘=IF(A2,“
”,“☐”)’ go ahead and include <Check> field in the Writer form letter document wherever you want.
Probably something similar could be dreamt up for radio buttons.
PS: The big blue checkmark is caused by this website; with the correct font, the checked and unchecked boxes should look like a nice pair.
1 Like
thank you for this idea, it was great and solved the problem for simple yes and no cells. cells with three or four choices still need work. thank you again.
If you would simply store your list as a dBase file, you could
- edit the database in the data source window
- refresh the mail merge after editing without restarting the entire office suite
- have a yes/no column with a check box in every single row
- have unlimited count of rows without performance issues
dBase is a database in a directory.
Convert your “yes” and “no” values into booleans =IF(X2=“yes”,TRUE();FALSE()).
Make sure that every column has a column header.
Create a new directory and store the current sheet with your list to that directory. Save as dBase(*.dbf)
menu:File>New >Database
[X] Connect to existing db of type “dBase”
Point to the new directory where you stored your sheet.
Check the option to register the database and store the new database document.
Create a new query in SQL view with a “formula” like this one:
SELECT * FROM "Sheet Name" WHERE "Column Name" = True
Replace the “Sheet Name” and “Column Name” with the actual names of sheet and column.
Hit F5 to test your query.
Save the containing database and use this database with the query for your mail merge.
Can you upload a screenshot of what you want the radio buttons (or whatever it may be) to look like in the end?
thank you for this detailed reply. i managed to make query and add it as a data source in writer, but, still don’t know how to make it check uncheck the box automatically. my column of two types either with 0/1 or with 0,1,2,3 as values. thanks
I think the question of using a data source during a mail merge to set the value of a form control is very interesting, but very tricky. Meanwhile, if you think the following is pretty:
Then you can continue by emulating the check mark idea. To type these in I’d start by copying all the circles below and pasting in all of them for each CHOOSE option, then just delete the ones you don’t want for each option:
⓪⓿①❶②❷③❸
Sorry, I got the check box wrong.
Selections based on the status number:
SELECT * FROM "Table" WHERE "Status"=1
SELECT * FROM "Table" WHERE "Status"=1 OR "Status"=5
SELECT * FROM "Table" WHERE "Status">=2 AND "Status" <=4
Generating characters such as ⓪⓿①❶②❷③❸ would be very easy with a database other than dBase or Calc.
P.S. A conditional mail merge field or a database report (alternative to mail merge) can do the trick with the special characters.
1 Like
in short i want to use this sheet in this document and i’m bound by this design.
sheet.ods (10.1 KB)
text.odt (10.5 KB)
I cleared database sources to reduce conflict. thaaaanks
The SELECT statements are 4 different examples, 4 different queries (“formulas”) to play with.
Okay, to belabor the idea of formatting at the data source…
OptionButtonDataSource.ods (13.4 KB)
OptionButtonText.odt (10.1 KB)
OptionButtonTextResult.odt (11.3 KB)
Obviously, the Unicode is messing with LO just a little bit, making the choices look a little different, but I suspect that could be handled.
while I managed to make a semi functional (forum) using both LibreOffice writer and LibreOffice Base, there is inconsistency in mail merge performing (print as PDF).
while mail merge fields act consistently normal and succeed in incorporating the data into individual pages in one single large file. the problem arises in check boxes and other Forum design elements. At some instances it seemed to be related to where i check on/off the Toggle Design Mode but even that was not enough to get fully successful mail merge and till now I don’t know what is wrong or incomplete that I’m doing or not doing so I can get successful mail merge with check boxes as those I get with mail merge Fields.
Here is a solution set that performs per the example files provided by the OP. The results are fully formatted and demonstrate the program workflow from raw data to final merged page document:
OptionButtonDataSource.ods (14.8 KB)
OptionButtonText.odt (11.2 KB)
OptionButtonTextResult.odt (12.9 KB)
The “trick” here really is a hack…formatting the responses at the data source (spreadsheet) rather than within the text document. Notice that if a person has a database source then they could load the database data into the spreadsheet as part of a 3-program solution: database to Calc to Writer.
Also notice that the special Unicode characters in use are not especially format-friendly. Here the fonts are tweaked to make things look “crisp,” but different fonts might cause slight irregularities in formatting.
Hi
What you are asking for seems to me to be achievable in Writer without modifying your data source.
For the explanation I take the examples kindly provided by @joshua4.
For a binary choice (check box) you have to insert a Conditional text field.
-
In the condition field:
[OptionButtonDataSource.FormattedResponses.BooleanChoice]
Instead of typing all this, just display the data source and drag and drop the column header from it. -
For the textboxes Then and Else we use special characters. Right-clicking in one of these fields gives access to the special characters. Unfortunately, their list is limited to the SegoeUI font.
It is therefore more convenient to insert all the characters you need into the document beforehand (e.g. with the OpenSymbol font). You can then simply copy and paste them into the Then and Else fields.
For the optional choices insert Hidden text fields. The condition will be for example :
[OptionButtonDataSource.FormattedResponses.OptionChoice]!=1 for the Hidden text ➊
[OptionButtonDataSource.FormattedResponses.OptionChoice]!=2 for the Hidden text ➋
Same for the other values.
See OptionButtonText.odt (14,4 Ko)
Best regards
@PYS , you have a good command of fields, so let me press you on this a little, since I could make use of something similar to what the OP wanted.
I like the idea of using conditional text, but if we really want each option listed, as shown here:
then conditional fields seem to become quite difficult. It would seem to require four sets of responses each having four Hidden Text fields and four Hidden paragraph fields. Are there more advanced field tricks we can use to insert multi-line text?
If done at the data source, or in an intervening spreadsheet, the composition of the results can be automated, translated easily, have icon substitution done directly, etc.
Thank you for your question which allows me to complete indeed.
I use sections in this case: they can be hidden using the same way.
A “trick” to know is that the “section” dialog does not allow drag and drop from the data source window. To avoid typing the condition (which is often a source of error), I first go through the field insertion dialog in which I drag and drop, then I copy the condition to be able to paste it into the section dialog).
ATB
1 Like