Jira Cloud automation rule to sum custom field value from JQL query

[Anthony B]

Hello,

I would like to trigger an automation rule that executes a JQL query and then sums a particular custom field (numerical), putting this value into a new field.

In one of our projects we capture an estimate score in the sub-tasks.  Every quarter we look at a given set of these sub-tasks and would like to compare the sum of all estimates with what we believe to be the capacity for a given delivery team.

I can create a JQL query in Automation but I don't know if it returns all results and nor do I know how to use smart values to sum a particular field, if possible.

Any help / suggestions are greatly appreciated.

Many thanks in advance

[Bill Sheboy]

Hi @Anthony B 

Yes, you can do that.  What you would need is:

• Trigger: what you want to start the rule, such as manually run
• Action: lookup issues using your JQL
• Action: edit your destination field, setting the value to the sum of the values from the lookup issues

For example, say you wanted to sum a field you call myEstimate, it would be:

{{lookupIssues.myEstimate.sum}}

Here are some documentation references to help you:

• list handling:  https://support.atlassian.com/jira-software-cloud/docs/smart-values-lists/
• lookupIssues handling: https://support.atlassian.com/jira-software-cloud/docs/smart-values-general/
• math functions: https://support.atlassian.com/jira-software-cloud/docs/smart-values-math-expressions/

Best regards,

Bill

[Anthony B]

Hi @Bill Sheboy ,

Thanks for getting back to me.  I did try your suggestion previously but from the documentation it would appear that custom fields are not supported with the .sum function - apparently it only works with the inbuilt 'Story Points' field.

I tried it again with a simple rule but it didn't work - see screenshot below

My workaround was simply to copy the Estimate value into the 'Story Points' field whenever an estimate was added/updated/removed, and run the .sum function on the 'Story Points'.  It doesn't feel very efficient, but seems to have done the trick.

If you can see that I am doing something wrong in the screenshot, do let me know :)

Many thanks

[Bill Sheboy]

Hi, and oops...my bad.  The lookup Issues feature only supports a subset of fields.  So sum() isn't the problem.  ( FYI if you want to vote for adding more fields to lookup, please review this suggestion: https://jira.atlassian.com/browse/JRACLOUD-75018 )

Per the documentation, lookupIssues doesn't support Estimate either, so your rule would need to put the custom field value in a supported field, such as Story Point, and then use sum().  That is why your rule doesn't work.

Let's pause for a second: how often do you need to do this?  I noted once-per-quarter up above, so I wonder if instead you could just create a filter, save it, subscribe to the filter quarterly, past the results from the email to a spreadsheet to sum the results.

__Bill

[Anthony B]

Hi @Bill Sheboy , thanks for the info and I have voted to have more fields added :) 

The information is used quarterly, but needs to be updated regularly in the lead up to our planning session(s).  Personally I wish to avoid extracting information to another system / tool, so I have gone with the approach of copying the values to the Story Points field for the time being.

Thanks for your help!