Bug Hunting Session
Bug 46062 - Autofilter should not tick "all" if each individual value is selected (blanks should be omitted)
Summary: Autofilter should not tick "all" if each individual value is selected (blanks...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.5.0 release
Hardware: All All
: low minor
Assignee: Not Assigned
URL:
Whiteboard: BSA
Keywords:
Depends on:
Blocks: AutoFilter
  Show dependency treegraph
 
Reported: 2012-02-14 12:57 UTC by wilkusz
Modified: 2019-10-31 16:28 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
bug.ods (7.54 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-02-15 14:31 UTC, wilkusz
Details
Testcase demonstrating the problem (8.36 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-07-23 21:29 UTC, Mark Bourne
Details
testfile for the 'spaces' issue (9.30 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-10-27 12:54 UTC, Rob
Details
screenshot of remaining issue (50.21 KB, image/png)
2017-10-27 12:56 UTC, Rob
Details

Note You need to log in before you can comment on or make changes to this bug.
Description wilkusz 2012-02-14 12:57:39 UTC
I have problem with autofiltr in version 3,5 don't work when only one option was evailable and in autofiltr print page was bad display...
              
Browser: Mozilla/5.0 (Windows NT 5.1; rv:10.0.1) Gecko/20100101 Firefox/10.0.1
Comment 1 Markus Mohrhard 2012-02-14 18:22:26 UTC
Created attachment 57122 [details]
bug.ods

Can you add some more details?

What happens? What does you expect?

Maybe even adding a test document and a detailed step by step description for us how to reproduce it?
Comment 2 wilkusz 2012-02-15 14:31:26 UTC
Witam

Please open a bug file and select john in auto filtr. If You select
john nothing change in file and display all data.

Pozdrawiam

S. Wilk


W dniu 15 lutego 2012 03:22 użytkownik
<bugzilla-daemon@freedesktop.org> napisał:

> https://bugs.freedesktop.org/show_bug.cgi?id=46062
>
> --- Comment #1 from Markus Mohrhard <markus.mohrhard@googlemail.com> 2012-02-14 18:22:26 PST ---
> Can you add some more details?
>
> What happens? What does you expect?
>
> Maybe even adding a test document and a detailed step by step description for us how to reproduce it?
>
> --
> Configure bugmail: https://bugs.freedesktop.org/userprefs.cgi?tab=email
> ------- You are receiving this mail because: -------
> You reported the bug.
Comment 3 Jorendc 2014-07-04 18:25:30 UTC
I fail to reproduce this issue, tested using Mac OSX 10.9 with LibreOffice Version: 4.2.5.2
Build ID: 61cb170a04bb1f12e77c884eab9192be736ec5f5

@S.Wilk: please attach a sample document if you still can reproduce this behavior, using a more recent version of LibreOffice. Also provide some steps how to reproduce that behavior.

Thanks.

Kind regards,
Joren
Comment 4 Urmas 2014-07-04 21:45:44 UTC
Confirmed in master.

If a column contains only X and empty cells, empty cells cannot be hidden while filtering for X.
However, if there are X, Y and empty cells, either X or Y will hide them.
Comment 5 Mark Bourne 2014-07-23 21:29:52 UTC
Created attachment 103367 [details]
Testcase demonstrating the problem

This has recently come up on the users mailing list:
  http://listarchives.libreoffice.org/global/users/msg39810.html

For some reason my reply there doesn't show up in the archive, but I've worked up the attached example based on that.

Using the filters in column B ("2 values + blanks"), which has more than one unique value in addition to some empty cells:
- Filter on "aaa" shows only rows containing "aaa" (not "bbb" or empty)
- Filter on "bbb" shows only rows containing "bbb" (not "aaa" or empty)
- Filter on Empty shows only rows with empty cell (not "aaa" or "bbb")
- Filter on All shows all rows ("aaa", "bbb" and empty)
- Attempting to filter for "aaa" or "bbb" automatically selects All, and includes the empty cells.

Using the filters in column C ("1 value + blanks"), which has only one unique value in addition to some empty cells:
- Filter on Empty shows only rows with empty cell (not "aaa")
- Filter on All shows all rows ("aaa" and empty)
- Attempting to filter for "aaa" automatically selects All, and includes the empty cells.

I've checked this on Windows using LO 4.0.4, which is admittedly quite old, but it looks like others are experiencing similar on more recent versions.

The way the tick boxes work is a bit confusing in this regard, but it is possible to work around this by selecting "not empty" from the filters. It is also possible to choose "Standard Filter..." from the dropdown rather than using the tick boxes, and from there explicitly filter e.g. on "aaa" in column C ("1 value + blanks").

It appears that the tick boxes are intended as a quick was to set up basic filters, and don't accurately reflect more complex filters, but that's not particularly clear. Although the tick boxes don't look right after selecting "Not Empty", clicking "Standard Filter..." does show that the filter is currently set to show Not Empty values.

Perhaps the tick boxes should all be greyed out if they can't accurately reflect the current filter? Even better, if (Empty) could be made one of the tick box options (without confusion with a possible value of "Empty" in a cell), it would be possible to select e.g. "aaa" and "bbb" without (Empty), or even "aaa" and (Empty) without "bbb".
Comment 6 Rob 2014-07-23 22:03:27 UTC
For comparison:
In Excel there is a tickbox marked '(blanks)' which filters values consisting solely of one or more spaces and empty rows.

Adding this also solves bug [Bug 81577] FILTER: Can't switch a filter of if only one value except NULL is deleted.
Comment 7 Owen Genat (retired) 2014-07-26 01:52:30 UTC
Summary edited for clarity.
Comment 8 Joel Madero 2014-07-28 16:09:19 UTC
Setting to UNCONFIRMED.

Note: REOPENED is for a bug that a developer says they fixed (thus the bug is put into RESOLVED -> FIXED) but a user can still see the problem on a version that should be fixed. 

For bugs never confirmed by a neutral third party the correct status is UNCONFIRMED. Thanks!
Comment 9 Joel Madero 2014-07-28 19:43:21 UTC
My test system:
Bodhi 2 (built on Ubuntu 12.04) x64
LibreOffice 4.2.6 rc

Confirmed (see notes below)
New - confirmed
Minor - can slow down professional quality work but will not prevent it
Low - the workaround is pretty straight forward (select not empty)


> Using the filters in column B ("2 values + blanks"), which has more than one
> unique value in addition to some empty cells:
> - Filter on "aaa" shows only rows containing "aaa" (not "bbb" or empty)
> - Filter on "bbb" shows only rows containing "bbb" (not "aaa" or empty)
> - Filter on Empty shows only rows with empty cell (not "aaa" or "bbb")
> - Filter on All shows all rows ("aaa", "bbb" and empty)
> - Attempting to filter for "aaa" or "bbb" automatically selects All, and
> includes the empty cells.
Assuming the last is "attempt to filter for "aaa" AND "bbb" not or (or works fine if you select just one as that's just step 1 and 2 above)

> 
> Perhaps the tick boxes should all be greyed out if they can't accurately
> reflect the current filter? Even better, if (Empty) could be made one of the
> tick box options (without confusion with a possible value of "Empty" in a
> cell), it would be possible to select e.g. "aaa" and "bbb" without (Empty),
> or even "aaa" and (Empty) without "bbb".

Not a fan of graying out anything, you do it once or twice and you learn how to workaround it. It's a pretty small issue but an issue none the less.

Changing title to better reflect the issue as this isn't dependent on only one category existing, it's if you select all categories available (whether that is one or ten) then "all" is automatically ticked.
Comment 10 Rob 2014-07-28 22:54:37 UTC
This bug is related to (but not the same as): "[Bug 81577] - FILTER: Can't switch a filter of if only one value except NULL".

I agree with you that graying out is not a very elegant solution. I repeat the suggested solution from [Bug 81577] since that solves both bugs in one go:

---
1- Add 'Empty' to the ticklist, and remove it from the text selection
2- Make clear 'not empty' has been selected (a tick after the text will do)
3- Make 'not empty' and 'All' mark appropriate list boxes
4- Mark 'not empty' if all boxes except 'Empty' are marked

FYI: Excel marks that are blank, and fields solely consisting of spaces as "(blanks)". Excel in fact trims all fields, so for the tick boxes 'a' is the same as ' a '.
---
Comment 11 QA Administrators 2015-12-20 16:09:46 UTC Comment hidden (obsolete)
Comment 12 Kevin Suo 2016-10-26 09:07:21 UTC
Set as WORKSFORME as I do not reproduce with the latest version.
Please set back to NEW if you do reproduce. Thanks.
Comment 13 Rob 2016-10-26 10:08:03 UTC
I retested LO 5.2.0.4, on OS X- 

Things are improved. Solution is close but not completely OK.

I created a test file with in col A the following:
---
Heading
(empty)
bbb
 (1 space)
ccc
     (4 spaces)
ddd
---

Then I ceate an autofilter.

Findings:
1- When I select 'not empty' the sheet shows all but row 2 (which is empty).
The tickboxes however only show (empty) ticked. This is wrong.

2- In the ticklist there are 2 ticks showing exactly the same (for row 4 [1 space] and row 6 [4spaces]). This is not very clear. 

MS trims the values, so <spaces> become the same as <empty>. This wou;d solve the problem (at the expense of not being able to file on a string of a specified amount of spaces).

Alternatively, the sting of spaces could be made viible by surrounding quotes in the ticklist.
Comment 14 QA Administrators 2017-10-27 08:49:16 UTC Comment hidden (obsolete)
Comment 15 Rob 2017-10-27 12:54:21 UTC
Created attachment 137320 [details]
testfile for the 'spaces' issue
Comment 16 Rob 2017-10-27 12:56:41 UTC
Created attachment 137321 [details]
screenshot of remaining issue

As cann be seen is this screenshot, there is no distinction between fields with 1 space and fields with more spaces (in this case 4 spaces.
Comment 17 Rob 2017-10-27 12:59:23 UTC
As can be seen from the uploaded files the selection issues are resolved, but the discrimination between fiels containing a various amount of spaces is not.
Comment 18 Rob 2017-10-27 13:04:11 UTC
Sorry, forgot environment:

Version: 5.4.0.3
Build ID: 7556cbc6811c9d992f4064ab9287069087d7f62c
CPU threads: 4; OS: Mac OS X 10.12.6; UI render: default; 
Locale: en-US (en_NL.UTF-8); Calc: group
Comment 19 QA Administrators 2018-10-28 03:58:06 UTC Comment hidden (obsolete)
Comment 20 Rob 2018-10-29 13:30:57 UTC
LO: Version: 6.0.6.2
Build ID: 0c292870b25a325b5ed35f6b45599d2ea4458e77
CPU threads: 4; OS: Mac OS X 10.13.6; UI render: default; 
Locale: nl-NL (en_NL.UTF-8); Calc: group

Re-tested- Last described problem about 'impossible to destinguish between cells with unequal amount of spaces is still a problem.
The attachment "screenshot of remaining issue" shows the problem. The same is true if a cell contains aaa and another "aaa  ".

Apparently we don't want to go the MS Excel way of trimming all cell contents (in that case empty includes 'spaces')

To slove this we either need to an indication on the length of the string (eg light grey square or a very thin line box around the string) or we need quotes around the string.
Comment 21 QA Administrators 2019-10-30 03:36:41 UTC Comment hidden (obsolete)
Comment 22 Rob 2019-10-31 16:28:51 UTC
Re-tested:

environment:
Version: 6.3.3.2
Build ID: a64200df03143b798afd1ec74a12ab50359878ed
CPU-threads: 4; Besturingssysteem: Mac OS X 10.15; UI-render: standaard; VCL: osx; 
Locale: nl-NL (nl_NL.UTF-8); UI-taal: nl-NL
Calc: threaded

Problems still not resolved:

Using the original uploaded file from Mark Bourne (attachment 103367 [details]):

1- 
In AutoFilter column B select 'Not empty'
Only box 'Empty' is autmatically ticked. This is wrong. It ahould be the only box NOT ticked, while all other boxes (in thes case aaa and bbb) should be ticked.

Using testfile uploaded in attachment 137320 [details]:

The problemn described in comment 20 still exists.
The weird thing is that it should be real easy to resolve:
either:
- Indicate the string length by putting quates around the whole string, e.g. "    "    ", "aaa ", "aaa   ","  aaa  ".
- In indicate leading and trailing spaces with a grey block