Mail merge source - a web-based application?

[judgej]

Is it possible to use a web-based application as a mail merge source?

The details to access the merge data would be a URL, a username and a password. I would be writing the web application to present the data in whatever format is needed. Ideally the source would provide a list of different reports that he user has run off on the web application, that the user can select from to do their merge.

In addition, is there a way in which the mail merge template (the "starting document"?) and the data source can be merged into one? Ideally the end user would simply open a document, select which set of data they want from the source that is presented to them, then press "go".

I don't know if this kind of thing is already built into OO, is available through available plugins, or is going to be a custom plugin or application. What I want to avoid is end users having to mess around with files, matching up fields, etc or having to expose database logins or merge data into local applications and databases.

Thanks!

-- Jason

[Villeroy]

Yes, of course you can do all this. But you actually have to do it. Before you can do it you need a fairly deep knowlege of this office suite.

[judgej]

Of course, the age-old question "is it possible..." - yes, *anything* is possible

Okay, so what you are saying is that these data sources are built into the core of OO, and it is a non-trivial task to build a custom one. I was hoping (wishing) that someone had maybe created a generic data source that can pull data from a URL, given a specified format and protocol. Even if authentication was not built in, usernames and passwords could always be tagged onto the URL. I can imagine such a data source would be quite well used, since it would be so versatile.

Unfortunately I don't have the time to dive into OO internals. My brain is already crammed full of frameworks, programming languages, CMSs, CRMS, ...

[aqualung]

You can always advertise in this forum's "Paid Support" section for someone to create what you want for you: http://user.services.openoffice.org/en/ ... m.php?f=53

[judgej]

Yeah, I may do that once I have a more concrete idea of how it would look.

[judgej]

Looking at it in a bit more detail, it looks to me like OO Write embeds some part of OO Base to handle the mail merge data. The data sources then emulate OO Base tables. I'm wondering if it is not so much Write that I am trying to extend with new data sources, but Base?

Maybe LDAP would work? I don't know whether LDAP supports multiple groups of users that would appear as separate tables in OO Base/Write mail merge, but if it does, then a lightweight LDAP server on my web-based application could provide all the mail merge data that I need, with the user information coming direct from the database. Is that going to be a goer?

[acknak]

I would think that the quickest way to implement this would be a spreadhseet that uses your web page as an external data source, then a data source that takes data from the spreadsheet for the merge.

I don't know if that's all automatable--it may require some user interaction to update the data and do the merge.

However, it seems a little strange to take such a roundabout path when your web application must already have access to some kind of data store. Is there some way to get data directly into Base from your data store? Then you could use that as the merge data source.

[Villeroy]

Unfortunately I don't have the time to dive into OO internals.

No internals, just the plain basics about mail merging and the Base component as a bridge between office documents and database data (including csv, spreadsheets, dBase, LDAP and more). Most problems can be solved by mere configuration without a single line of code.

[judgej]

Most problems can be solved by mere configuration without a single line of code.

That was certainly what I was hoping.

Looking at the data sources that are built in, all except for one operate on local files or local applications. Only the LDAP data source, so far as I can see*, connects to the source via the network. If I could just define a source as, say, an XML file - or collection of XML files - to be found at a URL, that would be ideal. Creating the data at that URL would be my problem to deal with.

Looking at LDAP as a solution, it is possible to drive LDAP from a MySQL database, but it looks like quite a specialist job (I can find lots of discussions, but no simple ready-made solutions). I guess an LDAP server could sit there in the middle on its own, and have OO Write+Base reading directories from it on one hand, while my web application creates and populates directories at the other end just using PEAR LDAP libraries or similar.

Edit: * The MySQL and ODBC connections access data sources over the network, but I am trying to deliver the address data over the web - this will be for users in several different countries. I only wish to expose HTTP and not direct database connections.

[judgej]

Okay, here is another approach that just may work, and is probably simpler than LDAP.

I didn't realise that the path to a file resource does not just have to be local; it can be specified as a URL.

Now, a single Excel spreadsheet can contain multiple named worksheets. Each worksheet can contain a list of addresses for use with mail merging. By delivering a multi-sheet spreadsheet from a URL that the "Spreadsheet" registered data source points to, a set of mail-merge tables can be made available to do a mail merge.

That spreadsheet can be generated on-the-fly by my PHP application from data derived from the database. The URL can contain the user login and password (or password token) so that the user doing the mail merge will see all their data.

That ought to be fairly easy to set up, and will be fairly easy for an end user to set up and use. All they will need to be given is the URL that will generate the spreadsheet, and (given the mail merge template documents are also available) they can mail merge to their heart's delight.

In fact, since the data source settings is itself just a zip archive of XML files, even that could be generated for the application user, by the web application, containing all the settings and details. It's a crazy plan that just might work.

It's a shame Write/Base does not support digest authentication for a URL that it fetches a resource from. While Firefox will prompt for a password if the URL requires a login, OO Base will simply report an error. If it prompted for a username and password, then there would be no need to hard-code those details into the URL, and no possible confusion if one user is logged into the application, and inadvertently uses the data source URL for a different user who happened to have used the machine before him or her. Better just to prompt for the username and password, I think.

[Villeroy]

Forget spreadsheets. Spreadsheets are nasty plastic toys. Simply use some database server over the network (any network). Base will prompts for authentification when required by the respective database.

[judgej]

I just don't want to expose databases directly to the Internet and have to create multiple user logins, privileges to separate their multiple created tables and synchronise all that authentication with the application. There are just too many things, wrt securty, that could go wrong with that.

[judgej]

It is not even that simple.

What I now realise is that a mail merge template (starting document) has field placeholders that fully qualify their source down to the data source, table and field. You can only merge in data from that table that is specified in the template. If you attempt to merge in data from another table - even if the fields have the same name - the fields just come out blank.

That is pretty restrictive IMO. Tying a mail merge template to a *specific* data source *and* table makes it a lot less flexible, not being able to just pick any data from a list of tables to merge in. The output formatting, and the input address source, really should not be so tightly coupled. It is like having to create a separate CSS stylesheet for every HTML page, and not being able to share the same stylesheet between different pages - just silly.

I wonder if OO mail merge is ever used in an enterprise situation, and not just a single-user one-shot mail merge scenario?

[Villeroy]

Let the user download a distinct csv file into a certain directory via secure ftp.

[judgej]

Let the user download a distinct csv file...

Oh, and we have lost the user already

I think what you are suggesting is to create a single CSV data source pointing at a specific folder and file name. Each mail merge will be done on that same data source, and so the same file each time. The file will get over-written with each new address list that needs to be mail merged.

Yes, that could work, with the right instructions. The downloaded file would come from the web application, and so there is authentication there that the user has permission to access the data. The data source would probably need to point to different directories, depending on what OS people are using, so a bit of preparation needed there to create a couple of Addresses.odb databases.

[Villeroy]

This is the year 2011. I reject to work with co-workers that refuse to learn how one saves a file at a distinct location (or how to use a virtual printer to mention my recent trouble with someone). Apart from that, there are scripts and configuration files to do such jobs (under Linux at least).
Using a true database (with adequate encryption and access privileges) could solve the whole issue of distribution:
SELECT * FROM "SomeRecords" WHERE "Destination Field"=CURRENT_USER AND DATEDIFF('days',"TimeStamp",CURRENT_DATE)=1

[judgej]

It's 2011 and we have more technology in more walks of our lives, and in more people's hands. However, the people who understand how it all works are still very rare. You may reject people who cannot grasp the concepts of data sources and URLs, but I can't tell the client that a whole bunch of his employees are now too stupid to use the new-fangled whizzy technology that is supposed to make things easier. Sorry, the real world contains real people, with a wide range skill levels.

There are lots of suggestions here that I'll follow up, including the database option. Much appreciated.

[Villeroy]

Admittedly, I'm in a very priviledged situation. However, I think that outsourcing the faintest trace of IT competence to web 2.0 developers is an ugly trap.