Automatically set a custom field select list

HI everyone,

I have written a script which should first query a database and extract a certain value. Then a custom field exists in JIRA which a single select list with exactly the same values. By comparing these two values I want to set in the end the custom field value in JIRA to the same I extracted from the external database.

What I can say is that the "customer" object from my SQL query is being retrieved correctly because another functionality based on these queries is working well.

JIRA Version 6.4.5

Script Runner 3.0.16

 

Unfortunately the following script is not working:

 

import com.atlassian.crowd.embedded.api.User
import groovy.sql.Sql
import java.sql.Driver
import com.atlassian.jira.issue.Issue
import com.atlassian.jira.issue.MutableIssue
import com.atlassian.jira.user.util.UserManager
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.ComponentManager
import com.atlassian.jira.issue.fields.CustomField
import com.atlassian.jira.issue.CustomFieldManager
import com.atlassian.jira.issue.ModifiedValue
import com.atlassian.jira.issue.customfields.manager.OptionsManager


//Create and setup a database connection object which allows to query customer DB
def driver = Class.forName('net.sourceforge.jtds.jdbc.Driver').newInstance() as Driver

def props = new Properties()
props.setProperty("user", "*******")
props.setProperty("password", "***********")

def conn = driver.connect("jdbc:jtds:sqlserver://localhost:1433/Footprints", props)
def sql = new Sql(conn)
//end database connection

//an issue object is needed to proceed
MutableIssue currentIssue = issue

//the user who has raised the issue needs to be identified; Therefore the email address needs to be retrieved which
//will be used afterwards within the SQL query "customer" to retrieve the customer value from customer DB 
JiraAuthenticationContext=ComponentAccessor.getJiraAuthenticationContext()
loggedUser=JiraAuthenticationContext.getUser()
def reporterEmail = loggedUser.getEmailAddress()

//the first query retrieves the customer from customer DB
def customer = sql.firstRow("SELECT FIRMA FROM FIRMA, USER WHERE FIRMA.ID = USER.Firmenid AND USER.Mailadresse = ?",[reporterEmail])

//Since the return object "customer" is a Groovy Map which contains only one k/v pair it needs to be addressed 
//to retrieve only the value string customer which will be used in the 2nd query to retrieve the corresponding supportagent
def supportagent = sql.firstRow("SELECT SLA FROM FIRMA WHERE FIRMA.Firma = ?",[customer.getAt(0).toString()])

//sql connection close
sql.close()

//an autoassignment of the ticket shall be processed to the support agent based on the email address of the
//support agent which is being retrieved from the SQL query "supportagent" from customer DB 
switch (supportagent.getAt(0).toString()) {
    case 'a@a.de':
        currentIssue.setAssignee(ComponentAccessor.getUserUtil().getUser('a'))
        break
    case 'b@b.de':
        currentIssue.setAssignee(ComponentAccessor.getUserUtil().getUser('b'))
        break
    case 'c@c.de':
        currentIssue.setAssignee(ComponentAccessor.getUserUtil().getUser('c'))
        break
    case 'd@d.de':
        currentIssue.setAssignee(ComponentAccessor.getUserUtil().getUser('d'))
        break
    default:
        break
}
//the "Customer" selection list should be set to the corresponding value retrieved by the SQL query "customer" based on the user
//who opens the ticket.
ComponentManager componentManager = ComponentManager.getInstance()
CustomFieldManager customFieldManager = componentManager.getCustomFieldManager()
def cf = customFieldManager.getCustomFieldObjects(currentIssue).find {it.name == 'Customer'}
def fieldConfig = cf.getRelevantConfig(issue)
def value = ComponentAccessor.optionsManager.getOptions(fieldConfig)?.find { it.toString() == customer.getAt(0).toString() }
currentIssue.setCustomFieldValue(cf, value)

 

Every help is highly appreciated.

thx

Gerhard

1 answer

Looks broadly correct. Can you explain how it's not working. Check the logs for errors. Add some log messages so you can see if value is correct... it should be an Option object.

Also see https://scriptrunner.adaptavist.com/latest/jira/recipes/misc/connecting-to-databases.html.

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Nov 29, 2018 in Marketplace Apps

How to set up an incident workflow from the VP of Engineering at Sentry

Hey Atlassian community, I help lead engineering at Sentry, an open-source error-tracking and monitoring tool that integrates with Jira. We started using Jira Software Cloud internally last year, a...

1,242 views 0 8
Read article

Atlassian User Groups

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

Find a group

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

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you