Read data from Base file into Calc/Writer

patrick.de · February 21, 2019, 9:49am

Hi,

my setup is an .odb file where I enter all data into a form to populate the database. I work on various cases and use a new database (.odb) file for every case (since I never have to send mass-emails or something like this). I just want to populate various .ods and .odt files with case-specific variables, but only enter them once. So I figured to create a database for each case and retrieve the values from the database in all the case-related .ods and .odt files.

I know how to add the base file as a data source, but how can I tell Calc to automatically retrieve the value from the database? I have lots of columns in my database file, so manual entry by drag&drop is not really an option. For example, I want calc to populate A1 with the string in the first (and only) entry in the column “Name” of the connected database.

Same goes for .odt, there I would like a string like "Dear Mister ", where is populated with the data entered into the “Name” column in the connected database.

Thank you!

mariosv · February 21, 2019, 9:31pm

With writer you can use Mail merge, create a form letter

With calc once the database data is dragged on the sheet can be updated,
Calc - Refresh database range

patrick.de · February 23, 2019, 3:00pm

Thanks, mail merge would be doing what I want if I were to enter all my cases into one database. However, I want to use a new database for each case (because I don’t want to store information of different cases in a single file - for safety reasons (data theft, data corruption etc.).

Say I have a case Doe and I have Doe.odb and Doe.odt, and the Doe.odt contains a field that is linked to Doe.odb. Now I’d like to be able to copy Doe.odt, rename it to Smith.odt and be able to update the fields linked in the new Smith.odt to point to another database Smith.odb. Is there a way to update all existing fields to point to a new database? If I unlink Doe.odb and link Smith.odb in the Smith.odt, the fields don’t update to point to the new database.

mariosv · February 25, 2019, 10:40pm

If I’m not wrong it’s possible change the linked database, Menu/Edit/Exchange database

patrick.de · February 28, 2019, 5:18pm

Yes that’s possible, but the fields don’t update automatically to point towards the new database.
I’ve decided to use one database for all cases. In Calc I have a sheet where I copy the case-specific entries into and all the other cell reference to this sheet. In Write I use fields and select the case ID when printing.