Hello, I am working on a Jira Automation to get currently active projects (tasks) and their subtasks. The subtasks have a custom field called 'Actual Start' and a 'Due Date.' Due Date is simply a date, while Actual Start is a DateTime stamp for the scheduled start time of the item.
My JQL is:
Project = 'Environment Management' AND issuetype in (Task) AND status not in (Archived) ORDER BY DueDate ASC
The top-level tasks seem to sort in the expected output by DueDate, but the subtasks do not.:
Any idea how to get this to sort correctly?
NOTE: I understand that this is a bit of an unconventional use of Jira, but I am using it for tracking system upgrade/implementation timelines and automating the weekly report that goes out to stakeholders for tracking progress.
Hi @Wes Crockett -- Welcome to the Atlassian Community!
Based on the details you posted in response to Trudy's questions, what you ask for also ordering the subtasks under each parent is not easy...
JQL only has basic ORDER BY syntax, and you have nested the subtask list within the results of the lookup issues results / iterations. And so the order of the subtasks will be based on how they exist within the parent issue: the ORDER BY has no impact.
Additionally, JQL has no GROUP BY syntax, as would an SQL, to simplify the groups.
To do what you asked, the parent issue and subtasks need to be returned at the same level within the JQL result, allowing the ORDER BY to apply to all of them together. So a workaround is to add a custom field which essentially build a sorting key for each issue, and then change to ORDER BY that field. This is often a brittle solution as the custom field would need updates whenever the due date changes.
Kind regards,
Bill
Oooph. rough limitation :(
I may have to go to a API based solution and just use the JSON returned.
Thank you for the insight
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello @Wes Crockett
Welcome to the Atlassian community.
The output you have shared does not appear to be from any native functionality within Jira Cloud. Are you using a third party app to get the output organized by parent Task and then subtasks nested below that?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks for the reply. I should have included more about the automation.
It is a three step automation doing a weekly email from a JQL lookup:
I am using {{#lookupIssues}} followed by {{#subtasks}} to generate the output in HTML tables with CSS for styling.
Sorry for that lapse in details.
Here is the main part of the email, minus the CSS and miscellaneous HTML parts:
{{#lookupIssues}}
<h2>{{summary}}</h2>
<table>
<thead>
<tr>
<th>Task</th>
<th>Completion Status</th>
<th>Scheduled Date/Time</th>
</tr>
</thead>
<tbody>
{{#subtasks}}
<tr>
<td>{{summary}}</td>
<td>{{status.name}}</td>
<td>{{Actual start.convertToTimeZone("America/Los_Angeles").format("MM/dd/YYYY, h:mm a")}}</td>
</tr>
{{/subtasks}}
</tbody>
</table>
{{/lookupIssues}}
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You said you wanted the information ordered by Due Date, and you use that in your filter.
But your email is printing Actual Date, not Due Date. So how do you know they are not in the right order?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Yes, the email is only showing the Actual Date, not Due Date, The JQL statement, I believe, is all that impacts the order of output.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.