How can I get existing values for a custom field in ScriptRunner?

Question

I have a Jira service desk set up, and I want to validate users' entries in the service desk fields. For example, I have text field and I want the users to only be allowed to enter unique values. So if user A submits a request with "My text" for the field then I want no other users to be able to successfully submit a request with "My text" in that field.

I currently have a Custom Script Validator to run code logic on user entries, but I cannot figure out how to retrieve the values previously entered by other users. If I could do that then I would be able to check 'is the value of this field in this list of existing values?' and deny the creation of the issue accordingly.

Is there a way to do this, or if not, what might my other options be for getting the desired behavior?

PD Sheehan · Answer

I would simply execute a JQL search in your validator. Since you field is a text field, you must use a match operator (~). Then take all the matching issues and further refine them with an exact comparison.

Here is a sample script (custom scriped validator):

import com.adaptavist.hapi.jira.issues.Issues
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.Issue
import com.opensymphony.workflow.InvalidInputException

def cfm =ComponentAccessor.customFieldManager
def uniqueFieldName = 'unique field name'
def uniqueField = cfm.getCustomFieldObjectsByName(uniqueFieldName)[0]

def currentValue = issue.getCustomerFieldValue(uniqueField)

def jql = """project=$issue.projectObject.key and cf[$uniqueField.idAsLong] ~ "$currentValue" """
def matchingIssues = Issues.search(jql).collect() as List<Issue>
def exactMatchIssues = matchingIssues.findAll { it.getCustomFieldValue(uniqueField) == currentValue }
if (exactMatchIssues) {
throw new InvalidInputException(uniqueField.hiddenFieldId, "${exactMatchIssues*.key} already contains '$currentValue' in '$uniqueField.name'. This field must be unique accross all issues.")
}

Gikku · Answer

You are looking the custom field values, right?

You can query the customfieldvalue table with filter customfield = <ID>, which you can find from the UI.

john history · Answer

The issue with this solution is that I would have to track down all existing values to manually enter as hidden options.

I did more investigation into reading from the database and it looks like the customfield table does not contain all the custom fields. I don't know where these missing custom fields are being stored. They show up in the UI but not in the database (at least in that table).