In this article we will be giving an overview of Filterable Select Questions in an advanced form by adding in additional data. For a basic overview, see our documentation.

A Filterable Select Question is a select question with options based on the option selected from another select question.

Say you have a parts list with 1,000 parts, but only 200 of them are associated with the northeast region. Your team in the northeast region only wants to see those 200 parts when they're filling out their form, not all 1,000. Using a Filterable Selection Question, when your team selects "Northeast" as the answer in the "Region" question, only the 200 corresponding parts are shown to them when they open the "Parts" question.

In this article we will do an advanced example of a Filterable Select Question that builds on the basic example we outlined in our documentation.

Filterable Select Questions are based on the data you upload as a Resource. Let's say you have a spreadsheet that contains data for all the cities and states in the USA, broken out over 2 sheets:

Sheet 1

Sheet 2

You want users to select the state, which should filter only the cities associated with that state, and automatically populate the population for that state. Before you proceed, please note that all select options will need to have unique identifiers regardless of the filter you've created. See the "City ID" column in the above screenshot which will be used as the identifier column for our filtered select question in our form. 

Note - Please be sure to verify your resource meets the following requirements:

First you need to create a new form so that you can enter build mode. From your dashboard, click the "Forms" icon on the blue bar in the left-side menu, then click "New Form".

Now you should see the form builder screen in front of you.

Now you will add your first select question for State and follow the next steps (screenshots) of how to apply your resource file and populate your first select question, as per usual.

Now that your resource is attached, select the "Options Table" value that you want the question to reference. In this example, that value is "US Geography.States".

Set the "Text Column" (The column of the data that needs to be displayed).

Now set the "Identifier Column" to "Abbreviation", as this is the text that will be displayed when the data is submitted by the user, instead of the state's full name. Please note, a separate identifier column isn't required when all select options are unique.

Now you are going to move on to the select question for cities. This select question needs to be filtered, depending on which state was selected in the previous select question.

Because you have already attached your resource file, you can click on the "File List" tab and attach the corresponding resource file to your select question.

You are working with the "State Cities" select question, therefore you will use the other sheet called "US Geography.Cities".

You will set the "Text Column" to "City".

Because there are cities with the same names, a column has been created for "City ID". This column contains the state abbreviation as a prefix to make it easier to identify the state of the city. You will use this for the "Identifier Column".

Select the option to filter by clicking on "Set Filter".

Before you click "Set Filter" lets add in 2 more questions that you will need:

1. An Integer field called "Population". You will use this to filter cities with populations of a certain number.

2. A calculated question to LOOKUP the actual population, as listed in your resource file.

Now let's setup the "Actual population" Expression, by clicking on the question and "Set Expression".

You will be presented with a popup window where you can add your expression.

You will need to do the following to get the desired results.

Under "Add Function", select "LOOKUP".

Select your resource (the one you have been working with) and your table, which is the sheet that contains the population column.

Now you need to choose your "Key Column". This is the column in your resource that you'll need to reference to match the answer from the "Key Value" you'll set in the step after next.

Next is your "Value Column". This is the column which data you want to have displayed as the answer to the calculate question.

Next you will select your "Key Value" field.

Your "Key Value" is the field in the form that is associated with your resource file, that the calculated question uses to determine what the answer needs to be for the "Actual population" field.

This lookup is setup to take your answer from the "State Cities" question, locate the matching value in the "City ID" column in the resource, and finally populate the value for "Population" in the same row as the matching City ID.
​
Now you can click on "Add Function" and click "Save".

Now let's go back to setting the filter of the State Cities select question.

Once you click on set filter you will be prompted with a screen to set the conditions to determine the outcome of your filtered list.

You want a list of cities to be displayed, so in order for you to achieve this you will need to set your condition as follows:

Set Filter translation:  The "State" column text value for each row must equal the answer to the "USA States" question or else the "City" value in the same row will not be a visible option in "State Cities".
​
You will now add a second condition for the "Population" field, by clicking on "Add Condition".

This second condition you would want to select the column, population, which is a number field and it must be greater than or equal to (>=) the population answer you have captured manually. This will filter down the cities by state even more, by looking at the population of each associated with the resource file.
​
​Now save the form and sync your forms list.

On your device you should now see the form and open it. On open you will be able to see the questions you have created.

In the example, you only want to see cities with a population that is greater than or equal to (>=) 30,000.

When choosing a state, you can start typing and it will filter down your options.

In this example, we have selected Rhode Island as the state. Now, when you open the "State Cities" question, you will only see the cities for Rhode Island with a population greater than or equal to 30,000.

Now you can select your city, then you will see the "Actual Population" field will give you the total corresponding with your resource file. In our example, you selected "Providence", and the "Actual population" field was automatically populated with the correct value. In this example, 178,042..

Check our our blog post series with more examples of Filterable Select Lists;

Filterable Select Questions to the Rescue: Part I

Filterable Select Questions to the Rescue: Part II

This concludes our overview for filterable select questions. If you have any questions or comments feel free to send us a message at support@devicemagic.com.