nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to determine state of a checkbox in a Confluence table

Scott Gillespie_ibixpx

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.

Accepted answers

Katerina Rudkovskaya _Stiltsoft_

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.png Fri 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" …

All comments

Katerina Rudkovskaya _Stiltsoft_

Scott Gillespie_ibixpx

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:

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:

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

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.png Sat 1-4.png

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

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!

Katie

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

server

confluence-server

filter

table

table-filter

checkbox

table-filter-and-charts

table-transformer