getPlaceholderString in ScriptRunner

[Mojtaba Mansouri]

Hi everyone,

I am trying to link a field in Jira to SQL database. I would like to filter the values based on another field called "Location". The script works fine  during initial selection but the list can not be modified afterwards so if the user had forgot to add an asset they have to delete all previously selected assets and re-enter them. I am not sure how I can use original values to be passed in new search query. Here is my code that works initially but throws error after updating:

---

---

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.Issue
import com.atlassian.jira.issue.customfields.option.Option
import com.onresolve.scriptrunner.canned.jira.fields.editable.database.SqlWithParameters
 
def customFieldManager = ComponentAccessor.customFieldManager

getSearchSql = { String inputValue, Issue issue, String originalValue ->
    def customField = customFieldManager.getCustomFieldObjects(issue).findByName('Location')
    def Lab_Location = issue.getCustomFieldValue(customField) as Option
 
    new SqlWithParameters(
        "select ID, Local_Name, Lab_Location from assets where lower(Local_Name) like concat('%', lower(?), '%') and Lab_Location = ?",
        [inputValue, Lab_Location?.value]
    )
}
 
getValidationSql = { String id, Issue issue, String originalValue ->
    def customField = customFieldManager.getCustomFieldObjects(issue).findByName('Location')
    def Lab_Location = issue.getCustomFieldValue(customField) as Option
 
    new SqlWithParameters("select ID, Local_Name, Lab_Location from assets where ID = CAST(? AS SIGNED) and Lab_Location = ?", [id, Lab_Location?.value])
}

[Richard Murry]

@Mojtaba MansouriI also find this difficult to configure, but I was able to get the following code to work for my database picker. The parent field is 'Purchase Order'. The child field (this field) is the line items on the Purchase Order.

getSearchSql = { String inputValue, Issue issue ->

    def customField = customFieldManager.getCustomFieldObjects(issue).findByName('Purchase Orders')

    def PONum = issue.getCustomFieldValue(customField)

    def placeholders = getPlaceholderString(PONum)

    new SqlWithParameters("select PO_ID, PO_LN_NO from PO_LN where PO_ID IN ($placeholders) AND CONCAT(PO_ID, ' Line ', PO_LN_NO) like CONCAT(? ,'%')"

                          , [*PONum, inputValue])

}

I think the key here is that your parent custom field's value is parsed out in the getPlaceholderString method provided by SR. Then, that string is used in the query by the use of the GString $placeholders. The size of this string is dynamically changed based on how many values are selected in the parent field.

Hopefully that helps!