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

[john history]

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]

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]

@john history Not a straight forward suggestion, but the below might work.

• Create a select list and make it hidden from the screens
• Whenever an issue is created with a value for the text field, add that value as an option to the select list
• Use the options currently available on the select list field to validate if the value entered on the text field is unique or not

[john history]

I like that idea. Its very simplistic. I will give it a try today.

[john history]

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).

[Gikku]

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.