I Am trying to write a JQL with the due date of a story issue

[Heinrich Graupner]

Hello Jira Community. 

I am trying to write a JQL to query issues in a certain status AND where the due date of a certain story issue in that project has been passed. 

The reason for this is that we want to insert milestone dates into the story issue and then compare issues in a certain status with a certain milestone date within the story issue, to determine if the specific issue is late to the milestone or not.

This in turn this will then cause the issues on a custom chart to turn red, or not.  

According to my knowledge, this is not possible on Jira Cloud as per standard. But this is a case where we hope someone else has experienced the same problem? Another option would be that we need project-wide variables (the milestone dates of the project) so they can be used in a JQL to determine the progress of issues according to milestones.  

Any help would be most appreciated. 

Best of regards

Heinrich

[Heinrich Graupner]

Support from the Atlassian team would be widely appreciated. This is a matter that we hold with very high value to and believe that it would open a lot of doors for other users also. 

It would take project management and tracking to the next level!

[Trudy Claspill]

@Heinrich Graupner 

Note this is a user community. While some Atlassian support personnel may monitor the community, if you want to work directly with Atlassian Support you need to open a support ticket rather than creating a community post. To create a support ticket go to:

https://support.atlassian.com/contact/#/

Atlassian team support is available for paid subscribers of the products.

[Trudy Claspill]

Hello @Heinrich Graupner 

Can you provide sample data that clarifies your need?

Which issues are you looking at for status?

Which issue has the Due date?

[Heinrich Graupner]

Hi @Trudy Claspill 

See attached screenshots.

We are looking for all types of issues in that certain status. 

A story type issue has the dates. 

Best of regards

Heinrich

[Trudy Claspill]

@Heinrich Graupner 

I am still trying to understand your need.

Let us says that issues XXX-1, XXX-2, and XXX-3 all have the statuses that you are looking for in your filter.

Which issue has the date that you want to check?

[Heinrich Graupner]

@Trudy Claspill 

It will most probably be an issue not in the same status. Call it issues XXX-4. 

The only way to distinguish and find that issue is the fact that it is the only story issue type in that project. 

[Trudy Claspill]

@Heinrich Graupner 

How do you know that the date in XXX-4 is relevant to XXX-1, XXX-2, and XXX-3?

Without including a third party app one way to get to the end result you want (getting a list of "overdue" issues based on a date stored in a different issue) is to add the date field to the issues where you are checking the status, and then update the date in those issues using an Automation rule that triggers when the date in XXX-1 is updated. But this would require there being some relationship established between XXX-1 and the other issues (such as linking the issues, or a unique field value that they all share) where that relationship is something the Automation rule could evaluate.

If you were able to maintain the date within the individual issues (in XXX-1, XXX-2, XXX-3, and all the other issues where you would be checking that status) then you could have a filter for 

status not in (a, b, c) and [dateField] < now()

You would not have to find a way to include XXX-1 in the filter at all.

[Heinrich Graupner]

@Trudy Claspill 

We will be using a custom field date picker in issue XXX-4, if this custom field is not empty, it will be relevant. 

Example: So let us presume the custom fields name in XXX-4 is "Design end date" and issue XXX-1 is still in one of the design statusses, we want to extract that data and show it on a dashboard chart, because our design team has missed the deadline in issue XXX-1. 

Using the automation rule is an option. Seeing that we are on the standard plan, we only have 500 executions per month. That 500 execution we are already basically on the limit with. 

In the event that we go with the option to try and update the field in all relevant issues in the project. What third party apps do you know of that would synchronize the dates in ALL of the issues if the date in XXX-4 is update. (Creating some relationship would be necessary I agree). We use JWT workflow toolbox which is great! But also workflow event driven and I cannot see that it will help us update all of the relevant issues in the project with the date field in XXX-4. 

Many thanks for the help so far Trudy

[Trudy Claspill]

I am 95% sure that the 500 automation/month limit applies to Global rules and single rules that are configured to run against more than one project. Would this rule have to run against more than one project?

I don't have any specific recommendations about other apps that are capable of synchronizing data between issues within a single JIRA instance. You might find such apps by searching the Marketplace for "jira sync issues".

[Heinrich Graupner]

@Trudy Claspill 

A funny thing happened. Now after trying it as a single project automation rule, it seems to work. But I dit not have enough time to test as per 12 hours only a certain time/processing amount is allowed. For larger projects, this is thus a major setback. 

I am still on the hunt for other software in the marketplace that can help. But most synchronization software carters for users looking to synchronize between different projects or Jira instances.