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

[Aditya M]

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: 

 

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*

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!

[Stiltsoft support · Accepted solution]

Hi @Aditya M ,

You may try the following SQL query:

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

Hope it helps your case.

[Aditya M]

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