Use the cascading and driving table to provide a dynamic selection

Owned by svitlana (Deactivated)
Last updated: Jul 30, 2018
3 min read

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.