Working with Autocomplete

Prerequisite

To work with autocomplete you need first to configure a datasource. If you did not, please refer to the How to configure datasource for SQL Custom Field

Configuring autocomplete Custom Field

Step 1: Choose the datasource

To retrieve data, you will need first to connect to the database that stores data for you. Choosing the right datasource will make the connection between the customfield and the database.

Step 2 : Construct the query

After choosing the datasource and the customfield type (we assume that you choose autocomplete as customfield type). You will need now to fulfill the configuration with a working query. The query must first work and retrieve data on your DBMS.

To illustrate properly how it works we will try to implement a real use case :

Suppose that we have the table  City in our database. Here is the structure of our database : 

A simple select query will retrieve cities from the table city  :

Column Alias : When using an alias for the main column. If no alias then it must be the same as the column name.

Column name : The name of the main column

Loading data : Please choose Automatique for autocomplete

Number of character to start search : The number of characters to input before autocomplete started.

Number of the element to return : The number of elements to return as a result of the autocompletion.

Here is the result after entering «ben» :

Step 3: Retrieve more data

Children will help you to retrieve more data into issues. To work with Children, no special customfield required, you can use JIRA customfield as Text, Date, number, etc.

In the below example, the requirement has been modified to select id; and depending on this id, the plugin will retrieve the name of the city :

Query: The query must contain all the columns that you want to retrieve.

Column Alias: Explained by the use of an alias for the main column: city_id as 'id'

And here is the result :

Complex configuration

Now that you are more familiar with using autocomplete we can go further with the use of this configuration.

Suppose now that our database contains two tables as below:

So the need now is to retrieve the country for each selected city. Here's the new configuration :

select city,country from city c inner join country co on c.country_id = co.country_id where city like '%' order by c.city_id

Here is the result of this custom configuration :