How Can I Sum Up Multiple Issues into a Parent Number Field?

[#1]

Whether it be an Epic and its children or a Task/Story and it's sub-tasks, we often have need to sum up the value of a number field to the parent. The question is, how do we iterate over all the children at one time, get the values and add them all up at the same time? 

The Answer

The answer is an automation rule using the lookup issues functionality. If you haven't tried a rule using lookup, now is a great time to give it a try with the sample below. 

Just for fun, let's throw a couple of things in the mix. Let's say you are hosting an event and charge admission. You set up a Jira Service Management project with a nice form to capture registrations. You have different ticket prices available for different levels of the event. Users select a level with a price/cost, complete the registration information and submit the request. The user either sees a link to make the payment or gets a separate email. 

As a result, you spin off separate issues in a Business project for each registration so your team can process information without the expensive Agent licenses. 

Now, before you get too excited, I am not showing you how to do all of that here! Might be a subject for a future article though. :-)

The Event

Though typically very humble, we were able to talk @Jack Brickey  into doing an autograph session. We setup 3 tiers of access - Standard (autograph and picture with Jack - $50) Gold (Standard plus a group cocktail hour with Jack - $75), and VIP (Gold plus dinner with Jack - $100).

Here's the parameters though - the event space only holds 120 people before the Fire Marshall shuts it down. And Jack's humble fee to just to get him in the door is $5,000. So, we need to make at least that much in revenue and not go over the limit because of a bunch of cheap Standard tickets. 

The Rule

 With the registrations rolling in, you need to check for capacity and whether we have sold enough tickets for the total revenue to hit at least our breakeven point. So, time for a report. We need two things - the number of tickets sold, and the total value of all of the tickets sold.

We quickly setup a simple automation rule using lookup issues. I used a Scheduled trigger so I could run the rule immediately and set it up to run early each morning with the latest results. 

I created an Epic for the event and as issues (Orders) were created, they were linked as children of the Epic. It's the only Epic I had for now, otherwise, I would need more Conditions to differentiate between them. 

Then I added an action for Lookup Issues. Here you can see the query that was used to get the issues I wanted. 

Finally, I add an action for Edit issue - in this case it will edit the Issue in my Condition - the Epic, which is where my totals will reside.

I have selected the Total Attendees custom number field to get a count of the Orders, and the Total Revenue custom number field to get the total dollar amount in tickets sold so far. 

The Results

So, how are we doing? I also created a simple dashboard with a couple of gadgets to check on the total ticket count and see those broken down by Ticket Level as well as see current Revenue. 

Hurray! We are under the 120 person limit, and topped our Revenue goal. Jack gets paid, and we have enough money to pay the venue. 

Maybe YOU will be lucky enough to attend Autograph Session #2 - maybe at Team '25??

Regardless, you are already a winner as you can now use Lookup Issues to sum up child issues. Let us know in the comments below what you like to use Lookup Issues for in your automation rules. 

[Jack Brickey]

excellent article @John Funk ! I can't wait to attend the grand event!

To help encourage attendance I wanted to let everyone know I plan to donate 50% of my take to my favorite charity Send Old Men to Mars for the Holidays. The balance will be used to offset my Team '25 travel costs.

[Bill Sheboy]

Hi @John Funk 

Thanks for your article, and...

For readers trying use Lookup Issues with a custom field this way, it will only work with Jira Cloud at this time.

For Jira Data Center, the Lookup Issues action only supports a limited number of fields.  Here is a suggestion to add more fields to it: https://jira.atlassian.com/browse/JIRAAUTOSERVER-877

There are two possible workarounds, depending upon what is needed for the sum of the custom field: 

1) When the rule has no branches, or just needs to report on the sum, such as with an email or Slack message, the bulk-handling feature of the Scheduled Trigger or branches can help: https://confluence.atlassian.com/automation/run-a-rule-against-issues-in-bulk-993924653.html

In this case, one replaces the {{lookupIssues}} smart value with the plural {{issues}} one.

2) When the rule already has a branch or needs to edit an issue (such as in John's article), one may instead use the Send Outgoing Web Request action to call the REST API for an issue search with JQL.  Then use the web response smart value to sum the field.

Here is a how-to article for calling the REST API from a rule: https://community.atlassian.com/t5/Jira-articles/Automation-for-Jira-Send-web-request-using-Jira-REST-API/ba-p/1443828

I believe this is the REST API endpoint you need: https://docs.atlassian.com/software/jira/docs/api/REST/9.13.0/#api/2/search-search

Finally, you may need to use the custom field id rather than the field name as a smart value.  This how to article will help identify that value for your field: https://confluence.atlassian.com/automation/find-the-smart-value-for-a-field-993924665.html

Kind regards,
Bill

[John Funk]

@Jack Brickey  - Sounds like a worthy cause! I am sure several will want to donate!

[John Funk]

@Bill Sheboy  - Thank you for the additional info - I was hoping you would chime in!! You are the GOAT of Automation Rules so anything you supply is well noted!