Bug 146018 - Group similar autofilter submenu items to save the vertical space
Summary: Group similar autofilter submenu items to save the vertical space
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: low enhancement
Assignee: Caolán McNamara
URL:
Whiteboard: target:7.4.0
Keywords: needsUXEval
Depends on:
Blocks: AutoFilter
  Show dependency treegraph
 
Reported: 2021-12-03 03:05 UTC by Kevin Suo
Modified: 2021-12-10 19:53 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
autofilter screen on 1920*1080 display (128.00 KB, image/png)
2021-12-03 03:05 UTC, Kevin Suo
Details
autofilter screen on 1368*768 display (106.67 KB, image/png)
2021-12-03 03:06 UTC, Kevin Suo
Details
autofilter screen MSO2010 (129.90 KB, image/png)
2021-12-03 03:06 UTC, Kevin Suo
Details
excel using english UI (132.67 KB, image/png)
2021-12-03 08:51 UTC, Caolán McNamara
Details
google sheets example (89.59 KB, image/png)
2021-12-03 08:51 UTC, Caolán McNamara
Details
libreoffice current expanded view (109.65 KB, image/png)
2021-12-03 09:19 UTC, Caolán McNamara
Details
another possible look of the automatic filter (183.46 KB, image/jpeg)
2021-12-03 10:44 UTC, kabilo
Details
how a combined list would look in gtk (112.69 KB, image/png)
2021-12-07 11:42 UTC, Caolán McNamara
Details
demo showing scrolling (159.72 KB, image/png)
2021-12-08 09:02 UTC, Caolán McNamara
Details
Consolidated autofilter menu (16.25 KB, image/png)
2021-12-09 08:11 UTC, Heiko Tietze
Details
proposal (84.00 KB, image/png)
2021-12-09 09:56 UTC, Caolán McNamara
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Kevin Suo 2021-12-03 03:05:43 UTC
Created attachment 176662 [details]
autofilter screen on 1920*1080 display

Currently we have:

Sort Ascending
Sort Descending
Top 10
Empty
Not Empty
Text Color
Background Color
Standard Filter...
Reset Autofilter (appears when a filter is set)
<an input field>
<ALL checkbox>
<actual filter items>

This makes the autofilter popup vertically very long. Importantly, those items above the input box are actually less useful than the input field and the actual filter items below it.

I suggest we treak the UI to make it appear as the following (i.e. group the similar items, and the items within the group should show up upon mouse hover-over):
Sort
    Asending
    Desending
Value Is
    Top 10
    Empty
    Not Empty
    <more may be added here>
Color:
    Text Color
    Background Color
Reset AutoFilter (appears when a filter is set)
<an input field>
<ALL checkbox>
<actual filter items>

This way the use can focus on important items and the un-important items are shown only when hover-over. Also this will avoid the "dialog-too-tall" isses on low resolution displays, such as tdf#145901, tdf#130449 etc.

See comment in https://gerrit.libreoffice.org/c/core/+/126106.
Comment 1 Kevin Suo 2021-12-03 03:06:15 UTC
Created attachment 176663 [details]
autofilter screen on 1368*768 display
Comment 2 Kevin Suo 2021-12-03 03:06:51 UTC
Created attachment 176664 [details]
autofilter screen MSO2010
Comment 3 Kevin Suo 2021-12-03 03:09:49 UTC
The above screenshots were on Fedora 34 gtk3, Wayland.
Comment 4 Heiko Tietze 2021-12-03 08:06:22 UTC
Comparing gtk and win is not fair. But for the grouping I second your idea, at least for the filter items (to name it). It requires an additional "All Values" entry shown when closed. Sort is not really a function of the auto filter, we could remove it. Otherwise it's convenient and could be as suggested. In respect to the color items, we should consider the uncommon interaction (checkbox selection in a menu). Clicking through a deep menu structure adds annoyance here.

Eventually it would be: 

Sort | Ascending | Descending
All Values | Top 10 | Empty | Not Empty
Color | Text > | Background >
Standard Filter
Clear All Filter
[Search Items....]

Is this really a win?
Comment 5 Roman Kuznetsov 2021-12-03 08:29:28 UTC
(In reply to Heiko Tietze from comment #4)
> Eventually it would be: 
> 
> Sort | Ascending | Descending
> All Values | Top 10 | Empty | Not Empty
> Color | Text > | Background >
> Standard Filter
> Clear All Filter
> [Search Items....]

May be this variant will be better:

Sort Ascending
Sort Descending
All Values | Top 10 | Empty | Not Empty
Text Color (should be context depended, if there is any color different from Auto)
Background Color (should be context depended, if there is any color different from None)
Standard Filter
Clear Filter
[Search items...]


And let's set it to NEW
Comment 6 Caolán McNamara 2021-12-03 08:51:37 UTC
Created attachment 176667 [details]
excel using english UI
Comment 7 Caolán McNamara 2021-12-03 08:51:57 UTC
Created attachment 176668 [details]
google sheets example
Comment 8 Caolán McNamara 2021-12-03 09:19:53 UTC
Created attachment 176669 [details]
libreoffice current expanded view
Comment 9 kabilo 2021-12-03 10:44:30 UTC
Created attachment 176673 [details]
another possible look of the automatic filter
Comment 10 kabilo 2021-12-06 16:15:59 UTC
menu options can be unified to the 'Filter by color' and omit the graphic separator between the color filter and the standard filter also. This will shorten the filter menu by two lines. Visually it will be correct, the 'Clear Filter' option is used to cancel both the color filter and the standard filter.
Background colors and font colors in one menu are clearer (each person has a different opinion), it is not necessary to check both filters to see which is used.
Graphical identification of the applied filter (identifier (checkbox) before the text of the applied filter) would be nice (all see apendix).
Thanks
Comment 11 Caolán McNamara 2021-12-06 19:45:27 UTC
filter by foreground/textcolor and filter by background are mutually exclusive right? So they could go into a shared menu and no need for a three layer thing, which would make things easier.
Comment 12 kabilo 2021-12-07 10:31:57 UTC
yes, in similar applications only 1 color filter (text or background)can be used at a time (MSO, Google, OnlyOffice).
Comment 13 Caolán McNamara 2021-12-07 11:42:54 UTC
Created attachment 176757 [details]
how a combined list would look in gtk
Comment 14 Heiko Tietze 2021-12-07 14:39:54 UTC
What is the overflow behavior in case of many different colors? Guess the menu just scrolls...
Comment 15 Roman Kuznetsov 2021-12-07 17:09:05 UTC
(In reply to Caolán McNamara from comment #13)
> Created attachment 176757 [details]
> how a combined list would look in gtk

I still think color filtering item should be in Autofilter window only if any cell has any background color or non-Auto text color
Comment 16 Caolán McNamara 2021-12-08 09:02:33 UTC
Created attachment 176788 [details]
demo showing scrolling

(In reply to Heiko Tietze from comment #14)
> What is the overflow behavior in case of many different colors? Guess the
> menu just scrolls...

It would look like this, though it didn't when you asked :-)
Comment 17 kabilo 2021-12-08 09:56:19 UTC
I think the main menu should be fixed. The user may be confused that the required option is sometimes in a different position. The top 10 option could now be hidden in a similar way, because it doesn't make sense in the non-numeric data in the column (In reply to Roman Kuznetsov from comment #15)

If we do not want to go the way of similar applications, I would suggest this: 
Sorting - I've never used it here, but others have. In addition, it is possible to add color sorting. Delete or create a submenu. 
Quick filter -> top 10, bottom 10, empty, nonempty, error and possibly other options (Since the empty option is usually at the top of the auto filter list, the submenu probably wouldn't matter.)
Color filter - is already in the proposal 
Standard filter
conditional Clear filter
 
This adjustment would shorten the upper part of the menu to max 5 lines.
Comment 18 Commit Notification 2021-12-08 09:59:15 UTC
Caolán McNamara committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/8d3107f16d32fd0b582a0a5557691296791327f3

tdf#146018 merge autofilter color dropdowns into a single dropdown

It will be available in 7.4.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 19 Heiko Tietze 2021-12-09 08:11:25 UTC
Created attachment 176824 [details]
Consolidated autofilter menu

How about this?
Comment 20 Kevin Suo 2021-12-09 08:41:07 UTC
(In reply to Heiko Tietze from comment #19)

That's good, but:

1. Is the "separator" between "Sort" and "Quick Filter" really needed?

2. The name "Quick Filter" is misleading - I do not see how that can be quick. I think it should be named "Value Is", or "By Value". 

3. Also its submenu should be re-ordered. The "Top 10" is much less useful than the empty check ones. So it becomes:
Value Is
    Empty
    Not Empty
    Zero
    Not Zero
    Is Error
    Not Error
    Maximum
    Minimum
    Top 10
    Bottom 10

(The new ones I suggested are just FYI and may be implemented one by one at a later stage)

4. And the last point, we should increase the number of items in the 
actual filter item list. Current there are 8. I think previously there were 9 and Caolán has reduced that in recent commits (commit 75517c177). This is the most import information shown to the user in the auto filter and I think there at least should be 10 items.
Comment 21 Caolán McNamara 2021-12-09 09:56:13 UTC
Created attachment 176833 [details]
proposal

Like excel and sheets and current calc, keep a sort section and a filter section.
Call the color submenu "Filter by Color", call the other one "Filter by Condition" (the launch filter dialog "Standard Filter.." option) moves into that submenu. I haven't done anything to that submenu except move the existing entries into that, but there is obviously a lot of space to work with for extra entries in there now and/or reordering[1] it

(this also demos the followup up patch to restore back to 9 rows of checkboxes for the values)

[1] there are a number of uitests for the autofilter so it is reasonably expensive in time to fix those up to new locations FWIW
Comment 22 kabilo 2021-12-09 10:33:57 UTC
Both suggestions are excellent .
I would like a suggestion from Heiko Tietze. I do not use sorting in the filter and this solution is better for its extension by color sorting.
I would leave the 'Standard filter' menu in the main menu.
Filter by condition or Quick filter - I don't know which is better. So far, a Quick filter. The filter by condition would be better if the offer was expanded in the future as in MSO, Google, OnlyOffice (similar to the Standard filter only for a given column).
Decide

Quick because it is a predefined filter type (it is not necessary to select from the list of automatic filter or from the standard filter).

I prefer more list items as well.
Thanks
Comment 23 Samuel Mehrbrodt (allotropia) 2021-12-09 10:36:57 UTC
(In reply to Caolán McNamara from comment #21)
> Created attachment 176833 [details]
> proposal
> 
> Like excel and sheets and current calc, keep a sort section and a filter
> section.
> Call the color submenu "Filter by Color", call the other one "Filter by
> Condition" (the launch filter dialog "Standard Filter.." option) moves into
> that submenu. I haven't done anything to that submenu except move the
> existing entries into that, but there is obviously a lot of space to work
> with for extra entries in there now and/or reordering[1] it

Standard filter also supports filtering by colors, so maybe place it below "Filter by condition" (not in the submenu).
Comment 24 Kevin Suo 2021-12-09 10:42:00 UTC
(In reply to kabilo from comment #22)
> I do not use sorting in the filter and this solution is better for its extension by color sorting.

No, please do not remove the sorting in the autofilter. The sorting there is a quick way to sort a certain column. It is intuitive. Sorting using the Data > Sort menu or the toolbar requires much more mouse-clicks and also requires more thinking to identify the columns to sort.
Comment 25 Kevin Suo 2021-12-09 11:02:29 UTC
(In reply to Caolán McNamara from comment #21)
I like your proposal. It looks good.
And I also like the Standard Filter to go to the Filter By Condition submenu. The key to the autofilter popup is "clear" and "quick" - the rows in the first level popup menu, the better. Standard Filter should be a submenu because it is by nature a more complex filter. People using autofilter normally set the conditions one column by column. If he/she want to use the standard filter directly, then he will simply use the Data > More Filers menu, or the Standard Filter in the (proposed) autofilter submenu.

Below is a simple use case showing how spreadsheet experts uses autofilter (take attachment 176404 [details] for example):

1. I want to verify that the MaterialNumbers are valid. So, I click on filter in column S, find that there are blanks. I filter those blanks, thinking while they are blank. Then I find that they are blank may be because the QTYs (column P) are zero.

2. Then I click the filter in column P, to verify that my guess was correct. Then I find that not all QTYs in column P are zeros. So in addition to the filter condition already set, I set filter the zeros in column P, and then filter the non-zeros, trying to find a clue. Nothing special found.

3. Then I clear the filter condition in column P, but keep the filter condition in column S, and then continue my research. Then I noticed that the empty MaterialNumbers may be due to the "GL_Account". So I filter column A, notice that there are only two General Ledger Accounts which have blank material numbers. I got it!

4. Then I want to know more about my data, so I clear all the autofilter conditions, and continue researching.

As shown in the above workflow, people working with autofilter are doing there research on a "experimental" basis. They do not know what they want exactly at the first time. After they know what they want and they have done enough evaluation of their data, then they may use the Standard Filter (or simply setting autofilter conditions one column by one column) to set more complex conditions to get what they need.
Comment 26 Kevin Suo 2021-12-09 11:12:29 UTC
And I want to add that, based on the workflow I mentioned above, during the "experimental" researching process, the most important information in the autofilter dropdown is the data entry items. Other rows would be "disturb" the user's thinking except they are really needed. This is also why I reported this bug.

I am an accountant expert, have been using spreadsheets for 16 years.
Comment 27 kabilo 2021-12-09 11:46:00 UTC
(In reply to Kevin Suo from comment #24)
> No, please do not remove the sorting in the autofilter. 

I did not mean to remove, but to create a submenu with a menu
sort ascending
sort descending
sort by background color
sort by font color
Comment 28 Caolán McNamara 2021-12-10 09:55:01 UTC
we don't have full consensus here, but I think what I have so far makes better use of space without becoming too alien vs similar software so I'll go with what I have and if there's violent disagreement I won't be offended if someone else wants to assign themselves a follow up bug and do more rearrangement.
Comment 29 Commit Notification 2021-12-10 09:55:53 UTC
Caolán McNamara committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/e2d498b778a3dac70a7faee4d1bbabb50cdcc103

tdf#146018 group filtering options under a single dropdown

It will be available in 7.4.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 30 Commit Notification 2021-12-10 09:57:07 UTC
Caolán McNamara committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/d8aae8057d76743cfc1591cbffda2f54c338a213

Related: tdf#146018 move Top10 below empty/not-empty add add Bottom10

It will be available in 7.4.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 31 Caolán McNamara 2021-12-10 10:07:47 UTC
sc/source/ui/cctrl/checklistmenu.cxx has

// number of rows visible in checklist
constexpr int nCheckListVisibleRows = 9;

// number of rows visible in colorlist
constexpr int nColorListVisibleRows = 9;

if anyone wants to tweak those further
Comment 32 Commit Notification 2021-12-10 14:11:47 UTC
Caolán McNamara committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/a25a573d5714ab196bbcdbd3a8dcb6d6d7814882

Related: tdf#146018 allow user to grow/shrink autofilter checklist by a row

It will be available in 7.4.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 33 Kevin Suo 2021-12-10 14:50:15 UTC
There is a minor issue here:
If the column is too close to the right side of the screen, the Filter By Color and Filter By Condition popup will be half-invisible. I think it should be smarter to switch to the left side if no enough space is left on the screen.

To reproduce, use attachment 176404 [details] and click the autofilter located on the most right side of your screen and hover-over the Filter By Color or Filter By Condition.

Caolán: I think you may also fix this minor issue in together, so that we do not need to open a new report?
Comment 34 Caolán McNamara 2021-12-10 15:48:53 UTC
I'll set that submenu to be constrained within the window (like the topmenu is) which should avoid that (gtk+wayland) problem.
Comment 35 Commit Notification 2021-12-10 19:53:37 UTC
Caolán McNamara committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/a298718eb807c1857cb488a2915126de331ccafa

Related: tdf#146018 constrain submenu to window like toplevel menu

It will be available in 7.4.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.