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:
: 102273 (view as bug list)
Depends on:
Blocks: Cell-Validity
  Show dependency treegraph
 
Reported: 2015-12-23 18:43 UTC by burnuser
Modified: 2018-11-19 04:50 UTC (History)
6 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 burnuser 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 burnuser 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 burnuser 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"