how to script a rolling budget field

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.  

2 answers

1 accepted

0 vote
Tarun Sapra Community Champion Dec 13, 2017

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.

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 Community Champion Dec 13, 2017

what's the error as the code looks fine 

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:

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

Thanks,

Steve

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.

working_script.png

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)

is that because expenses is a scripted field? 

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?

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??

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

order request.PNGProject budget.PNGHere you go. Thanks again. 

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.

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 ($)” :-)

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

rolling budget script.PNG

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. budget SH.PNGand 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)

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. 

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?

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

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)

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 :$

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

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?

Rolling budget link.PNGHere is what was returned. 

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") {

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

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.

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.

 Capture.PNG

 

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??

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)

Capture.PNG

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. 

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

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

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published yesterday in Jira

Mission-critical battery manufacturer fulfills FAA software requirements with Commit Policy Plugin

EaglePicher Technologies is a leading manufacturer of battery systems for diverse industries like defense, aviation, space or medical. As they operate in highly regulated industries, keeping a clear ...

155 views 0 2
Read article

Atlassian User Groups

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

Find a group

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

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you