Restrict issue creation with certain symbols

[Phil Evans]

Hi.

I want to restrict creation of issues from portal with certain symbols in Summary and Description.

I can put Simple Scripted Validator on workflow creation with

!issue.summary.value.contains("«") && !issue.summary.value.contains("»")

and it works, but how do i use this restriction only for portal request issues creation?

Or please advise how can i use this in Behaviours?

[Alejandro Suárez García]

@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

[Shah Baloch]

Thank you @Alejandro Suárez García 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.

[Shah Baloch]

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.