Bug 131309 - Validity>Custom>Formula does not have the expected effect without additional measures - evaluation of entered formula only happens after additional trigger
Summary: Validity>Custom>Formula does not have the expected effect without additional...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.2.5.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Cell-Validity
  Show dependency treegraph
 
Reported: 2020-03-12 13:47 UTC by Wolfgang Jäger
Modified: 2020-12-08 06:41 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Wolfgang Jäger 2020-03-12 13:47:10 UTC
Q&A thread: https://ask.libreoffice.org/en/question/233184/ 
In pursuit of the mentioned thread I first noticed that the Validity feature got the new Allow:>Custom>Formula variant.  

I tested it (though I judge it to be completely useless; 'Cell range' option always allowed to enter a formula, and this is sufficiently made clear there) and found with version 6.2.5.2 and with 6.4.2.1 as well that the new feature doesnt't work as requested and expected:

The formula entered under >Custom>Formula is passed to the editable line under the 'Cell range' path, but isn't put in force as long as it not got at least a fake edit IN THIS PLACE.  

The new setting will also work as soon as the document was saved to a file. That's the reason fo what I cannot provide an attachment showing the issue. 

Instead of a demonstrating attachment:

Create a new Calc document, use first sheet e.g.
Enter a, b, c, d into <Cells A2 through A5.
Go to cell D2.
Call >Data>Validity
Open >Criteria>Custom
Go to 'Formula'
Enter OFFSET($A$2;1;0;2;1)
OK

Expected: 
Selecting D2 results in the dropdown symbol shown right of it.
Clicking on it offers a list containing 2 rows with b and c.

Observed:
No such functionality.

Open the Valdity dialog for DE2 again.
Call >Critria>Cell range

Extected:
Nothing shown in 'Source'

Observed:
'Source' is containing the correct ecpression.

...

Store the document to a file

Expected:
Nothing new

Observed:
Now the Validity setting works as originally expected
(Same thing if the range expression was edited in any way under 'Source' without changing it.)

See also tdf#130738.

My suggsetion: Make sure that formulas entered in 'Cell range' mode are treated properly by the Excel filters when importing/exporting, and remove it from Calc again OR cancel the redundancy concerning 'Cell range'. First alternative clearly preferred.
Comment 1 Durgapriyanka 2020-03-12 15:56:05 UTC
Thank you for reporting this bug. I can reproduce this partially, but not fully in

Version: 6.4.0.0.alpha1+ (x86)
Build ID: ec7374ff84c71edfbb30d6e4dc5b486b6df7107f
CPU threads: 2; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: default; VCL: win; 
TinderBox: Win-x86@42, Branch:master, Time: 2019-11-10_21:37:30
Locale: en-US (en_US); UI-Language: en-US
Calc: threaded

when I Create a new Calc document, 
Enter a, b, c, d into <Cells A2 through A5>.
Go to cell D2.
Call >Data>Validity
Open >Criteria>Custom
Go to 'Formula'
Enter OFFSET($A$2;1;0;2;1)
and click OK, No dropdown symbol shown right of it.

But, again when I Open the Validity dialog for DE2 again.
Cell>Criteria>Cell range , 'Source' is containing the correct expression.

Even without saving the document and reopening it, Now the Validity setting works as originally expected.
Comment 2 Wolfgang Jäger 2020-03-12 16:45:34 UTC
(In reply to Durgapriyanka from comment #1)
> Thank you for reporting this bug. I can reproduce this partially, but not
> fully in
...
> Cell>Criteria>Cell range , 'Source' is containing the correct expression.
> 
> Even without saving the document and reopening it, Now the Validity setting
> works as originally expected.

This happened to me too, but only if I had applied at least an as-if action of editing by pressing 'OK' instead of 'Cancel'.
Comment 3 Cor Nouws 2020-03-16 21:18:50 UTC
confirming as per comment #1
Comment 4 Mike Kaganski 2020-12-08 06:00:07 UTC
(In reply to Wolfgang Jäger from comment #0)
> ... I first noticed that the Validity feature
> got the new Allow:>Custom>Formula variant.  
> 
> ... I judge it to be completely useless; 'Cell range' option
> always allowed to enter a formula, and this is sufficiently made clear
> there

Only answering the cited part.
Since comment 0 had mentioned tdf#130738, it's not quite clear to me why could you "judge it to be completely useless". tdf#130738 links to two Q&As with explanation what "Custom" is for; and it's sufficiently different from "Cell range". The latter is used to provide a *list of allowed values*. The "Custom" is for an arbitrary formula that returns TRUE or FALSE that is the result of validity check. E.g., it can check if the value matches some regex. IIUC thic can't be accomplished using 'Cell range' formula.
Comment 5 Mike Kaganski 2020-12-08 06:38:53 UTC
So the problem here seems that the two formula areas ("Custom" and "Cell Range") are not separated and checked strictly.

1. The formulas in the two areas are expected to be different. The "Custom" formula should result in a scalar value that would be converted to boolean for the purpose of the check. The "Cell Range" expects to return a range which would be used as a source to find a match for the value in the cell in the validity check. But when you enter the formula, it's not checked that its result matches the area.

2. A formula entered in "Custom", which gives a range, will automatically end up in "Cell Range" (but the drop-down will not appear immediately). This automatic "migration" could be unexpected (?).

IMO it's OK to have it migrated as it is now, but the check that it's in fact a range formula should be made at the stage of dialog closing, and the actions should be performed according to its final type (range result detected => Cell Range validity rule => create a list).
Comment 6 Mike Kaganski 2020-12-08 06:41:03 UTC
(In reply to Mike Kaganski from comment #5)
> ... but the check that it's in fact a range formula ...

sorry for the wrong use of "range formula" term here; I only meant "a formula returning a range".