Use the cascading and driving table to provide a dynamic selection
Introduction
Assume you want to limit the options of one list column based on the selection made in another list column.
Â
For instance - you want to setup a grid where the user can only select from a limited list of models based on the year he or she selected.
The user interaction should look like a bit like in following youtube movie.
Â
Â
How to setup this environment
Â
There are 2 components in this setup
a) A grid custom field 'Year selector' which allows to select the year
b) A driving table providing the base data for the grid
Â
Year Selector grid configuration
The default value of the grid custom field contains following propertiesÂ
#####
# Setup the general grid properties such as columns and the table and databaseÂ
# where the grid data should be stored
#
Â
gd.columns = endyear, model
gd.tablename = yearselector
gd.ds = jira
Â
#####
# Setup the dropdown list with all the years the user can choose from
# Note the col.endyear.cascade property which tells the grid addon that the
# option selected in the endyear column should be pushed to the 'model' column
#
col.endyear=End Year
col.endyear.type=list
col.endyear.cascade = model
Â
#
# The list is a static list, but it can also be a dynamic list built from a query
#
col.endyear.list.size=12
col.endyear.required = true
col.endyear.name1=2004
col.endyear.value1=2004
col.endyear.name2=2005
col.endyear.value2=2005
col.endyear.name3=2006
col.endyear.value3=2006
col.endyear.name4=2007
col.endyear.value4=2007
col.endyear.name5=2008
col.endyear.value5=2008
col.endyear.name6=2009
col.endyear.value6=2009
col.endyear.name7=2010
col.endyear.value7=2010
col.endyear.name8=2011
col.endyear.value8=2011
col.endyear.name9=2012
col.endyear.value9=2012
col.endyear.name10=2013
col.endyear.value10=2013
col.endyear.name11=2014
col.endyear.value11=2014
col.endyear.name12=2015
col.endyear.value12=2015
Â
#####
# The model column is also a list but built dynamically
# The query used to initialize the list is using the endyear selection
# This query returns all models which are active in a specific range.
Â
col.model=Model
col.model.type = list
col.model.query = select model from model_d2 where {0} >= active_yearstart and {0} <= active_yearend
col.model.query.param=1
col.model.query.ds = jira
Â
The query requires some explaination
col.model.query = select model from model_d2 where {0} >= active_yearstart and {0} <= active_yearend
col.model.query.param=1
col.model.query.ds = jira
model_d2 is a 'lookup' table containing the mapping between a model and the year interval where this model is 'active'
{0} gets replaced with the selected value from the year drop down list.
For instance when the user selects 2004 from the list, the query sent to the database will beÂcol.model.query.param = select model from model_d2 where 2004 >= active_yearstart and 2004 <= active_yearend
Â
This query will retrieve all the models which are active during that year
Â
Setting up the driving table
The table grid editor has the capability to manage Driving Tables. Â These are lookup tables for the purpose to initialize the content of a grid or a list.
For instance, the query above is using model_d2 as underlying table.  This table is created through the  driving table interface.
Check the video.Â
Â
Â
The configuration of this driving table is as follows
Â
####
# Setup the global grid configuration (columns, tablename and database where to store the driving table content
#
Â
gd.columns = model, active_yearstart, active_yearend
gd.tablename = model
gd.ds = jira
Â
####
# The model column is just a string
#
col.model=Model
col.model.type = string
col.model.required = true
Â
####
# The active_yearstart and active_yearend are integers in the range 2004 - 2020
#
col.active_yearstart = Active from
col.active_yearstart.type = integer
col.active_yearstart.minvalue = 2004
col.active_yearstart.maxvalue = 2020
col.active_yearend = Active until
col.active_yearend.type = integer
col.active_yearend.minvalue = 2004
col.active_yearend.maxvalue = 2020
Â
Â
Once that you setup the driving table you can modify the content using the driving table editor in the administration section of the add-on.
Â
Â
Â