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

Jira Service Desk automation with Scriptrunner

We recently created a Jira Service Desk, where customers create tickets. Created text field (Product Id) where customers enter the product id, in the backend I created two text fields (Product Name, Start Date). I just whenever customers enter the productId, it should show the product name and date from the backend to the service desk agent. I created a database an external database connection in scriptrunner and I can query the DB. 
I copied a script from online and try to make it work, but it is not working, I'm not sure what I'm doing wrong. To be honest, I don't have any about the groovy, so I just copied everything online. I'm also getting a warning from line 8 (screenshot). Can somebody help me please, thank you!

Custom Fields name: Product Id, Product Name, Start Date

Database Fields name: productId, productName, startDate

DatabaseName: Product

Poolname from resources: Product_Pool 

 

Script

import com.atlassian.jira.component.ComponentAccessor
import com.onresolve.scriptrunner.db.DatabaseUtil
import com.atlassian.jira.event.issue.IssueEvent
import com.atlassian.jira.issue.ModifiedValue
import com.atlassian.jira.issue.util.DefaultIssueChangeHolder
import java.sql.Timestamp

def event = event as IssueEvent
def issue = event.issue
def customFieldObjects = ComponentAccessor.customFieldManager.getCustomFieldObjects(issue)

def valueCF = customFieldObjects.findByName("Product Id")
def productName = customFieldObjects.findByName("Product Name")
def startDateCF = customFieldObjects.findByName("Start Date")

def value = issue.getCustomFieldValue(valueCF)
DatabaseUtil.withSql('Product_Pool') { sql ->
def row = sql.firstRow("SELECT * FROM Prdouct WHERE productId = ?", value)
if (row) {
def startDate = row.'CreateDate' as Timestamp
def startDateValue = """${startDate.time <= System.currentTimeMillis() ?
"{color:#d04437}${startDate.format('yyyy-MM-dd')}{color}" :
"{color:#00875A}${startDate.format("yyyy-MM-dd")}{color}"}""".toString()

productName.updateValue(null, issue, new ModifiedValue(issue.getCustomFieldValue(productName), row.'ProrductName'), new DefaultIssueChangeHolder())
startDateCF.updateValue(null, issue, new ModifiedValue(issue.getCustomFieldValue(startDateCF), startDateValue), new DefaultIssueChangeHolder())

}
}

 

Error log;

2020-06-16 12:01:40,115 ERROR [runner.AbstractScriptListener]: *************************************************************************************
2020-06-16 12:01:40,116 ERROR [runner.AbstractScriptListener]: Script function failed on event: com.atlassian.jira.event.issue.IssueEvent, file: null
org.codehaus.groovy.runtime.typehandling.GroovyCastException: Cannot cast object '2020-05-01' with class 'java.sql.Date' to class 'java.sql.Timestamp'
	at Script126$_run_closure1.doCall(Script126.groovy:20)
	at com.onresolve.scriptrunner.db.AbstractDbConnectionManager.withSql(AbstractDbConnectionManager.groovy:63)
	at com.onresolve.scriptrunner.db.AbstractDbConnectionManager$withSql$1.callCurrent(Unknown Source)
	at com.onresolve.scriptrunner.db.AbstractDbConnectionManager$withSql$1.callCurrent(Unknown Source)
	at com.onresolve.scriptrunner.db.AbstractDbConnectionManager.withSqlTracked(AbstractDbConnectionManager.groovy:92)
	at com.onresolve.scriptrunner.db.DbConnectionManager$withSqlTracked$1.call(Unknown Source)
	at com.onresolve.scriptrunner.db.DbConnectionManager$withSqlTracked$1.call(Unknown Source)
	at com.onresolve.scriptrunner.db.DatabaseUtil.withSql(DatabaseUtil.groovy:22)
	at com.onresolve.scriptrunner.db.DatabaseUtil$withSql.call(Unknown Source)
	at Script126.run(Script126.groovy:17)

 

variable.png

1 answer

@Hi @Shah Baloch here you go. There is one line commented in the code that you must change to retrieve the data.

Put this code in a Scripted postfuntion in the create transition of your workflow(s). Move it to the last place.

import com.atlassian.jira.bc.issue.IssueService
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.CustomFieldManager
import com.atlassian.jira.issue.IssueInputParameters
import com.atlassian.jira.issue.fields.CustomField
import com.atlassian.jira.issue.fields.Field
import com.atlassian.jira.timezone.TimeZoneManager
import com.atlassian.jira.user.ApplicationUser
import com.onresolve.scriptrunner.db.DatabaseUtil
import com.onresolve.scriptrunner.parameters.annotation.FieldPicker
import com.onresolve.scriptrunner.parameters.annotation.ShortTextInput
import com.onresolve.scriptrunner.parameters.annotation.UserPicker
import groovy.sql.GroovyRowResult

import java.text.SimpleDateFormat

@ShortTextInput(label = "Database Resource Name", description = "Write the DB Resource name configured in ScriptRunner'Resources' (I asume that is 'Product_Pool' but im not sure)")
String resource
@FieldPicker(label = "Product Id CustomField", description = "Select the Product ID CustomField")
Field productIdField
@FieldPicker(label = "Product Name CustomField", description = "Select the Product Name CustomField")
Field productNameField
@FieldPicker(label = "Start Date CustomField", description = "Select the Product ID CustomField")
Field startDateField
@UserPicker(label = "Admin User", description = "User with edit permissions in the project, usually a 'dummy' admin user")
ApplicationUser admin

DatabaseUtil.withSql(resource) { sql ->

IssueService issueService = ComponentAccessor.getIssueService()
CustomFieldManager customFieldManager = ComponentAccessor.getCustomFieldManager()
CustomField cfProductId = customFieldManager.getCustomFieldObject(productIdField.getId())
CustomField cfProductName = customFieldManager.getCustomFieldObject(productNameField.getId())
CustomField cfStartDate = customFieldManager.getCustomFieldObject(startDateField.getId())
String productId = issue.getCustomFieldValue(cfProductId).toString()

String query = "select productName, startDate from YOURTABLE where productId = ${productId}" //Change 'YOURTABLE' with the table that contains the info.

GroovyRowResult rowResult = sql.firstRow(query)

if (rowResult) {

String productName = rowResult.getProperty("productName")

Date startDate = rowResult.getProperty("startDate") as Date
TimeZone timeZone = ComponentAccessor.getComponent(TimeZoneManager).getTimeZoneforUser(admin)
String jiraTimeFormat = ComponentAccessor.getApplicationProperties().getString("jira.date.time.picker.java.format")
SimpleDateFormat simpleDateFormat = new SimpleDateFormat(jiraTimeFormat)
simpleDateFormat.setTimeZone(timeZone)
String formattedStartDate = simpleDateFormat.format(startDate)

IssueInputParameters issueInputParameters = issueService.newIssueInputParameters()
issueInputParameters.with {
addCustomFieldValue(cfProductName.getIdAsLong(), productName)
addCustomFieldValue(cfStartDate.getIdAsLong(), formattedStartDate)
}
IssueService.UpdateValidationResult updateValidationResult = issueService.validateUpdate(admin, issue.getId(), issueInputParameters)
if (updateValidationResult.isValid()) {
issueService.update(admin, updateValidationResult)
} else {
log.warn("Failed to update issue: ${issue.getKey()}: ${updateValidationResult.getErrorCollection()}")
}
}
}

 Regards

Thank you @Alejandro Suárez _Tecnofor_ for your response. I made it works. The team wants me to replace the Product Id custom field to the "Owner". I mean we want to have custom field "Owner" in frontend. Like when the requester enters the owner's name and in the backend, it should display product id and product name. I could simply switch the fields but the problem is there are two tables in the database, product, and customer, the product contains product information and the customer table have customer information, like firstName, LastName, id etc. I can query the database by using "JOIN" with a condition customerId=id and see both product and customer information.

How can I map the first and last name with the "Owner" field as a name?
How can I use the "JOIN" clause in the groovy script? Like right now it is ("SELECT * FROM Product WHERE productId = ?", value), how I can use name instead of product id in condition? 

I really appreciate your help.

Operational

  • Impact if not implemented
  • Type of change
  • Business Uni  Table
  • Communication plan (All Staff, or IT or External)

Update.....

The following script is working fine, whenever I type the FullName of the customer it'll display the desired results. 

The only issue I'm having that it is not working with a Database Picker Custom Field. Like I created a Database Picker custom field which is showing customers names in the dropdown, whenever I pick a name from the list it won't show any data when I use a normal custom text field and type customer name, it works perfectly. 

2 questions I have, to make this more useful for us.

1- How can you make the Database Picker custom field works with this script?

2- How I can make an autocomplete box? Like whenever customers start typing it should show the matching results where users can pick up the name instead of typing the complete name.

The reason I need one of those above fields because behaviour condition won't let the users to create a ticket unless they won't provide the correct name and some records contain 3 names, like first, middle and last name, I'll be doing the SQL part where I can put a clause that it displays the middle name too if customers have a middle name, but ticket creator always won't know that there are three names or 2. So a dropdown or autocomplete will be very helpful. I search online about it didn't find any Groovy script regarding the autocomplete. I'm not sure that I used the right wording for search.

import com.atlassian.jira.component.ComponentAccessor

import com.onresolve.scriptrunner.db.DatabaseUtil

import com.atlassian.jira.event.issue.IssueEvent

import com.atlassian.jira.issue.ModifiedValue

import com.atlassian.jira.issue.util.DefaultIssueChangeHolder

import java.sql.Timestamp

 

def event = event as IssueEvent

def issue = event.issue

def customFieldObjects = ComponentAccessor.customFieldManager.getCustomFieldObjects(issue)

 

def valueCF = customFieldObjects.findByName("Product Owner")

def productName = customFieldObjects.findByName("Product Name")

def productId = customFieldObjects.findByName("Product Id")

def value = issue.getCustomFieldValue(valueCF)

 

DatabaseUtil.withSql('Product_Pool ') { sql ->

def row = sql.firstRow("""SELECT Product.*, concat(FirstName,' ', LastName) as FullName, CustomerId
from Customer
left join Product on PersonId = CustomerId
having FullName like ?""" value)

if (row) {

 

productName.updateValue(null, issue, new ModifiedValue(issue.getCustomFieldValue(productName), row.'ProductName'), new DefaultIssueChangeHolder())

 

productId.updateValue(null, issue, new ModifiedValue(issue.getCustomFieldValue(productId), row.'ProductId'), new DefaultIssueChangeHolder())

}

}

 I really appreciate your help.

Suggest an answer

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

Admins, notify your Jira instance of system-wide changes with the new admin announcement banner

Hi All! We’re excited to share the launch of an announcement banner that lets Jira site administrators communicate directly to their users across their  Jira Cloud instance.  ...

595 views 15 18
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