Description: I wished to replace some complicated references to database ranges with the notation suggested in "Database Table Reference" in LibreOffice 25.8 Help. The formulas work as intended, but after save and reopen, the notation (ex : databaserange1[FieldX]) is replaced with the range (ex : $Sheet1.$F$7:$F$29). Steps to Reproduce: 1. Create a small database, define a database range covering the database 2. Write formulas such as sum of a particular column using the dbrange[field] notation 3. Save the spreadsheet, close and reopen - the database notation is replace with range references. Actual Results: The formula doesn't report an error but the database notation range is replaced with range references. The formulas are not dynamic - adding rows to the database would not be updated in the formulas. Expected Results: The database notation would be retained in the formulas, allowing a dynamic evolution of the database. Reproducible: Always User Profile Reset: No Additional Info: Version: 25.2.5.2 (AARCH64) / LibreOffice Community Build ID: 03d19516eb2e1dd5d4ccd751a0d6f35f35e08022 CPU threads: 10; OS: macOS 15.7.2; UI render: Skia/Raster; VCL: osx Locale: fr-FR (fr_FR.UTF-8); UI: en-US Calc: threaded
The Wiki https://wiki.documentfoundation.org/CalcTableRef has the section, "The ODF standard currently has no means to save "structured references". When saving as an .ods file, a "structured reference" is converted into a direct reference with values at the time of saving. Efforts to bring "structured references" to ODF are tracked in tdf#162088 and oasis#4162." I take this a request to add a corresponding warning in the help. To keep this kind of addressing in .ods format, is already tracked in bug 162088.
Thanks, Regina, for this clarification. I take it that for now the workaround is to save the workbook in Excel format. I must say it slightly makes my hair rise and gives me goosebumps, so I will evaluate reverting to my old formulas or saving as .xlsx, and I hope that LibreOffice will soon have the capacity to save the database table references in formulas.
Olivier Hallot committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/help/commit/33d3aaaec1d399ae2ef8f4c1fcc6cacf1b828f1d tdf#170237 Warn for Calc database references in ODF