How to determine state of a checkbox in a Confluence table

[Scott Gillespie]

Confluence Server/Data Center 

Is there a way to identify status via Table Transformer SQL?  For example, it would be great if this would work:  IF Table Cell with checkbox status = Checked, then <perform action>.  

We use checkboxes in several tables so users can quickly update a status, like "Ready for Billing" by checking a box.  This is easier and faster to update than using the Status macro.

It's understood (and disappointing) that Table Filter does not distinguish checked vs. unchecked status.  There are many threads related to this, including one that is somewhat useful about using a Pivot Table to count checked vs. unchecked status, but I can find nothing about operating on individual cell status.  

Thanks in advance for any guidance.

[Katerina Rudkovskaya [Stiltsoft] · Accepted solution]

Hi @Scott Gillespie,

Our macros understand the checked and empty checkboxes, please see the screenshots below related to the Table Filter macro:



What concerns the Table Transformer macro, please refer to the cells with checkboxes as “Completed” and “Incomplete” for your CASE WHEN queries.

I mean something like this: … T1.'Checkbox' = "Completed" …

[Scott Gillespie]

Thanks so much, Katerina!  I was clearly misinformed about Table Filter.  And the SQL tip is part of what I needed. To be clear, does a checkbox only have a complete/incomplete state regardless of what text follows it?

So now, let me see if you have a solution for this. If the answer above is yes, this may not be possible with my current table layout. A table has a column to indicate if an issue is with Customer, Supplier, or Other, with a short explanation string.  So, that’s 3 checkboxes and a free-form string per cell.  Is there a way for SQL to determine which of those checkboxes is ticked, and pick up the comment?

[Stiltsoft support]

Hi @Scott Gillespie ,

The Table Filter and Pivot Table macros understand the whole task (checkbox+description) as a whole thing and operate with it in terms of checked and unchecked item. The description itself is not taken into consideration, only the state of the checkbox is important.

But the Table Transformer macro is a bit tricky: to consider checkboxes and descriptions separately, you need to have them into separate cells (one checkbox per cell). But if you try to split the checkbox from the description via the SQL query, the checkbox won't be recognized and the query will be working with your description (as with the string, I mean).

So, to achieve your case, you may reorganize the table structure and keep your checkboxes and descriptions separately (a separate cell for each item). Please see an example below:

Then you'll be able to check both the state of the checkbox and the contents of its description. The query may look like this, for example:

SELECT *,
CASE WHEN 'Checkbox' = "Completed" AND 'Description' LIKE "%Customer%"
THEN FORMATWIKI("{status:colour=Yellow|title=Customer's fault}")
WHEN 'Checkbox' = "Completed" AND 'Description' LIKE "%Supplier%"
THEN FORMATWIKI("{status:colour=Blue|title=Supplier's fault}")
WHEN 'Checkbox' = "Completed"
THEN FORMATWIKI("{status:colour=Gray|title=Other}")
END
AS 'Status'
FROM T1

Then you may use the Table Filter macro or Pivot Table macro on top of the Table Transformer macro to filter/aggregate your projects by statuses:

Hope this workaround helps your case.

[Katie A]

@Katerina Rudkovskaya [Stiltsoft] I have a question related to your answer.
How does this apply when the Table Transformer is wrapping a "Task Report" macro?

For example, I wanted the description to be just a string, so I use this SQL

SELECT
T1.'Task appears on',T1.'Assignee',T1.'Label(s)',
MATCH_REGEXP(T1.'Description',"(?<=\")[^\"]*","u") AS 'Description'
FROM T1

This shows the same as a Task report, but my "Description" column now only has the text between "". (Like [] "Katie's Task" @katie ///date will now display Description only as "Katie's Task")

BUT the due date is no longer formatted nicely with a color indicating how soon it is due. Is there way to pull the due date from the description instead and keep the color scheme?

I tried re-ordering my tasks with the due date at the front, using SQL on "description" column to capture everything at the beginning. This does pull out the date, but it loses the knowledge that it is a "task" so it loses the formatting. Is there a way to trick the output to knowing it is a task still so that it doesn't lose the formatting?

Any tips are welcome!

Katie

[Stiltsoft support]

Hi @Katie A ,

Unfortunately, the Table Transformer loses the formatting if the data was modified. If you just select the column, it stays. But if you do smth with the column, is disappears.

You may use another Table Transformer on top of the first one and format your dates (color the background) using the FORMATWIKI function and comparing dates with, for example, "today".

[Yatiraj Sharma]

Hi @Stiltsoft support ,

It is losing the formatting anyway I tried this as what you mentioned above with Table Transformer..

I cant see it is updating the Status.

SELECT *,
CASE WHEN 'Checkbox' = "Completed" AND 'Activity' LIKE "%Ensure Billing Value Stream Structure is up to date%"
THEN FORMATWIKI("{status:colour=Green|title=Done}")
END
AS 'Status'
FROM T1

[Raphael R]

It is important to note here that the expected value inside the Checkbox column is language dependent. So if you're using Confluence in German,  

CASE WHEN 'Checkbox' = "Completed"

will not work. You have to use

CASE WHEN 'Checkbox' = "Abgeschlossen" 

or

CASE WHEN 'Checkbox' = "nicht abgeschlossen"

You can find out the exact words for your language by wrapping a "chart from table" macro around an example table like this:

[Stiltsoft support]

Hi @Yatiraj Sharma ,

Have also added the example here in a new thread: https://community.atlassian.com/t5/Confluence-questions/How-do-I-update-status-macro-automatically-if-my-confluence/qaq-p/2723192#U2724675

Also big thank you to @Raphael R for the thorough explanation regarding the different languages.

Maybe some additional hints from our side regarding the case: you don't need to create the 'Status' column at all, the column is created automatically by the SQL query and the In progress/Done statuses are also set automatically.

When you check/uncheck the statuses in the page view mode, at first you change the status and then reload the page - after the reloading the statuses are updated.

Maybe this is your issue that you don't see updated statuses.

[Yatiraj Sharma]

It is hiding the value of status column which was originally keyed in manually. if we add the Table Transformer macro.

Only in editing mode it is visible as given in second screen shot.

After marking  checkbox it is updating the status as Done but it will not be visible unless that page url is refreshed. 

[Yatiraj Sharma]

I am adding a Status column as I need the Status Macro and I dont think SQL will add the status macro (It will add the columns and value in it.... 

But I am trying to address the issue as given my above screen shot, it will not show value even though status macro values are present in the column. And also, it will not show even though it is updated as "Completed" We have to refresh the page. 

Any solution for these problems. 

[Stiltsoft support]

If you need some other column with manually created statuses (the Status macros), of course, you may create it but call it somehow different from the status column that is created by the Table Transformer macro.

For example, if you use this query from my example

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*

then the Table Transformer automatically creates the 'Status' column with virtual statuses. If your column with manually created statuses is also called 'Status', then it will be rewritten.

So, if you need to preserve it (and have two columns with statuses), then you should name it differently.

If the question is, that in the macro generated column with statuses you have the "Done" status but don't see the "In progress" status, the issue is in your SQL query:

CASE WHEN 'Checkbox' = "Completed" AND 'Activity' LIKE "%Ensure Billing Value Stream Structure is up to date%"
THEN FORMATWIKI("{status:colour=Green|title=Done}")
END
AS 'Status'

Here you set only "Done" statuses if the condition is true and nothing in the condition is not true.

In my example if the condition is not true, I set the "In progress" status (the ELSE part):

ELSE FORMATWIKI("{status:colour=Yellow|title=In Progress}")
END
AS 'Status'

What concerns the page refreshing - it's an expected behavior for all the macros. You check checkboxes, refresh the page and the statuses are also updated.

[Yatiraj Sharma]

Thank you so much @Stiltsoft support , I am doing like below now 

I will have status column only with Status macros in it if I would have not then SQL creates the column but does not add the status macros, (I tried refreshing it as well but it does not.)

Now I have below SQL

SELECT *,
CASE WHEN 'Checkbox' = "Completed"
THEN FORMATWIKI("{status:colour=Green|title=DONE}")
WHEN 'Checkbox' != "Completed"
THEN FORMATWIKI("{status:colour=Grey|title=TO DO}")
END
AS 'Status'
FROM T1

Which gives me result as below

Now if I check one of the box and refresh the page it gives me below

But now when I am trying to add the SQL like below (and I tried today as well) it is not taking second WHEN AND condition i.e. checking if the Due date is less than or greater than today or NOW

SELECT *,
CASE WHEN 'Checkbox' = "Completed"
THEN FORMATWIKI("{status:colour=Green|title=DONE}")
WHEN 'Checkbox' != "Completed" AND 'Due Date' > NOW
THEN FORMATWIKI("{status:colour=Grey|title=TO DO}")
WHEN 'Checkbox' != "Completed" AND 'Due Date' < NOW
THEN FORMATWIKI("{status:colour=RED|title=TO DO}")
END
AS 'Status'
FROM T1

It is not showing anything even what I had previously as "TO DO"

[Stiltsoft support]

Try the following query:

SELECT *,
CASE WHEN 'Checkbox' = "Completed"
THEN FORMATWIKI("{status:colour=Green|title=DONE}")
WHEN 'Checkbox' != "Completed" AND DATEDIFF(day,"today",'Due Date') < 0
THEN FORMATWIKI("{status:colour=Grey|title=TO DO}")
WHEN 'Checkbox' != "Completed" AND DATEDIFF(day,"today",'Due Date') >= 0
THEN FORMATWIKI("{status:colour=Red|title=TO DO}")
END
AS 'Status'
FROM T1

The "Red" color should be written as "Red" (not "RED") and the Date format inside the settings tab of your Table Transformer macro should correspond to the date format used in your table.

For your example it is dd M yy (07 Apr 2024) or d M yy (7 Apr 2024).

[Yatiraj Sharma]

Thanks a lot @Stiltsoft support ,

This is absolutely helpful,  I modified the same and now it is acting as one of good checklist that we can use using Table Transformer macro... You are not rising star.. You are actually Rocking Star... Thank you again.. 

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