Jira Formula for createdDate < ReleaseDate

[Robert G. Nadon]

Hi all,

I am wanted to run a report that will show all issues that were found prior to release.  unreleasedVersions() doesn't not work as that is the release's current state.   So what I was trying to do is to create some JQL that simply says createdDate > releasedDate.   However there does not appear to be anything in JIRA that gives the date of the release date of affectedVersion/s (there is only one as we are using this field for "found in").

The only thing I could think of is to go one by one for each version release date and the JQL:

(version = 1 and createdDate > 1/1) or (version = 2 and createDate > 2/2) or ...

Just wondering if there is any type of thing that can grab the affects version and get the released date of said version.  I do have script runner but am not a groovy expert by any stretch.

Thanks,

Robert

[Thanos Batagiannis _Adaptavist_]

Hi Robert,

You can check if one of the version related ScriptRunner JQLs do the trick.

Or you can create a Custom field of type Date and then set a custom listener that will update the value of this custom field, let's name it Affected Version Release Date, in an Issue Updated event.

The script for the listener will be

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.Issue
import com.atlassian.jira.issue.ModifiedValue
import com.atlassian.jira.issue.index.IssueIndexManager
import com.atlassian.jira.issue.util.DefaultIssueChangeHolder

Issue issue = issue

// get the first affected version
def affectedVersion = ComponentAccessor.getVersionManager().getAffectedVersionsFor(issue)?.getAt(0)?.getReleaseDate()

// update the value of the Affected Version Release Date
def cf = ComponentAccessor.getCustomFieldManager().getCustomFieldObjectByName("Affected Version Release Date")
cf.updateValue(null, issue, new ModifiedValue(issue.getCustomFieldValue(cf), affectedVersion), new DefaultIssueChangeHolder())

// reIndex the issue
ComponentAccessor.getComponent(IssueIndexManager).reIndex(issue)

Now you can use the Script Runner JQL 

issueFunction in dateCompare("", "Affected Version Release Date < created")

Hope that helps

[Ravi Sagar _Sparxsys_]

Yes if you have Script Runner installed then it should work!

[Robert G. Nadon]

I do have script runner.  I have no idea how to create a Date search template, that will hold the releasedDate of Affects Version/s.  Any thoughts on how to get started on that would be great.

As for the version related JQLs those are so close.  However they are queries on fix version/s and affects version/s to get the a set of versions from them that meets the criteria.  I need to grab the date of the version.  We use affects version as "Found in" version and force only a single entry at all times.  If an issue affects two versions there will be two issues, one per version that needs to be fixed.  

The reason for this is that is say an issue with version affects release a and b and has been fixed for a but not b what should the status of the issue be?   For a the issue needs to be at least implemented and for b just the opposite and the issue cannot be in two statuses at the same time.

This is fundamental problem for JIRA that each affects version needs to have a status for each version, but I digress.

[Thanos Batagiannis _Adaptavist_]

Hi Robert, 

I was wrong with my first proposal to create a scripted field, because you cannot use the scripted field with the dateCompare JQL. But I updated my answer with a solution that involves a listener that in every issue update will update and a custom field with the value of the release date of the affected version.

So now you will be able to use the dateCompare to compare a custom field, the Affected Version Release Date with the createdDate (the date that the issue created).

Let me know if this does the trick.

regards, Thanos

[Robert G. Nadon]

Very cool, let me try it.  I will let you know. Thank you!  

One quick question is what is the difference between created and createdDate?  I can JQL with either/both of them in the query and have found no difference in them.  As a matter of fact this JQL will 100% of the time return no values: 

created > startOfMonth() and createdDate < startOfMonth()

[Jesse Wisener]

I searched for this for days, and tried to write it myself, but couldn't figure it out. Thank you for sharing! I modified it to work with FixVersions:

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.Issue
import com.atlassian.jira.issue.MutableIssue
import com.atlassian.jira.issue.ModifiedValue
import com.atlassian.jira.issue.util.DefaultIssueChangeHolder
import com.atlassian.jira.project.version.Version
import com.atlassian.jira.project.version.VersionManager
import com.atlassian.jira.issue.index.IssueIndexManager
import com.atlassian.jira.issue.util.DefaultIssueChangeHolder
 
def issue = event.issue as Issue 
 
// get the first fix version
def fixVersion = ComponentAccessor.getVersionManager().getFixVersionsFor(issue)?.getAt(0)?.getReleaseDate()
 
// update the value of the Fix Version Release Date
def cf = ComponentAccessor.getCustomFieldManager().getCustomFieldObjectByName("Version Release Date")
cf.updateValue(null, issue, new ModifiedValue(issue.getCustomFieldValue(cf), fixVersion), new DefaultIssueChangeHolder())
 
// reIndex the issue
ComponentAccessor.getComponent(IssueIndexManager).reIndex(issue)

I also needed to change the following for some reason in order for it to work:

Issue issue = issue

to

def issue = event.issue as Issue

I also probably included too many things... Thanks again!

[Ravi Sagar _Sparxsys_]

Robert,

It is currently not possible, there is a feature request too for this https://jira.atlassian.com/browse/JRA-22640

I suggest you go through this issue and you will find some workarounds.

-Ravi

[Robert G. Nadon]

Thank you.   Actually I want to filter by affects version released date. But I could see filtering by fix version released date useful as well so I voted for it.   I see some of the work around ideas and will experiment.  I know I can do it with shell scripting and the CLI with:

Get selected issues into an array then

for each item in array do

      get createddate, affects version

            get date of affects version

            if date > createddate return found in unreleased version

            else found in released version

By my user base wants to do it with JQL.