Automation to notify members quarterly based on a due date field

[VVC]

The team would set the "Due Date" field and status to recurring for specific issue type tickets. An automation would notify on quarterly basis every year that the report is due in 30 days.

Take for example, ABC-123 issue type with the "Due Date" is Oct 17 2014 and status equals recurring is currently set by the team. Note that the due date can be from the past date or in the future date as well and now it would be beneficial to be alerted on September 17th, December 18th, March 18th, and June 17th every year that the report is due in 30 days. 

I have no luck in setting this up in JIRA Data Center Automation Rules and wanted to understand if this doable? Any suggestions with precise steps or screenshots will be greatly helpful.

[VVC · Accepted solution]

Thanks @Kalyan Sattaluri . It was my bad, as it is due to a typo. The days result is now displaying.

[Kalyan Sattaluri]

Awesome. Now I need some time to think through the logic to implement.. Basically I am thinking of doing brute force if checks on the date but need some time to think it through... I will update the post once I think through few scenarios.. 

[VVC]

Looks like, we're getting close.

Applied the change that you have recommended and looks like that did the trick. I tried a few changes on the last component in the Automation Rule but the diff value is still failing.

• Finally, instead of {{now.format("YYYY")}}-{{Date Needed By.toDate.format("MM-dd")}} , use, {{now.format("YYYY")}}-{{Date Needed By.format("MM-dd")}}

FYI - Date Needed By custom field

[Kalyan Sattaluri]

hello, i am going to continue posting in other comment so discussion is together for future reference..

[Kalyan Sattaluri]

Hello @VVC 

At a high level, requirements like these are solvable, so thats the good news... but we need details on what to do and how to solve.

For example, What is the field which hold if an issue is "recurring" or not?

Also quarterly, is it per calendar basis or based on due date? 

So, take the example you have given of Due date as Oct 17 2014, when is the next notification you expect to see?

Please think through this example's specifics and share details around it, else we cannot provide suggestions.

Also, Please share what you have so far so community can help..

[VVC]

Hello @Kalyan Sattaluri 

Thanks for looking into my question and I will try answering your questions.

1) Condition: Issue status = recurring and "due date" = 30d

2) Notification to be sent Quarterly based on the due date field that is already set on the ticket and when it is due in 30 days

3) There are a bunch of tickets with recurring status and due date published with either a past or a future date.

4) Automation should be able to trace each ticket with the ststus field set to recurring and when it is due in 30 days then trigger an email notification to the team on quarterly basis every year.

For example, ABC-123 ticket has a due date set to a past date as Oct 17,  2014. So the notification should trigger quarterly like starting on September 17th 2024, December 18th 2024, March 18th 2025, and June 17th 2025, September 17th 2025, December 18th 2025 and continue every year until that ticket is moved out of recurring in the status field. 

[Kalyan Sattaluri]

TY @VVC 

I think I understand requirement now.

Can I suggest below steps to know if our approach will work. We will test will an issue for now to check syntax and we can set up recurring schedule afterwards. Start with:

• Trigger = scheduled with JQL = key=ABCD-1234   (uncheck the box which says )
• Log {{Due date}}
• Log: {{now.format("YYYY")}}-{{Due date.toDate.format("MM-dd")}}
• Create Variable => newdueDate = {{now.format("YYYY")}}-{{Due date.toDate.format("MM-dd")}}
• Log statement: {{now.diff(newdueDate.toDate).days}}

Below is the explanation:

• First 2 steps are self explanatory,
• in third, We are logging reformatted due date (date to that of this year),
• In fourth, we are storing reformatted date in a variable,
• in fifth, we are doing a diff against current date.

If we can get the fifth step to get you the days, then we can check if this diff is 30 or 120 or 210 days and if so, we can send email...

if above works, you will check your Trigger JQL to run daily and JQL => status = recurring

Hope it helps, please try and share your feedback, screenshots of your rule/audit log will help. Thanks!

[Kalyan Sattaluri]

Actually, I havent fully thought through the If conditions on when to send notifications, that is should be 30/120/210 or what... but please go upto step 5 to get the diff.

And we can evaluate correct logic tomorrow when we can think deeply. sorry have to rush now.

[VVC]

I have tried implementing your steps in the automation rule but looks like it's not working as expected. Am I missing anything?

[Kalyan Sattaluri]

Hmm, This is strange. I tried the same with a date field I have (Target start) and as you can see further below in the screenshot, I am getting the "formatted" output correctly.

Couple of suggestions:

• Can you please re-run current rule once again to confirm if magically it works. :p Else,
• If you happen to know the customfield, use that instead of field name.
• Finally, instead of {{now.format("YYYY")}}-{{Date Needed By.toDate.format("MM-dd")}} , use, {{now.format("YYYY")}}-{{Date Needed By.format("MM-dd")}}

Just FYI. If you dont know how to find a customfield value, 

• Open Jira, in new browser, put below URL, replace bolded parts, hit enter,
• In JSON response, search for "Date Needed By", should have a customfield reference, use that instead of field name and log to see if it works.
• https://yourdomain.net/rest/api/2/issue/ABCD-123?expand=names

If none of the above works, we need to get heavy hitter SME's in here to help us find alternative ways.

[Kalyan Sattaluri]

I am guessingv below syntax worked to reformat the date?

 {{now.format("YYYY")}}-{{Date Needed By.format("MM-dd")}}

If so, assign it to variable -- newDueDate

And then log a statement: {{now.diff(newDueDate.toDate).days}} like I have in my screenshot.

Please share screenshot of the rule&audit log together so we can evaluate if any issues.

[Bill Sheboy]

Hi @VVC and @Kalyan Sattaluri 

Rather than building the date up as text, have you considered using the withYear() function to change the year to what is needed: https://confluence.atlassian.com/automation/jira-smart-values-date-and-time-993924864.html#Jirasmartvaluesdateandtime-withYearwithYear

The value from the current year could be extracted from now to pass the parameter.

Kind regards,
Bill

[VVC]

Hello @Bill Sheboy and @Kalyan Sattaluri 

How exactly is the syntax going to change for the below line with reference to {{now.withYear(1979)}} ?

{{now.format("YYYY")}}-{{Date Needed By.format("MM-dd")}}

[Kalyan Sattaluri]

TY @Bill Sheboy , I honestly was not aware of that function. Am taking a look at how we can use it.

But,  for the use case of @VVC - which was, 

"ABC-123 ticket has a due date set to a past date as Oct 17,  2014. So the notification should trigger on:

• September 17th 2024,
• December 18th 2024,
• March 18th 2025, and
• June 17th 2025,
• September 17th 2025,
• December 18th 2025

and continue every year until that ticket is moved out of recurring in the status field."

I interpreted the requirement as: "Based on a Date field in an issue, send notification 30 days before every quarter, where quarter is just in 90 day intervals from the date on the issue"..

So, I was thinking of below approach:

• Step 1: Get this "Due date" field converted to a date of this year. 
• Step 2: Do a diff of this date with now and convert to +ve number using abs.
• Step 3: If this diff is either 30, 120, 210, 300.. then send notification. (*need to confirm this logic*)

And we just got past step 1, trying to do step 2.

Can you please share if you can think of another/cleaner approach.

@VVC - Please do the diff step mentioned earlier, share screenshot of rule/audit log in the meantime.. I am curious why your diff is not working :p

[Bill Sheboy]

If the issue field's smart value is Date Needed By, and it is a date field, this would do it:

{{issue.Date Needed By.withYear(now.format("YYYY").asNumber)}}

If you need that in jiraDate, text format, please add that to the end:

{{issue.Date Needed By.withYear(now.format("YYYY").asNumber).jiraDate}}

[Kalyan Sattaluri]

Hello @VVC 

Apologies it took me sometime to get back to this thread. Been kinda busy. If you have solved the problem, please share, else here is the logic I think we can implement to build upon where we are so far..

• If the due date is in the future, we simply check if its 30 days away, we send notification. Else,
• If the due date is in the past, given we have formatted the date to one of this year, so we will do a diff on this date, take absolute value and check if this "formatted" date is either 30 or 120, or 210 or 300 days away. If so we send notification.

We will use If/Else conditions for this. So after your last step, do:

• New Component -> If/else
  • Add Condition -> Advanced Compare Condition
    • {{now.diff(Date Needed By).days}} equals 30
    • {{now.isBefore(Date Needed By)}} equals true
  • New Action -> Send your that due date is coming up.
• Else Block
  • Add Condition -> Advanced Compare Condition
    • {{now.isBefore(Date Needed By)}} equals false
    • {{now.diff(newdueDate.toDate).days.abs }} contains regular expression ("30|120|210|300")
  • New Action -> Send notification due date is coming up.

Explanation:

• First If block checks if due date is in future, if so, is it 30 days away, send email.
• Else block checks if due date is in past, if so, is it one of 30/120/210/300 to account for any of the quarters, if so, send email.

Below is the screenshot. Can you think about the logic I am suggesting and see if it meets the requirement. You can adjust the dates, I assumed a quarter as 90 days and we need to send notification 30 days before., so the 30/120/210/300

Also, When you get a chance, can you replicate the rule with log statements and put in due date which gives us values which enter appropriate if blocks and if its giving you data..

[VVC]

Hello @Kalyan Sattaluri 

Please don't apologize, as I really appreciate your favor in sharing this automation implementation logic here. Based on your proposed automation, it looks like this is promising in theory but please allow sometime for me to implement in my system and keep you updated. Thanks again for your time and knowledge sharing on this one. 

[Kalyan Sattaluri]

@VVC 

Now that I am thinking about it, there is a flaw in the # of days I am checking in earlier logic when due date is in the past.

Since I am struggling to come up with clean approach, I want to get you a working solution first, so I propose, we ditch everything we did up until now. and start fresh.

We will just hard code these intervals for past due date and get this rule done.

So please start a brand new rule and make your rule like below screenshot.

You need 2 if conditions:

First checks if due date is in future and difference is 30 days out,

Else checks if due date is in past and difference is one of: (spaced by 90 days)

60|150|240|330|420|510|600|690|780|870|960|1050|1140|1230|1320|1410|1500|1590|1680|1770|1860

So considering today,

• if you give due date in future as 6/13/24, since its 30 days out, it will go into first if block.
• Then if you give a past Due date of any of the below, if will enter Else block.

Date Needed By

3/14/2024

12/15/2023

9/16/2023

6/18/2023

3/20/2023

12/20/2022

9/21/2022

6/23/2022

3/25/2022

12/25/2021

9/26/2021

6/28/2021

3/30/2021

12/30/2020

10/1/2020

7/3/2020

4/4/2020

1/5/2020

10/7/2019

7/9/2019

4/10/2019

Its simple, we are not overthinking this, you can just add these past intervals for say 10 years and it should be good. 

In the mean time, I will also see how else to solve this issue.

[VVC]

Thanks @Kalyan Sattaluri for running some extended thoughts again in trying to simply the solution. I wonder that hardcoding dates might not work in our scenario, as there are multiple issue type tickets with recurring status and various due dates for each ticket set in the "Date Needed By" custom field, unless I'm not getting this correctly?

[Kalyan Sattaluri]

Hello @VVC 

Does not matter what the date is set to. Criteria is still the same. 

Your rule will look like:

• Trigger = Scheduled to run daily, with JQL = "Date Needed By" is not EMPTY and status = recurring
• Then you will continue with the If/Else checks I listed above.

So every day, rule will run, it will find issues based on above JQL, and based on today's date, check if notifications need to be sent and if so.. you will send.

The table I gave is for you to test the rule to simulate that if we ran above rule today, it should pick up all these issues which meet the criteria of the dates listed.. thats all.

[VVC]

Sounds like a plan, Thanks @Kalyan Sattaluri . I will implement the automation steps sometime in this week and then let you know.

[VVC]

Hello @Kalyan Sattaluri 

We are currently validating the results of this implemented solution in one of our staging instance, so I will keep you informed on how it is working for us.

[VVC]

Hello @Kalyan Sattaluri 

Our Ops team are still validating the reminder notifications for bunch of tickets that was triggered using this automation, so this may take some more time to receive their feedback.

[VVC]

Hello @Kalyan Sattaluri  -This is taking much longer than expected for our team to validate the automated notifications for bunch of tickets, so I will plan to tackle any inconsistencies at a later time whenever they're all ready. I really appreciate again for your superb assistance with this automation build.