Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

How do i preserve my bgColor after SQL transform

aloysius koh April 1, 2024

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?

1 answer

1 accepted

2 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.
April 1, 2024

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 April 2, 2024

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
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.
April 2, 2024

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!

 

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events