Need to retroactively add resolution with correct resolutionDate, somehow...

[Keith Hillyard]

I have a few JIRA projects that were created without a workflow transition post function to set the resolution in the transition to the DONE status. I have corrected the problem by adding the post function for all transitions to DONE.

However, I now have a data integrity problem because all issues that were previously transitioned to the DONE status do not have a resolution or resolution date.

As an experiment on one of the issues, I was able to set the resolution just fine using Power Scripts. The resolution was set and the resolutionDate was set to the current date time as expected. However, I would like to set the resolutionDate to the changelog history record date when the status was changed to DONE.

I have read all questions regarding resolutionDate and understand that it is a read-only calculated field that looks up the last changelog history record. So I assume that I need to first add a resolution to all affected issues, then change the date of the changelog history record for the resolution change. This can't be done with Power Scripts because I cannot access the changelog history records. I am on JIRA Cloud, so I can't use SQL.

Am I on the right track? Can this be done with the REST API?

[Petter Gonçalves · Accepted solution]

Hello Keith,

I believe the only option to set the Resolution date with the same date of when the issue was transitioned to Done in JIRA Cloud would be to import it using CSV, updating the current issues of your site. These would be the steps:

- Create a filter with all the issues with the incorrect Resolution date

- Export it to a CSV file

- Edit the resolved field with the correct values

- Import it back to JIRA

P.S: The import through CSV will keep all your issue fields except for the history of the issues. I suggest you export it first in a JIRA test environment before update the original issues, just to ensure it's going to work as you expect.

For more information about CSV imports, you can check the documentation below:

- Importing data from CSV

Let me know if you still have any questions.

[Keith Hillyard]

Thanks so much Petterson.  Much appreciated.  Most of the issues are legacy issues, so no problems with losing the history.  And I have a sandbox project to try it out.  I will try it this week.

[Keith Hillyard]

I was able to successfully import resolution and resolutiondate changes per Petterson's suggestion.  And surprisingly, it did preserve and add to the changelog history.  Note that you can export data across projects but you can only import data one project at a time. Do this in a test project first!!!  Here is what I did:

1. Use the Jira Cloud for Sheets Google add-on to export the Jira issues I wanted to change into a Google Sheet.  You can download it here.  You can do it by choosing an existing filter or enter a JQL query.  Remember to include a project filter in the JQL, you can only import to one project at a time.
2. Edit the Google Sheet, updating the resolution and resolutionDate fields as needed.  Do not delete any columns.  CAVEAT...  I knew the resolution value, but had to manually lookup the resolutiondate from the history changelog entry for the status change to Done.  If I had more time, I would have written a script to call the REST API to fetch the data from the changelog.
3. Download the Google Sheet as a CSV file.
4. Open the Jira Cloud Web app and choose "Import issues from CSV".  You will start a workflow to setup and and start the import.
5. Use the chooser button to choose the CSV file, click Next.  There is an option to use an existing configuration file.  Since this is the first time, leave unchecked.
6. Choose the project you will be importing into.  Remember, only import issues to related one project at a time.
7. Enter the date format to match the date format in your exported file.  I had to change the value from the default "dd/MMM/yy h:mm a" to "dd/MM/yyyy kk:mm:ss", click Next.
8. Map "Key"->"issuekey", "Resolution"->"resolution", "Resolved"->"resolutiondate" and "Summary" to "summary".  Do not select the checkboxes next to each field mapping, click next.
9. Click Validate to check your setup.  Correct any issues if necessary.
10. Click Begin Import to start the import.

You will have the option to save the log file for review or debugging and an option to save the import configuration to make it easier to repeat the process.

Worked for me!  Kudos to Petterson.

[Petter Gonçalves]

You are welcome, Keith!

Thank you very much for the details on how you performed the suggested workaround.

Have a nice day.

[David Harkins]

This doesn't work with data center.

I am not seeing the Resolution and Resolved dates to import the data into.

[George Mihailoff]

@Keith Hillyard There is a way to solve this! Try our free addon (Quantify) that has a special tool for this. Because we provide insights for Kanban boards like throughput (rate of resolved ticket per unit of time) we have the data to fix this. This tool will derive the resolution date from the status history!

[Keith Hillyard]

Thanks George!  Good to know that there are other tools out there that can do this.  I have fixed the problem.  But it was time consuming.  I will definitely try this out. Much appreciated.