How to extract date from the Summary Field in Jira?

[Harish ShanmugamDhanasekar]

Hello,

Can someone please tell how to extract date value from the following summary field?

Employee Termination Charles Hendridge 2022-02-23

 I need only 2022-02-23 , and that should be assigned to due date for further work.

Thanks,

Harish.

[Darryl Lee]

I'm going to *guess* that @Harish ShanmugamDhanasekar might be trying to create tickets from email, which is a pretty common thing.

If the email is computer-generated then it's *possible* to write a regex, but as @Chris Buzon correctly points out, this is definitely complicated.

For instance, here is a regex for matching dates in the format of:

dd/mm/yyyy , dd-mm-yyyy or dd.mm.yyyy

https://ihateregex.io/expr/date/

[Harish ShanmugamDhanasekar]

Hello @Darryl Lee ,

Actually there would be certain employee termination tickets with the summary field format as Employee Termination Name of Employee yyyy-mm-dd

So, firstly here I need to extract the date field from the summary field and assign it to due date.

Later I need to check whether the employee termination ticket is closed or not before 2pm on due date.

If is is not closed then I need to send notification message to some email and also send notification to private slack channel.

Can you please let me know how to approach this goal?

[Darryl Lee]

Another (slightly simpler) example (that matches your YYYY-MM-DD format) is here:
https://www.regular-expressions.info/dates.html

The trick then after obtaining that date would be to format it into something Jira understands:

Jira date format is:

2020-08-18T02:40:37.0+0000

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

So ok, maybe we can set Due Date to: 

{{issue.summary.replaceAll("Employee Termination.* (19|20)","$1").replaceAll("/","-").concat("T14:00:00.0+0000")}}

Let me test this...

[Darryl Lee]

As for the notification if ticket is not closed before 2PM on due date, you'd need to setup an Automation that:

• Fires at 2PM every day
• Searches your project for any tickets that are Open
• Checks to see if the Due Date is today
• If so, send Slack message

[Harish ShanmugamDhanasekar]

Thanks a ton @Darryl Lee for your valuable time and solution mapping.

I will definitely try this today and let you know my progress.

[Darryl Lee]

So, in my very quick test, the Due Date regex formula worked. But I don't think that field actually includes a timestamp.

(Note that you will need to add 21 if your Jira still exists in the year 2100.)

But I think it'll still work, since it's the automation that fires at 2PM daily.

[Darryl Lee]

Simplified the Due Date formula since due date fields don't actually include a time:

{{issue.summary.replaceAll("Employee Termination.* (19|20|21)","$1").replaceAll("/","-")}}

Added 21 to future-proof it. :-}

[Harish ShanmugamDhanasekar]

@Darryl Lee I have one quick question.

Will this logic works for any number of employee name characters

like for example:

1) Employee Termination Harish 2022-02-21

2) Employee Termination Charles Adam 2022-02-03

3) Employee Termination John Willington 2022-02-03

[Darryl Lee]

Yes, the .* is "any string of characters". Actually let me add a another space to be extra safe, and BTW if any employee happens to have 19, 20, or 21 in their name, this will break.

(Ex: John Hamilton the 19th)

{{issue.summary.replaceAll("Employee Termination .* (19|20|21)","$1").replaceAll("/","-")}}

[Harish ShanmugamDhanasekar]

Definitely , this is very much helpful @Darryl Lee . I will update once it works for me.

Thanks a ton :)

[Harish ShanmugamDhanasekar]

Hello @Darryl Lee 

I have done up to here upon your suggestion.

Can you please confirm whether am I going in the right direction or not?

Also please tell what condition can be used for checking before 2pm due date.

Thanks

[Darryl Lee]

What you want to do is if the Summary matches, then Edit the issue, like so:

For checking at 2PM, you'd want the trigger to be Scheduled, and then to do a JQL search:

And if then for each issue that matches, you can send an email, Slack, etc.

[Jack Brickey]

Could you not consider using an Issuetype that has the termination or hirer date as an actual date field? This is typically how you set these on-boarding off-boarding processes up. 

[Harish ShanmugamDhanasekar]

Thats a good idea @Jack Brickey but there is no such issue type in our organization.

Thanks for the suggestion :)

[Jack Brickey]

Understood. I'm just wondering if it could be implemented and successfully deployed. Basically, if those opening the tickets are using the portal or the application itself then it is easy to achieve success. However, if your team only creates issues via email then that is more challenging as you end up in the situation that you're currently faced with.

[Harish ShanmugamDhanasekar]

Basically a ticket for eg : Employee Termination Name of Employee 2022-02-09 will be created using a simple automation rule like below:

When issue is created:

         if Summary contains Employee Termination keyword:

                                Then the issue will be automatically assigned to team lead and he gets notified via email from no-reply@automation.atlassian.com

[Jack Brickey]

Understand, but can the triggering issue not have the date of termination in a 'Effective Date' custom field? We have this very same process. HR will create a ticket for termination and they are required to enter the termination date via the form. Subsequent issues are created and routed to the necessary responsible parties in each case that termination date is leveraged and carried forward.

I guess my point here is that having the date in the summary is not an effective process. If it can't be changed or simply won't be changed then so be it. But if it can be changed then that is the direction to go IMO.

[Harish ShanmugamDhanasekar]

Hello @Jack Brickey , Thanks a ton for your valuable suggestion.

So, in my case the description part of the ticket contains the termination date.

So, what is the better way to solve my use case?

[Jack Brickey]

Simply create a custom date field and require that field in the initial termination request rather than having the Requestor include it in the Summary. Invariably having a process that requires the requester to enter a date in the summary in a specific format will fail. Humans are not reliable systems. They will forget to add, or use a different format that your regex will not catch, etc. it simply comes down to this statement - any data required to process an issue effectively needs to be in a dedicated field (or new Proforma form) versus buried in a text field such as summary or description. The only time I use regex or contains condition is when the request is coming from a software system where I can control the format. This is just my opinion of course.

[Jack Brickey]

You might also find this article of interest - Forms-Use-Case-New-Employee-Onboarding-Cross-Project 

if the new forms capability was in place at the time I was building out my on boarding and off boarding process I absolutely would've used this methodology. I am anxious to give the new forms capability to try as it is quite powerful.

[Darryl Lee]

Yeah, as you say @Jack Brickey 

The only time I use regex or contains condition is when the request is coming from a software system where I can control the format. 

I made an assumption that @Harish ShanmugamDhanasekar was asking this question because it was likely that they:

might be trying to create tickets from email, which is a pretty common thing.

If the email is computer-generated then it's *possible* to write a regex, but as @Chris Buzon correctly points out, this is definitely complicated.

So to be clear, my proposed solution should only be used ONLY if whatever is creating these Employee Termination requests is a program, NOT a human.

@Harish ShanmugamDhanasekar can you confirm these requests are computer-generated, and that they are coming into Jira via e-mail, hence the inability to require any additional fields?

Because if the Employee Termination requests can actually be started on Jira, then you would have a lot more control over fields, as Jack notes.

But my guess is that the Jira piece is just part of a larger workflow that is out of your control, which means that yeah, you're stuck with parsing email.

[Harish ShanmugamDhanasekar]

After the above steps, a ticket will be automatically assigned to our team lead from no-reply@automation.atlassian.com email id

By looking at this, can you please tell your suggestion? Thanks a ton for your help :)

[Darryl Lee]

Hi @Harish ShanmugamDhanasekar it would really be helpful if you can share a screenshot of the *entire* rule so we can help you more thoroughly.

The other thing is, if you are having problems, it's really helpful to check the Audit Logs and share screenshots of what you see there.

As far as assigning to "team lead", is that a custom field you have?

Or did you mean Project Lead? Or Component Lead?

That kind of assignment cannot be done in Automation, but can be configured in your Project: 

• Add or change your project's default assignee 
• Select a default assignee (Component Leads)

[Darryl Lee]

To further help us help you, please detail:

• What you expect to happen
• What actually happens

[Harish ShanmugamDhanasekar]

Hello @Darryl Lee , Thanks a ton for your help :)

Please give me one day, I will get a good clarity and comment here regarding your queries. Thanks again. 

[Harish ShanmugamDhanasekar]

Hello @Darryl Lee ,

I have clearly known my problem. I will explain it with screenshots.

Please tell your suggestion.

Ticket Summary Format: Employee Termination Billy Bosse 2022-02-09

Basically, the below image is the current rule which checks the summary of the ticket and assigns it by default to a user(team lead). In this case, I have put my name.

So, basically I need to extract the date , IN this case 2022-02-09 from the ticket summary and I need to do the following:

1) Assign 2022-02-09 to DDate (which means due date)

2) Trigger notifications if the issue is not closed by 2 PM on the last working day( which is the DDate).

What I have done so far:

1) I have extracted the termination date 2022-02-09 from the ticket summary and assigned to DDate by using this document:  https://community.atlassian.com/t5/Automation-questions/Take-a-date-from-a-ticket-summary-and-apply-to-Due-Date/qaq-p/1812917 

For this, I have created a variable DDate and please check it in the screenshot attached below.

2) Next I created a JQL Condition which checks DDate = 1d (Not really Sure about this) Please help me here.

3) 

4) Lastly It will send email to my email id .

Waiting eagerly for your suggestion.

Thanks,

Harish.

[Darryl Lee]

Please look at my screenshots from here. 

The way you set a Due Date in Automation is by Editing an issue and updating the Due Date field, as my screenshot shows. Variables in Automation are not the same as issue fields.

To check at 2PM every day if any Employee Terminations that are due that day are still open, you need to create a *separate* Automation rule. The Scheduled trigger in my second screenshot shows how that works, including a JQL query that you will need to modify with your Project Key, and possibly which Status you want to match on.

I would encourage you to read through this guide on Jira Automation and also watch this webinar.

[Harish ShanmugamDhanasekar]

Hello @Darryl Lee ,

I understood your point clearly.

I have tried it and it worked successfully. Thanks a ton for that.

I really appreciate your help.

But what to do if need to check particularly on last working date (due date) at 2pm instead of checking it on a daily basis?

Please suggest me in this case.

Thanks,

Harish.

[Bill Sheboy]

Hello, all!  Jumping in to help as Harish asked this same question via another thread...

Harish, to do that consider creating a rule schedule triggered only on the time of 2pm (no JQL), and then use Lookup Issues with JQL to find any items which are due relative to {{now}}.

Kind regards,
Bill

[Harish ShanmugamDhanasekar]

Can you please tell what query to use to find due date relative to {{now}}?

Kind regards,

Harish.

[Bill Sheboy]

You could use {{now}} and the time zone functions, or just use endOfDay() from JQL, like this...substituting in your project name:

project = myProjectName AND statusCategory != Done AND due <= endOfDay()

[Darryl Lee]

I totally forgot about the Due part, thanks for checking my work, @Bill Sheboy !

[Harish ShanmugamDhanasekar]

Thanks @Bill Sheboy ,

So, what I understood from your comments is that generally we need to do the following

1) create a trigger that runs at 2pm with out mentioning any JQL

2) Now, To ensure that the ticket is related to Employee Termination, I would include a issue fields condition like Summary contains Employee Termination

3) Later I will extract the due date as per @Darryl Lee Solution.

4) Now, I will use the JQL Condition as per your suggestion and finally assign it to team lead.

Does this sequence work? Please tell your suggestion @Bill Sheboy 

Kind Regards,

Harish

[Bill Sheboy]

The missing part is getting the issues with Lookup Issues (if you need a batch) or with a branch (to processing individually).  Perhaps like this:

• trigger: scheduled at 2 pm with no JQL
• branch: on JQL to get the issues of interest
  • action: to extract the due date using string functions already mentioned
  • ...more actions, as needed

[Harish ShanmugamDhanasekar]

Will try this and let you know @Bill Sheboy . Thanks a ton for your kind help.

No words to describe how much you and @Darryl Lee helping me in this issue.

Kind Regards,

Harish.

[Harish ShanmugamDhanasekar]

Hello @Bill Sheboy and @Darryl Lee ,

In the schedule stage (first stage), I mentioned the JQL Condition. After that I done Iike this, but I want to check if due date time exceeds 2pm , I want to send email to particular user. Can you please suggest any possible way for this?

Kind Regards,

Harish.

Here for testing purpose, I mentioned 4.30 pm.

[Kara Cooper]

@Darryl Lee could you help me reserve this rule so that if I update the due date it updates the summary? 
https://community.atlassian.com/t5/Jira-Software-questions/Update-summary-with-updated-due-date/qaq-p/2329197

[Matt Doar]

You have a problem where two kinds of data are being stored in a single field. That's a config problem in how Jira has been used.

Employee Termination Charles Hendridge 2022-02-23

As others have noted, a date field would be appropriate for the date part of that.

[Harish ShanmugamDhanasekar]

Can you please tell how to include date field?

[Chris Buzon]

You can possibly do that using "Advanced Compare Condition" in Jira automation, but it will require that you use the same formatting for the date.  You can pass that condition a REGEX for date matching.

Regex can be quite complicated, and will require a lot of testing and ensuring the way the date is written is always the same in the summary.

I might suggest instead that you set a date field (an existing one, or a custom one) up in the issue creation instead,, maybe even set it as required.  It's a lot more reliable that extracting dates from the summary.

[Harish ShanmugamDhanasekar]

Hello Chris,

Thanks a ton for your response.

In my case, the date would be in the same format everytime.

Basically, after the extraction of date , I need to set it to a field called "due date"

And I need to check whether the issue is not closed by 2pm on the due date.

If it is not closed, I need to send a jira notification to private slack channel and to another email address.

Can you please mention the regex condition for this use case?

Also, please mention how to identify whether the issue is closed or not before 2 pm on the due date.

[Chris Buzon]

Regex is quite complicated, so I can't promise this will work.
You will likely need to find a pattern that works, then test it extensively.

This is what google comes up with for "regex pattern for dates"


This is the explanation text I get as well:

A regular expression for dates (YYYY-MM-DD) should check for 4 digits at the front of the expression, followed by a hyphen, then a two-digit month between 01 and 12, followed by another hyphen, and finally a two-digit day between 01 and 31.

/^\d{4}-(0[1-9]|1[0-2])-(0[1-9]|[12][0-9]|3[01])$/

[Harish ShanmugamDhanasekar]

Hello @Chris Buzon ,

Thanks a ton for your response :)

Just now I found one article according to your suggestion.

https://community.atlassian.com/t5/Automation-questions/Extract-a-date-from-summary-string-and-apply-to-Due-date/qaq-p/1838455

After we assigned a value to due date, can you please let me know how to identify whether the issue is closed or not before 2 pm of due date?

[Chris Buzon]

@Darryl Lee has that covered in his other reply (and it's a much better method, honestly). 

You'll need another/different rule to cover that, since it's checking for entirely different criteria (the status or statuscategory of that ticket at a specific time)

[Harish ShanmugamDhanasekar]

Yes @Chris Buzon , Just now I saw @Darryl Lee comment and its  very helpful in my case :)

I will check that and let you know about the working progress.