Cascading Dropdowns

This article describes how to create cascading dropdowns using the MegaQuery add-on functionality

We’ve worked on and seen numerous projects that require a series of cascading dropdowns, where the options in the second dropdown are filtered based on the selections from the first, and so on.

It’s a common UI pattern, and one which can be accomplished with Squirrel365’s native dropdown component. However, there are some inefficiencies with the native dropdown approach.

In this article, I will demonstrate the dropdown solution and then showcase a new approach.

Dropdown Approach

With the dropdown component, you can filter and move data based on a selection. This approach moves the data from the first dropdown, and then you can use that filtered data as the source of the next dropdown, and so on, cascading down.

In my example below, I have a list of US States, some cities in the state, and the population of the city. I want the dropdowns to cascade down, so that you select a state, and then the second dropdown shows only cities for that state. Once you select a city then it will give you the population

As you can see, each time I select a value from the dropdown, it brings the filtered rows across. The yellow section contains all the columns of data for the rows where US State = Texas

To create this behaviour, add two dropdowns to your canvas

• The first dropdown, our State dropdown, binds the List Items property to column B

  • Sheet1!B3:B96

• Change the Data Insertion type to Filtering Rows

  • Source: Sheet1!B3:D96

  • Destination: Sheet1!F3:H26 (the yellow section)

  • Selecting Filtering Rows will de-duplicate the list items, so for example, you only see California in the list once, rather than multiple times

• This has set up the first dropdown, now let’s repeat that for the second, City, dropdown

• Bind the List Items to column G (our filtered list of cities)

  • Sheet1!G3:G26

• Change the Data Insertion type to Filtering Rows

  • Source: Sheet1!F3:H26 (the yellow section)

  • Destination: Sheet1!J3:L3 (the orange section)

When you select a value from the State dropdown, it will take the corresponding rows from the source data and insert them in the yellow destination range. This yellow range drives the second dropdown and shows a unique list of Cities for the selected state. Selecting a state will insert the final filtered row in the orange section, where we can see the population count.

Pros and Cons

Great, we’ve built our cascading dropdowns, and it was easy too. It can be scaled up by adding more dropdowns and having it cascade down multiple times.

There are some downsides, though. As you’re filtering and inserting data each time you select a value, the volume of data you end up moving can grow quickly. In my example, I only have 3 columns of data. But what if I had 20 columns, and I wanted to cascade 5 times? That would mean I needed to have an additional 100 columns of data in my spreadsheet. A larger spreadsheet can impact the performance of your project. Not to mention adding complexity when trying to trace where the data originates from.

Another challenge is to do with how the dropdowns remember and reset their selected values. If you select Texas > Austin, and then change the State to California, the selected City resets itself back to “Please Select”. This is because there is no Austin in California. However, if there were an Austin in California, then this would stay selected. This is not the behaviour I wanted; I would want the City to clear itself if the selected State changed.

To do that, you would need to get creative with the Selected Item property and maybe use a Data Mover function to reset the values. It’s all possible, but it adds complexity to your project.

The MegaQuery† way

†Name to be confirmed

MegaQuery is a new function we’ll be introducing in 2025. This function gives you the power of SQL to query blocks of data.

With MegaQuery, you select a block of data, write a SQL statement to query, filter, aggregate, or do anything else to the data, and then tell the function where to write the results.

In SQL, the placeholder {{table}} is used to represent the block of data. In our example, that is Sheet1!B2:D62.

So, to get a list of States to use in the first dropdown, I would write:

SELECT DISTINCT `US State` 
  FROM {{table}}

MegaQuery will insert that into Column F. The first dropdown will use that as it’s the List Items and Data Insertion type will now be Label. And we’ll insert that into I3

The next MegaQuery uses an Excel formula to concatenate the value in I3 (our selected State) into the query.

=”SELECT DISTINCT City
  FROM {{table}}
 WHERE `US State`= '”& I3 & “'”

This query will return a distinct list of cities for the selected state. If we insert this list into Column G in the spreadsheet. Then we can use that to populate the second dropdown. Again, on this dropdown, set the Data Insertion type to Label and insert the label into J3

Now the final query is to take the selected State and City and get the corresponding population

="SELECT IFNULL(SUM(Population), 0) as Population 
    FROM {{table}}
   WHERE `US State` = '"&I3&"'
     AND City = '"&J3&"'"

I’ve used IFNULL in the SQL to ensure that if the query returned no rows, we set the population to 0. It also shows that you can do more complicated things than just simple SELECT statements with MegaQuery.

Pros and Cons

On the surface, this approach looks a lot more complex than my first example. However, MegaQuery for cascading prompts provides the same level of scalability as using Filtered Row without the need to move duplicate data around the sheet. All we are capturing is distinct dropdown list values and the selected item. So if my source data was 3 columns wide or 200 columns wide, I only care about a tiny subset of that whilst processing the cascading filters.

There is the added benefit with MegaQuery, which is that you can do a whole lot more with the SQL. For example:

• What if you wanted the total population count for a state if you haven’t selected a city?

  • You can do that with a simple SUM in SQL.

• Or you wanted to order the list of states in the first dropdown to show the largest populations at the top.

  • You can do that with ORDER BY in the query

Both approaches work and are viable solutions for something like cascading dropdowns. MegaQuery offers a new way to handle this common UI pattern. Out of the two solutions, I’ll be reaching for MegaQuery on larger or more complex projects, as I love the flexibility and additional opportunities it affords.