JQL Question (due date = startOfDay(+1d))

[min]

I want to send an email 3 days before the due date, so I set the JQL condition like:

due date = startOfDay(+1d)

The message I received from the Validate query was as follows.

Error in the JQL Query: 'date' is a reserved JQL word. You must surround it in quotation marks to use it in a query. (line 1, character 31)

What did I do wrong?

[Els Bassant · Accepted solution]

Hi Min, 

For date values in the future you don't need the +. So duedate = startOfDay(3d) would cover it. Alternatively you could use duedate = startOfDay("+3d"). For date values in the past you would use the - symbol. startOfDay("-3d") would be the start of day, three days ago).

Looking at your automation there are a few other points to keep in mind:

• double check what the due date field is actually called. I believe it is either "due" or "duedate". But it may be different for your instance.
• It looks like you are editing the due date, then checking that same field as a condition for the rest of the automation. You will probably need a re-fetch issue data action in between. Otherwise the automation will check against the due date before the edit.
• Do keep in mind that this will only send an email when the due date is 3 days from now at the moment the issue is transitioned. If you want to send an email at a later point in time you will need a separate automation that runs on a schedule.

Hope this helps!

Els

[min]

Hi. @Els Bassant 

As you said, what I have made is based on when the issue is transitioned.

What I want is to set the due date to 2 weeks later when the issue is converted, and automatically send a mail 3 days before the due date.

Could you tell me what automation I need to add to the current version to achieve this?

[min]

By the way, your answer was very helpful.

[Els Bassant]

What I want is to set the due date to 2 weeks later when the issue is converted, and automatically send a mail 3 days before the due date.

That makes sense.

In that case you will need two automations:

• One automation that adjusts the ticket on the transition
• One automation that periodically checks for issues that are due in three days to send the email.

The trigger that you need for this is "scheduled" and then configure it to check for issues that are due in three days. My example is scheduled for every day at 8am:

The JQL I used in my example is Resolution = unresolved AND due >= startOfDay(3) AND due <= endOfDay(3).

• Resolution = unresolved: this ensures no reminders are sent for issues that have already been resolved.
• due >= startOfDay(3) AND due <= endOfDay(3): this ensures that the reminder is only sent for issues with a due date that is three days in the future (not less, not more than 3 days).
• You can make the JQL more detailed if needed, for example the issue type or only when specific components are added.

Then you can configure the email that is sent. In my example I'm sending a reminder to the assignee that their issue is due in three days, but depending on your use case it may be something else. You can use smart values to insert information from the issue in the email. 

Note: I've put the conditions after the branch rule. That way you could potentially set up multiple different reminders in the same automation. For example one for issue type A, and a different one for issue type B. But you can also make the rule specific to only one use case, whichever you prefer.

Depending in your volume of tickets this may end up sending a lot of emails, so make sure to test thoroughly!

This ended up as a very long post, but I hope it helps!

Els

[Trudy Claspill]

Hello @min 

You don't need the "+" within the parameters for the startOfDay function. The function will assume you mean "+" unless you explicitly insert "-".

Additionally, if you want the message sent when the due date is 3 days in the future you need to use "3" rather than "1" in the startOfDay parameter.

[min]

I use '' to due date. 

Then...

[BHANU TEJA]

Hi @min 

duedate <= startOfDay(3) can you try this

[Trudy Claspill]

@BHANU TEJA 

That will not meet the requirement because it will say the issue matches if it is more than 3 days before the due date. The post is asking to match issues that are exactly 3 days before their due date.