Bulk updating date field based on comment text and date

[Chris Ripley-Duggan]

Hi all,

We have a custom "Publication Date" field that I'm attempting to bulk populate. I'm looking to set that field to the date that a comment was added which features a particular URL.

I've already created a project-based automation to achieve this going forward, but now wondering if I can bulk update the ~300 existing tasks...I have a horrible feeling I cannot, but thought I would ask! Thank you!

[Trudy Claspill · Accepted solution]

Hello @Chris Ripley-Duggan 

Welcome to the Atlassian community.

I believe you can do this with a Scheduled automation rule.

Create a rule with the Schedule trigger and add to it a JQL that selects the issues you want to review. Uncheck the box that says to only pick issues that have changed since the last time the rule ran. It doesn't matter what schedule you set as long as it is in the future. You will be running the rule once manually and then disabling it.

Then add a For Each Advanced branch that will let you iterate through each issues comments.

Within the For Each branch add a Condition to check the comment body for the URL. Here I'm checking just for the presence of the text keyword.

This assumes you will have only one comment that has the matching text.

And then, still within the For Each branch, add an Edit action to edit the field in the issue using the created attribute of the comment. Here I just used a Log action to log the information to the rule audit log.

You will likely have to apply some formatting to the created data to get it in the right format to insert into the field in the issue.

After publishing the rule you can click the Run Rule button to run it immediately. After it completes, you can this disable it so it won't run again.

The For Each branch will be executed against each issue that is returned by the JQL in the Scheduled trigger.

[Chris Ripley-Duggan]

Thank you for the extensive answer, Trudy! I really appreciate you putting this together. Going to try it out now!

[Chris Ripley-Duggan]

Success! 🥳 Thank you so much @Trudy Claspill! This is going to save so, so much time!

[Trudy Claspill]

You're welcome!

[Chris Ripley-Duggan]

Hi @Trudy Claspill 

Thanks again for this! It's saved me a load of time with tasks that have only mentioned "the keyword" once. I was wondering if you knew off the top of your head if it's possible for a smart value or advanced branching to look at the first instance of a comment with the keyword and then stop. As what I'm experiencing is it updating single tickets back and forth a few times before ending up on the latest comment.

[Trudy Claspill]

I don't know for sure that this would work, but you might try adding a condition to compare the formated Created date of the comment to your Publication Date field.

I have not tested this to see what it would do if the Publication Date was empty.

[Chris Ripley-Duggan]

That definitely improved the results! You'll see it only ran through and updated the ticket twice on the latest run, compared to the 4x earlier. I'll keep tinkering and see what I can do!

[Trudy Claspill]

Instead of using a straight comparison of the two fields I think you may want to use the diff function and compare that as greater than 0.

https://support.atlassian.com/cloud-automation/docs/jira-smart-values-date-and-time/

If the current Comment Created date is 1 or more days newer than the date already recorded in Publication Date, then this condition should pass and the Publication date field should be updated.

I think you should probably also add a Re-fetch Issue action in the branch to ensure that if the Publication Date was updated once during the execution of the branch then the next loop through the branch will have the most recent data for the issue.

[Chris Ripley-Duggan]

Thank you! I'm getting a "success" status in the audit log, but, that the Advanced Compare Condition did not match the condition.

[Trudy Claspill]

An excellent tool for debugging rules is to use the Log action to print messages in the Audit Log for the rule execution. You can add your smart values to these actions to see what value Jira is getting for the smart value, to determine if it matches your expectations.

In this case I would add Log actions to print each date separately and then also the entire smart value used in the comparison. Smart values can be case sensitive and are space sensitive so make sure that what you use in a Log action matches exactly what you are using in Conditions and other actions.

(I can see what the problem is, but I'm trying to step you through how to figure it out so you'll have some tools for debugging future problems.)

[Chris Ripley-Duggan]

I'll play with the Log action and see what I can get from that :)