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".