How to get values from database picker field via REST API?

Tomislav Tobijas

Hi,

Is it possible to get value names from the database picker field via REST call?

If just try to call the custom field via GET method, you will get a list/array of IDs of each selected item in the picker field. Now what I need to get is names (labels) for each value stored in this DB picker field.

Is this something that's possible to get via REST call?
I'm aware that you can use the script stated on the official docs page but that's not what I'm looking for.

Ram Kumar Aravindakshan _Adaptavist_

Hi @Tomislav Tobijas

In your description, you asked:-

Is it possible to get value names from the database picker field via REST call?

Are you referring to the value that is displayed in the DB Picker?

If yes, you could try doing this with the Server-Side Behaviour for the DB Picker field, i.e. for example:-

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

@BaseScript FieldBehaviours behaviours

def dbPicker = getFieldById(fieldChanged)
def dbPickerValue = dbPicker.value.toString()

def dbResultValue = new StringBuilder()

def output = []
def result = [:]

if (dbPicker) {

    DatabaseUtil.withSql('local_db') { sql ->
        if(dbPicker.value != null) {
            output = sql.rows("select name from sample_table where id = ?",  Integer.parseInt(dbPickerValue))
        }
    }

    output.findAll {
        result = it as Map<Integer,String>
    }

    result.values().findAll {
        dbResultValue.append(it.toString())
    }
}

log.warn "========>>>> ${dbResultValue.toString()}"

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

Below is a screenshot of the DB Picker's Server-Side Behaviour configuration:-

The variable local_db that the DatabaseUser object uses refers to the local database configuration that is setup using ScriptRunner's Resources configuration, as shown in the image below:-

Below is a screenshot of the DB Picker configuration that I have tested with:-

 

In the example above, I am trying to query a table in the database and pass the value selected in the database picker, i.e. the ID, to get the value displayed on Database Picker.

Once the value is obtained, it is stored in a StringBuilder, which is used to set the value in the Summary field.

Below is the sample table that I am testing:-

ID	Name
1	Malaysia
2	India
3	Singapore
4	Brunei
5	Sri Lanka
6	Thailand
7	Indonesia
8	Japan
9	Taiwan

Below are some sample test screens for your reference:-

1) When the issue is first created, the DB Picker value is not set as shown below:-

2) Once the option is selected from the DB Picker, as shown below, the value will be printed in the log as shown in the second screenshot below:-

3) If the DB Picker option is updated as shown below:-

the log output will also update accordingly, as shown below:-

I hope this helps to solve your question.

Thank you and Kind regards,

Ram