Filtered reference dropdowns are an advanced feature in database and spreadsheet applications that enhance user experience by dynamically refining the options presented in a dropdown menu based on another field’s selection or criteria. This functionality is especially useful in forms or data entry interfaces where the relevance and accuracy of the data input are crucial.
The concept revolves around the idea of “context sensitivity” – meaning, the options available in one dropdown menu depend on the selections made elsewhere in the form. For instance, if you have a form that requires users to select a company and then an employee from that company, a filtered reference dropdown would only display employees who are part of the selected company, rather than all employees in the database.
The example I will provide here will be based on selecting a country in a drop-down and then a second reference dropdown displaying only cities within the previously selected country
Without this, you would have two independent dropdowns and you might have a list of hundreds of cities in the second dropdown, increasing the chance of selecting a city that doesn´t belong to the country selected in the first. Obviously, the error probability increases even more if you have for example a first drop down with companies and a second drop-down with employees. You would need a widget showing companies and employees, filter it by the company you selected on the first form, then in the first form, employee dropdown, select one of those employees.
Possible, but less elegant than having a single form already doing the filtering.
other possible user cases:
-First Reference DropDown is for classes, second for Students
-First Reference DropDown for Type of Product, second for product
-First Reference DropDown for companies, second for employees
- etc
So, for this example I have 3 main tables, one for countries, other for cities (each city referencing a country) and another for the form
Notice that there is an EMPTY column (it will be used by the Action Button
And a formula column
if $Company.Name == $CountryAction:
return $Employee
else:
return " "
What the formula does is simply checking if Country column is equal to CountryAction, if it is, it returns the value at the column.
And finally we have the Form Page.
In the country reference, we select a country
As soon as a country is selected, the city dropdown is red and the reference is actually empty. I also created an error message, telling the user to click to update the button
So do as the messages tell you, click the button!
And voilá
What if the user changes the country after selecting a city?
It shows in red the city, because it doesn´t match the country, and tells the user to click the button to update the city again
After clicking the button to update the city…
The card list on the right updates accordingly.
. thanks again!
