How to sum data from customfield using jql and scriprunner

[Oleksiy Ohmush]

I try to use scriprunner jql search to find all issues i need and take a math sum off customfields in this issues.

How to use values from current issue in scriptrunner jql search? And how to sum customfields?

I find next:

import com.atlassian.jira.bc.issue.search.SearchService
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.search.SearchException
import com.atlassian.jira.web.bean.PagerFilter

def jqlSearch = "project = JIRA and created => current.issue.customfield_11200 and created <= current.issue.customfield 11300 and customfield_22100 = current.issue.customfield _22100" 

def user = ComponentAccessor.jiraAuthenticationContext.loggedInUser
def searchService = ComponentAccessor.getComponentOfType(SearchService)
SearchService.ParseResult parseResult = searchService.parseQuery(user, jqlSearch)

if (parseResult.isValid()) {
try {

sum+= sum of the value "customfield_14200"

}
} catch (SearchException e) {
e.printStackTrace()
}
} else {
log.warn("Invalid query")
return null
}

[Nic Brough -Adaptavist- · Accepted solution]

I tend to use the Jira API for this stuff, the calls you're looking for can be seen in action in this "scripted field" code: https://library.adaptavist.com/entity/calculate-the-sum-of-fields-from-multiple-issues-from-a-jql-query  (note, remember to switch to the "server" tab)

[Oleksiy Ohmush]

I receive 404 error on this page.

[Oleksiy Ohmush]

Yes, its great, last thing i try to find is how to use values from current issue customfields in jql 

[Oleksiy Ohmush]

But i have this error:

WARN [common.UserScriptEndpoint]: Script console script failed: groovy.lang.MissingPropertyException: No such property: issue for class: Script15 at Script15.run(Script15.groovy:7)

[Oleksiy Ohmush]

Sorry, i understand it will work in field but in ScriptConsole its error

[Peter-Dave Sheehan]

If you want to try in the script console, you can get your issue object like this:

def issue = ComponentAccessor.issueManager.getIssueObject("ABC-123")

[Peter-Dave Sheehan · Accepted solution]

Where is this code being used? DO you already have  "issue" in the binding (like you would for workflow)?

Let's assume you have a valid issue object.

To get the values of custom fields, you must first obtain the custom field object, Then, you can get the value from the issue.

def customFieldManager = ComponentAccessor.customFieldManager
def cf11200= customFieldManager.getCustomFieldObject(11200)
def cf11300= customFieldManager.getCustomFieldObject(11300)
def cf22100= customFieldManager.getCustomFieldObject(22100)
//or def yourCfObject = customFieldManager.getCustomFieldObjectByName("the name of your cf")
def cf11200Val = issue.getCustomFieldValue(cf11200) 
def cf11300Val = issue.getCustomFieldValue(cf11300) 
def cf22100Val = issue.getCustomFieldValue(cf22100) 

def jqlSearch = "project = JIRA and created => $cf11200Val and created <= $cf11300Val and customfield_22100 = $cf22100"

Note that in the case of select fields, the "myvalue" will be an option object with id and value attributes. 

[Oleksiy Ohmush]

I will use scriptedfield in issue, it will take values from current issue to use it in jql, calculate sum of values all issues by jql and displays sum value

[Oleksiy Ohmush]

def s = customFieldManager.getCustomFieldObject(11037) 
def en = customFieldManager.getCustomFieldObject(11603) 
def l = customFieldManager.getCustomFieldObject(11405) 

def jqlSearch = "project = JIRA and cf[11405] ~ $l and created >= $s AND created <= $en AND status = Closed AND resolution = Done" 

invalid jql search. Maybe i did something wrong with syntax?

[Peter-Dave Sheehan]

First, in want you most recently pasted, you are missing the code to actually get the value.

This line:

def s = customFieldManager.getCustomFieldObject(11037) 

Only returns the customfield object. You have to get the value separately from the issue

def s_value = issue.getCustomFieldValue(s)

Then, if you will build  JQL string, you need to make sure you transform the value into a format accepted by the JQL. In this case, looks like you are using dates.

def s = customFieldManager.getCustomFieldObject(11037) 
def en = customFieldManager.getCustomFieldObject(11603) 
def l = customFieldManager.getCustomFieldObject(11405) 
def v_s = issue.getCustomFieldValue(s)?.format("YYYY-MM-dd")
def v_en = issue.getCustomFieldValue(en)?.format("YYYY-MM-dd")
def v_l = issue.getCustomFieldValue(l)

if(v_s && v_en && v_l){
   def jqlSearch = "project = JIRA and cf[11405] ~ "$v_l" and created >= $v_s AND created <= $v_en AND status = Closed AND resolution = Done" 
} else {
   log.warn "One of custom fields (11037, 11603, 11405) is empty, JQL would be invalid"
}

[Oleksiy Ohmush]

Thanks you a lot for help!