Download it now!
Bug 58429 - "Contains" Conditional Formatting w/ Text Incorrect Behavior
Summary: "Contains" Conditional Formatting w/ Text Incorrect Behavior
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.0.0.0.beta1
Hardware: All All
: high minor
Assignee: Markus Mohrhard
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2012-12-17 19:41 UTC by Joel Madero
Modified: 2015-04-06 23:05 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Attachments
Displays Behavior (8.85 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-12-17 19:41 UTC, Joel Madero
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Joel Madero 2012-12-17 19:41:04 UTC
Created attachment 71686 [details]
Displays Behavior

LibO: Version 4.1.0.0.alpha0+ verified on 4.0 Beta1

The new "contains" feature for conditional formatting doesn't behave as expected unless you add quotes. If you add a conditional format without the quotes, no cells are triggers as "meeting criteria" which is incorrect, but if you add quotes around the text the behavior is correct.

Another point is that if you go back in to edit your conditional format you can see that the previously entered word without quotes now have single apostorophe's ('WORD'), this should probably be "WORD" and the problem would be resolved.

Example document, column A shows conditional formatting without quotes behaving incorrectly, column B shows with quotes and working as expected.
Comment 1 Markus Mohrhard 2012-12-17 22:03:56 UTC
So this one is quite tricky. I have to think a bit about it.

The problem is formula input vs string input. How to decide when the input is a string and when it is a formula.
Comment 2 Markus Mohrhard 2012-12-17 22:05:41 UTC
Moving to normal as this is not as bad as original thought. To some extend the current behavior is consistent with the other conditional formats.

I need to check some corner cases and how they are handled.
Comment 3 Joel Madero 2012-12-17 22:06:13 UTC
I'd argue that text is more likely with "contains" than numbers, so if there is no check, it should default to "" instead of ' ', but really, a check should probably be implemented somehow.

Another option is having "contains (text)" and "Contains (number"

That's a workaround at least temporarily perhaps
Comment 4 Markus Mohrhard 2012-12-17 22:20:42 UTC
(In reply to comment #3)
> I'd argue that text is more likely with "contains" than numbers, so if there
> is no check, it should default to "" instead of ' ', but really, a check
> should probably be implemented somehow.

It is not that easy. It is not number vs. text. It is that we can input formulas there too. Everywhere we can input formulas you need to escape strings with quotes. This is also true for all other conditional formats.

> 
> Another option is having "contains (text)" and "Contains (number"
> 
> That's a workaround at least temporarily perhaps

That is not an option.
Comment 5 Juanjo 2012-12-17 23:30:36 UTC
Hi,

Misleading behaviour also raises when "equal to" or "not equal to" operands are used.

OS: Windows 7
Comment 6 Markus Mohrhard 2012-12-17 23:38:10 UTC
(In reply to comment #5)
> Hi,
> 
> Misleading behaviour also raises when "equal to" or "not equal to" operands
> are used.
> 
> OS: Windows 7

This is not misleading behavior! This is absolutely correct for equal. It is questionable if it is the best behavior for contains.

Please note that this input field also accepts formulas and ranges which means that we have to escape strings with quotes as in all the other formula places.
Comment 7 Joel Madero 2012-12-17 23:40:28 UTC
my bad for opening the door on this one ;) Thanks for clarifying about what's going on with this, good luck trying to find a solution :) I do hope that we don't face having to explain to users why their conditional formatting isn't working with contains (because they fail to put in " "). Thanks again Markus, I'll be discussing other CF issues with you in the coming days
Comment 8 Markus Mohrhard 2012-12-19 20:00:43 UTC
Ok after looking at Excel I think I'll rename them to: Contains Text, Not Contains Text, Begins with Text, Ends with Text and don't try to interpret the input as a formula.
Comment 9 Joel Madero 2012-12-19 21:14:43 UTC
Thanks for the update :)
Comment 10 A (Andy) 2014-10-18 20:34:18 UTC
Reproducible with LO 4.3.2.2 (Win 8.1)
Comment 11 Markus Mohrhard 2015-04-06 23:05:02 UTC
Fixed in master by showinga  warning when the string is interpreted as a column label. Hopefully we can even disable column labels by default for LibreOffice 5.0.