Conditional formatting - Background color - Simple SQL help

[Meta Box]

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!

	Garden	Garage	Pool
House1	Yes	No	No
House2	No	Yes	No
House3	Yes	No	Yes

[Katerina Kovriga {Stiltsoft} · Accepted solution]

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

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]

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

[Katerina Kovriga {Stiltsoft}]

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

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

[Keith Lanier]

Hi @Katerina Kovriga {Stiltsoft} - I am trying to use this for my case, but I get this error:

TypeError: Cannot read properties of undefined (reading 'Executive Summary')

Here is my code:

SELECT 'Executive Summary',
CASE
   WHEN INSTR('Executive Summary', "R&D Status - 100")
       THEN FORMATWIKI("{cell:bgColor=#8FCA7D}" + T1.'Executive Summary' + "{cell}")
   WHEN 'Executive Summary' = "-"
       THEN FORMATWIKI("{cell:bgColor=#FA7E70}" + T1.'Executive Summary' + "{cell}")
   ELSE
       FORMATWIKI("{cell:bgColor=#FFE784}" + T1.'Executive Summary' + "{cell}")
   END
AS 'Executive Summary'

FROM (SELECT 'Executive Summary',
   CASE WHEN 'Executive Summary' IS NULL
   THEN
      "-"
    ELSE
      'Executive Summary'
   END
AS 'Exective Summary' FROM T*)

[Natalie Paramonova [Stiltsoft]]

Hi @Meta Box ,

We’ve checked your code and here are the tips:

• Remove the “T1.” indexes for the external SELECT. As you also use an internal SELECT, you can’t refer to your table as “T1.” here.
• Pay attention to the typo in the last line of your code: not “AS 'Exective Summary' FROM T*)” but “AS 'Executive Summary' FROM T*)”.
• You may also remove referrers to the 'Executive Summary' columns after both SELECTS. It is not a mistake, but there is no any sense: you select the column, then transform it and show it again with the same name. Your data is just overwtitten.

So, here is how it looks for us:

SELECT
CASE WHEN INSTR('Executive Summary', "R&D Status - 100")
THEN FORMATWIKI("{cell:bgColor=#8FCA7D}" + 'Executive Summary' + "{cell}")
WHEN 'Executive Summary' = "-"
THEN FORMATWIKI("{cell:bgColor=#FA7E70}" + 'Executive Summary' + "{cell}")
ELSE
FORMATWIKI("{cell:bgColor=#FFE784}" + 'Executive Summary' + "{cell}")
END
AS 'Executive Summary'
FROM (SELECT
CASE WHEN 'Executive Summary' IS NULL
THEN "-"
ELSE 'Executive Summary'
END
AS 'Executive Summary' FROM T*)

Hope it helps your case.

[Keith Lanier]

@Natalie Paramonova [Stiltsoft] - thank you for the response, I am able to get this to work and understand the mistakes. 

However, I am still struggling with SQL nesting issue. If I want to add more columns from T* to the table, how to add to the SELECT statement?

For example, I want to add the Jira Key. How do I add it as the first column and the 'Executive Summary' as the second column?

SELECT
'Key' AS 'Jira Key'
FROM T*

[Natalie Paramonova [Stiltsoft]]

Hi, to obtain several specific columns, you need to list them using commas:

SELECT 'Column 1', 'Column 2'
FROM ...

If you want to list all the columns from the original table, use asterisk:

SELECT *
FROM ...

So, for your original query it will be smth like that:

SELECT 'Column 1' AS 'New name 1', 'Column 2' AS 'New name 2',
CASE WHEN INSTR('Executive Summary', "R&D Status - 100")
THEN FORMATWIKI("{cell:bgColor=#8FCA7D}" + 'Executive Summary' + "{cell}")
WHEN 'Executive Summary' = "-"
THEN FORMATWIKI("{cell:bgColor=#FA7E70}" + 'Executive Summary' + "{cell}")
ELSE
FORMATWIKI("{cell:bgColor=#FFE784}" + 'Executive Summary' + "{cell}")
END
AS 'Executive Summary'
FROM (SELECT 'Column 1', 'Column 2',
CASE WHEN 'Executive Summary' IS NULL
THEN "-"
ELSE 'Executive Summary'
END
AS 'Executive Summary' FROM T*)

Hope it helps your case.

[Keith Lanier]

Thank you very much! Now it is working as I expect!

I realize now that since I am specifying the 'Executive Summary' column in the FROM statement, that I must also list ALL of the columns defined in the SELECT statement. 

Basically, the second SELECT statement is nested in the FROM statement for the first SELECT. This was what I was missing.

So the logical syntax for a table with 2 columns to apply conditional formatting to 1 of the columns which contains empty cell would be:  

SELECT
   'Column 1' AS 'New name 1', 
   CASE [code to format 'Column 2'] END AS 'Column 2'
FROM
   (SELECT
      'Column 1',
      CASE [code to change value of 'Column 2'] END AS 'Column 2' 
   FROM T*)

Thank you again!

[Keith Lanier]

@Natalie Paramonova [Stiltsoft] - I have another basic formatting question using FORMATWIKI:

Is this correct syntax to apply 2 formats to the same column cells?

FORMATWIKI("{cell:bgColor=#FA7E70|width=250px}" + 'Summary'+ "{cell}") AS 'Summary'

This does not work for me.

[Katerina Rudkovskaya [Stiltsoft]]

Hi @Keith Lanier,

The code is correct, and it works fine for me. For example, regarding your previous query it will be looking as:

SELECT
CASE WHEN INSTR('Executive Summary', "R&D Status - 100")
THEN FORMATWIKI("{cell:bgColor=#8FCA7D|width=500px}" + 'Executive Summary' + "{cell}")
WHEN 'Executive Summary' = "-"
THEN FORMATWIKI("{cell:bgColor=#FA7E70|width=500px}" + 'Executive Summary' + "{cell}")
ELSE
FORMATWIKI("{cell:bgColor=#FFE784|width=500px}" + 'Executive Summary' + "{cell}")
END
AS 'Executive Summary'
FROM (SELECT
CASE WHEN 'Executive Summary' IS NULL
THEN "-"
ELSE 'Executive Summary'
END
AS 'Executive Summary' FROM T*)



You may check the names of your columns for typos (in the table and in the query itself), sometimes it happens.

[Keith Lanier]

Thank you! It is working for me now. 

I am not sure if I had a hidden character or something that was causing it not to work initially.

[Keith Lanier]

@Katerina Rudkovskaya [Stiltsoft]  - now I have another request:

How can combine column "Key" with column "Summary" and keep the Key URL hyperlink in the combined column?

Here are the 2 columns which I want to combine:

'Key' AS 'Key'
, FORMATWIKI("{cell:width=15%}" + 'Summary' + "{cell}") AS 'Capabilities'

Here is what I tried:

FORMATWIKI("{cell:width=15%}" + 'Summary' + "\n" + 'Key' + "{cell}") AS 'New'

But, I want to keep the URL for the 'Key':

[Katerina Rudkovskaya [Stiltsoft]]

Hi @Keith Lanier,

Try to replace your pluses by commas, it preserves initial text formatting:

FORMATWIKI("{cell:width=15%}", 'Summary', "\n", 'Key', "{cell}")

[Keith Lanier]

Thanks again - that is a great tip!

For future reference, where can I find a user guide that documents all the syntax?

I've searched and can only fine more "how to" examples, but no real syntax user guide for all the options/formats/functions.

[Katerina Rudkovskaya [Stiltsoft]]

Hi @Keith Lanier,

We have a rather big section within our documentation related to the Table Transformer macro:

• General description
• Default presets
• Syntax and available functions (the macro is based on the AlaSQL library)
• General examples regarding the most common use cases (these cases come from our customers, and this section is often updated)
• General examples of the FORMATWIKI usage (for example, your question about preserving the initial cell formatting is the last one on the page)

Hope it helps.