Using business days instead of calendar days in JQL query for automation

[Igor Kolpakov]

Hi everyone!

I am having trouble setting up a simple automation. I want an automation to trigger every weekday at 10:00 AM that will leave a comment in the request (and cancel it) if there is no activity from the client (after 3 business days and after 7 business days of being moved to "Waiting for requester" status)

I did the following:

Scheduled

Mon-Fri 10:00

Run a JQL search: project = "IS" AND status = "Waiting for requester"

1. If matches

project = "IS" AND status = "Waiting for requester" AND NOT status changed after startOfDay(-3)

Then add comment

2. If matches

project = "IS" AND status = "Waiting for requester" AND NOT status changed after startOfDay(-7)

Then add comment and transition issue to cancelled

The problem is that it counts along with weekends, and I want ONLY weekdays (Monday - Friday 10:00 - 19:00). What should I change "NOT status changed after startOfDay(-3)/startOfDay(-7)" to so that my idea starts working correctly?

[Rudy Holtkamp]

Hi @Igor Kolpakov ,

I think you should use '.toBusinessDay()' or a derivative from this clause.

Take a look at this page it will give you the details how to find the next business day.

[Igor Kolpakov]

I will try now.minusBusinessDays(3) and now.minusBusinessDays(7). 

I'll reply later if it doesn't help. Thank you in advance!

[Igor Kolpakov]

Hmm, I got an error in the logs: No subsequent actions were performed since JQL condition did not pass due to error running JQL.

Also, in the "if" block, I entered a JQL condition: project = "IS" AND status = "Waiting for requester" AND NOT status changed after {{now.minusBusinessDays(3)}} and got a warning: If smart-values are used JQL can not be validated.

@Rudy Holtkamp  am I entering something wrong or is it not possible to use smart-values in automations?

[Rudy Holtkamp]

@Igor Kolpakov , you did nothing wrong. It is just that if you add a JQL (without smart values) it can directly show which issues are included in you JQL (In the edit mode of the automation rule). It can't do that if you use a smart value, however you can run the rule and verify if it works.

HINT: I use a 'scheduled' trigger to test if my automation works on a sub-set of issues (preferably 1). You can do this like the picture below

1. Set the run rule every to an insane value, so it does not accidently trigger

2. Use run a JQL and include one or multiple test issues with key = KEY-1 
    or with key in (KEY-1, KEY-2)

3. Remove the 'Only include issues that have changed' check.

Good luck

[Igor Kolpakov]

I clicked "Audit log" in the automation and after changing the configuration there is an error: No subsequent actions were performed since JQL condition did not pass due to error running JQL

If I understand correctly, the automation was not performed because I inserted smart-value.

[Rudy Holtkamp]

Your datetime format is not correct.

Add .jqlDateTime after {{now.minusBusinessDays(3)}}

So it will read  {{now.minusBusinessDays(3).jqlDateTime}}

This will produce a date time format which is accepted by the JQL: 2023-09-07 12:13

You also might need to add double quotes around the smart value.

[Igor Kolpakov]

I made an edit, but the rule did not apply anything to the request, although more than 3 business days have passed. Does it work with smart values?

UPD: I changed {{now.minusBusinessDays(3).jqlDateTime}} to "{{now.minusBusinessDays(3).jqlDateTime}}" and it seemed to start working.

[Rudy Holtkamp]

Thanks for the update. So the double quotes were necessary.

It is always a bit of trying and re-trying with the automation. 😀