Tip: Filter drop-downs with optional criteria

February 14, 2026

Drop-downs displaying columns from large data tables can be overwhelming. Filtering helps users find what they need by showing only relevant values.

A separate tip covers filtering by text. This tip focuses on filtering based on logical columns (TRUE and FALSE values) in your data table.

Users need three options for each filter criterion — TRUE, FALSE and “I don’t care.” Without that third option, filtering becomes too restrictive. For example, if users want to see all products that are on sale, they shouldn’t need to specify whether those products are discontinued or featured.

We’ll explore this with two example apps. The first demonstrates the concept using switch fields, while the second shows the practical solution using indeterminate drop-downs.

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

Understanding the concept: Switch fields

Here’s the first example app using switch fields:

The text drop-down field at the bottom contains a list of products, which may be on sale, discontinued or featured. Check the Filter switch field to enable filtering with three switch fields.

This simpler approach helps explain the filtering concept, even though we’ll need to enhance it to be truly useful.

With switch fields, users can only specify TRUE or FALSE for each criterion — there’s no “I don’t care” option yet. Initially, all three switch fields are off, meaning only products that are not on sale, not discontinued and not featured appear in the drop-down.

If you enable both the Discontinued and Featured switch fields, no products remain. That’s consistent with the table — no rows have both values set to TRUE.

To achieve this, assign this formula to the Values property of the products drop-down:

If the Filter switch field is on, the drop-down comes 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 store any formula result. They help shorten formulas and improve readability.)

Here, we use a named value to represent the filtered values of the drop-down field. Here’s the formula for 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 that are TRUE are the ones where the OnSale switch field has a value that 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 that 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.)

The critical limitation

This approach has a fundamental problem: users must specify a value for every criterion. What if they want to see all products on sale but don’t care whether they’re featured or discontinued? They can’t express that with switch fields.

This limitation makes the switch-field approach impractical for real use. We need a way to represent “I don’t care” — which brings us to the actual solution.

The real solution: Indeterminate state

Switch fields can’t represent “I don’t care,” but text drop-down fields can. Here’s the second example app using drop-downs that allow an indeterminate state:

Instead of switch fields, this app uses three text drop-down fields named OnSaleText, DiscontinuedText and FeaturedText. Each offers three choices: Yes, No or leave it blank (representing “I don’t care”).

How it works:

• Blank drop-downs: no filtering for that criterion.
• Yes or No: filter based on that choice.
• Multiple criteria: products must match all non-blank selections.

Try selecting Yes for On Sale and leaving the other two blank. The drop-down shows all products on sale, regardless of whether they’re discontinued or featured. That’s the flexibility we need.

Implementation details

Since this app uses text drop-down fields instead of switch fields, we need named values to convert the text selections (“Yes”, “No”, blank) into logical values (TRUE, FALSE, blank) that the FILTER formula can use.

For example, the formula for the OnSale named value is:

(SWITCH selects between values, but IF, IFS and CHOOSE could also be used.)

The formula for FilteredProducts needs to handle blank values differently from the switch field version:

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.

This formula produces a logical array whose elements are TRUE if the criterion is blank (don’t care) or if it matches the corresponding column in the data table.

The crucial difference from the switch field version: ISBLANK checks whether each criterion is blank, and || (the OR operator) ensures products are included when that criterion doesn’t matter to the user.

---

Note: This technique requires named values, which are unavailable in Starter plans. You can work around this by directly embedding the formulas instead of using named value references.