Confluence has been updated to version 6.15.9

Page tree
Skip to end of metadata
Go to start of metadata

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.