How to get values from database picker field via REST API?
1 answer
1 accepted
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
Hi Ram,
Firstly, thank you for the detailed explanation and instructions!
If I got this right, what you're doing with this behavior is to visualize the name of the DB object inside the DB Picker field. Please correct me if I'm wrong.
I'm quite new to the REST calls and responses so it might be the case that I've misinterpreted your answer.
So, what I would need is to get the names of selected objects (after they have been updated in the DB Picker field), as when I call Jira to give me an issue response, it provides only a list of DB object IDs where I would need to get/show names.
In the example below, if you look at customfield_13010 which is a multi-choice DB picker field, values stored inside are IDs of selected objects (e.g. "11", "14"...) where I would want something like "Name_1", "Name_2"...
Is this something that can be achieved by using the GET method?
Best regards,
Tomislav
Below is the sample code for the REST Endpoint:-
import com.atlassian.jira.component.ComponentAccessor
import com.onresolve.scriptrunner.runner.rest.common.CustomEndpointDelegate
import groovy.json.JsonBuilder
import groovy.transform.BaseScript
import groovyx.net.http.HttpResponseDecorator
import groovyx.net.http.RESTClient
import javax.ws.rs.core.MultivaluedMap
import javax.ws.rs.core.Response
@BaseScript CustomEndpointDelegate delegate
getDBPickerValue { MultivaluedMap queryParams ->
def issueKey = queryParams.getFirst('issueKey')
def applicationProperties = ComponentAccessor.applicationProperties
final def baseUrl = applicationProperties.getString('jira.baseurl')
final def username = 'admin'
final def password = 'q'
final def headers = ['Authorization': "Basic ${"${username}:${password}".bytes.encodeBase64()}", 'Accept': 'application/json'] as Map
def http = new RESTClient(baseUrl)
http.setHeaders(headers)
def resp = http.get(path: "/rest/api/2/issue/${issueKey}") as HttpResponseDecorator
if (resp.status != 200) {
log.warn 'Commander did not respond with 200 for retrieving project list'
}
def issueJson = resp.data as Map
def dbPickerValue = issueJson['fields']['customfield_10800'] //Custom Field ID for DB Picker
Response.ok(new JsonBuilder(dbPickerValue).toPrettyString()).build()
}
Below is a screenshot of the REST Endpoint configuration:-
And below is the sample code for the Escalation Service in Jobs:-
import com.atlassian.jira.component.ComponentAccessor
import com.onresolve.scriptrunner.db.DatabaseUtil
import groovy.json.JsonSlurper
def key = issue.key
def applicationProperties = ComponentAccessor.applicationProperties
final def host = applicationProperties.getString('jira.baseurl')
final def restEndpointName = 'getDBPickerValue'
def baseUrl = "${host}/rest/scriptrunner/latest/custom/${restEndpointName}?issueKey=${key}"
def output = baseUrl.toURL().text
def dbPickerValue = new JsonSlurper().parseText(output) as Integer
def dbResultValue = new StringBuilder()
def result = []
DatabaseUtil.withSql('local_db') { sql ->
if(dbPickerValue) {
result = sql.rows("select name from sample_table where id = ?", dbPickerValue)
}
}
result.collectEntries {
it
}.values().findAll {
dbResultValue.append(it.toString())
}
log.warn "===>>>> ${key} ${dbResultValue.toString()}"
Below is a screenshot of the Escalation Configuration:-
Please note that the sample code provided is not 100% exact to your environment. Hence, you will need to make the required modifications.
The REST Endpoint queries the issues by the issue key and extracts the value from the DB Picker field. The DB Picker field id is used to filter the REST result as shown in the code, i.e.:-
def dbPickerValue = issueJson['fields']['customfield_10800'] //Custom Field ID for DB Picker
In my environment, the id for the DB Picker field is 10800; hence it is filtered with customfield_10800. You will need to modify this accordingly.
Once the result is filtered, the ID of the DB Picker will be returned by the REST Endpoint.
Next, the Escalation Service is used to query the REST Endpoint and extract its value.
The value returned, i.e. the field's ID, will be added to the SQL Query to get the field's name as shown in:-
def dbPickerValue = new JsonSlurper().parseText(output) as Integer
def dbResultValue = new StringBuilder()
def result = []
DatabaseUtil.withSql('local_db') { sql ->
if(dbPickerValue) {
result = sql.rows("select name from sample_table where id = ?", dbPickerValue)
}
}
result.collectEntries {
it
}.values().findAll {
dbResultValue.append(it.toString())
}
log.warn "===>>>> ${key} ${dbResultValue.toString()}"
The query result is filtered, and the DB Picker value that has been selected for the issue is returned in the log as shown in the image below:-
I hope this helps to answer your question. :)
Thank you and Kind regards,
Ram
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.