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

Table Transformer | Highlighting Dates past due

Tommy Strømme March 6, 2024

Hi, 
I have searched the forum and also used chatgpt but cant really get it to work.

I have a table with a date column that is called "Updated".

I simply want to have all dates older then 7 days turn red, otherwise they should stay green. 

This code gives me no errors, but it will list all dates (even those 3 weeks ago) green:

SELECT *,
FORMATWIKI("{cell:bgColor=" +
CASE
WHEN T1.'Updated' IS NULL THEN "#FFFFFF"
WHEN CAST(T1.'Updated' AS DATE) < DATE_SUB(CAST("today" AS DATE), INTERVAL 7 DAY) THEN "#FA7E70"
ELSE "#8FCA7D"
END
+ "}" + FORMATDATE(T1.'Updated') + "{cell}")
AS 'Updated'
FROM T1

Can anyone point me in the right direction? Date format is fine btw.

Regards

/Tommy

1 answer

1 accepted

5 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.
March 6, 2024

Hi @Tommy Strømme ,

You may also search the Atlassian Community, there are similar cases with the Table Transformer, for example, this one: https://community.atlassian.com/t5/Confluence-questions/Conditional-Formatting-for-Confluence-tables/qaq-p/1568284

Wed 9-1.pngSELECT *,
FORMATWIKI("{cell:bgColor=" +
CASE
WHEN T1.'Updated' IS NULL THEN "Grey"
WHEN DATEDIFF(day,"today",T1.'Updated') <= -7 THEN "Red"
ELSE "Green"
END
+ "}" + FORMATDATE(T1.'Updated') + "{cell}")
AS 'Updated'
FROM T1

Wed 9-2.pngHope it helps your case. 

Tommy Strømme March 6, 2024

Ahh..that was just brilliant!!! Thank you so much!!! Awesome!

One final question.. do you know why on earth the date now splits into two lines? In the screnshot is the same table with table filters on top and without on the bottom ( i swapped Green for White (looked better)

Regards

Tommy

Screenshot 2.png

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.
March 6, 2024

I think it's because your table is really wide. As there is a space in the date cells and other columns contain a lot of data, Confluence decided to split dates into two lines.

You may set a predefined width for this column though https://docs.stiltsoft.com/tfac/cloud/advanced-table-cells-formatting-58426218.html#Advancedtablecellsformatting-Changingcolumnwidth

Tommy Strømme March 6, 2024

Hmm.. tried to add as it said there, but if I change it to 80 og 480 it stays the same. No errors though.

SELECT *,
FORMATWIKI("{cell:width=80px}" + 'Updated'+ "{cell}") AS 'Updated',
FORMATWIKI("{cell:bgColor=" +
CASE
WHEN T1.'Updated' IS NULL THEN "Grey"
WHEN DATEDIFF(day,"today",T1.'Updated') <= -7 THEN "Red"
ELSE "White"
END
+ "}" + FORMATDATE(T1.'Updated') + "{cell}")
AS 'Updated'
FROM T1

Tommy Strømme March 6, 2024

Figured it out!

SELECT *,
FORMATWIKI("{cell:width=150px|bgColor=" +
CASE
WHEN T1.'Updated' IS NULL THEN "Grey"
WHEN DATEDIFF(day, "today", T1.'Updated') <= -7 THEN "Red"
ELSE "White"
END
+ "}" + FORMATDATE(T1.'Updated') + "{cell}")
AS 'Updated'
FROM T1

Like Stiltsoft support likes this
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.
March 6, 2024

Yes, the first variant at first set the width and then rewrote it with the second part of your SQL.

Glad it works for you!

Like # people like this

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events