how to script a rolling budget field

[Scott Federman]

Hey @Tarun Sapra here it is. This has been a very illusive request. The scenario is that I need to script a rolling budget field. 

I have an issue type "Project Budget". This issue type has a "Budget" number field and a scripted "Remaining Budget" field.

There is another issue type called "Order Request" which contains a scripted "Expense" field. 

These to issue types are linked using the "Related" link type.

I need to have the "Expense" from all of the order request issues that are linked to the project budget issue subtracted from the "Budget" field and return the "Remaining Budget" in the project budget issue.  

[Stephen Cheesley _Adaptavist_ · Accepted solution]

Hi Scott,

As per your question here (https://community.atlassian.com/t5/Jira-questions/Listener-script-needed-for-linked-issues/qaq-p/683466#M225230) which I believe is the same, I have tested the script that I gave you (above) in my local instance.

I have managed to get it to work, here is the same script with the field names changed that I had working in my local environment:

import com.atlassian.jira.component.ComponentAccessor

def issueLinkManager = ComponentAccessor.getIssueLinkManager()
def customFieldMgr = ComponentAccessor.getCustomFieldManager()

def budgetField = customFieldMgr.getCustomFieldObjectByName("Budget")
def oeField = customFieldMgr.getCustomFieldObjectByName("Expense")

// Get all of the linked issues for the project budget "issue"
def total = 0
issueLinkManager.getOutwardLinks(issue.id).each {issueLink ->
    if (issueLink.issueLinkType.name == "Relates") {
        // Linked Issue is going to be your order request
        def linkedIssue = issueLink.destinationObject
        // Get the Budget number field
        total += linkedIssue.getCustomFieldValue(oeField) as Double
    }
}

// Now get the budget field value
def budgetVal = issue.getCustomFieldValue(budgetField) as Double

return budgetVal - total

The setup for my field is:

 If you want to let me know what issues you are having then I am happy to try to help you through them.

Thanks,

Steve

[Stephen Cheesley _Adaptavist_]

Just a quick update, here is a screenshot of it working on a basic test project. The issue that has the expense has a value of 5.

[Scott Federman]

Hey Steve, Thank you for that. Its returning the same number thats in the budget field. 

Here is the error im seeing in the log. 

Time (on server): Wed Dec 13 2017 09:39:14 GMT-0600 (Central Standard Time)

The following log information was produced by this execution. Use statements like:log.info("...") to record logging information.

2017-12-13 08:39:14,016 ERROR [customfield.GroovyCustomField]: *************************************************************************************
Script field failed on issue: OPS-616, field: Remaining Budget
java.lang.NullPointerException: Cannot invoke method minus() on null object
 at Script117.run(Script117.groovy:23)

[Scott Federman]

is that because expenses is a scripted field? 

[Scott Federman]

Okay thats what im looking for on mine. Is the issue that has that expense a different issue than the one that contains the budget? 

Budget and Remaining are on "Project Budget" issue type and expense is on  "Order Request" issue type, linked by Relates link type.

So to make sure im on the same page The example you show is taking the expense from the linked order request and subtracting it from the budget on the project budget issue?

[Stephen Cheesley _Adaptavist_]

Hi Scott,

Thanks for that error message. That's super helpful. This means that one of the values in the return statement is null. Either it can't get the budget field value OR the total has come back as null.

Do you have other Relates links that aren't for Order Request issues? Can you send me a screenshot of your Order Request Issue and your "Project Budget" issue??

[Stephen Cheesley _Adaptavist_]

I have updated the script to be a bit more robust. When we were getting the expense field we weren't checking to see if it existed before we were adding it ;-)

import com.atlassian.jira.component.ComponentAccessor

def issueLinkManager = ComponentAccessor.getIssueLinkManager()
def customFieldMgr = ComponentAccessor.getCustomFieldManager()

def budgetField = customFieldMgr.getCustomFieldObjectByName("Budget")
def oeField = customFieldMgr.getCustomFieldObjectByName("Expense")

// Get all of the linked issues for the project budget "issue"
def total = 0
issueLinkManager.getOutwardLinks(issue.id).each {issueLink ->
 if (issueLink.issueLinkType.name == "Relates") {
 // Linked Issue is going to be your order request
 def linkedIssue = issueLink.destinationObject
 // Get the Budget number field
 if (linkedIssue.getCustomFieldValue(oeField)) {
 total += linkedIssue.getCustomFieldValue(oeField) as Double
 }
 }
}

// Now get the budget field value
def budgetVal = issue.getCustomFieldValue(budgetField) as Double

return budgetVal - total

This could explain your issue. Please use this script and run again :-)

[Scott Federman]

Here you go. Thanks again. 

[Scott Federman]

I also just realized you are pulling  expense not Total Order Expense ($). Will that make a difference? The Expense field is on the the subtask of the order request.

[Stephen Cheesley _Adaptavist_]

That will make a big difference, in my test setup I called the field “Expense” for simplicity’s sake. I can see from your given examples that you want to use “Total Order Expense ($)” :-)

[Scott Federman]

If i use that field the remaining budget field disappears and i get this error:

Time (on server): Wed Dec 13 2017 11:04:31 GMT-0600 (Central Standard Time)

The following log information was produced by this execution. Use statements like:log.info("...") to record logging information.

2017-12-13 10:04:31,987 ERROR [customfield.GroovyCustomField]: *************************************************************************************
Script field failed on issue: OPS-563, field: Remaining Budget
org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed:
Script171.groovy: 7: illegal string body character after dollar sign;
   solution: either escape a literal dollar sign "\$5" or bracket the value expression "${5}" @ line 7, column 56.
   Mgr.getCustomFieldObjectByName("Total Or
                                 ^
1 error

[Scott Federman]

i just realized i needed to add the slash before the $. I did this and the remaining budget field cam back but it is still showing the same number as the budget itself. and im still returning this error

2017-12-13 10:10:05,187 ERROR [customfield.GroovyCustomField]: *************************************************************************************
Script field failed on issue: OPS-616, field: Remaining Budget
java.lang.NullPointerException: Cannot invoke method minus() on null object
 at Script184.run(Script184.groovy:25)

[Stephen Cheesley _Adaptavist_]

Nice catch with the dollar sign ;-)

There are two things here that don't quite line up. In your error, it states:

Script field failed on issue: OPS-616

The error in your screenshot the parent issue is OPS-563 and the linked order is OPS-615. This would indicate that this is for another issue.

The fact that a number is being returned for Remaining Budget, suggests that the script is running fine, but that it is not picking up the linked order. Is there a way to confirm the link type of the linked order? This does not look like a standard JIRA link. 

[Scott Federman]

yes i was just looking at it and realized the outward link type is "is related to" so i changed that and im no longer throwing errors but i am still returning the same value as the budget field. 

 Could this be because its looking for the total order expense ($) which is a scripted field that is generated by summing up the expense field in the subtask, which is a calculated field based on quantity and unit price?

[Stephen Cheesley _Adaptavist_]

Hi Scott,

I think we're nearly there. It's good that we've got to the point that the script runs and returns a value.

The most likely thing here is that the script is not picking up your link. (If your total order expense field is returning a value, it should be fine to use).

I have written a script that will take an audit of all of the linked issues on a given issue. This will allow us to confirm that the link definitely exists in an expected state.

Please run the script below in you Script Console and send me back a screenshot of the output. The expected output is a list of all of the links for the issue with ID OPS-563 which is the project budget from your last screenshot.

import com.atlassian.jira.component.ComponentAccessor

// Get the issue that we wish to check the links for
def issueMgr = ComponentAccessor.getIssueManager()
def issue = issueMgr.getIssueObject("FED-1") //<-- Change this to match the issue key you want to check on...

def issueLinkManager = ComponentAccessor.getIssueLinkManager()

// Grab all links and add them to the output
List outputRows = []
issueLinkManager.getOutwardLinks(issue.id).each {issueLink ->
 def details = [
 "<ul>",
 "<li><b>Linked Issue: </b>${issueLink.destinationObject.key}</li>",
 "</ul>"
 ]

 def output = [
 "<td>${issueLink.issueLinkType.id}</td>",
 "<td>${issueLink.issueLinkType.name}</td>",
 "<td>OUTWARD</td>",
 "<td>${details.join("")}</td>"
 ]

 outputRows.add("<tr>${output.join("")}</tr>")
}

issueLinkManager.getInwardLinks(issue.id).each {issueLink ->
 def details = [
 "<ul>",
 "<li><b>Linked Issue: </b>${issueLink.destinationObject.key}</li>",
 "</ul>"
 ]

 def output = [
 "<td>${issueLink.issueLinkType.id}</td>",
 "<td>${issueLink.issueLinkType.name}</td>",
 "<td>INWARD</td>",
 "<td>${details.join("")}</td>"
 ]

 outputRows.add("<tr>${output.join("")}</tr>")
}

def tableHeader = [
 "<th>Link ID</th>",
 "<th>Name</th>",
 "<th>Direction</th>",
 "<th>Details</th>"
].join("")

"<table class=\"aui\"><tr>${tableHeader}</tr>${outputRows.join("")}</table>"

Link Audit: Run the following script as-is in Script Console

[Scott Federman]

So i ran the script and got an error

2017-12-14 09:11:57,626 WARN [common.UserScriptEndpoint]: Script console script failed: java.lang.NullPointerException: Cannot get property 'id' on null object at Script34.run(Script34.groovy:11)

[Stephen Cheesley _Adaptavist_]

Hi Scott,

Apologies for the late reply, this is my goof. I told you to run it as-is but then I didn't change the issue key (faceplam).

You need to update this line:

def issue = issueMgr.getIssueObject("FED-1") //<-- Change this to match the issue key you want to check on...

Change "FED-1" to "OPS-563"

Thanks :$

[Scott Federman]

so the interesting thing is now its returning the budget number in the remaining budget field. Not showing the actual remaining budget. 

[Stephen Cheesley _Adaptavist_]

Q1: Did you run the script in the script console? What was the output?

Q2: "so the interesting thing is now its returning the budget number in the remaining budget field" - Have you made any amendments to the script?

[Scott Federman]

Here is what was returned. 

[Stephen Cheesley _Adaptavist_]

Hi Scott,

That would explain the issue. The link name isn't "Relates" as we have in the script. It is "Related".

Does it work if you try changing this line:

if (issueLink.issueLinkType.name == "Relates") {

to

if (issueLink.issueLinkType.name == "Related") {

[Scott Federman]

no more errors but same result, it just reflects the number in the budget field. 

[Stephen Cheesley _Adaptavist_]

Hi Scott,

Apologies for my late reply, I have been on vacation. I have found the issue that your having here.

You are getting the number in the budget field because the script is not subtracting any of the related issues from it. That is happening because it can't find the link.

Answer / Solution

The reason that is not happening is because in the script I provided we are only looking at outward links. In the script output that you attached on the 20th of December you only appear to have "Inward" links on the issue. If you want to check inward and outward links you need to update the script to reflect this...

issueLinkManager.getOutwardLinks(issue.id).each {issueLink ->

You can use getInwardLinks instead of getOutwardLinks if you just want to check inward links.

[Scott Federman]

Hey Stephen, welcome back. Hope you had a great holiday and vacation. 

SO i looked at that as well as the result did show inward linking, however the script itself says its looking for outward just as you suggested.

 

[Stephen Cheesley _Adaptavist_]

Absolutely, that's my point. I didn't realise before that we were dealing with inward links. Do you get the expected result if you switch the script to look for inward links??

[Scott Federman]

Nope. Unfortunately. It does the same exact thing. But now it throws the error

The following log information was produced by this execution. Use statements like:log.info("...") to record logging information.

2018-01-03 09:22:01,455 ERROR [customfield.GroovyCustomField]: *************************************************************************************
Script field failed on issue: OPS-728, field: Remaining Budget
java.lang.NullPointerException: Cannot invoke method minus() on null object
 at Script96.run(Script96.groovy:25)

[Stephen Cheesley _Adaptavist_]

I’ve setup a mock environment with your use case again to get this straight. I’ve spotted two issues, which I hope that when solved, will fix this script for you.

Null Pointer Exception

The null pointer exception you are getting states:

Cannot invoke method minus() on null object

I am certain that this is because it cannot find a value in the budget field on the “Project Budget” issue that the field is running on. I have reproduced this issue in my local environment, and get the same error as you. As soon as I put the budget value back in, I get the correct values and response back. You need to triple check that the issue you are testing with, definitely has a value in the budget field.

Incorrect Value

We have had a continuing theme that we can’t seem to get the subtraction to work. You always appear to end up with the “Remaining Budget” matching the “Budget”. I’ve done some investigation on this and this is happening because we are looking at the wrong side of the budget for the expense value.

In order to solve this issue, if you switch to looking for inward links, then you need to change this line:

def linkedIssue = issueLink.destinationObject

To this line:

def linkedIssue = issueLink.sourceObject

I hope this helps clear up your issue. 

[Scott Federman]

ding ding ding!!!! Nice one. It is working. Thank you for everything on this. HUGE HELP. 

[Stephen Cheesley _Adaptavist_]

Yey!!! We got there in the end :-D

Just a couple of follow up points. I didn't think to put it in, but you might want to add some null pointer checking if your use case allows for the expectation of nulls. It's not critical but good practice.

You might also want to come up with a mechanism for checking for outward and inward links at the same time.

If you've found this answer helpful, feel free to accept and like ;-)

[Tarun Sapra · Accepted solution]

Hello @Scott Federman

If you follow these steps then it should do the trick, basically the script will need to be in the scripted field "Remaining Budget"

Use following pseudo algo in the code

1) Fetch all the linked Issues in the script using this API 

https://developer.atlassian.com/static/javadoc/jira/latest/reference/com/atlassian/jira/issue/link/IssueLinkManager.html#getLinkCollectionOverrideSecurity(com.atlassian.jira.issue.Issue)

2) Now, you will have all the linked Issues, then from the linked issues fetch the "Expense" field and do a summation in a loop i.e.

if(expense field is not empty) , then

totalExpense+= expense

3) Now, you have the totalExpense , thus you now need to do return the following in the scripted field - "Remaining Budget"

return (current issue "budget" - totalExpense)

And this should give you the desired value.

[Scott Federman]

heres what ive been trying to do. but it hasn't worked out. Any ideas?

def issueLinkManager = ComponentAccessor.getIssueLinkManager()
def customFieldMgr = ComponentAccessor.getCustomFieldManager()

def budgetField = customFieldMgr.getCustomFieldObjectByName("Budget")
def oeField = customFieldMgr.getCustomFieldObjectByName("total order expense (\$ )")

// Get all of the linked issues for the project budget "issue"
def total = 0
issueLinkManager.getOutwardLinks(issue.id).each {issueLink ->
    if (issueLink.issueLinkType.name == "Related") {
        // Linked Issue is going to be your order request
        def linkedIssue = issueLink.destinationObject
          // Get the Budget number field
        total += linkedIssue.getCustomFieldValue(oeField) as Double
    }
}

// Now get the budget field value
def budgetVal = issue.getCustomFieldValue(budgetField) as Double

return budgetVal - total

[Tarun Sapra]

what's the error as the code looks fine