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

[Jay Salvay]

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:

[Jay Salvay]

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

[Jon Bevan [Adaptavist]]

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).

[Jay Salvay]

Perfect Jon, it worked! 

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

Is there good way to debug that you can recommend me?

Here an example of one of the errors I got:

Thanks again,

Javier

[Jon Bevan [Adaptavist]]

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

[Jay Salvay]

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

[Jon Bevan [Adaptavist]]

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

[Jay Salvay]

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,

[Jon Bevan [Adaptavist]]

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