Autocomplete Value based on selection - Database Picker fields - Scriptrunner

Albert Manuel

Hi guys,

I think this is a hard one, but I will try my luck here.

I have 2 database picker fields (Scriptrunner), is there a way to auto-complete a value from the database, based on a selection still from the database?

Example:

I have a MySQL database where I have 2 values in two tables, the value Project_Name and Client_Name. I have created a connexion to this database in ScriptRunner and i have added two fields where i select values in Jira from that database,

Now, is possible somehow when i select Project_Name for example in a field, to autocomplete Client_Name in the other field?

 

Thanks.

Ram Kumar Aravindakshan _Adaptavist_

Hi @Albert Manuel,

For your requirement, you could use the ScriptRunner Behaviour.

You can set up a Server-Side Behaviour for the first DB Picker, i.e. Project_Name and use it to update the option selected in the Client_Name DB Picker based on the option chosen in the Project_Name DB Picker field.

Below is an example code for your reference:-

import com.onresolve.scriptrunner.db.DatabaseUtil
import com.onresolve.jira.groovy.user.FieldBehaviours
import groovy.transform.BaseScript

@BaseScript FieldBehaviours behaviours

def activeProjects = getFieldById(fieldChanged)
def activeProjectsvalue = activeProjects.value as Long

def dbValues = getFieldByName("Project Components")

def output = []
def result = [:]


DatabaseUtil.withSql('local_db') { sql ->
    if(activeProjects.value != null) {
        output = sql.rows("select id from sample_components where project_id = ?",  activeProjectsvalue)
    }
}

output.findAll {
    result = it as Map<Integer,String>
}
result.values().findAll {
    dbValues.setFormValue(it)
}

Please note, this sample code is not 100% exact to your environment. Hence, you will need to make the required modifications.

If you notice in the code, I am using the local_db as the connection name. Below is a print screen of the example DB Connection that I am using:-

In this example, I am just using a Local Database configuration, i.e. it will connect to the Database the Jira instance is using.

Below is a print screen of the Behaviour configuration:-

 

Below are the sample tables that I am using:-

1) For the Active Projects DB Picker

+--+--------------+
|id|project_name  |
+--+--------------+
|1 |Analytics     |
|2 |Mobile Apps   |
|3 |Web Components|
+--+--------------+

2) For the Project Components DB Picker

+--+--------------+----------+
|id|component_name|project_id|
+--+--------------+----------+
|1 |Statistics    |1         |
|2 |UI/UX         |2         |
|3 |REST Endpoint |3         |
+--+--------------+----------+

The project_id in the Project Components refers to the id from the Projects table.

Below are a few sample test screens:-

1) The Active Project DB Picker List

2) The Project Components DB Picker List

3) If the Analytics option is selected from the Active Projects DB Picker, the Statistics option is selected automatically in the Project Components DB Picker as shown in the image below:-

4) If the Mobile Apps option is selected from the Active Projects DB Picker, the Statistics option is selected automatically in the Project Components DB Picker as shown in the image below:-

5) If the Web Components option is selected from the Active Projects DB Picker, the REST Endpoint option is selected automatically in the Project Components DB Picker as shown in the image below:-

I hope this helps to answer your question.

Thank you and Kind Regards,

Ram