Help with email automation - complete sprint

[Samuel Jobin-Morissette]

Hello all,

I'm trying to automate an email when completing a sprint with the following information:

------------------------

During this sprint we completed X story and X tasks.

During this sprint, we resolved X bugs.

Stories Completed:

/List all story completed

Tasks Completed:

/List all tasks completed

Bugs Resolved:

/List all Bugs resolved

--------------------------

Below is my code, can you help identify why it is not working? I have tested a dozen time with different iterations and can't figure it out... Thanks!!!

Sprint {{sprint.name}} was completed.

During this sprint, {{#lookupIssues("project = PROJECT AND issuetype = Story AND status = Done AND Sprint = sprint.name").size}} story and {{#lookupIssues("project = PROJECT AND issuetype = Tasks AND status = Done AND Sprint = sprint.name").size}}tasks were completed.

During this sprint, {{#lookupIssues("project = PROJECT AND issuetype = Bugs AND status = Done AND Sprint = sprint.name").size}} Bugs were resolved.

Stories Completed:

{{#lookupIssues("project = PROJECT AND sprint = '" + {{sprint.name}} + "' AND issuetype = Story AND status = Done")}}

- {{key}}: {{summary}}

{{/}}

Tasks Completed:

{{#lookupIssues("project = PROJECT AND sprint = '" + {{sprint.name}} + "' AND issuetype = Task AND status = Done")}}

- {{key}}: {{summary}}

{{/}}

Bugs Resolved:

{{#lookupIssues("project = PROJECT AND sprint = '" + {{sprint.name}} + "' AND issuetype = Bug AND status = Resolved")}}

- {{key}}: {{summary}}

{{/}}

[Bill Sheboy]

Hi @Samuel Jobin-Morissette -- Welcome to the Atlassian Community!

For a question like this, please post an image of your complete automation rule, images of any relevant actions / conditions / branches, an image of the audit log details showing the rule execution, and explain what is not working as expected.  Those will provide context for the community to offer ideas.  Thanks!

Until we see those...

The lookup issues action accepts JQL to load a set of issues meeting the criteria.  And then those issues can be iterated using the {{lookupIssues}} smart value: https://support.atlassian.com/cloud-automation/docs/jira-automation-actions/#Lookup-issues

You cannot pass JQL directly to the lookup issues smart value.

Your email references quite a few different conditions / filters for the issue in the sprint.  You have two options to access all of those different things for your email:

1. Perform one lookup issues action to gather all issues in the sprint, regardless of issue type, status, etc.  Then use smart value, list filtering for each section, such as filtering on the issue type of Bug.  Please look here to learn more about that technique: https://community.atlassian.com/t5/Automation-articles/Filtering-smart-value-lists/ba-p/1827588
2. Perform multiple lookup issue actions, with separate JQL for each.  Iterate over the results for each item (e.g., Bugs resolved in sprint), saving the results in a created variable.  And then include the created variables in your email.

I recommend trying the first approach.  While doing so, add just the first reporting item, test it fully, and then incrementally add the others until done.

Kind regards,
Bill