How do i preserve my bgColor after SQL transform

[aloysius koh]

This is the code I used:

SELECT
FORMATWIKI("{cell:textColor:",'Color',"}",'Date',"{cell}") AS 'Date',
FORMATWIKI("{cell:textColor:",'Color',"|align=center|font-weight: bold}",'Countdown',"{cell}") AS 'Countdown',
FORMATWIKI("{cell:textColor:",'Color',"}",'Details',"{cell}") AS 'Details',
FORMATWIKI("{cell:textColor:",'Color',"}",'Owner',"{cell}") AS 'Owner'

FROM
(SELECT *,
CASE
WHEN 'Date' < "today" THEN "lightgray"
ELSE "black"
END AS 'Color'
FROM T1)

I noticed that the background Color for the table cells are reverted back to white. How do I fix this?

[Stiltsoft support · Accepted solution]

Hi @aloysius koh ,

Seems that it is an expected behavior: the FORMATWIKI function overrides the manually adjusted background colors.

The only workaround may be to to set background using the FORMATWIKI function and CASE WHEN clauses if it suits you.

By the way, it seems that your initial SQL query is not correct: you should use pluses in these parts of your query

FORMATWIKI("{cell:textColor:"+'Color'+"}",

I mean, we concatenate this part (combine it into one string). 

[aloysius koh]

Hi,

I actually changed from + to , so it can preserve other formatting I did. For the bgColor, I used CASE statement instead and it works!

Thanks for the help!

[Stiltsoft support]

Hi @aloysius koh ,

To preserve initial text formatting you change pluses to commas here:

FORMATWIKI("{cell:textColor:" + 'Color'+ "}" + 'Date' + "{cell}") AS 'Date'

->

FORMATWIKI("{cell:textColor:" + 'Color' + "}", 'Date', "{cell}") AS 'Date'

The "{cell:textColor:" + 'Color'+ "}" part should remain with pluses.

Glad it works now!