Dependent Property Controls in Spotfire®

Introduction

It is often necessary to create dependent property controls in Spotfire^® ... Imagine the following scenario:

You have one drop-down control that contains a list of continents/regions and a second that contains a list of countries - you want the second property control to show the list of countries in the region that is selected in the first control. However, in Spotfire you can only pre-specify the list of values in each control - you can't do it dynamically. As you can see here, this is not the desired functionality:

What we really want is the second drop-down control to contain only the countries in Africa! So what's the problem and how do we fix it?

The problem is that Spotfire doesn't natively support dependent property controls (where the values in one control are dependent on a selection in another control), and neither does it have an API for inspecting or controlling the possible values of property controls. However, we can achieve the effect of dependent property controls with a data table, some clever usage of calculated column(s) and some IronPython.

Throughout this article, for ease, the two property controls will be referred to as:

• The independent property control (this is the control that contains the list of regions). It is the control that is not dependent on another
• The dependent property control (this is the control that contains the list of countries). It is the control that is dependent on the other - the one that updates when the first control is updated

1: Create a Data Table for the Property Controls

For this solution to work, we need a data table that contains the possible values for the property controls. In this case I have created an Excel spreadsheet with a list of regions and countries and loaded it into Spotfire:
 

This data table is what provides the relationship between region and country.

2: Configure the Independent Property Control

Now we need to configure the property controls to use the values from our data table. The first one to configure is the independent property control. It is set to use the list of unique values from the "Region" column in the data table:

Notice that the control is configured to show the unique values in the Region column.

3: Create a Calculated Column

Now that we have configured the independent property control, we need to configure the dependent control. It needs to get its values from the data table as well, but its values need to be the values for the countries based on the currently selected region. The way we do this is to create a calculated column that will contain the current set of valid countries based on the selected region.

The calculated column expression is:

 if("${Region}" = [Region], [Country])

In my example, I have named the column "Region Country"
 

So now, we have a data table looking like this:

You'll notice that, because "Africa" is currently selected in the Region drop-down, countries for other regions are not present in the "Region Country" column. Excellent! This is just what we need for our dependent property control.

4. Configure the Dependent Property Control

Now that we have a calculated column that contains values for the currently selected region, it's a simple matter to configure the dependent property control to use the values from the calculated column:

Once this is done, we can go back and test the two drop-down controls:

Great! It seems to be working then, right? Err... no... it's close, but not good enough for a proper "Spotfire application". You'll notice that if you change the independent property control, the dependent one shows "---":

This is because the document property "Country" is still set to the previously selected value, but it's not a valid value for the control given its current set of values as determined by the calculated column. This can be seen by looking at the document properties:

So how do we fix this? With some IronPython. I've developed a script to do this. It's designed to be flexible and generic and as a result It's a little complicated to set up, so bear with me as I set it up in the next section.

5. IronPython to Set Dependent Control Value

We saw in the previous section that there's a problem with the dependent control - its value doesn't get initialised when the value of the independent control changes. Therefore, we need an IronPython script to set its value to something that's valid.

Here's the script:

# Copyright © 2017. TIBCO Software Inc.  Licensed under TIBCO BSD-style license.

#
# Updates a dependent control, setting the value of the 
# dependent property according to the first valid value 
# for that property.
#
# This script should be configured to run when the independent
# control is changed
#
# Andrew Berridge, TIBCO Spotfire, July 2014, Revised September 2016

from Spotfire.Dxp.Data import *

#Updates one or more dependent property based on the selection made in another drop-down control
independentPropertyValue = Document.Properties[independentPropertyName]
i = 0
for dependentPropertyName in dependentPropertyNames.Split(","):
	dependentPropertyName = dependentPropertyName.Trim()
	dependentValuesDataTableName = dependentValuesDataTableNames.Split(",")[i].Trim()
	dependentValuesDataTable = Document.Data.Tables[dependentValuesDataTableName]
	dependentPropertyName = dependentPropertyNames.Split(",")[i].Trim()
	dependentPropertyValue = Document.Properties[dependentPropertyName]
	dependentValuesColumnName = dependentValuesColumnNames.Split(",")[i].Trim()
	dependentValuesColumnCursor = DataValueCursor.CreateFormatted(dependentValuesDataTable.Columns[dependentValuesColumnName])
	independentValuesCursor = DataValueCursor.CreateFormatted(dependentValuesDataTable.Columns[independentValuesColumnName])
	firstValidValue = ""
	for row in dependentValuesDataTable.GetRows(dependentValuesColumnCursor, independentValuesCursor):
		independentValue = independentValuesCursor.CurrentValue
		val = dependentValuesColumnCursor.CurrentValue
		if independentValue == independentPropertyValue and val != "(Empty)" :
			if firstValidValue == "" : 
				firstValidValue = val
				break

	print "Setting value of dependent property " + dependentPropertyName + " to: " + firstValidValue
	Document.Properties[dependentPropertyName] = firstValidValue
	i += 1

It must be configured with the following parameters (with their descriptions):

1. independentPropertyName - String - The property that the script "responds" to from document property change events
2. independentValuesColumnName - String - The name of the the independent values column in the configuration table
3. dependentPropertyNames - String - The names of the properties that are referenced by the dependent control
4. dependentValuesDataTableNames - String - The names of the data tables that contain the values for the dependent controls
5. dependentValuesColumnNames - String - The names of the columns that contain the values for the dependent controls

You'll notice that there's lots of plurality in the names of the parameters - indeed, this script is designed to be able to update multiple dependent controls all at once, but for simplicity we will focus on one dependent control in this article. Just note that multiple tables, columns, etc. will work with the script - you need to separate the names of each of these with commas.

In our example of regions and countries, the parameters will be specified as:

1. independentPropertyName: Region
2. independentValuesColumnName: Region
3. dependentPropertyNames: Country
4. depndentValuesDataTableNames: Continents and Countries
5. dependentValuesColumnNames: Region Country

The script should be triggered when the Region document property changes, like this:

Once this is done, everything should work nicely! Unfortunately, one small issue remains that we can't do anything about - the order of the values in the dependent property control isn't settable through any known means. The order depends on the original order of the data loaded into Spotfire. It's most likely (but not guaranteed) to be the same as the order of the values in the original Excel spreadsheet that was loaded into Spotfire.

I have attached Sample DXP file to this article below: 

dependent_property_controls.dxp_.zip

Andrew Berridge (Spotfire Data Science)

License:  TIBCO BSD-Style License