Apache OpenOffice Community Forum

I've successfully used a CALC spreadsheet, saved as a DBF file, to import into Base for query and reporting purposes.

The CALC sheet has calculations on it, particularly date addition, which does work.

After connecting to the DBF file in Base, and doing what I need to do, I then return to the DBF file in CALC, with the intention of adding more records.

However, even though I've copied the date calculations from the 1st record down to the last row of records I expect (the rows actually haven't been filled in, except for the calculations)

When I return to the spreadsheet the results of the calculations are still there, but the formula's are no longer there. In order to enter new records, or update the current records, the formula must be entered again.

I hope I made this clear.

The same thing happens if I connect as a spreadsheet.

Is there a solution?

Thanks,
Al

Any database engine can do the date calculation properly, but not the built-in HSQLDB 1.8 nor dBase. In Base you can get today's date, time or time-stamp and you can get the difference between two times as integer numbers of days, month, years, hours, minutes. But you can not get another date/time from one date/time and some offset.

Edit: I forgot a rather complex work-around for the built-in HSQLDB 1.8: http://www.oooforum.org/forum/viewtopic ... 693#434199

You may collect the raw data in a database, and do the time calculation in Calc.
Drag a table or a filtered/sorted query from the data source window into Calc.
Apply any formatting, charts and formulas you want.
Call Data>Define... select your import range and set the additional options "Insert/Remove Cells" and "Keep Formatting". You may also decide to not store the imported data in the spreadsheet which enforces a refresh on open.
No the size of your formula ranges will adjust to the resizing import ranges if the formula ranges are adjacent to the import range.
[Tutorial] Using registered datasources in Calc