I am trying to use the Database Picker custom field to present a drop down single-selection list for my users to choose 1 approver to remove at a time and associate a justification of removal for each. I have the following query set in the Search SQL field:
select cfv.id, upper(app_user.lower_user_name), issue
from customfieldvalue cfv
JOIN app_user on app_user.user_key = cfv.stringvalue
where customfield IN ( select id from customfield where cfname = 'Approvers' ) and app_user.lower_user_name like ? || '%'
;
The problem is that it's returning the full list of Approvers from all my tickets, whereas I am trying to only return the approvers for the issue that the field is on.
I've tried the customizations, but it did not appear to have any effect:
import com.atlassian.jira.issue.Issue
import com.onresolve.scriptrunner.canned.jira.fields.editable.database.SqlWithParameters
getSearchSql = { String inputValue, Issue issue, String originalValue ->
// return SqlWithParameters, that will control the search SQL..., eg:
new SqlWithParameters("select cfv.id, upper(app_user.lower_user_name), issue " +
"from customfieldvalue cfv " +
"JOIN app_user on app_user.user_key = cfv.stringvalue " +
"where issue = ? and customfield IN ( select id from customfield where cfname = 'Approvers' ) and app_user.lower_user_name like ? || '%';", [issue.getId() , inputValue])
}
Either method does not change the results and outputs the complete list of approvers from all issues.
Any help would be greatly appreciated.
