Help with how to use Jira Automation to find distinct sprints for an Epic's children

[Marc Schadee]

Hi!

I could use some help with finding all sprints that work for an epics children

I use this lookup "Epic Link" = {{triggerIssue.key}} AND Sprint is not empty

{then i create a variable {lookupIssues.sprint.name.distinct}}

When i print to the audit log i get this

RPT-2024_Sprint08, RPT-2024_Sprint09, RPT-2024_Sprint10, RPT-2024_Sprint08, RPT-2024_Sprint09, RPT-2024_Sprint10, RPT-2024_Sprint11,  PM1 Backlog, RPT-2024_Sprint10, RPT-2024_Sprint11, RPT-2024_Sprint11,  RPT-2024_Sprint08,  RPT-2024_Sprint07, RPT-2024_Sprint08

which is great except for the duplicates (work items that were in 2 sprints)

So the issue is I am trying to parse this list (or use substring) to find just unique sprints but nothing im doing is working so far.

My current idea was to use substring but that seems to not work with the list and returns [] [] [] []

Any suggestions on how to parse this list? my Thought was to use a character count as there is a standard naming length that does not change but open to help

[Marc Schadee · Accepted solution]

so took some thought but for anyone trying to parse unique sprints that an epic has work that has been this is a way

lookup ->  "Epic Link" = {{triggerIssue.key}} AND Sprint is not empty

create variable (smart value) {{#lookupIssues}}{{#sprint}}{{#if(not(equals(state,"future")))}}{{name}},{{/}}{{/}}{{/}}

(I only wanted current/closed sprints youll need to play with above if you want different)

create variable (smart value) {{firstvariablename.split(",").distinct}}

[Bill Sheboy]

Hi  @Marc Schadee 

I am glad to learn you found the workaround, and...

FYI, the distinct was working as expected: it just did not match your scenario because the sprint field can contain multiple values, and "distinct" was over the sets of values in this case.

For example, if the issues had these sprint values:

• issue A: sprint 1, sprint 2, sprint 3
• issue B: sprint 2
• issue C: sprint 4
• issue D: sprint 2

Then using this:

{{lookupIssues.sprint.name.distinct}}

Would produce the following, with a list of arrays, and not necessarily in this ordering:

[sprint 1, sprint 2, sprint 3], [sprint 2], [sprint 4]

The list of sprints for issue A is distinct as a set, even though it contained overlapping sprints with other issues.

As you found, the workaround is to expand everything into a variable so it can be reprocessed as a list for using distinct later.  One gotcha for this technique is ensuring the delimiters are consistent, helping split() to work predictably later.  The fix for that is often to replace(", ", ",") before the split attempt.

Kind regards,
Bill

[Marc Schadee]

Hey @Bill Sheboy !

I missed mentioning a step! in the first variable i add commas (bolded below) deliberately to have a comma between each sprint iteration since you can get multiple sprints as 1 value but they are always separated as a comma (that im aware),

e.x.

[Sprint 1],[Sprint 1, Sprint 2],[Sprint 2]

 {{#lookupIssues}}{{#sprint}}{{#if(not(equals(state,"future")))}}{{name}},{{/}}{{/}}{{/}}

to get (because its now a string we lose the [])

Sprint 1,Sprint 1,Sprint 2,Sprint 2

then run {{firstvariablename.split(",").distinct}}

Sprint 1, Sprint 2

and thank you! Im pretty certain i got that last bit off of one of your prior comments!

[Marc Schadee]

Really the best part was getting this to work outside of a for each branch

[Joanna Weir]

@Marc Schadee & @Bill Sheboy , I am having trouble getting this to work.  Can you help.

I am looking for a distinct list of sprints in the future.  Note:  since the sprint field can contain multiple values, I want a distinct list of sprints using the LAST Sprint value.

Here is my automation rule:

1. When: Scheduled Daily

2. Then: Create Variable called "SprintList".  Smart Value = {{#lookupIssues}}{{#sprint}}{{#if(equals(state,"future"))}}{{name}},{{/}}{{/}}{{/}}

3. And: Create Variable called "DistinctSprint".  Smart Value = {{SprintList.split(",").distinct}}

4. And: Send email .  In the content/body of the email i have: {{DistinctSprint}}

Result: This produces an empty email.

Please advise.

[Marc Schadee]

@Joanna Weir  Can you use the log action to return {{SprintList}} between step 2 and 3 and tell me what that says?

its odd that its returning nothing.. it should return atleast 1 ","

[Marc Schadee]

@Joanna Weir 

oh i think i see the issue now
I dont see how you are looking up the information initially as you dont do it from a triggered epic like i did in step one

Example 
Lookup ->  "Epic Link" = {{triggerIssue.key}} AND Sprint is not empty

I would first create a lookup or post what lookup you are using before step 2

[Joanna Weir]

@Marc Schadee , Thank you so much for responding

Here is my updated automation rule:

1. When: Scheduled Daily, running a JQL search: "sprint in futureSprints()"

2. Then: Create Variable called "SprintList".  Smart Value = {{#lookupIssues}}{{#sprint}}{{#if(equals(state,"future"))}}{{name}},{{/}}{{/}}{{/}}

3. And: Create Variable called "DistinctSprint".  Smart Value = {{SprintList.split(",").distinct}}

4. And: Send email .  In the content/body of the email i have: {{DistinctSprint}}

Result: This produces an empty email.

Please advise.

[Marc Schadee]

@Joanna Weir 

I would like you to run the jql search in jira as close to the same as what you have in automation and tell me if you return any results

[Marc Schadee]

If you have results i would like you to add a log action to  your script after step 2 

then go to your log and tell me what it listed out

[Joanna Weir]

@Marc Schadee 

Thank you so much for responding.  When I validate the Scheduled trigger using JQL, i get 126 results.

Here is my updated automation rule & results, per your request:

1. When: Scheduled Daily, running a JQL search: "sprint in futureSprints()"

2. Then: Create Variable called "SprintList".  Smart Value = {{#lookupIssues}}{{#sprint}}{{#if(equals(state,"future"))}}{{name}},{{/}}{{/}}{{/}}

3. And: Add value to the audit Log: SprintList: {{SprintList}}

4. And: Create Variable called "DistinctSprint".  Smart Value = {{SprintList.split(",").distinct}}

5. And: Send email .  In the content/body of the email i have: {{DistinctSprint}}

Result:

1. This produces an empty email.
2. The Audit Log says:  

Please advise.

[Marc Schadee]

@Joanna Weir 

Click  validate query on the when scheduled box for me

does that return the same amount of results as the jql query?

if so
I would then move the lookup to a separate action rather than a part of the scheduled action and try again
if you do this please add a log action for the lookup as well 

[Marc Schadee]

Sorry if this is taking a bit I dont have a direct answer so im trying to troubleshoot why the lookup is not being parsed by the variable

[Joanna Weir]

@Marc Schadee , Thanks for you help Marc.  I played around more and finally got it to work.  My solution is posted here:

1. Scheduled Trigger with JQL: sprint in futureSprints() 

2. Then: Create Variable called LastSprint:  {{#Issues}}{{sprint.name.last}},{{/}}

3. Then: Create Variable called Last SprintDistinct: {{LastSprint.split(",").distinct}}

4. Send Email

[Marc Schadee]

Ah interesting!

Thank you for sharing and glad you got it to work!

[Bill Sheboy]

Hi @Joanna Weir -- Welcome to the Atlassian Community!

Sorry for the delay in responding as I have been offline for a few weeks.

I am glad to learn you got something working, and as a follow-up...Are you using Jira Server / Data Center or Jira Cloud?

I ask because the {{issues}} (please note the plural) smart value is only supported for Jira Server / Data Center automation, bulk-handling, and not for Jira Cloud.  Using it may lead to unexpected results.

For Cloud, the Lookup Issues action is used for bulk-handling of issues.

Kind regards,
Bill