How Convert Summary Field to a dropdown?

[Shah Baloch]

Is it possible to convert the Jira software project summary field to a dropdown with predefined options? We have a purchasing project, we would like to have the standard name in the summary field. for example, whenever a user types something it should display possible options that the user can choose. Like when the user type "Windows" it should display all option that contains windows, for example, Windows Operating System, Windows Licensing, etc. Our goal is to map each summary with a code that can be displayed in a custom field. But that'll be the second step. First, we would like to have the Summary dropdown but all options that we have for users.  If it was one option I could easily add it by using the setFormValue command but there will be more values and we don't have any default value, the values should populate when the user starts typing. Is it doable?

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.customfields.manager.OptionsManager
import com.onresolve.jira.groovy.user.FieldBehaviours
import groovy.transform.BaseScript

@BaseScript FieldBehaviours fieldBehaviours

def summary = getFieldById("summary")
summary.value(["Windows Licensing","Application Training","Lenovo Laptops"])

Thank you for your help.

[Peter-Dave Sheehan · Accepted solution]

I'm not sure how advisable it is, but it's possible:

def options = [
  "" : "Select a value...", //if you want to default to no selection, otherwise, the first option will be selected
  "Windows Licensing" : "Windows Licensing",
  "Application Training" : "Application Training",
  "Lenovo Laptops" : "Lenovo Laptops"
]

def summary = getFieldById('summary')
summary.convertToSingleSelect().setFieldOptions(options)

 You can refer to the Select List Conversion documentation for other options such as getting the options from ajax.

But in short, the "left side" or key of each map entry in the options is the value that will be stored/visible in the ticket when the issue is created and the "right side" or value is what will be visible in the drop down.

So that first option will fail any validation for required field.

You could add extra information on the right-side if that will help your users.

Then you are free to react to the summary field change with a separate script and have other logic based on which value was selected (left-side of the option).

E.g.

def summary =getFieldById('summary')

if(summary.value == 'Lenovo Laptops'){
 //do stuff here like show/hide certain fields or set default value to other fields
}

[Shah Baloch]

Hi, @Peter-Dave Sheehan thank you for your response. I added the script in Behaviour and mapped a project, however, the screen gets frozen when I click on create issue button. I have to remove the project and refresh or close the page in order to unfreeze the screen. Any idea why it is happening?

 I tried the ajx option from the Rest EndPoints but couldn't figure it out. The below script I tried from Rest Endpoints but it didn't work. I know it's not correct. Can you please help

import com.atlassian.jira.component.ComponentAccessor
import com.onresolve.scriptrunner.runner.rest.common.CustomEndpointDelegate
import groovy.json.JsonBuilder
import groovy.transform.BaseScript
import javax.ws.rs.core.MultivaluedMap
import javax.ws.rs.core.Response
 
@BaseScript CustomEndpointDelegate delegate

 eventTypes(httpMethod: "GET") { MultivaluedMap queryParams ->
    def rt = [footer: 'Please select product name']

    
    def options = [

  "" : "Select a value...", //if you want to default to no selection, otherwise, the first option will be selected

  "Windows Licensing" : "Windows Licensing",

  "Application Training" : "Application Training",

  "Lenovo Laptops" : "Lenovo Laptops"

]

def summary = getFieldById('summary')

summary.convertToSingleSelect().setFieldOptions(options)

}

Thank you

[Peter-Dave Sheehan]

You are mixing apples and organges here...

If you want to define a custom rest endpoint to use in a selectListConversion, you have to do that in the Rest Endpoint section of scriptrunner.

That rest endpoint script would serve only 1 purpose, output your list of options. If you're going to do that, you need to follow the format prescribed in the documentation.

Here is a what your rest endpoint script might look like:

import com.atlassian.jira.component.ComponentAccessor
import com.onresolve.scriptrunner.runner.rest.common.CustomEndpointDelegate
import groovy.json.JsonBuilder
import groovy.transform.BaseScript

import javax.ws.rs.core.Response

@BaseScript CustomEndpointDelegate delegate

eventTypes(httpMethod: "GET") { MultivaluedMap queryParams ->
    def options = ['Windows Licensing', 'Application Training', 'Lenovo Laptops']
    
    String query = queryParams.getFirst("query") ?: ''
    def rt = [footer: 'Please select product name']
    rt.items = options.collect { option ->
        [
                value: option,
                label: option,
                html : (query) ? option.replaceAll(/(?i)$query/) { "<b>${it}</b>" } : option
        ]
    }
    rt.total = options.size()
    return Response.ok(rt).build()
}

This will return your 3 options in a format expected by the selectListConversion with ajax option.

Then in your behaviour (in the initializer script):

def summary = getFieldById('summary')
summary.convertToSingleSelect([
   ajaxOptions: [
      url: "$baseUrl/rest/scriptrunner/latest/custom/enventTypes",
      query: true,
      format: 'general'
   ]
])

 Note that with this, you can't use summary.setFormValue('some default value').

If you need to programmatically control the selected value of the drop-down field, your values have to be controlled manually with "setFieldOption" rather than dynamically via the ajaxOptions

[Shah Baloch]

@Peter-Dave Sheehanthe create issue screen is getting hung. I'm getting errors when I'm on create issue screen to create an issue. Any idea? How can I fix this? Here are the screenshots.

thank you

[Peter-Dave Sheehan]

Are you able to access your newly created endpoint?

What happeds if you points your browser to <jira base url>/rest/scriptrunner/latest/custom/enventTypes

Can you see any errors in your chrome developer tools?

[Shah Baloch]

I get a 404 error.

This jira.myweb.com page can’t be found

No webpage was found for the web address: https://jira.myweb.com:8443/rest/scriptrunner/latest/custom/enventTypes

HTTP ERROR 404

Thank you

[Peter-Dave Sheehan]

Add 

import javax.ws.rs.core.MultivaluedMap

To your REST Endpoint script.

[Shah Baloch]

I added that but got an error when I click the dropdown from the summary. Here is the error:

The Jira server could not be contacted. This may be a temporary glitch or the server may be down.

Thank you

[Peter-Dave Sheehan]

First thing first though, can you access your rest endpoint now?

[Shah Baloch]

If I try to access this link "<myurl>rest/scriptrunner/latest/custom/enventTypes" I get the below error. If that's what you're asking. If not then how can check it? Thank you

This jira.myweb.com page can’t be found

No webpage was found for the web address: https://jira.myweb.com:8443/rest/scriptrunner/latest/custom/enventTypes

HTTP ERROR 404

[Peter-Dave Sheehan]

Can you share a screenshot of your REST End point configuration and the Rest Endpoint list.

[Shah Baloch]

Here is the screenshot.

Thank you

[Peter-Dave Sheehan]

And what do you see on the list of REST Endpoints (after you save the endpoint)?

[Shah Baloch]

It shows no error. Here is the screenshot.

[Peter-Dave Sheehan]

It doesn't look like your rest endpoint is registered correctly.

Maybe you have other code outside the range of what was visible in the screen you shared. 

Here is my endpoint script code and what it looks like in the list:

The list entry should include a link to the endpoint

[Shah Baloch]

Yes, there is an entry. That was another script related to the database pickup I was trying. Initially, I disabled the script, then I deleted it today and tested this script. The same issue is about the Jira gilitch. Here is the last log I believe it's from the other script.

2022-04-28 09:12:34,473 WARN [sql.Sql]: Failed to execute: SELECT FirstName, LastName from Person WHERE FirstName like ? because: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1 2022-04-28 09:12:34,479 ERROR [common.UserCustomScriptEndpoint]: ************************************************************************************* 2022-04-28 09:12:34,480 ERROR [common.UserCustomScriptEndpoint]: Script endpoint failed on method: GET eventTypes java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1 at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1200) at com.zaxxer.hikari.pool.ProxyStatement.executeQuery(ProxyStatement.java:111) at com.zaxxer.hikari.pool.HikariProxyStatement.executeQuery(HikariProxyStatement.java) at Script24$_run_closure1$_closure2.doCall(Script24.groovy:21) at com.onresolve.scriptrunner.db.AbstractDbConnectionManager.withSql(AbstractDbConnectionManager.groovy:70) at com.onresolve.scriptrunner.db.AbstractDbConnectionManager$withSql$0.callCurrent(Unknown Source) at com.onresolve.scriptrunner.db.AbstractDbConnectionManager.withSqlTracked(AbstractDbConnectionManager.groovy:109) at com.onresolve.scriptrunner.db.DbConnectionManager$withSqlTracked.call(Unknown Source) at com.onresolve.scriptrunner.db.DatabaseUtil.withSql(DatabaseUtil.groovy:24) at com.onresolve.scriptrunner.db.DatabaseUtil$withSql.call(Unknown Source) at Script24$_run_closure1.doCall(Script24.groovy:16) at com.onresolve.scriptrunner.runner.rest.common.UserCustomScriptEndpoint$_doEndpoint_closure2.doCall(UserCustomScriptEndpoint.groovy:185) at com.onresolve.scriptrunner.runner.rest.common.UserCustomScriptEndpoint$_doEndpoint_closure2.doCall(UserCustomScriptEndpoint.groovy) at com.onresolve.scriptrunner.runner.diag.DiagnosticsManagerImpl$DiagnosticsExecutionHandlerImpl$_execute_closure1.doCall(DiagnosticsManagerImpl.groovy:388) at com.onresolve.scriptrunner.runner.diag.DiagnosticsManagerImpl$DiagnosticsExecutionHandlerImpl$_execute_closure1.doCall(DiagnosticsManagerImpl.groovy) at com.onresolve.scriptrunner.runner.ScriptExecutionRecorder.withRecording(ScriptExecutionRecorder.groovy:13) at com.onresolve.scriptrunner.runner.ScriptExecutionRecorder$withRecording.call(Unknown Source) at com.onresolve.scriptrunner.runner.diag.DiagnosticsManagerImpl$DiagnosticsExecutionHandlerImpl.execute(DiagnosticsManagerImpl.groovy:385) at com.onresolve.scriptrunner.runner.rest.common.UserCustomScriptEndpoint.doEndpoint(UserCustomScriptEndpoint.groovy:218) at com.onresolve.scriptrunner.runner.rest.common.UserCustomScriptEndpoint.getUserEndpoint(UserCustomScriptEndpoint.groovy:72)

Thank you

[Peter-Dave Sheehan]

Create a separate entry just for the envenTypes endpoint.

It should have no other code then was I suggested in my second reply.

Then you should be able to access that endpoint and see the values. And optionally, app "?query=W" to get partial results.

Once you can get the restAPI to work, the behaviour should work normally.

[Shah Baloch]

I removed the behaviour script for now. However, I didn't understand this part "And optionally, app "?query=W" to get partial results". How can I get the result?

Thank you

[Peter-Dave Sheehan]

Argh ... I'm so sorry, looks like I had a couple of mistakes that caused us both to waste some time.

Here is an updated behaviour script for the summary:

if(underlyingIssue){
    getFieldByName('Employee').setReadOnly(true)
}
getFieldByName('Next Trigger').setHidden(true)

def summary = getFieldById('summary')
summary.convertToSingleSelect([
        ajaxOptions: [
                url: "$baseUrl/rest/scriptrunner/latest/custom/eventTypes",
                query: true,
                formatResponse: 'general'
        ]
])

2 corrections: 1) I had a typo in the URL, 2) the 3rd ajax option should be "formatResponse" not just "format"

Then, if we want the query to actually cause the restEndpoint results to be filtered, we need to adjust the endpoint logic:

import com.atlassian.jira.component.ComponentAccessor
import com.onresolve.scriptrunner.runner.rest.common.CustomEndpointDelegate
import groovy.json.JsonBuilder
import groovy.transform.BaseScript
import groovy.transform.Field

import javax.ws.rs.core.Response
import javax.ws.rs.core.MultivaluedMap

@BaseScript CustomEndpointDelegate delegate

@Field List<String> options = ['Windows Licensing', 'Application Training', 'Lenovo Laptops']
eventTypes(httpMethod: "GET") { MultivaluedMap queryParams ->
    def rt = [footer: 'Please select product name']
    String query = queryParams.getFirst("query") ?: ''
    def optionsToReturn = options
    if(query){
        optionsToReturn = optionsToReturn.findAll{it.toLowerCase().contains(query.toLowerCase())}
    }
    rt.items = optionsToReturn.collect { option ->
        [
                value: option,
                label: option,
                html : (query) ? option.replaceAll(/(?i)$query/) { "<b>${it}</b>" } : option
        ]
    }
    rt.total = optionsToReturn.size()
    return Response.ok(rt).build()
}

What I was saying before with ?query=W was just a way to verify that the end point works.

If you points your browser to 
<baseUrl>/rest/scriptrunner/latest/custom/eventTypes  you should see all the options

And if you point it to

<baseUrl>/rest/scriptrunner/latest/custom/eventTypes?query=W you should see just options that contain a w

[Shah Baloch]

@Peter-Dave Sheehanthe updated script works, thank you so much.

Now I'm working on the second part of the task. I created a separate behavior script to add product code in a custom field base on summary selection. The script is working fine, I'm able to get product code when I select an option from the summary dropdown.

There will be more than 100 options that I need to map each with a product code. Just wanted to check with you if that's how it should be or if there is an easier way.

Here is the working code

import com.atlassian.jira.component.ComponentAccessor
import com.onresolve.jira.groovy.user.FieldBehaviours
import com.onresolve.scriptrunner.db.DatabaseUtil
import groovy.transform.BaseScript
@BaseScript FieldBehaviours fieldBehaviours

def summary = getFieldById('summary')
def proCode = getFieldById('customfield_14300')

if(summary.value == 'Windows Licensing') {
    proCode.setFormValue('5100')
}

else if(summary.value == 'Application Training') {
    proCode.setFormValue('4501')
}

else if(summary.value == 'Lenovo Laptops') {
    proCode.setFormValue('17594')
}

else {
    proCode.setFormValue('')
}

The second question is about multi Rest Endpoint scripts. just wanted to check if there won't be an issue to have different Rest Endpoint scripts for different projects? Because if I try to create a different rest endpoint script I get an error, the error is the same that I shared with you and you can see it in the log that you pasted earlier. It's SQL script. Is that error about SQL connector or something else?

Thank you for your help.

[Peter-Dave Sheehan]

It will be much more efficient from a coding perspective if you use a simple data structure for all your options:

def optionsCodeMap = [
   'Windows Licensing': 5100,
   'Application Training': 4501,
   'Lenovo Laptops': 17594
]

def option = getFieldById('summary').value
def prodCode = optionsCodeMap[option]
getFieldById('customfield_14300').setFormValue(prodCode)

And if you have the list of option/prod code in a sql db (perhaps indexed by project):

def optionCodeData = DatabaseUtil.withSql('sqlresourcename'){sql->
   sql.getRows('select option, prodcode, project from optionsTable where project = :pkey', [pkey:issueContext.projectObject.key])
}
def option = getFieldById('summary').value
def prodCode = optionCodeData.find{it.option == option}.prodcode
getFieldById('customfield_14300').setFormValue(prodCode)

 This assumes your sql data contains these 3 columns: 'option', 'prodcode' and 'project'

The data in "option" matches the summary values. The data in prodcode matches the integers to put in the customfield_14300 and project contains the project key where this option is applicable.

You can't have multiple endpoints with the same name. 

If you are getting some syntax errors with using sql in your rest endpoint, I'd have to see the entire script to help debug.

[Shah Baloch]

I'm sorry I was maxing up both tasks and making you confused. SQL task is a completely different thing. The script we were talking about was just about making the summary field a drop-down with a bunch of options and the second part was mapping each option of summary value into a product code where when a value gets selected from the summary then it adds a code to a custom field. I don't need SQL code for this, but I'll take other code you modified that looks much better and smoother than my ifs and elses.

My second question was about another script. Like we added a summary script in Rest Endpoint and made it work. I was asking that I have another script that is about pulling data from the database. It used to work, however, I never got a chance to apply the script in our production environment.  When we were working on the summary script and I noticed an error in the execution log which I pasted in an earlier reply.

I just wanted to ask you if that error is related to MySQL connector or anything else. We also recently upgraded our server. Before making any changes I thought I'll ask you if you have an idea.

022-04-28 09:12:34,473 WARN [sql.Sql]: Failed to execute: SELECT FirstName, LastName from Person WHERE FirstName like ? because: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1 2022-04-28 09:12:34,479 ERROR [common.UserCustomScriptEndpoint]:

Thank you

[Peter-Dave Sheehan]

Like I said, to debug that other script, I'd have to see it.

The following sql statement could be valid depending on how it's being called.

SELECT FirstName, LastName from Person WHERE FirstName like ?

Generally, in groovy, I use this format

def query= "SELECT FirstName, LastName from Person WHERE FirstName like :input"
sql.getRows(query, [input: "%Peter%"])

[Shah Baloch]

@Peter-Dave SheehanMySQL query is displaying Jira glitch error. I'll create a separate post of that since it's not related to this one. Thank you so much for your help.