JQL Last Updated Record based on Custom Field

Sara Toliver November 21, 2015

I have a custom field (Account) and a custom field (Status - Happy, Sad, Indifferent).  We will create a task for each time we reach out to the account to gauge their status.  The status could change each time we reach out to the account, but I want to know the last status for each account.

On Nov 1 we reached out to the account and they were Sad.  On Dec 1 we reached out to the account and they were Happy.  I need to know that they are now Happy.

Any recommendations on the JQL I could use to find the last resolved record for an Account?

3 answers

1 vote
Pablo Beltran
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 21, 2015

JQL filters return issues pnly, therefore getting specific issue data like the history of changes of a custom filed along the time is not possible with JQL.

However, there are alternative ways to get such information via the SQL for JIRA:

select c.fromvalue as "Previous State", c.created as "Date"  
from issues i inner join isssuechanges c on c.issueid=i.id 
where c.field='Status' and i.key=? 
order by c.created desc

would provide the history of the Status  custom field along the time given a particular issue key = ?

You might also want to show the results above on a table in the Issue Detail View by creating a custom field for the SQL query above with SQL for JIRA Custom Fields extension.

Or maybe you prefer to create a report for some issues by using the SQL for JIRA Reports and Gadgets.. extension.

Pablo Beltran
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 21, 2015

Oops, unfortunately, not available for Cloud.

0 votes
Midori
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 26, 2015

I would model this like this:

  • Every "reach out" is a sub-task (of a custom type) of the issue which has the custom field "Account".
  • The "reach out" sub-task type also has a custom field "Status".

This every reach out is maintained separately, and can handled in JQL as sub-task.

 

0 votes
MikeyS
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 22, 2015

Is your Account field a select list or a text field?  The query will look slightly different depending on the field type.

Your JQL statement will also depend on how you are tracking dates.  Do you have a separate custom field for tracking the date that you reached out to the customer or do you only have JIRA's standard date fields (e.g. Created, Updated, Resolved)?

In this example I'll assume the Account field is a select list and you have a Contact Date custom field.

Project = MYPROJ AND Account = "Customer Co." ORDER BY "Contact Date" DESC

Your most recent contact with that account should then appear as the top issue in the result set.

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events