Bug Hunting Session
Bug 60645 - Formulas not refreshing on document (.ods) opening
Summary: Formulas not refreshing on document (.ods) opening
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.0.0.3 release
Hardware: Other All
: medium normal
Assignee: Markus Mohrhard
URL:
Whiteboard: target:4.1.0 target:4.0.1
Keywords:
Depends on:
Blocks:
 
Reported: 2013-02-11 09:50 UTC by Mark Rogers
Modified: 2013-02-20 16:09 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Demonstration of bug (8.17 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-02-14 17:25 UTC, Mark Rogers
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Mark Rogers 2013-02-11 09:50:30 UTC
Problem description: 
I have several documents that must auto-refresh formula values on document open, but do not do so any more.

Use case: I use a file naming convention which allows me to create a copy of an existing proposal, rename it, and several fields in the document get updated to reflect new proposal reference numbers etc. This no longer works.

Steps to reproduce:
1. Create document, set A1 to =CALC("filename",A1)
2. Save document as OOCalcBug.ods, close document
3. Reopen document, confirm A1 now shows filename in A1. Close document.
4. Rename document to OOCalcBug2.ods
5. Open OOCalcBug2.ods, confirm A1 shows new filename.

Current behavior:
Step 5: old filename is shown.

Expected behavior:
Step 5: new filename should be shown.

Note:
I am aware that this is a new feature when opening spreadsheets. Looking in the options (under Calc->Formulas) there is the option to turn this off for .XLS or .ODF but not .ODS.
Comment 1 Jorendc 2013-02-11 09:56:08 UTC
Thanks for reporting!

Does a recalculation (ctrl+shift+F9) solve this issue? If so, this is a Import Cache problem.

Thanks for your time retesting,
Kind regards,
Joren

PS:I set status to 'NEEDINFO' for now, please set back to UNCONFIRMED if you provide us a bit more information (does it resolve the issue?).
Comment 2 Mark Rogers 2013-02-11 10:08:30 UTC
Yes, Ctrl-Shift-F9 does correct the value.
Comment 3 Jorendc 2013-02-11 10:12:16 UTC
Thanks for retesting.

Therefore it's probably an Import Cache problem.

@Markus, Kohei, Eike: not sure this is a duplicate, but this is also a problem which can be resolved using ctrl+shift+F9; therefore I CC'ed you.
Comment 4 Markus Mohrhard 2013-02-14 16:49:27 UTC
Can you please attach a test document? Thanks!
Comment 5 Mark Rogers 2013-02-14 17:25:26 UTC
Created attachment 74829 [details]
Demonstration of bug

File was created and saved as test.ods. At time of saving, cell A1 reads "'file:///C:/Users/Mark/test.ods'#$Sheet1".

The file was renamed to Bug60645.ods before uploading here.

Expected behaviour (as per LibreOffice 3.x): On opening, A1 should show the filename as "'file:///<path to doc>/Bug60645.ods'#$Sheet1".

Current behaviour (4.0.0.3): A1 will retain old value until refreshed (Ctrl-Shift-F9)

Alternative expected behaviour: There should be a way to disable the new Import Cache behaviour selectively for .ods files.
Comment 6 Not Assigned 2013-02-14 20:53:26 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=9c55eab0309681991eae7382b682c26550786f56

CELL needs to be recalculated during import as well, fdo#60645



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 7 Markus Mohrhard 2013-02-14 20:56:26 UTC
> 
> Alternative expected behaviour: There should be a way to disable the new
> Import Cache behaviour selectively for .ods files.

Just for the record. Setting the import option to "Always recalculate" in Tools->Options->Calc->Formula will prevent the use of cached values even if the text there is misleading.
Comment 8 Mark Rogers 2013-02-15 10:13:58 UTC
I did try Tools->Options->Calc->Formula->ODF->Prompt User before reporting the bug, but it didn't do anything (I assume it should have prompted me?)

Tools->Options->Calc->Formula->ODF->Always recalculate does indeed work, though, so thanks for that.

If I understand correctly, that means that the current behaviour as reported is not itself a bug, however:

1. Tools->Options->Calc->Formula->ODF is labelled incorrectly, as this applied to .ODS files saved by LibreOffice
2. Tools->Options->Calc->Formula->ODF->Prompt User should do something

It would be very nice to be able to set a "recalc on load" setting in the document itself (rather than be restricted to a global settings), or indeed even have a "recalc this cell on load" or "don't cache this cell" option.
Comment 9 Not Assigned 2013-02-15 13:02:06 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=ff319d052659974d1aa5d6ac8c468a7259a46cc4

use AddRecalcMode(RECALCMODE_ONLOAD), fdo#60645 related



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 10 Not Assigned 2013-02-15 13:19:01 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=61c72ef4bebebdfa505176e7d5db82920ad3822e

ocColumn and ocRow do not need recalc on load, fdo#60645 related



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 11 Eike Rathke 2013-02-15 13:22:23 UTC
Squashed these commits into one and submitted for 4-0 review https://gerrit.libreoffice.org/2168
Comment 12 Not Assigned 2013-02-15 14:07:56 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=2045714acd0a8858ed7c1c60d42996bd00be4dac&h=libreoffice-4-0

CELL needs to be recalculated during import as well, fdo#60645


It will be available in LibreOffice 4.0.1.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 13 Eike Rathke 2013-02-20 14:50:00 UTC
Let's mark this RESOLVED FIXED then.
Comment 14 Mark Rogers 2013-02-20 15:41:38 UTC
As the OP, thank you to all involved. Seeing my experience of reporting and following a bug here has made a colleague who used Office think again!
Comment 15 Joel Madero 2013-02-20 16:09:55 UTC
@Mark - usually we don't do this on comments but our QA team is looking for volunteers to help other users have good experiences like you have :) If you have just a little time (or a lot ;) ), we could definitely use the help, your bug report was clear and easy to reproduce, overall just looks like the process went smoothly.

Feel free to contact me directly or join our IRC chat at freenode, #libreoffice-qa


Glad that you got the help you were looking for here!