If your app contains tables with many 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.
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.)
Adding an intermediate state
The current 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?
Switch fields don’t have an “indeterminate state”. Instead, we’ll use three text drop-down fields to allow users to select between Yes, No and nothing (representing the indeterminate state).
When Allow indeterminate is enabled, the three switch fields are replaced by three text drop-down fields. If all three drop-downs are blank, no filtering occurs. If any drop-down is set to Yes or No, the products are filtered accordingly. When multiple text drop-down fields have determinate selections, the product drop-down is filtered to include only items that meet all selected criteria.
Since the app supports both switch and text drop-down filtering, new named values — OnSale, Discontinued and Featured — normalize the values.
For example, the formula for OnSale is:
({calcapp.link SWITCH} selects between values, but IF, IFS and CHOOSE could also be used.)
Here’s the complete updated formula for FilteredProducts:
Let’s zero in on the OnSale part of the second parameter:
ISBLANK checks if OnSale is blank, meaning no filtering should occur for this criterion. The || operator (or + in Excel) ensures that products are included if filtering is not required.
Taken together, the formula produces a logical array whose elements are TRUE if OnSale is blank, or if OnSale matches its namesake column in the data table.
A real app would likely use either switch fields or indeterminate drop-downs, not both, but this example allows users to choose.
Note: Named values are unavailable in Starter plans. To work around this, use hidden switch fields instead of the three named values, and move FilteredProducts’ formula directly into the drop-down field’s Values formula, replacing the reference to FilteredProducts.