Filter for subtasks that has a later delivery date than parent task

I need to filter subtasks that has a delivery date later than the parent task. I'm trying to do a comparison between dates, but I'm not able. I tested this: project = MONRO AND issueFunction in subtasksOf("Delivery Date") >= "Delivery Date" without luck

Please see the following image for more information:

SR_Filter.png

2 answers

Hi Jay - at the moment we only support comparing dates on individual issues, not between issues or their parents.

I'll raise this internally though and see if we can come up with a way to achieve what you're looking for.

As an aside, your query doesn't work because the text between the subtasksOf() brackets must be a valid JQL query, in other words something like this:

issueFunction in subtasksOf("project = MONRO AND 'Delivery Date' >= now()")

Additionally, the "issueFunction in subtasksOf" clause in your query returns a list of issues, so you can't then compare the results of that clause using >=

Thanks,
Jon

Hi Jon,

Thanks for your message. OK, so there is no way to get a JQL advanced query for this. However, I would like to ask you if you think it can be built an script to do this. If so, do you think you can help me out with some code to start?

Thanks,

Hi Jay,

I hope this helps:

// Fetch the custom field ID we're interested in
def fields = get("/rest/api/2/field")
.asObject(List)
.body
def deliveryDate = fields.find { it.name == 'Delivery Date' }
logger.info("Custom Field: ${deliveryDate}")

// Get all the subtasks for a project that have a delivery date
def subtasks = get("/rest/api/2/search")
.queryString("jql", "project = MONRO AND issuetype in subtaskIssueTypes() AND \"${deliveryDate.clauseNames[0]}\" IS NOT EMPTY")
.queryString("fields", "parent,${deliveryDate.id}")
.asObject(Map)
.body

logger.info("Found ${subtasks.total} subtasks with delivery date")

// Extract their parent keys
def parentKeys = subtasks.issues.collect {
it.fields.parent.key
}.unique()

logger.info("${parentKeys.size()} parent issues")

// Get all the parent issues
def parents = get("/rest/api/2/search")
.queryString("jql", "issue IN (${parentKeys.join(',')})")
.queryString("fields", deliveryDate.id)
.asObject(Map)
.body

// Build a map of parent key -> date field value
def parentKeyMap = parents.issues.inject([:]) { map, issue ->
map[issue.key] = issue.fields[deliveryDate.id]
map
}

// Filter the subtasks and generate a nice map to print out the issue details
def subtasksThatHaveDateAfterParent = subtasks.issues.findAll {
parentKeyMap[it.fields.parent.key] == null ||
parentKeyMap[it.fields.parent.key] < it.fields[deliveryDate.id]
}.collect {
[
subtask: it.key,
subtaskDate: it.fields[deliveryDate.id],
parent: it.fields.parent.key,
parentDate: parentKeyMap[it.fields.parent.key]
]
}

subtasksThatHaveDateAfterParent

Thanks,
Jon

Thanks Jon! I'm a little new on this scripting thing, that would help.

Let me ask you another question about execution and debugging of the script. I'm trying to create an script, but actually, I'm not sure either where to paste and execute this code. Can you help me out?

I'm thinking that I will need to create a transition to execute this code, right?

Thanks!

Javier

That depends on what you're trying to achieve with this script.

You can run the code above as it is from the Script Console, or you could create a Script Listener for Issue Created/Updated events and then modify the script above to perform some other action with the results (for example, comment on the issues).

Alternatively you could create a Scheduled Job and have this code run every day at a certain time - again you'd probably want to modify the script to do something with the results (e.g. assign yourself to the relevant issues).

And yes, you can put this script on a Workflow Post-Function to run every time an issue is transitioned - again, you'll need to modify the script to do something useful with the results (e.g. notify someone of each issue in the result set).

Perfect Jon, it worked! 

Let me ask you in regards to debugging. I got several errors after execution:

image.pngIs there good way to debug that you can recommend me?

Here an example of one of the errors I got:

SR_error_detail.png

Thanks again,

Javier

 

Hi Javier,

Those "errors" are reported by our type-checking code which tries to report possible bugs in your code.

Unfortunately it can't always figure out what types are being used so unless we add in additional type hints we get these errors even though nothing is wrong. I've just become lazy with adding in the relevant type hinting.

The following code should not have those errors:

// Fetch the custom field ID we're interested in
List<Map> fields = get("/rest/api/2/field")
.asObject(List)
.body

Map deliveryDate = fields.find { it.name == 'Delivery Date' }
def clauseNames = deliveryDate.clauseNames as String[]
logger.info("Custom Field: ${deliveryDate}")

// Get all the subtasks for a project that have a delivery date
Map subtasks = get("/rest/api/2/search")
.queryString("jql", "project = MONRO AND issuetype in subtaskIssueTypes() AND \"${clauseNames[0]}\" IS NOT EMPTY")
.queryString("fields", "parent,${deliveryDate.id}")
.asObject(Map)
.body

logger.info("Found ${subtasks.total} subtasks with delivery date")

// Extract their parent keys
def parentKeys = subtasks.issues.collect {
(((it as Map).fields as Map).parent as Map).key
}.unique()

logger.info("${parentKeys.size()} parent issues")

// Get all the parent issues
def parents = get("/rest/api/2/search")
.queryString("jql", "issue IN (${parentKeys.join(',')})")
.queryString("fields", deliveryDate.id)
.asObject(Map)
.body

// Build a map of parent key -> date field value
def parentKeyMap = parents.issues.inject([:]) { Map map, Map issue ->
def issueFields = issue.fields as Map
map[issue.key] = issueFields[deliveryDate.id]
map
} as Map

// Filter the subtasks and generate a nice map to print out the issue details
def subtasksThatHaveDateAfterParent = (subtasks.issues as Map).findAll {
def issueFields = (it as Map).fields as Map
def parentKey = (issueFields.parent as Map).key
parentKeyMap[parentKey] == null ||
(parentKeyMap[parentKey] as String) < (issueFields[deliveryDate.id] as String)
}.collect {
def issueFields = (it as Map).fields as Map
def parent = issueFields.parent as Map
[
subtask: it.key,
subtaskDate: issueFields[deliveryDate.id],
parent: parent.key,
parentDate: parentKeyMap[parent.key]
]
}

subtasksThatHaveDateAfterParent

 Thanks,
Jon

Thanks Jon! that's great! I will start to compare between this script and the previous one to learn more about this. 

Suggest an answer

Log in or Sign up to answer
Community showcase
Published yesterday in Statuspage

194 years of downtime: looking back on incident data from 2018

Statuspage customers logged more than 194 years of collective incidents in 2018. That’s a whopping 87% increase from the  104 years logged in 2017 , and we aren’t even through December yet....

58 views 1 5
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you