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.