using lookup issues to get the total number of tickets for each status.

[phebean Atoyebi]

I am trying to get the total number of tickets for each status in Jira. but the output keeps giving me 1 or 0. So I write a jql to get all the jira ticket types. example below

Trigger is schedule

then action is lookup issues using this jql: project = "Fake" AND type IN ("Fake1", "fake1 Subtask") AND status IN ("New", "In progress", "Follow up", "Update", "second Followup") ORDER BY created DESC

and then send email with this

- Total tickets in "In Progress": {{lookupIssues.size for status "In Progress"}}

- Total tickets in "New": {{lookupIssues.size for status "New"}}

- Total tickets in "Follow up": {{lookupIssues.size for status "Follow up"}} etc

Output is below

- Total tickets in "In Progress": 0
- Total tickets in "New": 0
- Total tickets in "Follow up": 0
- Total tickets in "Update"": 0
- Total tickets in "Second Follow up": 0

Please can you assist this. the expected output should look like below

- Total tickets in "In Progress": 20
- Total tickets in "New": 6
- Total tickets in "Follow up": 5
- Total tickets in "Update"":30
- Total tickets in "Second Follow up": 55

[Trudy Claspill · Accepted solution]

Hello @phebean Atoyebi 

Welcome to the Atlassian community.

Are these literally the smart values you are using in your email?

{{lookupIssues.size for status "In Progress"}}

{{lookupIssues.size for status "New"}}

{{lookupIssues.size for status "Follow up"}}

If so, those are not valid smart values. Only {{lookupIssues.size}} is a valid smart value. You can't add for status "Follow up" to get a subset of the Lookup Issues results.

There are a few ways you could handle this.

One is to use a Lookup Issues action for each status, and then save each result to a separate variable. Then use the variables in your email

[Malka Jackson _Isos Technology_]

Not that this affects your answer, but isn't there a limit of 100 issues returned when you do a lookupissues? Or am I remembering another limit for something else? 

[Trudy Claspill]

Good point @Malka Jackson _Isos Technology_ 

Yes, there is a limit of 100 issues being returned by the Lookup Issues action. That is documented here:

https://support.atlassian.com/cloud-automation/docs/automation-service-limits/

Given the numbers shown in the example by @phebean Atoyebi the total number of issues expected when combining all statuses into one Lookup exceeds the 100 issue limit. So based on that, separate Lookup Issues actions will be needed to get subsets of the issues.

[phebean Atoyebi]

Thank you so much, I am new to automations in Jira, @Trudy Claspill what you provided worked. 

[phebean Atoyebi]

@Malka Jackson _Isos Technology_ I will figure out other options other than lookup issues that exceed 100

[Trudy Claspill]

@phebean Atoyebi 

You may want to take advantage of the free, on-demand training available from Atlassian at https://university.atlassian.com . Among the many courses they offer are some courses specifically about Automation. You can find them by searching for jira automation

[phebean Atoyebi]

Will do. Thanks for the link

[Bill Sheboy · Accepted solution]

Hi @phebean Atoyebi -- Welcome to the Atlassian Community!

In addition to the 100 issue limit noted by other suggestions, another way to get the counts is using smart value, list filtering and a math expression: 

• https://community.atlassian.com/t5/Automation-articles/Filtering-smart-value-lists/ba-p/1827588
• https://support.atlassian.com/cloud-automation/docs/jira-smart-values-math-expressions/

The benefit of this approach is there is only one call to Lookup Issues, making the rule faster and less likely to have maintenance errors if the JQL changes.

For example, if there is one Lookup to return all of the issues, the counts would be:

- Total tickets in "In Progress": {{#=}}0{{#lookupIssues}}{{#if(equals(status.name, "In Progress"))}}+1{{/}}{{/}}{{/}}

- Total tickets in "New": {{#=}}0{{#lookupIssues}}{{#if(equals(status.name, "New"))}}+1{{/}}{{/}}{{/}}

...

That works by:

• iterate over the issues in the lookup results
• filtering by ones matching a particular status name
• adding +1 when found
• wrapping the entire expression in a math expression, with a default value of 0 at the front

Kind regards,
Bill

[Trudy Claspill]

Thank you for providing that, @Bill Sheboy .

I thought there was probably a method like that, but I haven't spent much time working with the logic options.

[phebean Atoyebi]

Thanks bill. This also worked