Using Values In One Single Select Menu to Dynamically Populate Values in a Second Single Select Menu (Part 1)

[John]

In a recent office hours, a Tulip user asked a great question:

Let’s say I have an application where I need to update quantities of parts in a table. There are hundreds of parts, and they’re organized such that you have part numbers, and broader classes of related parts. The table looks as follows:

Her question was this: If I have classes of parts in a single select menu, how can I populate a second single select menu to only parts that belong to the class selected in the first single select menu.

I couldn’t solve the question on the spot, so I wanted to follow-up with two available methods for solving this problem,

1. Using Table Queries and Aggregations
2. Using the Tulip Table API

The rest of this post will walk you through how to execute the first option—using table queries and aggregations. Part 2 will show the same technique with the Table API, and will follow soon.

I’ve recorded a video walkthrough here, and you can follow step-by-step below:

Using Table Queries and Aggregations to Dynamically Populate 2 Single Select Menus

This is the preferred method for solving this problem. In short, the method works as follows:

1. A first, empty table query is paired with a “unique values” aggregation on the CLASS OF PARTS field to populate the initial single select menu
2. A second table query filters results based on app input, with the attached variable being the user’s selection from the first single select menu
3. The second single select menu is populated with an aggregation of “unique values” over the field PART, paired with the second table query

Let’s walk through how to do this in practice.

Create the first Table Query

First create a table query.

Name is something like “First Single Select Menu.” You don’t need to add any filters on this query because it’s just going to be used as a placeholder for the unique values aggregation.

Next, create a unique values aggregation over the CLASS OF PARTS field. This will allow your app to fill the first single select with each unique class of parts.

Create The Second Table Query

Create a second table query. Name it something to the effect of “Second Single Select Menu”. For this query, create a filter that takes an app input. It should look as follows:

Then, create a second unique value aggregation. This time, aggregate over the field PARTS.

Configure Your Queries and Aggregations in the App

First, add your table to an application. Then add your two queries to the table.

For the first table query—Single Select Menu 1—simply add your aggregation for pulling unique values from the CLASS OF PARTS COLUMN.

For the the second table query—Select Menu 2 here—you’ll need to do one extra step of configuration. First, add your table query for aggregating unique values from the PARTS column. Then open the query configuration menu by clicking on the query, and set the app input to whatever variable you assign to your first single select.

This is the key to this technique. We are using the selected value from the first single select as the app input for our second table query. When configured, the menu looks as follows.

Set the datasource on your single select menus to

The last step is to configure the data sources for your two single select menus. For the first single select, set the select options data source to your table query that aggregates on CLASS OF PARTS. Here, that’s my aggregation “select menu 1”. Then make sure that the variable assigned to your single select menu is the same variable used in your second table query.

***NOTE: make sure you’re configuring the datasource for the select options (the bottom of the two data sources), not the variable placeholder for a user’s selection.

Then, set the data source for your second single select menu to your table query that aggregates on PARTs while filtering for the selected CLASS OF PART. Here, that’s “Select Menu 2”.

My configuration menus look as follows:

At this point, your application should be sufficiently configured. Your second single select should dynamically populate based on the value of the first.

I’ll follow-up next week with a post on achieving this same goal using the Tulip Table API.

[carlin]

A very helpful tool when you want to make the options in a single-select menu dependent on a previous selection! Thanks John!

[thorsten.langner]

Thanks for this example.

Unfortunately this only works for TULIP Tables.
It would be helpful to have filters and sortings on single and multi select widgets the same way you deliver them on interactive tables. Then it would be an easy way to also use this on any list variable.

If someone wants to do this with a connector result set of data, the first selection must trigger a new connector call to get the filtered data… this is possible but slow and depends on the possibilities given by the connector

[John]

@thorsten.langner, I think the suggestion of allowing more robust filtering on different input types is a good one.

For now i’m getting around that by hardcoding a filter into the connector function, and selecting only the output that I’d like to go in the second single select menu as the output. So I make my first single select the input parameter, and put a trigger on that single select.