Calculate the Sum of custom field across projects

[anthony_butler]

Hi,

I have two projects, Incident Management and Problem Management. In the Incident Management project I have a field titled 'Total Agent Hours' that we fill in with a numeric value at the end of every incident to reflect how many hours it has cost us from an end user CS agent perspective. We then link our incidents to problem tickets in the Problem Management project. One problem can have multiple incidents linked to it if necessary. 

I would like to be able to set up a rule or automation that takes the 'Total Agent Hours' value from the incident tickets and returns a sum of them on the problem ticket. I would like this to update each time a new incident ticket is linked to the problem ticket so as to show a running total. For example:

Incident ticket 123 has a value of 10 agent hours, when it is linked to problem ticket 123 the value of 10 is shown in the agent hours field.

Incident ticket 345 has a value of 20 agent hours and is subsequently linked to problem ticket 123. The value in he agent hours field should now read 30. 

Is it possible to set this up using Project Automation? We do have scriptrunner installed, but this seems more complex to set up and I am the only one that can set these things up and I'm not good with scriptrunner! 

Many thanks for your help.

[John Funk · Accepted solution]

Hi Anthony,

You might be able to use Automation For Jira for that. Take a look at this post and see Rule #2 as a guide. 

https://community.atlassian.com/t5/Jira-Software-discussions/3-Jira-automation-rules-we-will-cover-in-our-advanced-webinar/m-p/1398109#M2801%3

[anthony_butler]

Thanks John,

This looks like it might be a solution however, when I tried on Friday last week to make it work, I can only get it to remove a value from the field rather than adding it in. For example, 

I manually added in the value of 0 to the field in the Problem ticket. I ran this rule and updated the same field in a linked Incident ticket to 40. The rule told me it ran successfully (in the audit log) but when I went to the Problem ticket I found that the field had been cleared?

The only thing I changed was in the edit field section to match the field I needed to look up:

Any ideas where I'm going wrong?

Thanks,

[anthony_butler]

Thought I should also include a screen grab of the whole rule in case I've really messed it up! :) 

[John Funk]

Why do you have issue.inicident.Incident Cost and not just {{issue.Incident Cost? I think you have an extra incident word in there. 

[anthony_butler]

Hi @John Funk thanks. No idea how that got in there! I've removed but the rule still doesn't pull the agent hours from the incident to the problem. The audit log doesn't show any errors which makes me think the rule is working but just not doing what I want it to do?

Any ideas?

[John Funk]

Maybe it's a syntax thing. This is the format I use:

{{#=}}{{issue.customfield_16719}}-{{issue.customfield_17252}}{{/}}

[anthony_butler]

Thanks four your help @John Funk really appreciate it. I'm now getting errors if I try and use the above (with my custom field id's).

I've tried a variation of both:

{{#=}}{{issue.customfield_14962}}-{{issue.customfield_14962}}{{/}}

and

{{#=}}{{issue.customfield_14962}}+{{issue.customfield_14962}}{{/}}

and for both I get the reult in audit log of 'Some Errors' and under the details I have the error of 'Missing parameter(s) for operator -u: -/+' (depending on the code above).

Sorry to be a massive pain, but anything else you can suggest? 

[John Funk]

In your example above you are subtracting (or adding) the same field from itself. Try a different number field for one of them and see if that is better. 

[anthony_butler]

Thanks, I still get the same error when doing that too. I'm beggining to wonder if this is actually possible in Jira or whether or not I should look into eazyBI (we have that installed) to see if I could do it that way. It's not ideal but at least it would give us the information at a glance. Or even if Scriptrunner is something that I should explore more (I'm not very good with that either!) 

Do you have any suggestions as to how else I might be able to get this?

[John Funk]

That's really weird because it works fine for mine. 

I would go ahead and open a support ticket with Atlassian:

https://support.atlassian.com/contact/#/

Then please come back here and update with the resolution so we can help future folks, and can close this post out. 

[anthony_butler]

Thanks I will do

[John Funk]

Anthony - can you post the latest rule that you have and what is not working with it? 

[anthony_butler]

Hi @John Funk 

I've revisited this and have found that the calculation above is actually grabbing the numerical value in the field on the problem ticket (PMT) and adding it to itself. Rather than getting the numerical value from the incident ticket (IMT) and adding to the value in the PMT ticket. For example:

PMT ticket has 5 in the field

IMT ticket has 10 in the field

The calculation should take 10 and add it to 5 equalling 15

What is is actually doing is taking 5 and adding 5 and coming up with 10. 

I need something to differentiate between the incident ticket and the problem ticket in the code used. Is that possible?

[John Funk]

Probably so as along as the two tickets are related in some way. Can you post the rule that you have? 

[anthony_butler]

Hi thanks for the reply,

My rule is as follows:

Breakdown of steps:

As you can see I've tried looking at things like destinationissue.xxx and that is giving me the same result as issue.xxx

The tickets in Jira are linked by the link type 'is Problem Ticket for' / 'is linked to Problem ticket'

[John Funk]

For the branch, try using For Linked Issues instead of Current Issue. 

Then use {{triggerIssue.Incident Cost - Lost Agent Hours}}+{{issue.Incident Cost - Lost Agent Hours}}

[anthony_butler]

That works an absolute charm. Thanks so much John. 

[John Funk]

Fantastic!

[anthony_butler]

Can anyone suggest anything that could solve this with Scriptrunner?

[Prince Nyeche]

Hi @anthony_butler 

Welcome to community! For Project Automation, if i'm understanding this correctly, we're looking at just a single field but different Project is it? If so I believe script runner might be a more suitable candidate for this use case. As i'm now wondering how to does the Automation know which tickets to calculate or will this be pre-determined?

[anthony_butler]

Hi thanks for your reply. The tickets would come from a pre-determined project but would be different references. 

I'm happy to work with scriptrunner if you can offer any assistance with that side of things?

[anthony_butler]

@Prince Nyeche I'm still struggling to get this done, do you have any suggestions at all? All of the advice I've been given so far, whilst it is brilliant, sadly hasn't worked. 

[Prince Nyeche]

Hi @anthony_butler 

Although Script runner  is an ideal candidate, I don't have a workable example out of the box I could use to explain this and Groovy isn't my strong suit yet so wouldn't be able to help much here with the coding aspect of it.