Bug 96698 - Data => Validity => Custom (like Excel) is missing
Summary: Data => Validity => Custom (like Excel) is missing
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard: target:6.2.0
Keywords:
: 90324 102273 (view as bug list)
Depends on:
Blocks: Cell-Validity
  Show dependency treegraph
 
Reported: 2015-12-23 18:43 UTC by burnuser2
Modified: 2020-06-29 14:09 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:


Attachments
customValidation.xlsx: a couple of scenarios for testing custom validation (10.39 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-06-15 10:06 UTC, Justin L
Details
partial fix: custom formulas work as long as they don't reference the edited cell. (3.50 KB, patch)
2017-06-17 11:44 UTC, Justin L
Details

Note You need to log in before you can comment on or make changes to this bug.
Description burnuser2 2015-12-23 18:43:07 UTC
In Excel (all the time) there is in Data => Validity an additional item to select: "Custom"

https://support.office.com/en-nz/article/Apply-data-validation-to-cells-c743a24a-bc48-41f1-bd92-95b6aeeb73c9#bm4_8

Checking for values of other cells (or the actual content of the active cell) can be done in LibreOffice also with a formula in the Minimum/Maximum values.

But 2 problems left:

1. A general valitdity check for the entered new content of the active cell - especially for text - with a custom formula like in Excel is not possible.
A sometimes painful shortcoming :-(

2. Excel compatibility: As there is no "Custom" formula functionality in LO, the Excel import and re-export must kill this (traditional) Excel functionality.
Comment 1 Buovjaga 2015-12-27 16:00:45 UTC
Sounds fair -> NEW.
Comment 2 burnuser2 2017-01-08 10:05:42 UTC
Bug is still present. No changes in bug behavior.
Testsystem:
LO 5.2.3.3 on Windows Vista
Comment 3 Justin L 2017-05-25 15:52:55 UTC
*** Bug 102273 has been marked as a duplicate of this bug. ***
Comment 4 Justin L 2017-06-15 10:06:26 UTC
Created attachment 134044 [details]
customValidation.xlsx: a couple of scenarios for testing custom validation

code pointers
-xlsx import: sc/source/filter/oox/worksheethelper.cxx
-xls import: sc/source/filter/excel/xicontent.cxx
-validation: sc/source/core/data/validat.cxx ::IsDataValid()
-validation: sc/source/core/data/conditio.cxx ::IsValidStr()
Comment 5 Justin L 2017-06-17 11:44:02 UTC
Created attachment 134080 [details]
partial fix: custom formulas work as long as they don't reference the edited cell.

Currently, custom formulas do not work at all. The only time it MIGHT work out is working with non-zero numbers.

The larger problem here is that the formula runs on the previous data, so whatever you typed is not being checked.  So using my example document, if you type "comma,", then it will be accepted (since it evaluated an empty cell), but anything you enter after that will be rejected (because it is evaluating the old value of "comma,").

I think that is what this comment is trying to indicate.
            //  for Custom, it must be eOp == SC_COND_DIRECT
            //TODO: the value must be in the document !!!

It is easy to ensure that for Custom, eOp == SC_COND_DIRECT. That is why the patch does for .xls[xb] and .xls. I'm not submitting it on it's own though because the formula will almost always be checking it's own value, so the TODO part is an essential part of any fix.
Comment 6 Commit Notification 2018-05-31 17:09:11 UTC
Marco Cecchetti committed a patch related to this issue.
It has been pushed to "master":

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

tdf#96698 - calc: add support for custom validation (through a formula)

It will be available in 6.2.0.

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 Justin L 2018-06-29 06:29:36 UTC
I love it. Verified in 6.2 alpha using my personal TSC problem document. Thanks for sticking with this Marco.
Comment 8 Commit Notification 2018-07-25 05:54:56 UTC
Zdeněk Crhonek committed a patch related to this issue.
It has been pushed to "master":

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

uitest for bug tdf#96698 , Validity-> Custom

It will be available in 6.2.0.

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 9 burnuser2 2018-11-18 09:38:16 UTC
This is a very important fix - should be documented in the changelog:
https://wiki.documentfoundation.org/ReleaseNotes/6.2#Calc
Comment 10 Cor Nouws 2018-11-18 22:23:47 UTC
(In reply to burnuser from comment #9)
> This is a very important fix - should be documented in the changelog:
> https://wiki.documentfoundation.org/ReleaseNotes/6.2#Calc

Feel free to do it yourself :)
Comment 11 Justin L 2018-11-19 04:50:29 UTC
(In reply to burnuser from comment #9)
> This is a very important fix - should be documented in the changelog:
Done (since I already have an account for the wiki). Added "Data Validation now supports custom formulas (Marco Cecchetti - Collabora) tdf#96698"
Comment 12 Wolfgang Jäger 2020-03-12 11:51: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 respective control under the 'Cell range' path, but isn't put in force as long as it not got at least a fake edit.

(The feature also is not mentioned in the online helöp for 6.4)

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 13 Wolfgang Jäger 2020-03-12 11:59:10 UTC
(As a clarification to Wolfgang Jäger from comment #12)
> ...

Wanted to say:
> The formula entered under >Custom>Formula is passed to the respective
> control under the 'Cell range' path, but isn't put in force as long as it
> not got at least a fake edit IN THE 'Cell range' EDIT LINE.
Comment 14 Aron Budea 2020-03-12 12:35:40 UTC
Wolfgang, thanks for the comment, please open a new bug report with it, and reference this one in the 'See also' field.
Comment 15 Wolfgang Jäger 2020-03-12 13:15:56 UTC
(In reply to Aron Budea from comment #14)
> Wolfgang, thanks for the comment, please open a new bug report with it, and
> reference this one in the 'See also' field.

Isn't this unnecessarily "bureaucratic"? 
I well noticed that my hints concerning the missing functionality didn't fit exatly under the subject. There are many bug reports, however, basically matching their subject even less. 

Surley user "burnuser" didn't just want a fake feature, but a working one, and insofar the bug (enhancement) isn't resolved.

It would cost me another half hour probably to create an extra report, and there are already at least two more important ones I already deferred. (I'm not as fast as you, obviously.)

(I may surrender anyway.)
Comment 16 Buovjaga 2020-03-12 13:24:42 UTC
(In reply to Wolfgang Jäger from comment #15)
> (In reply to Aron Budea from comment #14)
> > Wolfgang, thanks for the comment, please open a new bug report with it, and
> > reference this one in the 'See also' field.
> 
> Isn't this unnecessarily "bureaucratic"? 

No. This is an enhancement report. The commit referenced in comment 6 adds something new. Thus, it is really fair to create a new report. Developers hate reports that turn into festivals with continued requests in dozens of comments.
Comment 17 Wolfgang Jäger 2020-03-12 16:50:28 UTC
(In reply to Buovjaga from comment #16)
...
> No. This is an enhancement report. The commit referenced in comment 6 adds
> something new. Thus, it is really fair to create a new report. Developers
> hate reports that turn into festivals with continued requests in dozens of
> comments.

See tdf#131309.
Comment 18 NISZ LibreOffice Team 2020-06-29 14:09:10 UTC
*** Bug 90324 has been marked as a duplicate of this bug. ***