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.

[Jordan Hauser]

-editing to move to a response to a comment instead of header 'answer'

[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

[Jordan Hauser]

Hi, thank you Ram for posting this solution, it has been incredibly helpful in learning scriptrunner.

While trying to implement this myself I keep running into errors after inputting my own instance's items and I'm not sure what's going wrong.

Could you post screenshots of the database picker configs for (Retrieval/Validation, Search, Configuration Script)? It would be greatly appreciated.


EDIT: I found some additional information, the problem I'm running into is that the primary key for the table I need has alphanumeric and special characters instead of just being a straight number sequence

EDIT 2: There were a couple spots where the value was being changed to 'Long' and the map function for the output/result wanted an integer, when I changed these to be untyped and a string respectively it started functioning - Solved!