Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,298,379
Community Members
 
Community Events
165
Community Groups

preserving jira links in table transformer SQL after background coloring

Edited

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

2 answers

1 accepted

Hi @Holly Perry ,

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

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',

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.

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

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence

An update on Confluence Cloud customer feedback – June 2022

Hi everyone, We’re always looking at how to improve Confluence and customer feedback plays an important role in making sure we're investing in the areas that will bring the most value to the most c...

183 views 1 3
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you