Multi-line mail merge field from Evolution address book

Writing a book, Automating Document Production - Discuss your special needs here
Post Reply
MarkRS
Posts: 4
Joined: Thu Jun 25, 2009 7:34 pm

Multi-line mail merge field from Evolution address book

Post by MarkRS »

Is it possible to cause a mailmerge field to occupy more than one line in the output?

I can produce the merge just fine, but I have an input field (address line 1) than can contain text on more than one line, but I haven't found a way to get the output to occupy more than one line.

I have searched the (Open- & Libre- Office) docs, and here, & the LibreOffice forum, but haven't found an answer.

 Edit: Changed subject, was Mail merge field to more than one line? 
Make your post understandable by others 
-- MrProgrammer, forum moderator 
Last edited by MrProgrammer on Tue Feb 21, 2023 7:11 pm, edited 1 time in total.
Reason: Edited topic's subject
LO 7.3.7.2 Fedora 37
User avatar
MrProgrammer
Moderator
Posts: 5097
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Mail merge field to more than one line?

Post by MrProgrammer »

MarkRS wrote: Tue Dec 13, 2022 2:57 pm
202212130910.png
202212130910.png (31.51 KiB) Viewed 6466 times
Do you really use OpenOffice 3.1 on Windows XP? Do you use OpenOffice or LibreOffice? What version? What is your operating system? I choose not to contribute further to this topic until you confirm or update that information.

MarkRS wrote: Tue Dec 13, 2022 2:57 pm I haven't found a way to get the output to occupy more than one line.
You are more likely to get your problem solved quickly when you attach documents demonstrating the difficulty (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself).

MarkRS wrote: Tue Dec 13, 2022 2:57 pm I have an input field (address line 1) than can contain text on more than one line
Describe your data source for the merge. The source is a database. Explain how you have a database record with a field which contains "more than one line". Instead of putting all of that information in words, just attach your data source, since that will be simpler for you and for us.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.7, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
John_Ha
Volunteer
Posts: 9584
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Mail merge field to more than one line?

Post by John_Ha »

Data in my Xmas card labels spreadsheet pulled in to the labels sheet spills if the line is too long. Note the long line in Line 1 and the two empty lines for the empty fields Line 2 and Line 3 before the postcode field.

Or, if it's just a few lines which are too long save the merge as a .odt file instead of printing it and edit it.
Attachments
Clipboard02.gif
Clipboard02.gif (6.85 KiB) Viewed 6441 times
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
MarkRS
Posts: 4
Joined: Thu Jun 25, 2009 7:34 pm

Re: Mail merge field to more than one line?

Post by MarkRS »

Many thanks for your replies.

MrProgrammer, yes, sorry about that. As you might guess it's a very long time since I was last here. It's so long that I forgot about the signature. I've updated it.

The database is my address book in Evolution (3.44.4-2.fc36). Evolution shows up in LO as the datasource EvolutionLocal. Its data is stored in SQLite, one database per address book, I've created a test book (the file is called contacts) which I've attached. I had to change te extension because FireFox wouldn't allow me to attach it otherwise. The correct extension is .db . It has one (bogus) record. two lines in its "address" field, and then things in the other supplied fields (city, &tc.) The main data seems to be in the vcard field. I don't know how Evolution manages to split its contents up into the various fields shown on the contact sheet.
Looking at it with an SQLite browser, I suspect the problem is that Evolution terminates fields with a semicolon, and seems to do the same with separate lines in that first field, added after the \n character to indicate new line.

What's happening when there are two lines in the text box is that only the first is produced.

So, it's not that it's a long line, it's that there may be more than one line. I think that means I can't leave it to spill over automatically (though I didn't know that it would do that, thank you John_Ha), it needs to break in the right places.

Is that enough information to be clear?
Attachments
Envelope.odt
(12.35 KiB) Downloaded 278 times
contacts.txt
(84 KiB) Downloaded 280 times
LO 7.3.7.2 Fedora 37
User avatar
MrProgrammer
Moderator
Posts: 5097
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Mail merge field to more than one line?

Post by MrProgrammer »

MarkRS wrote: Tue Dec 13, 2022 10:38 pm Is that enough information to be clear?
No. I understand more about what you're doing and thank you for providing your Writer document, but I don't know what is in your data source.

The values for the fields to be shown in your Writer document, Envelope.odt, come from your data source, which is a Base document. In your case, the Base document gets the data from an Evolution database. But what's important is the data coming into the merge from the LibreOffice Base driver connecting to the Evolution database, not the Evolution database records themselves.

When viewed in Base, a database has records which contain fields, a 2-dimensional structure. But it is unlikely that this is how data is stored in Evolution. The address book layout I'm familiar with (the MacOS Contacts application) involves 28 interconnected tables, and I will guess that Evolution, since it is also an address book, is similarly complex. The driver has to map the Evolution data into the 2-dimensional structure needed by Base. I have neither Evolution nor LibreOffice, and OpenOffice Base is not able to connect to the contacts.db file which you attached, so I have no idea what the Base driver has returned from the Evolution connection.

However, as a test of multi-line fields I created my own Base document to connect to the following CSV file which I put in its own folder:

full_name,home_addr_line1,home_addr_line2,home_addr_City,home_state,home_zip,home_country
My Full Name,"Address 1 first
Address 1 second","Address 2 first
Address 2 second",My City,My State,My ZIP,My Country

202212141414.csv
Data for data source
(208 Bytes) Downloaded 307 times

File → New → Database → Connect to existing database → Text → Next → specify path to folder → Comma Separated Values → Next → Register → Finish

Then I open your Evolution.odt document → Edit → Exchange database → select the database I just registered → File → Print → Form Letter? → Yes → All records → OK
202212141414.odt
Resulting Writer document after merge
(10.91 KiB) Downloaded 284 times

You can see in my attached Writer document 202212141414.odt that the mail merge fields for home_addr_line1 and home_addr_line2 each have two lines, as do the fields in my CSV file. Thus I have demonstrated that a mail merge field can display more than one line of text. Above, John_Ha also finds this to be the case, as long as the data source has the proper text for the field.

MarkRS wrote: Tue Dec 13, 2022 10:38 pm I suspect the problem is that Evolution terminates fields with a semicolon, and seems to do the same with separate lines in that first field, added after the \n character to indicate new line.
I'd say it's the responsibility of the LibreOffice Base driver for Evolution to process the semicolon-delimited fields correctly and return the data to the merge. Perhaps you've discovered a limitation in that driver which causes it to not retrieve the information which you expect. Potential workaround (which I have not tested): import Evolution's data into the MacOS Contacts application, then use File → New → Database → Connect to existing database → Mac OS X Address Book. Questions about how to import Evolution into Contacts would be off-topic for this forum.

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.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.7, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
MarkRS
Posts: 4
Joined: Thu Jun 25, 2009 7:34 pm

Re: Mail merge field to more than one line?

Post by MarkRS »

Interesting.
The data is stored in that file I sent. It's all in the "vcard" field, stored as a text vcard. Changing the extension and looking at that file with a SQLite browser will let you see the whole thing. There are only five tables there, mostly for making access easy. All the data is in the vcard field.

I think LibreOffice is doing something "extra". There's another multi-line field available, a notes field. When I put two lines in there and add that field to the bottom of the address it shows both lines! I then got suspicious and wondered if it was a positional thing, so I put the two line address field at the end of the address but it still only showed one line. I then put the notes field in the middle of the address and it still showed two lines!

I'd say that points to a LibreOffice bug since you aren't seeing it in OpenOffice. A shame, but I s'pose inevitable, that the two codebases are diverging.

I'd'a thought it would be in Writer, but I s'pose it could be Base. Either way, treating a field specially (because of its name?) sounds very suspicious. What do you think?
LO 7.3.7.2 Fedora 37
Post Reply