Dependent Drop Downs

Introduction

When working with data in a user interface, it is common to use dependent (or cascading) drop down lists. Drop-down lists are a great way to use data validation in applications and thus ensure that data is clean. Using dependent drop-downs introduces a level of usability as users are presented with filtered options in subsequent columns once they have chosen a value in an initial column.

In this article, we demonstrate how to set up dependent drop down lists in a SQL Spreads workbook using Excel’s data validation feature. This solution can be implemented using version 7.5 or higher of SQL Spreads.

The sample data

We are going to use a simple example where a user first selects a Country and then a City from a second drop down.

Getting the demo up and running

You can download the demo file to see the setup and how it works.

Download the demo files to your desktop. Run the DependentDropDownDemoDBScript to create your demo database. And then open up the DependentDropDown_Demo Excel file to see the solution.

What the data looks like

Here is what the data looks like:

The 3 tables shown above can be initially set up in Excel - AmountPerCity, City and Country table. Then you can create the tables and data in SQL Server using the Create New SQL Server table from Excel feature in SQL Spreads.

How to setup dependent drop downs

Once the tables have been created in SQL Server, the next step is to import the data into a new Excel workbook using SQL Spreads.

Step 1: Import tables into Excel

Select your AmountPerCity table from the list of databases and tables in the SQL Spreads Designer to import into Excel. Run through the same process to import both the City and Country tables into separate tabs in the Excel document.

Once the data is in Excel, we can set up the drop downs in the AmountPerCity table – first the drop down for Country, and then the drop down for City.

Step 2: Setup a parent drop down list

Click on the SQL Spreads tab, then the Open Designer button. In the SQL Spreads Designer, click on the icon for the look-up list next to the Country column.

In the Lookup dialog, select the table from which to lookup the Country and the column in the AmountPerCity table that we want to match against – in this case, it is simply the Country table and the Country column (in the AmountPerCity table).

In the Text to Display, select Country, and then click OK.

Now, in the Country column in the AmountPerCity table, we can select from the Country drop-down.

Step 3: Setup a child drop down list

To create the City drop down and make it dependent on the value selected in the Country column, we need to specify a list in the Data Validation feature in Excel.

Click on the first value in the City column in the AmountPerCity table and then click on Data > Data Validation > Data Validation.

In the Data Validation dialog box, in the Settings tab, select List under the Allow option and then paste the following formula into the Source box and click OK.

=OFFSET( City!$B$1, MATCH($B4,City!$B:$B,0)-1, 1, COUNTIF(City!$B:$B,$B4), 1 )

The City drop down will now only display values for City that are relevant to the selected Country. This formula will also work when additional Cities are added to the City table.

How to setup dependent drop downs using key-lookups

There are times when the lookup data that you want to use has key values, instead of actual values. With SQL Spreads you can handle this by specifying the key value that you want to look up and the label value that you want to display in the dropdown in Excel. You can find more details on how to set this up in our article called Lookup key columns and replace with texts.

For example, instead of the tables looking like this:

they could look like this –

To cater for this in our solution we need to do two things:
• Create the key value lookups for Country and City in the SQL Spreads Designer
• Change the formula for the Data Validation in Excel

Step 1: Setup a parent drop down list with lookup

First, let’s create the lookup for Country in the SQL Spreads Designer using the Lookup in Database feature. You will lookup the Country ID and only display the Country text in the drop down:

Step 2: Setup child drop down list with lookup

Next, let’s do the lookup for City.

An important thing to note is that the Disable creation the Excel Data validation list option needs to be checked. This will make sure that the data validation formula that we enter is not cleared by SQL Spreads when we refresh the data in the tables.

Step 3: Create Data Validation for dependent drop down list

Click on the first value in the City column in the AmountPerCity table and then click on Data > Data Validation > Data Validation in the Excel ribbon.

In the the Data Validation dialog box, in the Settings tab, select List under the Allow option and then paste the following formula into the Source box and click OK.

=OFFSET( City!$B$1, MATCH(XLOOKUP($B4,Country!$B:$B,Country!$A:$A),City!$B:$B,0)-1, 1, COUNTIF(City!$B:$B,XLOOKUP($B4,Country!$B:$B,Country!$A:$A)), 1 )

Note that this formula uses an XLOOKUP function to convert the key value for the City.

Step 4: Sorting dependent drop down

The columns in the City table need to be sorted by Country and then City – this can be done in the SQL Spreads Designer using the sort feature.