nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Screen Shot 2022-06-08 at 9.32.37 AM.png

Accepted answers

Katerina Kovriga _Stiltsoft_

Hi @Holly Perry ,

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

All comments

Katerina Kovriga _Stiltsoft_

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

Thu 1-1.png

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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

server

confluence-server

sql

table-transformer