Automate flag when issue more than X days in column

[Melina Parisi]

Hello! 
I was wondering if there's any way to flag automatically a ticket when the field "days in column" is more than X days. 

I've tried with automations but I can't find the way to add the "days in column" 

Thanks in advanced

[Bill Sheboy · Accepted solution]

Hi @Melina Parisi 

The information about "days in column" appears to be dynamically calculated from the history, so it is not easy to access from an automation rule.

Some possible work-arounds depend about the status values and columns for your board, and your desired level of effort:

• If your steps are just to-do, in-progress, and done, you could compare the Status Category Changed Date to Now to measure the time, such as this expression for the elapsed business days:

{{#=}}max(1,{{issue.statuscategorychangedate.toDate.convertToTimeZone(reporter.timeZone).diff(now.convertToTimeZone(reporter.timeZone)).businessDays}}){{/}}

• Another approach is to add a custom field to save when an issue transitions into a status (or column's mapped status).  Then perform the same diff() on Now for the elapsed time.  To do this, add the custom field, add one rule to set it on issue transition, and add a scheduled rule (e.g every 24 hours) to check your condition for flagging.
• A more complicated approach would be to call the REST API from the rule to read issue history and directly measure the time.  This could be a very slow rule, depending upon the number of issues on the board.
• There are marketplace reporting addons which do this kind of analysis.  No doubt...others will reply to this thread to suggest those  :^)

Kind regards,
Bill

[Melina Parisi]

Thank you so much Bill! the first option worked like a charm!! 

[Bill Sheboy]

Awesome! I am glad to learn that helped.  Please consider marking this question as answered.  That may help others in the community with similar needs find solutions faster.  Thanks!

[beth_sutton]

Hi Bill - I would also like to do the same as Melina but I am unfortunately struggling! Whenever I try to set up a scheduled rule, I get this error message and I'm at a bit of a loss for how to solve it:

This component may be incompatible

One of the following needs to be produced earlier in the rule by the trigger or with a 'for each' branch for this component to work:

• Issue

Any advice would be greatly appreciated, thank you!

[Bill Sheboy]

Hi @beth_sutton 

First thing, as this is an older thread I recommend creating a new question, and perhaps including a link to this one.  That will ensure the maximum number of people see it to offer suggestions.  When you create your question, please include an image of your complete rule and the audit log details showing the rule execution to provide context.

Back to your question...

That is a new / recently-added error message to help rule writers know an action / condition needs an issue to act upon and none are present.  I hypothesize your rule's scheduled trigger does not have any JQL and so there are no issues to process.  Seeing your complete rule, and the trigger's details, will help to confirm this.

Kind regards,
Bill

[beth_sutton]

Hi Bill :)

My apologies, I realised after I sent my comment that I should probably set up a new question, which is what I have done, but then forgot to come back and clarify here 🙈

Someone has replied to my new question and said something very similar to you, i.e. I have not included any JQL so I'm currently working on that! 

Apologies again and thank you for getting back to me :)

[Bill Sheboy]

No worries; I wanted to help ensure your question was seen so the community could offer ideas.  I hope you have a great day!