I have created a case management database for my office in LibO Base that we have been using since 2005ish. (I actually created it in OpenOffice.org before LibO existed, but I digress.) If it makes a difference (it probably doesn’t, but in case it does) it’s running on an old version of HyperSQL (I think 1.7) because that’s what OOo used when I created it, and I haven’t had the time/ability to convert it to the more modern version of the database. (That may be my next project.)
I do a lot of court-appointed work, and this means generating a lot of documents for each case I am assigned. Currently, we have all of these documents in one file, divided into sections, and use user-defined variables to fill in everything that is unique: the case number, court, judge name, prosecutor info, client name and address, etc. Click it once, enter the information, and it populates it throughout the document. This works really well and saves us a ton of time. However, we are still entering everything twice: we enter all of this information into the database, and then we enter it into the documents. I’d like to make it even easier by drawing the information that goes into the documents from the database.
This should be easy enough to do as a mail merge or something, but here’s the problem: the information is spread over several tables. The client information (name, address, etc.) is in one table, the case information (court, case number, and so on) is in another table, and the opposing counsel information (name, Bar number, address, etc.) is in yet another table. I can’t seem to find a way to do some kind of merge where I can query information from several different tables and populate it into one document automatically.
Is there some kind of clever way to do this? Import the client and opposing counsel information into the case table? Create a multiple-table query? Something else I’m missing? The goal is to avoid entering the same information twice, and to avoid regressions (such as the client with multiple cases who moves and when we change his address, it only changes on one case and not the others). Is this even possible?