Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Autocomplete Value based on selection - Database Picker fields - Scriptrunner

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.

2 answers

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

Hi @Albert,

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:-

db_connection.png

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:-

behaviour_configuration.png

 

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

image1.png

2) The Project Components DB Picker List

image2.png

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:-

image3.png

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:-

image4.png

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:-

image5.png

I hope this helps to answer your question. :)

Thank you and Kind Regards,

Ram

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!

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Jira Service Management

Next level incident management in Jira Service Management

Hello Atlassian Community! We’re thrilled to announce some exciting changes to incident management in Jira Service Management Cloud, in order to give your teams extra muscle during incidents, and ...

1,555 views 20 12
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you