Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,296,507
Community Members
 
Community Events
165
Community Groups

Conditional formatting - Background color - Simple SQL help

Edited
Meta Box I'm New Here Jun 02, 2022

Hi!

I have a pretty simple use-case, related to the below type of tables: have text centered for all values and background color green when the value is "Yes".

I spent a long type looking into FORMATWIKI but without success. Thanks!

 

 GardenGaragePool
House1YesNoNo
House2NoYesNo
House3YesNoYes

1 answer

Hi @Meta Box ,

I assume that you are talking about our Table Filter and Charts for Confluence app and its Table Transformer macro?

If this is the case, please try the following SQL query:

SELECT T1.'__',
CASE WHEN T1.'Garden' = "Yes"
THEN
FORMATWIKI("{cell:bgColor=#00CC00|align=center}" + T1.'Garden' + "{cell}")
ELSE
FORMATWIKI("{cell:align=center}" + T1.'Garden' + "{cell}")
END
AS 'Garden'
FROM T1

Thu 9-1.png

Repeat the CASE WHEN abstract for every column that you need to format.

Here is our documentation regarding conditional formatting for you to check.

Also keep in mind the following order of attributes: bgColor, textColor, align, width.

Meta Box I'm New Here Jun 03, 2022

Thank you, it worked perfectly!!!

One more question, what is the code if I want to put a specific background color for empty cell?

Thanks again

Hi @Meta Box ,

The FORMATWIKI function works if your cells are not empty. So, you need to put some characters (for example, "-") into them manually or using an additional internal SELECT for your SQL query.

For example, if my cells are "Yes", "No", " " (empty) and I want to color "Yes" as green and empty cells as red, I can use the following SQL query:

SELECT '__',
CASE WHEN 'Garden' = "Yes"
THEN
FORMATWIKI("{cell:bgColor=#00CC00|align=center}" + 'Garden' + "{cell}")
WHEN 'Garden' = "-"
THEN
FORMATWIKI("{cell:bgColor=Red|align=center}" + 'Garden' + "{cell}")
ELSE
FORMATWIKI("{cell:align=center}" + 'Garden' + "{cell}")
END
AS 'Garden'
FROM (SELECT '__',
CASE WHEN 'Garden' IS NULL
THEN
"-"
ELSE
'Garden'
END
AS 'Garden' FROM T*)

Fri 12-1.png

Here we firstly insert "-" into empty cells and only then color them.

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence

Confluence: Where work and wellness meet

Feeling overwhelmed by the demands of work and life? With a 25% increase in the prevalence of anxiety and depression worldwide during the pandemic, for most of us, it’s a resounding yes . 🙋‍♀️ ...

1,032 views 18 28
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you