It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

How to sum data from customfield using jql and scriprunner Edited

Oleksiy Ohmush Apr 21, 2019

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
}

2 answers

2 accepted

1 vote
Answer accepted
Peter-Dave Sheehan Apr 22, 2019 • edited

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 Apr 22, 2019

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 Apr 23, 2019
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 Apr 23, 2019

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 Apr 24, 2019

Thanks you a lot for help!

1 vote
Answer accepted
Nic Brough [Adaptavist] Community Leader Apr 22, 2019

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 Apr 22, 2019

I receive 404 error on this page.

Oleksiy Ohmush Apr 22, 2019 • edited

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

Oleksiy Ohmush Apr 22, 2019

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)

Image 1.png

Oleksiy Ohmush Apr 23, 2019 • edited

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

Peter-Dave Sheehan Apr 23, 2019

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

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

Suggest an answer

Log in or Sign up to answer
This widget could not be displayed.
This widget could not be displayed.
Community showcase
Published in Jira

Try Jira Cloud for Outlook: Organize your work without leaving your inbox

Hi Atlassian community, My name is Max and I work on the product integration team at Atlassian. I am pleased to announce the early access program for the Jira Cloud add-in for Outlook. This add-in...

2,018 views 6 15
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you