[Solved] Using database fields in Writer mail merge conditional text

[LeroyTennison]

OpenOffice 4.1.10
Given a Calc spreadsheet:
Sheet name is: N-A, only one sheet
Column names are: Last, First, Street, City, ST, ZIP, Spouse, SpYN

In Writer, use File->New->Database, Connect to an existing database (spreadsheet), <pick spreadsheet>, Register but do not open, name the database ALS and save it.

File->New->Labels added fields from the database in the order below

First Last
Street
City, State ZIP

Synchronize content is active

1st attempt - View->Field Names active

Positioned cursor between the First and Last field adding a space before and after, Insert->Field->Other, Functions tab, Conditional text

Condition: (Typed in as simple text) ALS.N-A.Spouse!=""
Then: and ALS.N-A.Spouse
Else: (nothing)

Click Synchronize Labels

Print to file and get: Charlie and ALS.N-A.SpouseAdler ...

Second attempt - View->Field Names not active, added field Spouse to the field list, copied the field itself

Positioned cursor between the First and Last field adding a space before and after, Insert->Field-Other, Functions tab, Conditional text

In Condition I pasted the copied field and it appeared as <Spouse>, added !="" (no intervening spaces)
Condition: and <Spouse> where <Spouse> is the pasted field

Click Synchronize Labels

Ctrl-P, click Yes, select file (supply unique name) and get: Charlie and <Spouse> Adler

Third attempt - View->Field Names active, copied field from initial screen before clicking new Document

Positioned cursor between the First and Last field, Insert->Field-Other, Functions tab, Conditional text

In Condition I pasted the copied field and it appeared as <ALS.N-A.0.Spouse>, added !="" (no intervening spaces)
Condition: and <Spouse> where <Spouse> is the pasted field

Click Synchronize Labels

Ctrl-P, click Yes, select file (supply unique name) and get: Charlie Adler

Fourth attempt - Synchronize contents not active

View->Field Names active, copied field from top-left label

Positioned cursor between the First and Last field, Insert->Field-Other, Functions tab, Conditional text

In Condition I pasted the copied field and it appeared as <Spouse>, added !="" (no intervening spaces)
Condition: and <Spouse> where <Spouse> is the pasted field

Manually inserted the Conditional text in each label

Ctrl-P, click Yes, select file (supply unique name) and get: Charlie and <Spouse>Adler

Fifth attempt, try File->New->Text document
Insert fields manually in the same format as the labels (including the conditional text)
Print and get Charlie and <Spouse>Adler

I also tried NEQ instead of !=, same result. Per https://forum.openoffice.org//en/forum/ ... base+field this feature should work, what haven't I tried?

[MrProgrammer]

Subject: Using database fields in … conditional text
1st attempt
• Positioned cursor between the First and Last field adding a space before and after, Insert->Field->Other, Functions tab, Conditional text
Second attempt
• Positioned cursor between the First and Last field adding a space before and after, Insert->Field-Other, Functions tab, Conditional text
Third attempt
• Positioned cursor between the First and Last field, Insert->Field-Other, Functions tab, Conditional text
Fourth attempt
• Positioned cursor between the First and Last field, Insert->Field-Other, Functions tab, Conditional text
Fifth attempt
• Insert fields manually in the same format as the labels (including the conditional text)

All of those attempts were doomed to failure.

Conditional text
With conditional text, you can have two alternative texts (a word, phrase, or sentence). One text will be displayed and printed if the condition you specify is met, and the other will be displayed and printed if the condition is not met. … You cannot include a cross-reference or other field in the text.

I suggest that you insert both the First and Spouse mail merge fields in the label. After the label has been created, conditionally insert between the fields the word and followed by a space. You could also use a formula in your spreadsheet to conditionally join the cells containing the First and Spouse names with the word and between them if both cells contain data. Use the revised spreadsheet as your mail merge data source.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[LeroyTennison]

Thanks for your reply, I hadn't noticed the qualification you pointed out. Trying your first suggestion, I used File->New->Labels again to start the process over then inserted the Spouse field as follows:

<First> <Spouse><Last>
<Street>
<City>, <ST> <ZIP>

I copied the <Spouse> field then placed the cursor right before <Spouse> (but had to add a space before Spouse or the conditional text replaced the Spouse field) and inserted conditional text via Insert->Fields->Other, Functions tab, Type conditional text:
Condition: <Spouse>!=""
Then: and (that's an "and" followed by a space)
Else: (left empty)

Afterward i used Synchronize Labels to replicate the change to three columns and 10 rows (Avery 5160 labels). When I did I noticed that the first row (all three columns) read:

<First> and <Spouse> <Last>

Printing (answering "Yes") inserted an "and " in every label:

Charlie and Karen Adler Beth and Baker Andy and Case
(BTW, these are fictitious names)

Originally the first and third record of my spreadsheet had text in the Spouse field so I wondered if what was happening was that the field contents were being used in the condition. To test this I moved the Spouse name in the first record to the second record and created new labels. The "and " still appeared in all labels so something about <Spouse>!="" is getting interpreted as always being true. Using the long format of the field didn't change anything. In the next test I tried keying in the long name instead of copying the field - no change. I tried putting the field in quotes (single/double), graves, parenthesis to see if anything would change the behavior but nothing worked.

https://wiki.openoffice.org/wiki/Docume ... al_content, under "Choose or define a variable" says that database field contents can be used and, in the forum post I referred to previously, Villeroy specifically stated how to do it. Unfortunately, i haven't been able to translate what he posted into something that works.

I also tried the Calc approach and created a column using

=IF(G2="",B2,CONCAT(B2," and ",G2))

where B2 is the first name and G2 is the Spouse name. I then inserted that database field into the mail merge. The result in the merged document when the "else" condition existed (CONCAT...) was "#NAME?". I finally got it to work by using an old Excel trick where you copied just the text of a column into another column. That at least works but is anything but a great solution. Anything else I can try?

[Villeroy]

OpenOffice does not support function CONCAT. The name is CONCATENATE. Instead of CONCATENATE(A1;" and ";C1) you may prefer the concatenation operator: A1 & " and " & A3

[MrProgrammer]

I copied the <Spouse> field then … inserted conditional text [with] Condition: <Spouse>!="" Then: and

In my test I found that condition Spouse>"" was successful in inserting and as needed.

… so something about <Spouse>!="" is getting interpreted as always being true.

That might be. It's not clear to me from the documentation how an empty cell in the spreadsheet is represented in the database, nor how that representation is going to be treated by a Spouse<>"" test. For example, maybe the database field is Null instead of an empty string. I'm not going to investigate further. I would avoid all of this muddle with Writer fields and set the desired values in Calc cells using formulas, since that's much easier for me. Than I'd populate the label only with database fields.

=IF(G2="",B2,CONCAT(B2," and ",G2)) [and the] result was "#NAME?"

I used =B2&REPT(" and "&G2;ISTEXT(G2)).

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[LeroyTennison]

This is amazing, THANK YOU! I would have never guessed the approach you took: Using Spouse>"" (not copying the field but just keying in its name then adding >"") worked (the Then text was " and " without the double quotes). I should mention that I had to add two spaces between the First database field and the Spouse database field then place the cursor between the two spaces before adding the conditional text in order to keep the conditional text from replacing on of the fields. After the conditional text field was in place I removed the two spaces to get final output spacing to be correct. This is an extremely minor inconvenience compared to what I tried previously.

Also, your second solution using REPT and ISTEXT (which, again, I'd have never thought of) worked. This has been a real "educational experience" for me, I certainly appreciate it.

[LeroyTennison]

I just noticed Villeroy's reply, thanks for pointing out the error in using CONCAT.

Also, a CAUTION if you have both Apache OpenOffice and LibreOffice installed, LibreOffice Calc has a CONCAT function and LibreOffice Writer worked successfully using CONCAT. I noticed that the spreadsheet was being edited by LibreOffice Calc (due to file association rather then explicitly opening with OpenOffice Calc) but hadn't thought about the possibility that I was setting myself up for failure in doing so.