How do I update status macro automatically if my confluence table task checkbox is ticked/marked don

Question

I am trying to prepare a checklist with Status macros and RAGs colors to it along with confluence task list macro in the confluence table as these columns.

How can I achieve without any third-party tools/macros and only either by markup wiki language code or configuration or existing macros where I can change the status macro from In Progress to Done and color to Green automatically once my task checklist tick box is clicked/marked as done?

Yatiraj Sharma · Answer

@Stiltsoft support

We have installed the Handy Macro at our testing site, and I am trying to see if it works with Handy Macro in terms of updating Status automatically and as well as manually

Could you please support if you know how we can join these two solutions together to minimize the page edits and manual updates?

Use case:

Part A: Checklist status should automatically be updated

1) To "Done" status When Checkbox is ticked without page edit. (From Any Status)

2) To "IN PROGRESS" status When Comment column is updated with page edit. (From Any Status)

Part B: Checklist status can manually be updated to "Blocked" status when actual activity is blocked. (Ignore the comments section and let it be blocked, DO NOT move to "IN PROGRESS" even if Comment column is updated)

Note:  Two parts to it

1) Status: They are nothing but only statuses like "TO DO", "IN PROGRESS", "BLOCKED" and "DONE"

2) RAGs: " They are nothing but actual indicators to know if activity requires any help or not/is on Time or Not

"RED" Need immediate help to resolve/ Missed the Due Date/ETA/Going to miss Due Date/ETA 100%

"AMBER" May need help in near time/ May miss the Due Date/ETA 50%

"Green" Not needed any help and no issues/On track 100%

"BLUE" Complete/Done

So based on Due date we should be able to update the colour of the statuses

Yatiraj Sharma · Answer

This is the solution which was suggested by @Stiltsoft support and modified to my need. SELECT *, CASE WHEN 'Checkbox' = "Completed" THEN FORMATWIKI("{status:colour=Blue|title=DONE}") WHEN 'Checkbox' != "Completed" AND 'Comments' IS NOT NULL AND DATEDIFF(day,"today",'Due Date') >= 10 THEN FORMATWIKI("{status:colour=Green|title=IN PROGRESS}") WHEN 'Checkbox' != "Completed" AND 'Comments' IS NOT NULL AND DATEDIFF(day,"today",'Due Date') BETWEEN 1 and 9 THEN FORMATWIKI("{status:colour=Yellow|title=IN PROGRESS}") WHEN 'Checkbox' != "Completed" AND 'Comments' IS NOT NULL AND DATEDIFF(day,"today",'Due Date') <= 0 THEN FORMATWIKI("{status:colour=Red|title=IN PROGRESS}") WHEN 'Checkbox' != "Completed" AND 'Comments' IS NULL AND DATEDIFF(day,"today",'Due Date') >= 10 THEN FORMATWIKI("{status:colour=Grey|title=TO DO}") WHEN 'Checkbox' != "Completed" AND 'Comments' IS NULL AND DATEDIFF(day,"today",'Due Date') BETWEEN 1 and 9 THEN FORMATWIKI("{status:colour=Yellow|title=TO DO}") WHEN 'Checkbox' != "Completed" AND 'Comments' IS NULL AND DATEDIFF(day,"today",'Due Date') <= 0 THEN FORMATWIKI("{status:colour=Red|title=TO DO}") END AS 'Status' FROM T1

Yatiraj Sharma · Answer

This is the solution which was suggested by @Stiltsoft support and modified to my need. SELECT *, CASE WHEN 'Checkbox' = "Completed" THEN FORMATWIKI("{status:colour=Blue|title=DONE}") WHEN 'Checkbox' != "Completed" AND 'Comments' IS NOT NULL AND DATEDIFF(day,"today",'Due Date') >= 10 THEN FORMATWIKI("{status:colour=Green|title=IN PROGRESS}") WHEN 'Checkbox' != "Completed" AND 'Comments' IS NOT NULL AND DATEDIFF(day,"today",'Due Date') BETWEEN 1 and 9 THEN FORMATWIKI("{status:colour=Yellow|title=IN PROGRESS}") WHEN 'Checkbox' != "Completed" AND 'Comments' IS NOT NULL AND DATEDIFF(day,"today",'Due Date') <= 0 THEN FORMATWIKI("{status:colour=Red|title=IN PROGRESS}") WHEN 'Checkbox' != "Completed" AND 'Comments' IS NULL AND DATEDIFF(day,"today",'Due Date') >= 10 THEN FORMATWIKI("{status:colour=Grey|title=TO DO}") WHEN 'Checkbox' != "Completed" AND 'Comments' IS NULL AND DATEDIFF(day,"today",'Due Date') BETWEEN 1 and 9 THEN FORMATWIKI("{status:colour=Yellow|title=TO DO}") WHEN 'Checkbox' != "Completed" AND 'Comments' IS NULL AND DATEDIFF(day,"today",'Due Date') <= 0 THEN FORMATWIKI("{status:colour=Red|title=TO DO}") END AS 'Status' FROM T1