Jira Automation: Invalid JQL

[Ylli Dervishi]

We are attempting to create the automation rule described below, but we encounter an invalid JQL error (see screenshot).

Rule: "If there is an issue with a start date or due date falling within the duration of a Sprint, then automatically move that issue into the Sprint."

JQL: (project in (10142)) AND (Sprint in openSprints()) AND (Sprint.endDate > now)

[Bill Sheboy]

Hi @Ylli Dervishi 

First thing: automatically adding issues to a sprint could cause unintended consequences and problems for a Scrum team.  I instead recommend the team make a deliberate decision to add / remove issues for the Sprint, and then doing so manually with Jira.

Back to your question...

The sprint attributes (such as endDate) are not available with the built-in JQL features.

And the rule is trying to compare an issue's fields to an active sprint which the issue is not in, and so the data is not available.  I know of two ways to get those sprint attributes with a rule:

1. Assuming a sprint has started and there are issues in it, use the Lookup Issues action with JQL to find those issues, and use the data from an example issue in the sprint.  One challenge with this approach is if any issues have been in multiple sprints, and so the list of sprints must be filtered to confirm they are "active".
2. Using the Send Web Request action, call the Jira REST API to gather the sprints for the project, filter by the "active" state, and then use the attribute data.

Kind regards,
Bill

[Natasha Tomm]

Hi @Bill Sheboy

How exactly could we implement option 2?

Thanks!

[Manne Kjærby - ProProces]

Hi Ylli

Sprint.endDate is a automation smart value. You can't use it in JQLs.

You can use the smart value condition and do something like this: {{issue.Sprint.endDate}} greater than {{now}}

If above doesn't work, you might have to work a bit with the format.

You read more about working with date smart values here: here: https://support.atlassian.com/cloud-automation/docs/examples-of-using-smart-values-with-dates/

[Natasha Tomm]

Hi Manne,

we tried with smart values as well, however we can't retrieve it either.

What we're trying to do is:

- When: issue is created or updated

- If issue status is not "done", and if issue is not assigned to any Sprint yet (so issue.Sprint is empty)

- Check the start and end date of Active Sprint

- If start date of issue is greater than Active Sprint start date and less than Active Sprint end date --> add issue to the Sprint

- If due date of issue is greater than Active Sprint start date and less than Active Sprint end date --> add issue to the Sprint

When trying just with 

{{sprint.startDate}}
{{sprint.endDate}}

it returns nothing, even if I just try to print it (in a comment for example).

That's why we shifted to trying with a JQL condition instead of smart values, but this is also giving an error.

Any idea how to access the Active Sprint dates using JQL, or any recommendations on how to perform the automation rule mentioned above? 

[Natasha Tomm]

Here are some snapshots of trying to use smart values:
for example, add comment to issue (with empty Sprint), just to try to reach the value:

The comment added to the issue is empty:

[Nic Brough -Adaptavist-]

I am sorry that the answer you got here is misleading - it was generated by an AI that has no understanding of your question or Atlassian software.

[Manne Kjærby - ProProces]

I'm not sure if you are referring me as a AI, but i'm certainly not AI :-). I'm okay with the I, but let's remove the A.

I made a follow up on Natasha comment, but somehow it doesn't show..

@Natasha Tomm However it look like you are trying to fetch the active sprint through JQL and as far as I know there isn't any way of fetching the active sprint like that or through smart values if its not connected to the trigger issue.

[Natasha Tomm]

I see, thanks for the answer!
So I can only fetch the sprint properties if the sprint is somehow related to the trigger, is that correct?