Created attachment 175976 [details] A RTF doc made with Microsoft Word - load this info LibreOffice Writer 1. Create a RTF document in Microsoft Word with field formula of MIN(SUM(<A1:A3>),333) 2. Load that RTF into LibreOffice Writer 3. Right click on the field formula and select "Edit fields" and the value becomes incorrect and the formula has changed to MIN(SUM(<A1:A3>)|333) which is invalid syntax.
Created attachment 175977 [details] Screenshot of a the RTF file in a text editor showing the codes. The field formula is highlighted
Created attachment 175978 [details] Screenshot: The damanged formula in LibreOffice Writer
(In reply to Dave McKellar from comment #0) > 3. Right click on the field formula and select "Edit fields" and the value > becomes incorrect and the formula has changed to MIN(SUM(<A1:A3>)|333) which > is invalid syntax. Actually, that is correct syntax. Writer field formulae syntax is a bit different from Calc. One way in which this is the case is that lists are separated by | rather than ','. This is documented here: https://help.libreoffice.org/7.2/en-US/text/swriter/02/14020000.html?&DbPAR=WRITER&System=UNIX What may be a bug is that when I click on the field (thus causing it to recalculate), the displayed value changes from 333 to 933. I'm not sure if that is expected based on the parentheses placement. Even if I change the formula to MIN (SUM <A1:A3>)|333 and update fields it remains at 933.
Almost forgot to mention I am using: Version: 7.2.2.2 / LibreOffice Community Build ID: 02b2acce88a210515b4a5bb2e46cbfb63fe97d56 CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: gtk3 Locale: en-US (en_US.UTF-8); UI: en-US Calc: threaded
Thank you, Michael, for looking at this. I should have checked for docs for the LibreOffice formula syntax. Yeah, from a user experience point of view, if you import an document, make no changes, click on a field to edit it - it should not immediately change. That's not nice for the user ;)
Able to replicate the issue on the latest version Version: 7.3.4.2 (x64) / LibreOffice Community Build ID: 728fec16bd5f605073805c3c9e7c4212a0120dc5 CPU threads: 4; OS: Windows 10.0 Build 19044; UI render: Skia/Vulkan; VCL: win Locale: en-US (en_US); UI: en-GB Calc: CL 1. Create an RTF document in Microsoft Word with the field formula of MIN(SUM(<A1:A3>),333) 2. Load that RTF into LibreOffice Writer > Result of the formula is ‘333’ 3. Follow a or b flow. a. (Left or Right) Click Last Column (Result) of the table > Will be updated to ‘933’ b.Copy the table to a new document i. Ctr + a = Select all ii. Ctr + c = Copy iii. Ctr + n = New Document iv. Ctr + v = Paste v. Value will be ‘933’ instead of ‘333’ •Issues: a. Recalculation happens thus formula result is updated without updating the actual field (formula) b. Providing alternative result 4. “Right-click on the field formula and select "Edit fields" and the value becomes incorrect and the formula has changed to MIN(SUM(<A1:A3>)|333) which is invalid syntax.” .Confirmed on the 7.3 documentation and as inherited from previous documentation this is the actual or correct conversion of the formula for the Writer where “|” was used as a line separator. Source: https://help.libreoffice.org/7.3/he/text/swriter/02/14020000.html?DbPAR=WRITER#bm_id3149687 Able to replicate the issue on the version reported Version: 7.2.1.2 (x64) / LibreOffice Community Build ID: 87b77fad49947c1441b67c559c339af8f3517e22 CPU threads: 4; OS: Windows 10.0 Build 19044; UI render: Skia/Vulkan; VCL: win Locale: en-US (en_US); UI: en-GB Calc: CL Able to replicate still in the prerelease version using the above steps Version: 7.4.0.0.beta1 (x64) / LibreOffice Community Build ID: cec1fe9b57a55c032f9f118c907f34e22a63d040 CPU threads: 4; OS: Windows 10.0 Build 19044; UI render: Skia/Vulkan; VCL: win Locale: en-US (en_US); UI: en-GB Calc: CL Check-in Similar Product: 1. Tried to Open the document in MS Word using Steps 1 to 3 (above), recalculation is not happening upon clicking on the result field or copying and pasting to a new document. a.Following Step 4, only then you can edit the formula. Microsoft® Word for Microsoft 365 MSO (Version 2205 Build 16.0.15225.20278) 64-bit 2. Tried in Google Docs, recalculation is not happening upon clicking on the result field, as the table formula is not supported by the application. https://support.google.com/docs/thread/11444767/sum-table-cells-in-docs?hl=en Version 102.0.5005.115 (Official Build) (64-bit) Additional Notes: • I tried using a different set of Basic Calculation Functions (Basic Calcu) i.e., Addition, Subtraction, Multiplication, and division, and found no issue upon loading of the file. • I tried using Statistical Functions (Stat Calcu) i.e., MIN, MAX, COUNT, and found no issue upon loading the file as well. • However, using a complex formula (Complex Formula Office) the error is encountered. a. Office Formula / Writer Formula I. =MAX(SUM(A1:A3),333) / MAX(SUM(<A1:A3>)|333) II. =MIN(SUM(A1:A3),333) / MIN(SUM(<A1:A3>)|333) III. =MIN((A1:A3),333) / MIN(<A1:A3>|333) IV. =MAX((A1:A3),333) / MAX(<A1:A3>|333) Note that iii and iv Office Formula is showing a !Syntax Error results in Word, yet following Steps 1 to 3 in Writer, it will recalculate and display results. • I tried to check the formula in both (Complex Excel) and (Complex Calc) and they are returning the same results for the same sets of data and formula. • (Location) of the table does not matter in the file, be it the only content, at the middle, or at the bottom. The issue will still be encountered. Conclusion: • Issue 1: Recalculation happens thus formula result is updated without updating the actual field (formula) • Fixing the problem ought to be able to deal with the problem in consumer expectations and experience. To maintain the document's integrity, no data or information should be changed without the user's actual involvement. • Additionally, I believe that updates on the results for the table's formula should only take place when the user activates the Edit field or Update Formula so that they are aware of the changes. •Issue 2 Updated value is incorrect • This is a separate issue and thus needs a separate bug report and a detailed assessment of the actual computation and combination of the formulas. • Per initial assessment, the recalculation happens when the formula is extracting different results from the initial value. • The recalculation seems to leave out the MIN/MAX part in the formula and focuses on the SUM(<A1:A3>)|333) given that the updated results are correct if we are to omit the MIN/MAX part (based on both (Complex Excel) and (Complex Calc) • Below formula is able to produce a correct result even with MIN/MAX given a re-arrange value in Writer • MIN((<A1>+<A2>+<A3>)|333) • MIN(333|SUM<A1:A3>) • MAX((<A1>+<A2>+<A3>)|333) • MAX(333|SUM<A1:A3>) A similar issue is encountered in the below ticket related to table formula with a more complex one; not limited to MIN/MAX • Bug 141320 - [RTF] .rtf Rich Text: broken file error due to inserted formula (edit)
Created attachment 181074 [details] Basic Calcu
Created attachment 181075 [details] Stat Calcu
Created attachment 181076 [details] Complex Formula Office
Created attachment 181077 [details] Complex Excel
Created attachment 181078 [details] Complex Calc
The field value remained stable as 333 before version 5.4 and https://git.libreoffice.org/core/commit/c568eb7d3bb4584867f0a1f0a7965f73097f009b tdf#105975 Add Formula field parsing (docx) in SWriter With that change, the field started to automatically change to ** Expression is faulty ** In 7.1, it started to automatically change to 933 with Michael Warner's https://git.libreoffice.org/core/commit/68e74bdf63e992666016c790e8e4cfd5b28d6abe tdf133647 tdf123386 tdf123389 Improved .docx table formula import The changing became non-automatic with Miklós Vajna's https://git.libreoffice.org/core/commit/1abf4e6d07ca0ac31bc54f812df84efc82d2af1b DOCX import: don't throw away cached value of SwHiddenTextField ...