Scheduled automation to find tickets that's 3 business days overdue.

[Hoa Walsh]

I have created a scheduled automation to find tickets that hasn't been updated that's more than 3 days.  However this is not 3 business day, just calendar days. 

This is the logic I currently have:

(assignee in (712020:f033c51b-9d71-4374-88f0-d41003981be4, 712020:e19a5a7e-cdc8-4104-b87c-9dd3953df1f9)) AND (status = "WAITING FOR TEAM MEMBER") and ("Follow up[Date]" = -3d)

From other postings, I've tried the following:

(assignee in (712020:f033c51b-9d71-4374-88f0-d41003981be4, 712020:e19a5a7e-cdc8-4104-b87c-9dd3953df1f9)) AND {{issue.key}} = "FS-1484" AND (status = "WAITING FOR TEAM MEMBER" AND ((status changed TO "WAITING FOR TEAM MEMBER" BEFORE -3d AND status changed TO "WAITING FOR TEAM MEMBER" AFTER startofWeek()) OR status changed TO "WAITING FOR TEAM MEMBER" BEFORE -5d))

and

(assignee in (712020:f033c51b-9d71-4374-88f0-d41003981be4, 712020:e19a5a7e-cdc8-4104-b87c-9dd3953df1f9)) AND {{issue.key}} = "FS-1484" AND NOT ( "Follow up[Date]" >-3d OR ("Follow up[Date]" > -5d AND "Follow up[Date]" > startofWeek(-3d) AND "Follow up[Date]" < startofweek()))

But they don't seem to work. Any assistance would be greatly appreciated. Thank you in advance!

Hoa Walsh

[Trudy Claspill · Accepted solution]

Hello @Hoa Walsh 

I see that you are using a JQL in your Schedule trigger. Is that this JQL?

(assignee in (712020:f033c51b-9d71-4374-88f0-d41003981be4, 712020:e19a5a7e-cdc8-4104-b87c-9dd3953df1f9)) AND (status = "WAITING FOR TEAM MEMBER") and ("Follow up[Date]" = -3d)

You could try this:

(assignee in (712020:f033c51b-9d71-4374-88f0-d41003981be4, 712020:e19a5a7e-cdc8-4104-b87c-9dd3953df1f9)) AND (status = "WAITING FOR TEAM MEMBER") and

("Follow up[Date]" = -3d or "Follow up[Date]" = -4d or "Follow up[Date]" = -5d)

Then replace the condition you have currently with this:

On a Monday, 3 business days in the past would be Wednesday, which is 5 calendar days in the past.

The -5d in the filter would pick up issues with a follow up date on last Wednesday, -4d picks up the issue a follow up date on last Thursday, and -3d the issue with a follow up date on last Friday.

When the rule runs on Monday, the above condition would compare the Follow up date (Wednesday) to today minus 3 business days (Wednesday) and send the email.

The issues with Follow up dates on Thursday and Friday would not pass the above condition so no email would be sent.

When the rule runs on Tuesday it picks up issues with Follow up dates on the previous Thursday, Friday, and Saturday.

The issues with Follow up dates on Thursday would match the above condition.

When the rule runs on Wednesday it picks up issues with Follow up dates on the previous Friday, Saturday, Sunday.

The issues with Follow up dates on Friday would match the above condition.

When the rule runs on Thursday it picks up issues with Follow up dates on the previous Saturday, Sunday, and Monday.

However, I would like to point out that the Follow up date is not inherently the same thing as the date the issue was last updated. Without understanding what you mean by "updated" I'm not sure that my suggestion will get you the results you want.

[Hoa Walsh]

Hi @Trudy Claspill ,

Hope you're doing well!  Thank you for your quick response. 

It looks like it works but not exactly.  For some context, we are working tickets and manually assigning the Follow up date by updating it two business days into the future, when we need more information from the user.

I have an automation that will run daily to figure out if there's been 3 business days that past and the user haven't updated the ticket.  If they didn't, then we want the automation to send them an email reminder to go and update the ticket. 

I also have a 2nd automation I want to set up to send a 2nd reminder if they don't respond to the first one, after another # of business days.  But we don't have to get into this one.  If I could get one to work, I can be able to mirror it and set the # of business days that we need.

With the logic you provided, it is still counting just the # of days, not the business days. 

Scenario: If the 3 business days counts starting today (Tuesday), then the ticket should only be picked up if the Follow up date = Sept 30th (Friday).  But when I change the date to Sept 29th (Thur), the logic is still picking up the ticket.

I tried to use the {{now.plusBusinessDays(3)}} logic by replacing "now" with my custom date field but it didn't work: {{"Follow up[Date]".plusBusinessDays(3)}} .

... and... "Follow up[Date]" = {{now.minusBusinessDays(3).jqlDateTime}} , also didn't work.

I hope I didn't make this more confusing than it's already is.

Hoa

[Trudy Claspill]

You said:

Scenario: If the 3 business days counts starting today (Tuesday), then the ticket should only be picked up if the Follow up date = Sept 30th (Friday).  But when I change the date to Sept 29th (Thur), the logic is still picking up the ticket.

If Tuesday's date is Sept. 3, and you subtract 3 days from that you get Aug. 31. But there are 2 non-business days in there (Saturday and Sunday), so if you subtract 2 more from that you get Aug. 29. That's Thursday, not Friday.

What you want is issues where the follow up date is 2 business days in the past. Try changing the 3 to a 2 in the logic I provided, and in the JQL replace the -5d criteria with -2d.

Also, can you provide evidence that the emails are sent based on calendar days rather than business days?

[Hoa Walsh]

Hi @Trudy Claspill ,

LOL, I didn't do my math correctly.  But I understand your explanation.  I will have to make the change you suggested and wait over the weekend to see if it works correctly.

I will update the results, mid-next week.

Thank you for your help on this!! <3 :)

Hoa

[Hoa Walsh]

Hi @Trudy Claspill ,

I'm so sorry for not following your instructions to the 'T'.  Side note: I do want to look for tickets that's 3 business days overdue.

I went back and retest your directions and the logic did identify my ticket (3 business days prior) but the email was not generated.

This is the JQL:

(assignee in (712020:f033c51b-9d71-4374-88f0-d41003981be4, 712020:e19a5a7e-cdc8-4104-b87c-9dd3953df1f9)) AND (status = "WAITING FOR TEAM MEMBER") and ("Follow up[Date]" = -3d or "Follow up[Date]" = -4d or "Follow up[Date]" = -5d)

When I query the results, I got the ticket I'm looking for, FS-1484:

This is the Smartvalue condition:

I manually ran the automation and the log shows this:

Am I missing anything else?

Thanks!

Hoa

[Trudy Claspill]

Smart values are case and spacing sensitive. 

In the condition you used:

{{issue.Followup.jiraDate}}

But the field name has a space in it. You need to match the field name exactly in the smart value.

{{issue.Follow up.jiraDate}}

If an Automation rule can't resolve a smart value you provide often it will use NULL as the value and not tell you what the actual problem is. I believe that is what you have encountered.

An excellent debugging tool is the Log action.

https://support.atlassian.com/cloud-automation/docs/jira-automation-actions/#Log-action

You can use that to write text and smart values into the rule execution audit log. In this case I would add two Log actions priori to the condition and use them to log the following:

Follow up date: insert the exact smart value you use in the Condition

now minus 3 business days: insert the exact smart value you use in the Condition

I think you will find that {{issue.Followup.jiraDate}} prints no value which, of course, cannot be equal to now minus 3 business days.

[Hoa Walsh]

Wow, thank you for the tip on debugging!  This is a great tool!!

I did added a space but it looks like the {{now.minusBusinessDays(3).jiraDate}} is only returning one prior business day (09/02/2024), instead of 3 (08/30/2024).

[Trudy Claspill]

Jira uses UTC for date/time fields in the backend. Unless you explicitly convert the date/times you are using to your local timezone the calculations will be based on UTC.

What is your local timezone?

For instance I am in the America/Los Angeles timezone and the local date/time is currently Sept. 4 at 18:50. The UTC date/time is Sept. 5 at 02:50. Subtracting 3 business days from Sept. 5 would indeed by Sept. 2.

If you want the calculations based on your local timezone you need to use the function to convert the date/time of now to your local timezone. 

https://support.atlassian.com/cloud-automation/docs/jira-smart-values-date-and-time/#Convert-timezone-date---

Example

{{now.convertToTimeZone("America/Los Angeles").minusBusinessDays(3).jiraDate}}

[Hoa Walsh]

OMG! Thank You!! <3

I didn't realize I needed to add the time zone.  I did as you suggested and I'm in MST so I replaced {{now.minusBusinessDays(3).jiraDate}} with ...

{{now.convertToTimeZone("America/Denver").minusBusinessDays(3).jiraDate}}

and it WORKED! Yahooo!!

You are AWESOME!!!! XXOO