Conditional formatting for the row based on a qualifying value in a column

[MudCo]

Hi Team,

I am trying to find a way to format the row of my table based on the values in a column. For e.g, I would want the rows of the Class named 1 colored in yellow, 2 in blue and 3 in green. Most of the codes, even this in link below, looks like we'd have to conditionally format each column for colours and then another conditional formatting for new columns.

https://community.atlassian.com/t5/Confluence-questions/Want-to-colour-a-table-row-automatically-based-on-the-value-of-a/qaq-p/1984456 

This is my sample data but my original data would have over a dozen columns. Also, is there a way (and how) to work this out with CSS Styling? I am basic to both sql and CSS so I have difficulty working this out. 

[Stiltsoft support · Accepted solution]

Hi @MudCo ,

If you are talking about the Table Transformer macro provided by our Table Filter, Charts & Spreadsheets for Confluence app, then your approach is correct regarding the FORMATWIKI function: you need to conditionally color each column one by one. 

The Stylesheet tab and CSS coding help you to format your tables in general (the whole table, the header row, the second column, the fifth row, and so on). You may find plenty of examples in our documentation.

If your source table has a lot of columns and it is hard for you to copy the parts of your FORMATWIKI query, you may take the Table Spreadsheet macro and use the Excel-like conditional formatting there.

[MudCo]

Hello there, 

Yes, sorry I missed to mention that it is Table Transformer Macro. I used FORMATWIKI to highlight but could only do one cell, below is my code for the same. I am understanding that there is no way to colour the existing cell but we will need to end it as a new column. I tried to understand the code in some of the examples but was not very successful

SELECT *,
CASE
WHEN ('Class') IS "1"
THEN FORMATWIKI("{cell:bgColor=#CCCCFF|align=center}" + 'Class' + "{cell}")
ELSE FORMATWIKI("{cell:bgColor=#8FCA7D|align=center}" + 'Class' + "{cell}")
END AS 'CLASS'
FROM T1

[Stiltsoft support]

Not sure that I got this part "I am understanding that there is no way to colour the existing cell but we will need to end it as a new column." but you may check this example for reference and adjust your query (there we work with Status column and you have Class, Name, Score, etc. columns): https://community.atlassian.com/t5/Confluence-questions/Table-transformer-macro-how-to-color-both-the-text-and/qaq-p/2725075 

[MudCo]

Thanks I made the change and found my error in understanding. I was seeing a few examples where people were ending in a new column so I thought that was how it was supposed to be done and I also ended it as 'END AS 'CLASS' instead of 'Class' which of course created a new column named CLASS. By changing that to 'Class', i.e, the original name of the column, the formatting is retained to the original column. Now I understand this, how do I go about colouring the row instead the just the cell? how do I give conditions to each column when my only condition was originally based on the value of column 'Class'? 

SELECT *,
CASE
WHEN ('Class') IS "1"
THEN FORMATWIKI("{cell:bgColor=#CCCCFF|align=center}" + 'Class' + "{cell}")
ELSE FORMATWIKI("{cell:bgColor=#8FCA7D|align=center}" + 'Class' + "{cell}")
END AS 'Class'
FROM T*

[Stiltsoft support]

Just like you've mentioned in the original request - "we'd have to conditionally format each column for colours and then another conditional formatting for new columns".

Leave the condition as it is (WHEN ('Class') IS "1", I mean) and repeat the THEN, ELSE, END parts for each column that you need to color (replace the column names).

[MudCo]

I also wanted to share that our Enterprise model does not support Stylesheet tab but I do use CSS for formatting my tabs. I saw a few examples of CSS coding like below as you suggested. These are great for some types of formatting. But how can I apply my condition of 'Class' value to these codes? My actual data will have class as a text such as "Actively Hiring", not just a number like in my sample data. Which I am not too worried about but just curious to know if CSS works with such conditions.

th {font: 15px Verdana;} /* Styles the header row */

tr:nth-child(odd) {background-color:yellow;} /* Styles all odd rows */

tr:nth-child(even) {background-color:red;} /* Styles all even rows */

tr:nth-child(5) {background-color:blue;} /* Styles the 5th row*/

tr:nth-child(n+6) {background-color:green;} /* Styles the 6th and all following rows */

[Stiltsoft support]

If you don't see the Stylesheet tab in you macro, it means that you use a rather old version of the app - it was introduced in 10.4.0 and the current one is 12.1.2.

The CSS coding formats tables in general without checking the cell's contents. 

[MudCo]

Yes, we are in datacentre 8.5.9 so it's much behind the current version. I tried to do it column wise like suggesteed- Leave the condition as it is (WHEN ('Class') IS "1", I mean) and repeat the THEN, ELSE, END parts for each column that you need to color (replace the column names).

I might have, quite literally, tried to do it, without having much understanding of the syntax.

And of course it did not work. 

SELECT *,
CASE
WHEN ('Class') IS "1"
THEN FORMATWIKI("{cell:bgColor=#CCCCFF|align=center}" + 'Class' + "{cell}")
ELSE FORMATWIKI("{cell:bgColor=#8FCA7D|align=center}" + 'Class' + "{cell}")
END AS 'Class'
THEN FORMATWIKI("{cell:bgColor=#CCCCFF|align=center}" + 'Name' + "{cell}")
ELSE FORMATWIKI("{cell:bgColor=#8FCA7D|align=center}" + 'Name' + "{cell}")
END AS 'Name'
THEN FORMATWIKI("{cell:bgColor=#CCCCFF|align=center}" + 'Score' + "{cell}")
ELSE FORMATWIKI("{cell:bgColor=#8FCA7D|align=center}" + 'Score' + "{cell}")
END AS 'Score'
FROM T*

[Stiltsoft support]

Think you forgot commas after END AS 'Class' and END AS 'Name' parts.

[Stiltsoft support]

And also there is a 

CASE
WHEN ('Class') IS "1"

condition only for the first column. Then it disappears for the next two columns.

[MudCo]

That worked perfectly!! Amazing! Thanks very much for your help! Here are the screenshots. I further explored adding another condition for 'Class' value 3 the same, different colours but they worked.  

and that also works perfectly