Table transformer macro: how to color both the text and background in the same table cell

Aditya M
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
June 12, 2024

Hello Team, I have a simple conditional formatting requirement. I have this cell 'Status' that should be red/yellow/green if 1/2/3 respectively. I would like to conditionally format both text (1/2/3) color and the cell background color. I've tried this 2 ways, both unsuccessful:

1. Query used:

SELECT FORMATWIKI("{cell:textColor=" + CASE WHEN T1.'Status' = 1 THEN "#FA7E70" WHEN T1.'Status' = 2 THEN "#FFE784" ELSE "#8FCA7D" END + "}" + T1.'Status' + "{cell}") AS 'Status',
FORMATWIKI("{cell:bgColor=" + CASE WHEN T1.'Status' = 1 THEN "#FA7E70" WHEN T1.'Status' = 2 THEN "#FFE784" ELSE "#8FCA7D" END + "}" + T1.'Status' + "{cell}") AS 'Status' FROM T*

Output: 

Screenshot 2024-06-12 192129.png 

2. wrapped the column in an outer and inner macros. for the inner macro, input following code:

SELECT *,

FORMATWIKI("{cell:textColor=" +

   CASE WHEN T1.'Status' = 1 THEN "#FA7E70"

   WHEN T1.'Status' = 2 THEN "#FFE784"

   ELSE "#8FCA7D"

   END

   + "}" + T1.'Status' + "{cell}") AS 'Status'

FROM T*

 

for the outer, input the following code:

SELECT *,

FORMATWIKI("{cell:bgColor=" +

   CASE WHEN T1.'Status' = 1 THEN "#FA7E70"

   WHEN T1.'Status' = 2 THEN "#FFE784"

   ELSE "#8FCA7D"

   END

   + "}" + T1.'Status' + "{cell}")

AS 'Status'

FROM T*

Screenshot 2024-06-12 193317.png

The output is the same.

Expected output: the text 1/2/3 should not be visible, should blend in with the cell background color red/yellow/black.

Looking for help on this!

1 answer

1 accepted

4 votes
Answer accepted
Stiltsoft support
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 12, 2024

Hi @Aditya M ,

You may try the following SQL query:

Wed 3-1.png

SELECT
CASE
WHEN T1.'Status' = 1
THEN FORMATWIKI("{cell:bgColor=#00FFFF|textColor=red}", T1.'Status', "{cell}")
WHEN T1.'Status' = 2
THEN FORMATWIKI("{cell:bgColor=#66FF66|textColor=black}", T1.'Status', "{cell}")
ELSE FORMATWIKI("{cell:bgColor=#FFFF99|textColor=green}", T1.'Status', "{cell}")
END AS 'Status'
FROM T1

Wed 3-2.png

Hope it helps your case.

Aditya M
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
June 12, 2024

small changes and it worked! Thank you so much, much appreciated!

Like # people like this

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events