Conditional formatting - Background color - Simple SQL help

Meta Box June 2, 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

1 accepted

3 votes
Answer accepted
Katerina Kovriga {Stiltsoft}
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.
June 2, 2022

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 June 3, 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

Katerina Kovriga {Stiltsoft}
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.
June 3, 2022

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.

Keith Lanier November 30, 2022

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_
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.
December 2, 2022
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:
Thu 1-1.png
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*)
Thu 1-2.png
Hope it helps your case.
Like # people like this
Keith Lanier December 2, 2022

@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_
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.
December 5, 2022

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.

Like # people like this
Keith Lanier December 5, 2022

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!

Like Stiltsoft support likes this
Keith Lanier December 12, 2022

@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_
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
December 13, 2022

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

Tue 9-1.png

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

Like # people like this
Keith Lanier December 13, 2022

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 December 13, 2022

@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':

 

Screenshot 2022-12-13 135558.png

Katerina Rudkovskaya _Stiltsoft_
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
December 13, 2022

Hi @Keith Lanier,

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

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

Like # people like this
Keith Lanier December 13, 2022

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_
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
December 13, 2022

Hi @Keith Lanier,

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

Hope it helps.

Like # people like this

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events