Smart value for the date the issue transitioned to "In progress"

[Áron]

Hi All,

Could you please help me with the smart value of the date when an issue has been transitioned to "In Progress"?

E.g.:

{{issue.created.format("dd/MM/yyyy")}}

I would need this for an automation rule that fills in the "Start Date" field with the date when an issue has been transitioned to "In progress" for future cases and retrospectively as well.

Thanks a bunch in advance!

[Bill Sheboy · Accepted solution]

Hi @Áron 

As you note, for future transitions this problem is straightforward to solve: save {{now}} on the transition.

For prior items which are still in progress, you can use this to capture the start of the first "in progress" category status:

{{issue.statuscategorychangedate.toDate}}

For prior items which are are now done, you would need a bit of complicated work pulling that information from the change logs, using the REST API, one issue at a time.

Best regards,

Bill

[Áron]

Thanks Bill. Worked like a charm. 

I am not sure that I want to start REST API tinkering for the rest of the tickets. But if you could help me figure out a way I would greatly appreciate it. 

[Bill Sheboy]

Hi @Áron 

I am glad to hear that helped.  If you are interested in getting at the historical information using the REST API, please take a look at the explanation from @Gideon Nolte [Jodocus]  in this thread.

__Bill

[Karmene Hassell]

Why mess with REST API? Use Automation TEST Manually to verify.

[Áron]

Thank you Karmene. 

Could you please elaborate? How can this automation fill in the field with the date the issue had been transitioned to "in progress" retrospectively (some tickets are already in another status)? 

This line: 

{{issue.statuscategorychangedate.toDate.jiradate}}

 only gives back the date of the last transition to whatever status and not specifically the date of "in progress" status. 

[Gideon Nolte [Jodocus] · Accepted solution]

Hi @Áron , I may have solved your problem...

Intro

Ok, this might look a little scary at first, but we will get through this!

Buckle up! Here we go:

This answer will be based on a handful of assumptions about you:

• you really need this to work
• you are committed to look a bit over your plate

and your problem:

• you are only interested in the latest transition to "In Progress"
• the first workflow-transition ever done on the issue is the transition to "In Progress" 

The Jira Cloud API & Web Requests

For this automation we will use the "Send Web Request" Action against our own Jira Cloud Rest API. To get a response from the API you will have to authenticate your requests. You can follow this guide on how to do it.

After you have your authentication header set, you will need to set your request URL to this:

{{baseUrl}}/rest/api/2/issue/{{issue.key}}?expand=changelog

Change the HTTP Method to GET, send an empty body and select the option to delay the subsequent execution of the role.

Smart Values, Webhooks & Variables

Next, you can create a Smart Value Variable with the "Create Variable" action to get the 'Created Date' for the oldest/last item in the history of the issue. Name it however you like and set the value to:

{{webhookResponse.body.changelog.histories.last.created}} 

Done!

You can then use the newly created variable to set the required field to the date.

Note: While the API provides all information needed to actually determine the first item in the issue history that was of type "status" and has a toString of "In Progress", the limited possibilities of iteration on Smart Values (especially for Webhook Responses) makes it merely impossible to set up a system to do this in practice.

I hope this helps and is feasible for you, feel free to ask if you run into any problems.

Greetings
Gideon 

P.S.: I am really not that sure on how 'correct', 'best-practice', 'bodge' or 'hack' this solution would be considered as I only recently joined the Community Threads. Maybe @John Funk  and @Alexis Robert have a take or an opinion on this pattern to enrich issue information.

[Áron]

Hey @Gideon Nolte [Jodocus], I really appreciate this, thank you! 

My only problem is that there are a few transition steps before we reach 'In progress' as our workflow looks like this: Created->Backlog->Selected for Development->In progress->..etc.

@John Funk's answer seems to be the one I am looking for, but there is a slight issue here that I cannot set a JQL query as a value for the 'Start date' field. Only a smart value or copy from another field's value.

So my plan was to set up a rule like this:

1. Scheduled trigger with a JQL query on all my tickets in the given project.

3. Set the value for 'Start date' based on a {{SMART VALUE}} where the smart value = the date the ticket has been transitioned to 'In Progress'

So my general problem is that I have zero clue what smart value could dig up the date of transition. But i guess it will look sthg like this: 

{{issue.updated.isEquals(issue.created)}}

[Gideon Nolte [Jodocus]]

Heureka! Got it running! 

So yeah, none of the Smart Values really turned out this helpful here but with a bit of magic help from our friends type-casting and regular expression there is nothing to be worried about! :D

As I mentioned in my first post, the API provides the information you need but using Smart Values only it becomes hard to access them. Once melted into a Smart Value Variable our JSON-like Smart Value {{webhookResponse.body}} turns into a sad little string - making iterations and so on quite painful experiences. I tried various different setups and came up with this solution (for some reason #if won't work in Smart Value list loops, so yeah - buckle up again...) 

Once again setup the request action so it receives the data.

Then create a variable called "richDateString" with the following value:

{{#webhookResponse.body.changelog.histories}} {{created}}${{#items}}{{field.indexOf("status")}},{{toString.indexOf("In Progress")}}x{{/}}#{{/}} 

This will produce a long string containing all timestamps the issue ever changes with as well as information on whether the field of the change was status and the toString was "In Progress" - again I wish the conditionals would have worked but well...

Once we know how the information we are looking for is represented (in this case its a "0,0" we append to the date info when both Strings (field and toString) matched), we are able to use regular expressions to match this pattern. 

Since we want the first time it changed to "In Progress" we reverse the whole string and look for our pattern - again I tried a few other options but this just works.

So let's create a variable called "reversedDates" with the following value:

{{richDateString.reverse()}}

Now just create one last variable for the date, give it a name you like and set the value to:

{{reversedDates.match("0,0.*?\$(.*?) ").first.reverse()|reversedDates.match("0,0.*?\$(.*?) ").reverse()}}

And there you have it: A Smart Value containing the date when your Issue was first moved into "In Progress".

To be honest, this problem got me quite hooked as I felt it was wrong that this is not solvable - this is no go to solution but a bodge that works... It ain't beautiful but I still love it! 

Hope this does it for you too, feel free to ask if you run into any problems setting up this rule :)

Have a great weekend. 
Gideon

[Bill Sheboy]

Hi @Gideon Nolte [Jodocus] 

Thanks for your walk-through on this approach.  I definitely understand getting hooked on solving these automation puzzles.  ;^)

One need I find with complex rules is how to document the approach used so the rule is maintainable and not "too clever".  I am trying to add an explanation paragraph in the rule description and log messages thoughout as we don't have rule comments yet in the language.

What are your ideas to improve maintainability of the more complex rules?

Best regards,

Bill

[Áron]

Thanks @Gideon Nolte [Jodocus] ! You are the MVP here. I will dig in and start implementing. 

Also thanks to everyone in this collab! I really appreciate all your help!

[Katarzyna]

Hi @Gideon Nolte [Jodocus] 

Thank you for your answer! It looks amazing. I'm trying to implement it in my automation but I received some errors.

Error response HTTP body:{"errorMessages":["Issue does not exist or you do not have permission to see it."],"errors":{}}Error publishing webhook. Response HTTP status:404

Do you know what can be a reason? 

What I want to achieve:

I want to update the Due Date field with the date when the issue changed its status to Resolved.

It looks like a problem with authorization. I checked this rest in the postman and everything works very well. Do you know how to authenticate in the automation?

[Gideon Nolte [Jodocus]]

Yeah this looks a lot like an issue with the authorization or possibly even the account used for authorization not having the right permissions. Did you try postman with the exact same Header and issue key? Maybe check for typos or white-space around the values. Can't really tell you anymore ideas on how to fix this based on the post...

Furthermore once you got the request working, you do not need the bodged way to make this work with the time of resolution.

{{webhookResponse.body.fields.resolutiondate}} should return the value you are looking for - so no need for converting back and forth between list and string with regex and so on. 

Hope this helps 
Gideon

[Alexis Robert]

Hi @Áron , 

if you trigger your rule when the transition happens, then you can use the smart value {{now}} as this will capture the date at exactly the moment that the status changed.

Let me know if this helps, 

--Alexis

[Áron]

Hi Alexis,

That part is clear, thank you. My problem is with this: " for future cases and retrospectively as well." So I need this rule to check previously transitioned issues, and retrospectively set the start date, hence the trigger you suggested is not what I am looking for.

Thanks

Aron

[John Funk]

Hey Aron,

Then you can use a JQL condition that looks like:

Project = ABC and status changed to "In Progress" during (2020-01-01, 2020-01-31)

[fziesemer]

Hi John,

Would you be able to share a screenshot of the automation you describe?