preserving jira links in table transformer SQL after background coloring

[Holly Perry]

Hi! 
I'm wondering if it's possible to have my project key and/or project summary once again be a link back to the issue. They were previously links, but as soon as I added  format wiki sql language for coloring the background of the rows, the links disappeared. 

Here's the SQL I did for the background coloring: 

SELECT

FORMATWIKI("{cell:bgColor=" +

CASE WHEN T1.'Severity Score' <= "4" THEN "#b3ff99"

WHEN T1.'Severity Score' > "4" and T1.'Severity Score'<"15" THEN "#ffff99"

WHEN T1.'Severity Score' >= "15" and T1.'Severity Score'<="25" THEN "#ff9999"   

ELSE "#ffffff"

END

+ "}" + T1.'Key' + "{cell}") AS 'Key',

FORMATWIKI("{cell:bgColor=" +

CASE WHEN T1.'Severity Score' <= "4" THEN "#b3ff99"

WHEN T1.'Severity Score' > "4" and T1.'Severity Score'<"15" THEN "#ffff99"

WHEN T1.'Severity Score' >= "15" and T1.'Severity Score'<="25" THEN "#ff9999"   

ELSE "#ffffff"

END

+ "}" + T1.'Summary' + "{cell}") AS 'Summary',

FORMATWIKI("{cell:bgColor=" +

CASE WHEN T1.'Severity Score' <= "4" THEN "#b3ff99"

WHEN T1.'Severity Score' > "4" and T1.'Severity Score'<"15" THEN "#ffff99"

WHEN T1.'Severity Score' >= "15" and T1.'Severity Score'<="25" THEN "#ff9999"   

ELSE "#ffffff"

END

+ "}" + T1.'Severity Score' + "{cell}") AS 'Severity Score'

FROM T*

Any ideas would be great! :) 

Best,

Holly

[Katerina Kovriga _Stiltsoft_ · Accepted solution]

Hi @Holly Perry ,

Please check this part of our documentation - the "Preserving initial cell formatting" section. Seems that it should help your case.

[Holly Perry]

Thank you! I attempted this and it's still the white background color, when it should be yellow. Is there a way to incorporate my background CASE with preserving the links in the cells? Is it doing the formatwikis in a particular order? Any help or other ideas you might have would be awesome. 

SELECT T1.'Key', T1.'Summary', T1.'Severity Score', T1.'Created', T1.'Assignee',

FORMATWIKI("{cell:textColor=blue}",

'Key', "{cell}") AS 'Test Key',

FORMATWIKI("{cell:bgColor=" +

CASE WHEN T1.'Severity Score' <= "4" THEN "#b3ff99"

WHEN T1.'Severity Score' > "4" and T1.'Severity Score'<"15" THEN "#ffff99"

WHEN T1.'Severity Score' >= "15" and T1.'Severity Score'<="25" THEN "#ff9999"   

ELSE "#ffffff"

END

+ "}" + T1.'Key' + "{cell}") AS 'Key',

[Katerina Kovriga _Stiltsoft_]

It seems that you didn't change the initial query: the difference is in pluses vs commas.

For example, this query colors the background but ruins links:

SELECT
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Col 2' = 10 THEN "#b3ff99"
ELSE "#ffffff"
END
+ "}" + T1.'Col 1' + "{cell}") AS 'Col 1'
FROM T1

And this query colors the background and preserves links:

SELECT
FORMATWIKI("{cell:bgColor=" ,
CASE WHEN T1.'Col 2' = 10 THEN "#b3ff99"
ELSE "#ffffff"
END
, "}" , T1.'Col 1' , "{cell}") AS 'Col 1'
FROM T1

And again what concerns your white background - are you sure that the quotation marks are required here?

CASE WHEN T1.'Severity Score' <= "4" THEN "#b3ff99"

If you compare smth with numbers, use plain number format (not a string):

CASE WHEN T1.'Severity Score' <= 4 THEN "#b3ff99"

Try to remove the quotation marks around numbers for each condition that you use in this query and check the result.

[Holly Perry]

ahhh okay this makes so much sense. thank you so very much - I'm learning so much about SQL and confluence's table transformer. this is gold. I figured it was something like this that I didn't have knowledge of (comma v plus) etc.

thank you!! :) I truly appreciate it.

holly

[Holly Perry]

t