Getting 'Field 'End Date' does not exist or you do not have permission to view it.

[Vijay Kumar]

I am trying to create an automaton. The automation is that JIRA should be able to send a email notification to some of the project team members just two days before the end date of the sprint, saying these are the issues currently are NOT in DONE status. As part of this automation, where I need to provide JQL, and i am providing below JQL,
sprint in openSprints() AND "End Date"<= startOfDay(2d)
But while validating query, its giving me below error,

I tried to fix this error by providing end date field is following ways,
1) "End Date"
2) EndDate
3) endDate

None of this helped. Now i started getting the doubt that, my JQL itself is correct or not. 
Can somebody help me on this please?

[Bill Sheboy]

Hi @Vijay Kumar -- Welcome to the Atlassian Community!

The sprint's endDate attribute is not available with built-in JQL, and so may not be used in a query.

If your sprints are occurring on a consistent cadence you could try a Scheduled Trigger rule, selecting the starting point and frequency to align with your sprint's time frame.  This may be the simplest approach (other than the team just reminding itself at the daily standup).

Another alternative is to run your rule every day, gather an example issue in the openSprints() and then check the smart value for the endDate to determine if it is within 2 days.  https://support.atlassian.com/cloud-automation/docs/jira-smart-values-development/#--sprint--

Kind regards,
Bill

[Vijay Kumar]

Thanks Bill, for the help. Will explore this approach.

[Semih Tolga Yazici]

Hey Vijay,

I will try to help you in 2 different ways.

1. First you can create a scheduled automation rule.
2. And add a JQL condition like that: sprint in openSprints() AND statusCategory != Done
3. Add advance compare condition
     {{sprint.endDate.diff(now).days}} (if this does not work find a smart value in a same purpose)
     Equals 2
4. Send email notification.

Secondly and easier way,
You can just use the JQL that you're created and Add a subscription for it according to relevant project members and send daily emails for the all open issues.
To be able to do that just save the JQL as filter and Add subscription.

[Vijay Kumar]

Thanks Semih, will work on the approach suggested and keep you posted.

[Semih Tolga Yazici]

You're welcome, btw statusCategory != Done will exclude all the green status issues.

[Vijay Kumar]

Hi Semih,

Thank you so much again for all the inputs. I was able to successfully create the automation as per your instructions. I want to understand each step of the process. So, hope you don't mind answering me following questions.
1) openSprints() AND statusCategory != Done
    The above JQL, basically creates the list of issues from active sprint, which does not          meet the criteria of done.
    Also, you mentioned about green status issues. I am not sure, which of the issues are        called 'green status issues'. I am just curious to know.
2) Advance compare condition
    {{sprint.endDate.diff(now).days}}
    My understanding is that, this condition will first find the sprint end date, current date      and find the difference which should be equal to 2. Once it meets this condition, it            will trigger the email with a list of all the issues in the email body in HTML format.

Please let me know.

[Semih Tolga Yazici]

1. Green status means. Statuses have 3 different categories, if you realize in the workflow. There are To Do, In Progress and Done status categories. Whites are the To Do, In Progress are the blue, Done are the green. If you say status != Done, it will only exclude the issues that are Done. However, maybe you used another status called Resolved (green color). Therefore, if you do it statusCategory != Done, it will exclude all the green statuses without naming convention.

2. Yeah that is correct. I answered that from my mobile phone. If the smart value is wrong, try to find a correct one to get that value 2 in the condition.

And if it works, I would appreciate if you accept my solution.

Have a fun! :) 

[Vijay Kumar]

Hello My friend, sorry for not replying on this topic as I was down with fever in the last three days. 
I tried to test this by making this rule to work for 6 days. Right now, there are six days left on my sprint. When i have below condition and run the rule by only having my email in the rule, It didn't send me any email with a list of issues which are NOT IN 'Done' status. 
{{sprint.endDate.diff(now).days}} equals 6
Could you tell me what could be the reason? Is there anything wrong with syntax in the condition. However, my JQL works absolutely fine when i validated it. Looks like its a problem with SmartValue i have used here.

[Semih Tolga Yazici]

Can you share the screenshots of your Automation? Let me check.
Btw, if the trigger is scheduled you can test it manually by running the rule on the right top 3 dots. 

[Vijay Kumar]

Yes.i tried to run it manually by pressing the 'Run Rule' button. Didn't help.

[Semih Tolga Yazici]

Add log action and put the smart value into that action.
And rerun the rule then can you show me the Audit log of the Automation as well?

And make sure your send email action has the smart value that gets all the issues.

[Vijay Kumar]

Thanks for the reply. 
I have added a log action and put the smart value in it. After running the rule, 'No action was performed' and i am attaching screenshot of the 'Audit Log' for your reference.
I am not sure, how to add smart value to email action as well. because, we have no fields which is meant for adding smart value.

[Vijay Kumar]

I am sorry to send messages one after the other.
Now it seems, "this rule executed successfully; however, no actions were performed due to a condition stopping the rule execution."I am not sure which condition has stopped rule execution.  Can you help me with this?