How to determine state of a checkbox in a Confluence table

Scott Gillespie May 11, 2023

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.

1 answer

1 accepted

2 votes
Answer accepted
Katerina Rudkovskaya _Stiltsoft_
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
May 12, 2023

Hi @Scott Gillespie,

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

Fri 5-1.pngFri 5-2.png

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 May 13, 2023

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 13, 2023

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:

Sat 1-1.png

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:

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}")
AS 'Status'

Sat 1-2.png

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:

Sat 1-3.pngSat 1-4.png

Hope this workaround helps your case.

Katie A January 24, 2024

@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

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

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

Screenshot 2024-01-24 at 11.35.01 AM.png

Screenshot 2024-01-24 at 11.35.57 AM.png

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?

Screenshot 2024-01-24 at 12.45.15 PM.png

Screenshot 2024-01-24 at 12.45.46 PM.png

Any tips are welcome!


Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 25, 2024

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

Like Katie A likes this

Suggest an answer

Log in or Sign up to answer
AUG Leaders

Atlassian Community Events