Excel Style Filtering

Excel Style Filtering Specification

1. Revision History

Version	User	Date	Notes
0.1	Danail Marinov	Jan 10, 2019	Initial draft
0.2	Danail Marinov	Jan 18, 2019	Update
0.3	Danail Marinov	Jan 22, 2019	Update
0.4	Danail Marinov	Jan 25, 2019	Update - Column moving
0.5	Danail Marinov	Jan 30, 2019	Keyboard interaction
0.6	Danail Marinov	Mar 20, 2019	Display density, Design and UX changes
0.7	Danail Marinov	Mar 26, 2019	Display density, Design and UX changes-update
0.8	Danail Marinov	Apr 24, 2019	No items found screen
0.9	Danail Marinov	July 24, 2019	Move Column and Pinning Behavior within ESF dialog
1.0	Tacho Zhelev	August 6, 2019	Developer user stories
1.1	Dimitar Dimitrov	July 31, 2020	Developer user stories
1.2	Hristo Anastasov	October 26, 2020	Developer user stories
1.3	Martin Dragnev	November 2, 2023	Add search list keyboard navigation and aria support
1.4	Ivaylo Barakov	November 8, 2023	Update aria support
1.5	Martin Dragnev	November 14, 2023	Update handling for ArrowUp/ArrowDown when the current focused element is first/last
1.6	Georgi Anastasov	July 11, 2024	Add Text filter behavior

Stefan Ivanov | Date:
Simeon Simeonoff | Date:
Konstantin Dinev | Date:

2. Overview

The Excel Style Filtering allow users to execute filtering based on the experience of the Excel style filtering plus some general UX improvements. The Excel style filtering reads and modifies the collection of igxGrid filtering conditions, and reacts to changes in the filtering conditions. Each filter reacts when filtering condition/s has been set externally for its corresponding column. The Excel style filtering exposes to the end user a subset of filtering operations that can be applied to the whole grid following the experience that Excel user got used to.

3. User Stories

As a developer, I want to:

load unique column values on demand (remotely).
provide custom templates for column operations and filter operations inside the dialog and include some or all of the ESF UI components
be able to host the Excel style filtering dialog outside of the grid.

As an end user, I want to:

filter the data (simple filter - contains).
filter data on one or more data containers.
choose the filtering condition.
put multiple filtering conditions in a single column.
have overall experience very similar to Excel filtering.
be able to perform filtering by using one of the unique values in the column (similar to Excel Filtering).
be able to drag the dialogue window containing the ESF UI components.
be able to search in all unique values within a column.
be able to perform ascending and descending sorting on a column for filtered and unfiltered values.
be able to move columns within the grid.
be able to hide columns from the grid.

4. End User Experience

Initially, there are no filters applied on the grid columns. As it is shown on the design example below, there are several types of columns(not all combinations are covered in the example): filterable and sortable, not filterable and not sortable and just sortable. Hiding/unhinding column combobox is placed in the toolbar.

The Excel style filtering is opened by selecting the filter action icon which triggers dialog window or with ENTER (after having focus on the ESF action icon). There is indication on the header cell showing that filtering is applied on it plus the name of the column on the header of the dialog. In the dialog window are placed UI controls for:

sorting with predefined Ascending sorting
pinning column
hiding column
option for adding custom filter base on the type of column (numeric, text etc)
inactive search bar plus list of all column values for quick filtering (by selecting them)
Cancel and Apply buttons Moving column, pinning, hiding depend on whether the feature is enabled.

Initially, the dialog window is placed in the centre of the viewport. The column values are all selected. The dialog window is draggable along the grid area. The "drag" icon (in the dialog header) indicates that the dialog window is draggable but it is not an action icon because dragging can be performed at on-click everywhere on the dialog header. Sorting action in the header is also accessible despite that dialog box is opened. Sorting can be performed also from the dialog UI. Compared to Filtering Row, Excel-style filtering is set to defer which means that the filtering is NOT live by default, so changes will be applied after confirmation by the user. Also, I, as an end user, can apply custom filter with predefined condition (similar to Excel UX). As to the keyboard interaction - by using TAB button, I, as an end user, can move focus on each button one after the other. Initially after opening the dialog, the focus is on the Ascending sorting button, so if clicking TAB will move it Descending sorting button. If Sorting action is triggered, it will be executed immediately without closing the dialog. Focusing on the Pin column button and then clicking ENTER will pin the column immediately and will close the dialog, because Pinning column may change column header position. Hiding column would immediately hide the column and close the dialog. Focusing on Text Filter option (the filter type depends on the data type) and the clicking ENTER, will open the menu with filter conditions. I, as an end user, can put focus on each of them using TAB and can trigger one of them by confirming with ENTER. Then, this dialog will be closed and the Custom Auto-filter will open.

Note: From that ESF dropdown can be applied column hiding, but obviously unhiding can be performed only from the combobox in the toolbar.

If using keyboard in the ESF, the search field will immediately take focus where if nothing is typed, the selection will include all records and if the value is not empty all matching list items will appear as selected. Upon confirming with enter the ESF dialog will close and filter condition applied.

If there is already a filter condition through selected items from the list and a filter criterion is typed in the Search field, then an additional option appears to add the existing selection to the currently filtered items.

If using Tab navigation when the search field is focused then the first item of the list with all records will be focused. There the following keys can be used to navigate around:

Key	Action
Tab	Focus `CANCEL` button
Shift + Tab	Focus search field
ArrowUp	Focus the previous record inside the list. If the current focused element is the first one, the focus stays on the same element
ArrowDown	Focus the next record inside the list. If the current focused element is the last one, the focus stays on the same element
Home	Focus the first record in the list
End	Focus the last record in the list
Space	Select or Deselect the current focused record

Note: As per the listbox pattern if the first/last element is focused and ArrowUp/ArrowDown is pressed then the focus stays on the same element.

Selection of Adding custom filter closes the initial dialog and opens another dialog window where the user can define custom filters. The first filter condition is predefined based on the previous user's selection and the focus is put on the value input. If I, as an end user, want to change the previously chosen condition, I can do it with SHIFT+TAB which will move the focus to the condition selector and triggering the dropdown can be done with ENTER. Filters can be fully edited and removed, as well as new filters input can be added, but minimum one filter will remain available (otherwise is pointless to have Custom auto-filter dialogue). The predefined value of the And/Or selector depends on the type of condition selected in the previous step.

The Custom AutoFilter contains inputs where I, as an end user, can define condition, type value or select (using the input dropdown) from the column values. In this dialog can be added more filters and removed the existing ones. If, I as an end user, add more filters, every new filter consists of two inputs - one for condition and another for specific value, as well as And/Or selector. After typing at least one symbol, the Clear filter button goes from disabled to inactive. Using keyboard navigation I, as an end user, can move to the value input (to start typing manually) or trigger the dropdown with this column unique values. After confirming my selection with ENTER, the focus moves to the Remove condition action icon. When it is focused, I, as an end user, can confirm removing the condition with ENTER which will remove also the And/Or selector attached to the condition input. Using the keyboard navigation, I, as an end user, can navigate to Add filter button, then Clear filter, Cancel and Apply.

After defining filter condition and value, all the grid data stays unfiltered until user's confirmation.

As an end user, I can add more than two filters. The size of the dialog does not change, a scroll appears instead.

When the dialog is being closed, the filter icon on which filtering has been applied changes its color to Secondary.500 indicating that filters are applied. The grid data got filtered.

The list of unique values may include also "Blank cells". On the design below is shown also example when the dropdown's initial position is changed.

Column moving can be applied on opened dropdown by dragging the columns headers. If so, when clicking out of the dialog, it closes and the user rearrange column headers. Column moving can also be applied from the Move left/right buttons in the dropdown. If so, when clicking on the button Move left/right, the column headers moves, but the very dialog stays at its initial position. If the column that the user wants to move is the last one on left/right, then the button Move left/right is disabled.

If I, as an end user, apply filter(s), then close the dialog and open it again with intention to edit filtering, a Clear column filters button is added to the ESF dialog which has the following design:

Display density Zeplin file: zpl://project?pid=5ce53acf5f66941e1fafe414 https://zpl.io/V0LY9Xm

Comfortable

Cozy

Compact

No results found when searching in the ESF list:

Move Column and Pin Column in the context of the ESF

If I, as an end user, use Move Column Left action from the ESF dialog for a column that is the first not pinned column, then this column moves over the pinning line. In addition, it becomes the last column in the Pinned area (the grid area that includes all pinned columns). In this way I, as an end user, can perform Pin column action through Move Column action and reverse.

After applying Pin column for some of the grid columns, I, as an end user, may reach the maximum width of the Pinned area (the grid area that includes all pinned columns). The total width of all the pinned columns cannot exceed the grid width. Note that maximum width of the Pinned area is not determined by the number of pinned column, it is determined only by the total width of the pinned columns. If I, as an end user, reach the maximum of the Pinned area, then the Move Column Left button action from the ESF dialog goes disabled. If I, as an end user, reach the maximum width of the Pinned area, I cannot pin more columns and the Pin Column button within the ESF dialog goes disabled.

In order to be able to Move a column to the left or Pin it, I, as an end user, first have to Unpin some of the pinned column to reduce the Pinned area width.

Text Filter Behavior

When selecting items in the filter:

When one list item is selected, the Text filter is displayed as Text filter (1) and "Equal" is selected from the filters.
When two list items are selected, the Text filter is displayed as Text filter (2) and the "Custom filter" is selected.
For three or more items, the condition changes to 'IN' and the Text filter does not display the number of conditions, as 'IN' is treated as a single condition.

text filter

Values formatting

Values in the ESF list should be formatted in the same way as values in the column itself, according to the grid locale property and column pipeArgs property values, or according to the column formatter. If formatter is available, it takes precedence. If the formatter formats the blank values too, the label for blank values in the ESF should be formatted accordingly.

Acceptance criteria

5. ARIA Support

The list below provides details about what changes have been made to the Excel Style Filtering to support WAI-ARIA. Please note that no special settings are needed to leverage these changes, as they are all enabled by default.

tabindex attribute allows access to the elements through the use of TAB and SHIFT + TAB keys.
The list containing search values is decorated with a listbox role.
aria-activedescendant attribute is set to the focusable list which value is the id of the currently focused list item. Format: ESFID-item-recordIndex.
The list items containing information about each search value are decorated with a option role.

Additionally, a hidden container with the following attributes is included to show the number of matches in the list.

aria-live="polite" informs assistive technologies that the content may be dynamically updated and should be announced to the user without interrupting the user's current activity.
aria-atomic="true" ensures that when changes happen, assistive technologies should announce the entire content of the element rather than incremental modifications.

6. Assumptions and Limitations

Assumptions	Limitation Notes

7. Test Scenarios

Automation

Clicking 'Ascending' - sorts the grid properly, clicking 'Descending' - sorts the grid correctly and deselects 'Descending' button.
After opening filter dialog on a sorted column, the correct button should be toggled.
Clicking 'Move left/right' should correctly move the column.
Clicking 'Pin/Unpin' column should correctly pin/unpin the column.
Clicking 'Hide column' should hide the column.
After entering value in first input, the grid shouldn't be filtered, 'And' operator should be selected and clear button should become inactive.
If two values with And operator are entered, there shouldn’t be a selection in the list.
If two values with Or operator are entered and one of them has operator different from ‘equals’, there shouldn’t be a selection in the list.
If two values with Or operator are entered and they are in the list below, they should be selected.
Changing And/Or operator should reflect in the selection of the list.
Changing operator should reflect in the selection of the list.
If three or more values are selected and we enter a new one in the input, the selection should be cleared and the grid should be filtered.
Deselecting all values and then selecting two should populate the inputs.
Selecting ‘all filters’ item should clear the filter.
When dialog is closed and the filter has been changed the filter icon should be updated.
Selecting 'Contains', 'Does not contain', etc, from the menu should open another filter dialog and populates the correct operator.