Beyond Basic Filters: Excel-Style Grid Multi-Selects

Simple Grid Multi-Selects

Simple grid multi-selects operate independently, meaning each filter shows available data from the unfiltered full set of data. For example, in a table listing products with categories and subcategories, applying a filter on the category "Electronics" will only show rows for products in the "Electronics" category. However, the multi-select filter for the subcategory column remains unaffected and will still display subcategories from other categories.

Excel-Style Filter Behavior

As demonstrated in previous articles, Microsoft Excel has it's own quirks with its multi-select filters. When a filter is applied to one column, the available options in the multi-select filter of another column are updated to reflect the remaining data. For example, if a category filter is applied, the subcategory filter will only display subcategories available within the filtered category.

Implementing Excel-Style Filters

To implement Excel-style filter behavior, we cannot just use the list of visible items to generate the filter values. Even if the category "Electronics" is the only one available after applying the filter, the multi-select should still show all other non-selected categories. The solution is to have each filter receive a list of remaining items after all other column filters have been applied. This approach ensures that the filters display the items that could be available if the current column filter was removed.

Every filter needs to apply all other filters on the base set of items to generate the list of available values. As a result, each filter component must either be aware of the other filters or receive an individually generated list of items from a global controller that has access to all data and filters.

Retrofitting Excel-Style Behavior

Our grid component already had a controller at its core, making it relatively easy to retrofit this behavior onto our previously independent filters. One key decision was whether to preemptively (eagerly) calculate the resulting set of values for each filter whenever any filter changes or to perform the calculation when opening the filter for a specific column. Given the potential for large datasets, we anticipated that eager calculation would noticeably slow down the application when setting a new filter. Therefore, we opted to calculate the list of available values on-demand.

User Preferences

Our users were accustomed to filters always showing all available data, not just the data remaining after other filters were applied. To accommodate this, we added a global setting allowing users to choose between classic filters and Excel-style filters.

Conclusion

Excel-style grid multi-selects offer a more dynamic and context-aware filtering experience compared to simple grid multi-selects. By understanding and implementing this behavior, we can provide users with a more intuitive and efficient way to filter data in grid components. Whether users prefer classic or Excel-style filters, having the option to choose enhances the overall usability of the application.