If your app contains tables with a large number of rows, displayed by drop-down fields, these drop-downs may become unwieldy. To improve usability, enable users to filter the data so they see only relevant values.
A separate tip covers filtering by text. This tip focuses on filtering using switch fields, which work well with logical columns containing TRUE or FALSE values.
[Another tip][drop-downs-filter-indeterminate] explores the same problem, but solves it using text drop-down fields instead. These fields allow users to specify not only whether a value should be TRUE or FALSE in the underlying data table, but also whether it should be disregarded entirely.
Here’s an example app:
The text drop-down field at the bottom contains a list of products, which may be on sale, discontinued or featured. Had the list been larger, filtering would help manage the number of displayed items.
To filter products, check the Filter switch field. Three switch fields appear, along with an Allow indeterminate switch field — let’s ignore this for now and revisit it later.
The underlying data table
The next form screen, Products, contains this data table (edited using Calcapp Creator’s built-in data editor:
| Product Name | On Sale | Discontinued | Featured |
|---|---|---|---|
| Instant Pot | FALSE | FALSE | TRUE |
| Smart Speaker | TRUE | FALSE | FALSE |
| Wireless Earbuds | TRUE | TRUE | FALSE |
| Electric Mixer | FALSE | TRUE | FALSE |
| Smartwatch | TRUE | FALSE | TRUE |
| Bluetooth Speaker | FALSE | FALSE | FALSE |
| Gaming Console | TRUE | FALSE | TRUE |
| Digital Camera | FALSE | FALSE | TRUE |
| LED TV | FALSE | FALSE | FALSE |
| Game Controller | TRUE | FALSE | TRUE |
| Fitness Tracker | FALSE | FALSE | TRUE |
| Home Appliance | TRUE | FALSE | FALSE |
| Air Purifier | FALSE | TRUE | FALSE |
| Food Processor | FALSE | FALSE | TRUE |
| Electric Kettle | TRUE | FALSE | FALSE |
Filtering using switch fields
Initially, all three switch fields are off, meaning that only products that are not on sale, discontinued or featured appear in the drop-down field.
If you enable both the Discontinued and Featured switch fields, no products remain. That is consistent with the table — no rows contain both TRUE values in these columns.
To achieve this, associate the Values property of the products drop-down field with this formula:
If the Filter switch field is on, the drop-down come from the FilteredProducts named value. Otherwise, it contains all product names from the Products form screen (representing the data table).
(A named value is normally hidden and can any formula result. They help shorten formulas and improve readability.)
Here, we used a named value to represent the filtered values of the drop-down field. Here’s the formula of FilteredProducts:
FILTER takes an array and returns a version of it, potentially without certain elements (thereby filtering them out). The first parameter is the array to filter and the second is a logical array of the same size as the first array, where TRUE means the element at the same position in the first array is kept and FALSE means it is removed.
Let’s imagine that the second parameter only consists of this formula fragment:
It references the OnSale named value of the Products form screen, thereby representing whether a product is on sale in the data table. It uses the = operator with the OnSale switch field of the first screen.
What does that do? It produces a logical array where the only elements which are TRUE are the ones where the OnSale switch field has a value which is equal to the OnSale column of the data table. If FILTER was given that logical array as its second parameter, it would produce a filtered version of the product names containing only those products which were or were not on sale, depending on the value of the OnSale switch field.
Of course, this is the complete second parameter we give to FILTER:
Here, we’re using the && operator to mean “and,” implying that all three switch fields are used for the filtering operation. (If you’re used to Excel, you can also use the * operator.)
This approach has a limitation: what if users want to see all products that are on sale, but don’t care if they are featured or discontinued? We need to go beyond switch fields to realize this — refer to [this tip][to learn more].