Created attachment 145796 [details]
A Writer document with an invoice table
attached is a writer document, that demonstrates an area, where Writer could improve a little, and any user of similar table will celebrate!
It shows a typical simple invoice table, containing a few items, and each item line contains a formula for calculating the unit sum. Below that, it comes with the usual summing, VAT, etc.
* When adding an item below the microbes (add table row below), it is missing the sum formula, and consequently, this item is missing in the net amount sum.
* Moreover, with removing an item (delete table row), all summing formulas are invalidated, and have to be redone.
* Another minor issue lurks in the VAT field (A9), if simply editing the VAT value, and leaving the field, it is loosing its special VAT value format. Reassigning the format and replacing the content with just the value is necessary to recover.
Of course, this behaviour could be changed by embedding a Calc table, but this comes with a price to pay. Apart from the inconvenient handling, it creates issues with page break, etc...
I'm using such tables since more than 15 years with SO, OO, LO, and I'm enjoying todays state a lot, as it preserves the item row formatting, when adding rows. This used to fail in former times.
Solving these issues would make a lot of people, that create invoices with LO, very happy. I'm sure, I know some of them...
Hi! Thanks for the suggestions.
While I agree that the last suggestion is a valid issue, I suppose that first two bullets in the list are not.
> * When adding an item below the microbes (add table row below), it is missing
> the sum formula, and consequently, this item is missing in the net amount sum.
> * Moreover, with removing an item (delete table row), all summing formulas
> are invalidated, and have to be redone.
This one is because the sum formula in your Net Amount lists individual cells. In this case, the program has no way of knowing that adding a row requires to modify the arguments to a formula. If you use simply `=sum(<D2:B7>)` instead of `=sum(<D2|D3|D4|D5|D6|B7>)`, it will work as expected. Well - you could have valid reasons to not include the last line (Shipment) into the range; then you could simply add an empty row below the Microbes, assign it a minimal height, remove inner borders (so it would look like simply double-border), and include it into the range formula (in that case, it would look like `=sum(<D2:D7|B8>)`). Then adding rows below Microbes would work fine, and removing rows also would work (except for the rows explicitly mentioned in the formula).
thanks for your quick response and valuable suggestions.
I've no idea, why I stuck with the silly sum formula. Most probably by simulating the ∑ method...
'=sum(<D2:B7>)' is almost fixing 2 of the 4 issues mentioned.
I say almost, because, it doesn't work for removing the first item, but then
'=sum(<D1:B7>)' is doing fine for that case, too.
Thank you very much, will incorporate such adjustments to all invoice and offer templates in a second.
What's remaining is the third bullet, and the first half of the first. I'm sorry for not describing the first bullet properly, and for not splitting this topic in two issues, which it deserved. Here is the first part, which wasn't formulated properly:
* Adding a row of another issue is missing to clone the sum formula for the new row (amount * unit price).
I've attached an updated version of the test file, incorporating the changes, you suggested for further discussion and hopefully, to support somebody, who might want to tackle the remaining issues.
Created attachment 145801 [details]
A Writer document with an improved invoice table
* Adding a row for another item is missing to clone the sum formula for the new row (amount * unit price).
(In reply to Hans-Peter Jansen from comment #4)
> * Adding a row for another item is missing to clone the sum formula for the
> new row (amount * unit price).
I don't understand what you would like. Clone what, row where cursor is?
We need steps, with Experienced and Expected result. But, that must be a new request.
This bug with multiple issues is Invalid report.
But we can convert it to a proper bug for this:
(In reply to Hans-Peter Jansen from comment #0)
> * Another minor issue lurks in the VAT field (A9), if simply editing the
> VAT value, and leaving the field, it is loosing its special VAT value
Looks like a bug. Please explain what's value format here. So that we can understand what happens with s simple change, let's say from 12,3% to 17%.
Thanks Timur, for your reply.
(In reply to Timur from comment #5)
> (In reply to Hans-Peter Jansen from comment #4)
> > * Adding a row for another item is missing to clone the sum formula for the
> > new row (amount * unit price).
> I don't understand what you would like. Clone what, row where cursor is?
> We need steps, with Experienced and Expected result. But, that must be a new
> This bug with multiple issues is Invalid report.
> But we can convert it to a proper bug for this:
Okay, understand. Lesson learned.
> (In reply to Hans-Peter Jansen from comment #0)
> > * Another minor issue lurks in the VAT field (A9), if simply editing the
> > VAT value, and leaving the field, it is loosing its special VAT value
> > format.
> Looks like a bug. Please explain what's value format here. So that we can
> understand what happens with s simple change, let's say from 12,3% to 17%.
The value format is '0,0%" VAT"'. If you load the attached document, put the cursor in the VAT field (A10), and edit the value to say "13,2", and tab out of the field, the format is lost (-> Text '@'), the new value is displayed left aligned, although the field is still right aligned(!), and the calculation in B10 fails (-> 0,00 €). I wasn't able to recover from this without reassigning the specific %-format (0,0%" VAT"), and *enter* the plain value again. The only proper way to change the VAT value for an experienced user is selecting the whole value, and replacing it with a plain number.
I would expect the field A10 to keep the assigned format, and just accept the new value. Then, the calculation in B10 wouldn't fail as well, I bet.
I change the title and confirm since this looks like a bug. Repro 6.3+.
We still need an explanation why this happens and a check for being a duplicate.
Dear Hans-Peter Jansen,
To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.
There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.
If you have time, please do the following:
Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/
If the bug is present, please leave a comment that includes the information from Help - About LibreOffice.
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice.
Please DO NOT
Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not
appropriate in this case)
If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/
2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to 'inherited from OOo';
4b. If the bug was not present in 3.3 - add 'regression' to keyword
Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa
Thank you for helping us make LibreOffice even better for everyone!
Replicated with 22.214.171.124 (openSUSE TW 20210113)
Load attached document, edit VAT field, and see calculation failing.
One cannot restore from this state with ^Z anymore.
To recover, either reload document, or reassign the custom number format to the cell, which takes these steps: select VAT cell of table, assign custom VAT format (no context menu available: Table -> Number format -> Custom formats -> Select VAT format -> OK), select the text of the cell, replace with a number (eg.: 10), tab out of the cell, é voila, the new VAT is calculated correctly.
The last step is the *only* way available today to edit a cell with a custom number format without breaking stuff.
Hopefully, anybody can imagine, why this procedure isn't going to fly.