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

[Yatiraj Sharma]

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?

[Stiltsoft support]

Hi @Yatiraj Sharma ,

Seems that it can't be done out-of-the-box.

We can suggest trying the app that we develop - Table Filter, Charts & Spreadsheets for Confluence.

Its Table Transformer macro "understands" checked and unchecked checkboxes and can set colored statuses based on this condition. Later you'll be able to count your statuses and create various reports.

If you are not familiar with the app and decide to try it, you may refer to our support for a thorough guidance regarding the case.

[Yatiraj Sharma]

Thanks a lot @Stiltsoft support , 
Yes, I believe Table transformer can be used however I don't know which field it will be used to check the condition if the task list checkbox is checked or unchecked? Could you please assist me in the same?

[Stiltsoft support]

So, you should split your 'Activity' column into two parts: the checkbox itself (with a space afterwards for it to be saved but without any text) and your task description.

The SQL query will be as following:

SELECT *,
CASE WHEN 'Checkbox' = "Completed"
THEN FORMATWIKI("{status:colour=Green|title=Done}")
ELSE FORMATWIKI("{status:colour=Yellow|title=In Progress}")
END
AS 'Status'
FROM T*

Hope it helps your case.

[Yatiraj Sharma]

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]

@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]

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