Filter JQL Lookup Issue Results Set and Sum Story Points for Each Status

[Brandon Kidd]

Objective

Send a Slack message with a breakdown of the current sprints' issues showing the number of tickets and story points per status and components. Retrieve the data once and filter the results and store those in lookup tables.

Description

In my automation rule, I am doing a lookup using the following JQL:

project = MyProjectNameHere AND type NOT IN (Epic, Initiative, Test) AND sprint IN openSprints()

To avoid running multiple lookups for the same data, I am using a lookup table to store the values. This is working as expected when storing the data for the sprint:

The next challenge was to use the same lookupIssues data and filter the results by status and store the number of issues and sum of the Story Points for the issues that match my criteria. Similarly, I am storing those values in a lookup table.

I have tried numerous routes and have added to my collection of gray beard hairs.

What I've tried thus far...

There may be more, but some smart value conditionals were deleted with the fury of a thousand suns.

Option 1

Using multiple JQL lookups to filter the results and store the values. Here is one of the 8 lookups I am doing:

project = CRM AND type NOT IN (Epic, Initiative, Test) AND sprint IN openSprints()

This was my first approach and it works, but I did not like that it was calling the same data set multiple times, pushing the maximum number of components in my rule, and taking over 20s to run. Not ideal. That is when I had the idea to get the data once and try filtering the result from my first JQL lookup.

Option 2

I created a new rule and kept the first lookup issue step where I returned all of the issues in the current sprint. At a loss of how I would be able to select the issues whose status matches those above, I tried the simpler ask of getting the story points and count for the issues that have the RTB component.

In my tblRTB I have a key of storyPoints and its value is:

{{#lookupIssues}}{{#if(components.name.indexOf("RTB").gte(0))}}{{Story Points}}{{/}}{{/}}

This did not return any values when I logged the key (using size or sum).

Option 3

Then I tried a different conditional for my storyPoints value:

{{#lookupIssues}}{{#if(exists(components.name.match("(RTB)"))), "{{Story Points}}{{^last}}, {{/}}", "0.0")}}{{/}}{{/}}

This did not yield any values again.

---

There are probably 20 other options I tried, but before I give up and go back to Option 1, I thought I would reach out for help. Any assistance is greatly appreciated!

[Bill Sheboy · Accepted solution]

Hi @Brandon Kidd -- Welcome to the Atlassian Community!

Using the smart value, list filtering with one call to Lookup Issues sounds like a good approach.

For the indexOf() function, that is a 0-based index, and when a value is not found it returns -1: https://support.atlassian.com/cloud-automation/docs/jira-smart-values-text-fields/#indexOf-String-str-

However, I believe for a list, index search like that it is collapsing to null in some cases.

A match() will work if you test the size of the result set rather than using exists().  Please try this to get the Story Points for issues containing the RTB component:

{{#lookupIssues}}{{#if(components.name.match("(RTB)").size.gt(0))}}{{Story points}},{{/}}{{/}}

Note the trailing comma is always added as the {{^last}}... will not help in this nesting structure.

Then add this to the result to get the count, assuming it was stored in a variable named varRTBPoints:

{{varRTBPoints.substringBeforeLast(",").split(",").size|0}}

Kind regards,
Bill

[Brandon Kidd]

Thank you! This worked for me! And just in case someone finds this I was able to get the story points by modifying what @Bill Sheboy posted:

{{varRTBPoints.substringBeforeLast(",").split(",").asNumber.sum|0}} 

---

How would you recommend checking the status.name to see if it matches one of multiple values? Should I create variables for each of the statuses and then just add their points/sizes?

[Bill Sheboy]

Awesome; I am glad to learn that helped!

For the status values, that would work also...and...

If you want the sum or count, without using the variables, adding a math expression wrapper will work.  For example, let's redo what I suggested earlier:

This would be the sum of the story points for the RTB components:

{{#=}}0{{#lookupIssues}}{{#if(components.name.match("(RTB)").size.gt(0))}}+{{Story points|0}}{{/}}{{/}}{{/}}

And this would be the count:

{{#=}}0{{#lookupIssues}}{{#if(components.name.match("(RTB)").size.gt(0))}}+1{{/}}{{/}}{{/}}

The leading 0 takes care of cases where there are no matches and simplifies the use of the plus sign.