Dependent select lists - SQL datasource

Configure the datasource

The first step is to configure the data source, this is easily done, just follow our documentation and adapt it to your own configuration.
Let us say our data are organised in different tables :

Create the Connect fields

Creating the Elements Connect fields is pretty straightforward and is described here in our documentation.

We require 3 fields:

• Connect Live Text: Continent

• Connect Live Text: Country

• Connect Live Text: City

Configure the Continent selection field

The first select list is simple, it is supposed to fetch all the Continent names that are stored in the continent table. We are going to use the id attribute as the key, so that this value will be used to configure the next select lists.

Edit

Configure of the Edit view, from the "Edit" tab:

Query

SELECT id, continent_name
FROM continent

Key

0

This number refers to the column number in the result set (numbering starts at 0).
This is the value stored in the customfieldvalue table in the Jira Database. It's use to retrieve the selected value when the field is edited.

You can find more information about how to configure the edit view here.

Editor 

Select list

Selection

The user can select only one value

Columns

1

Template

{continent_name}

Display

Configuration of the Display view.

From the "Display" tab, in the "Main view" section check the "Configure a specific query - This view uses the edit query" box.

Query

SELECT continent_name
FROM continent
WHERE id = '$currentCustomfieldValue'

Here, we specify a SQL query that will only retrieve the continent selected during the edit.

This query is optional, but it's recommended. Indeed, instead of executing the edit query and searching for the selected value in the entire result set it will only retrieve the selected item

Configure the Country field

The values that are displayed in the Country field needs to be different depending on what value is selected in the Continent field. The Country table contains an attribute that will be useful to determine whether the value must be displayed. The continent_id attribute contains the id of the corresponding continent. That attribute must match the continent that has been selected.

Edit

Configure of the Edit view, from the "Edit" tab:

Query

SELECT id, country_name
FROM country
WHERE continent_id = '$issue.customfield_10000'
## where 10000 is the id of the "Continent" field

Note here the Edit query depends on the Continent value that has been previously selected. This dependency also appears in the Configure field section of the Elements Connect administration page under the Dependencies column.

Key

0

This number refers to the column number in the result set (numbering starts at 0).
This is the value stored in the customfieldvalue table in the Jira Database. It's use to retrieve the selected value when the field is edited.

You can find more information about how to configure the edit view here.

Editor

Select list

Selection

The user can select only one value

Columns

1

Template

{country_name}

Display

Configuration of the Display view.

From the "Display" tab, in the "Main view" section check the "Configure a specific query - This view uses the edit query" box.

Query

SELECT country_name
FROM country
WHERE id = '$currentCustomfieldValue'

Here, we specify a SQL query that will only retrieve the country selected during the edit.

This query is optional, but it's recommended. Indeed, instead of executing the edit query and searching for the selected value in the entire result set it will only retrieve the selected item

It's mandatory if you want to display this filed in a statistical gadget in a Jira Dashboard (such as the Pie Chart).
The $issue dependencies cannot be resolved when a field is display in this type of gadget.

Configure the City field

Almost done, finally the City field. The configuration here is very similar to the previous one, except that here we are going to match the country_id attribute with the value that has been selected in the Country field.

Edit

Configure of the Edit view, from the "Edit" tab:

Query

SELECT id, city_name
FROM city
WHERE country_id = '$issue.customfield_10001'
## where 10001 is the id of the "Country" field

Key

0

Editor

Select list

Selection

The user can select only one value

Columns

1

Template

{city_name}

Display

Configuration of the Display view.

From the "Display" tab, in the "Main view" section check the "Configure a specific query - This view uses the edit query" box.

Query

SELECT city_name
FROM city
WHERE id = '$currentCustomfieldValue'

Here, we specify a SQL query that will only retrieve the city selected during the edit.

This query is optional, but it's recommended. Indeed, instead of executing the edit query and searching for the selected value in the entire result set it will only retrieve the selected item

It's mandatory if you want to display this filed in a statistical gadget in a Jira Dashboard (such as the Pie Chart).
The $issue dependencies cannot be resolved when a field is display in this type of gadget.