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; } } }

Table Transformer with SQL for difference in dates and then further colour code

MudCo

@Katerina Kovriga _Stiltsoft_ this is in continuation to the solution you provided here Solved: Conditional Formatting for Confluence tables (atlassian.com)

I was able to apply that in my project and to worked very well. However, I wanted to further fine tune it for another type of report.

In my below sample table, I have this code that gives me the difference between today and the date in the column 'Date of Completion'. The Date difference column would ideally be the number of days left for me to follow up. However, I found out that this code doesn't add another column (not visible in editing mode but only when published). I would like to know if it's possible to do 2 things here:

1-output the results in an existing column (or if I have manually added a new one-say, named 'Days left for Follow up'). I am ok if this is not a possibility, as long as colour coding the results works, if should be manageable.

2- And colour code the range in 'Date of Completion' /or 'Days left to follow up (my manually added column) based on the below condition

If days left to follow up is:

*>=30, show Green

*>=15 and <=29, show Amber

*>=1 and <=14, show Red

3- Is it possible to further give a special condition/customize only to a specific row, say for the Emp3, I can show Green even if the value is between 1-29

SELECT *,
DATEDIFF(day,T1.'Date of completion',"today")
AS "Date difference"
FROM T*

Accepted answers

Stiltsoft support

Hi @MudCo ,

Regarding your first question: no, it is not possible to show a virtually created column in the page view mode.

If you need to see this data while adding/updating other fields, you may take the Table Spreadsheet macro instead of the Table Transformer macro. There you'll be able to use Excel-like cell formulas and edit your tables from the page view and page edit mode.

And I've also noticed that you refer to your new column as AS "Date difference". When we refer to strings (contents of the cell), we use "some_string". And when we refer to columns, we use 'column_name'.

That's why you've got 'Date difference' instead of the Date difference (without any quotes) as a column name in the Table Transformer macro preview. In other SQL queries you may even get an error.

Regarding your second question: you've found the similar question regarding conditional formatting yourself. The CASE WHEN statement indeed is used for conditional formatting within the Table Transformer macro.

And if you decide to use the Table Spreadsheet macro mentioned above, please note that it also provides the Excel-like conditional formatting.

Regarding your third question: the CASE WHEN statement supports the AND clause.

So, you'll be able to check if the Name = Emp3 AND the Date difference >=15 and <=29 and set the green color and then check if the Name is not Emp3 AND the Date difference >=15 and <=29 and set the amber color.

The same double check goes for the Red condition.

Hope I was able to clarify these points for you.

All comments

Stiltsoft support

Hi @MudCo ,

Regarding your first question: no, it is not possible to show a virtually created column in the page view mode.

And if you decide to use the Table Spreadsheet macro mentioned above, please note that it also provides the Excel-like conditional formatting.

Regarding your third question: the CASE WHEN statement supports the AND clause.

The same double check goes for the Red condition.

Hope I was able to clarify these points for you.

MudCo

Hi @Stiltsoft support thanks for the idea,

I am ok with not having a new column as Date Difference, as long as the formula works to give me the results, I am fine having it work as is.

Sorry of my codes look haphazard, not a coder at all and I am fairly new to confluence, but here's what I tried:

So now, I have removed the condition for that one special row. Currently, I am only trying to get the colours right for the range in the date difference column sql made. Since we cannot refer the Date difference as a column (as in T1.Date difference), here is what I did:

SELECT *,
DATEDIFF(day,T1.'Date of completion',"today")
AS "Date difference"
FORMATWIKI("{cell:bgColor=" +
WHEN DATEDIFF(day,T1.'Date of completion',"today") IS NULL THEN "#FFFFFF"
WHEN DATEDIFF(day,T1.'Date of completion',"today") IS >=1 and <=14 THEN "#CC0000"
WHEN DATEDIFF(day,T1.'Date of completion',"today") IS >=15 and <=29 THEN "#FF9966"
ELSE DATEDIFF(day,T1.'Date of completion',"today") IS >=30 THEN "#00CC00"
END
FROM T*

FYI

"#FF9966" is my colour code for Amber
"#CC0000" is my colour code for Red
"#00FF00" is my colour code for Green

But it's empty, I am hoping it doesn't paint it up but keep it plain white so I gave a condition WHEN DATEDIFF(day,T1.'Date of completion',"today") IS NULL THEN "#FFFFFF"

By doing this this, I got a syntax error. I know it's wrong because of how I am referencing the newly formed column.

(Once I get this right, I'll work on adding a condition for the special condition for that one row later on)

Stiltsoft support

Hi @MudCo ,

You may try to use two Table Transformer macros for your case:

In the internal macro you can count the 'Date difference' and it will be added as a new virtual column to your table

SELECT *,
DATEDIFF(day,T1.'Date of completion',"today")
AS 'Date difference'
FROM T*

In the external macro you will have a more complex SQL query with two SELECT functions
- In the internal SELECT you put "-" in the empty cells of the 'Date of completion' column
- In the external SELECT you color the 'Date of completion' column

An example for this query may be found here (please check the third reply of the thread).

Also from the query provided I see that you somehow missed the CASE part from the CASE WHEN structure, don't specify which column you are trying to color and left extra IS in your conditions IS >=1.

The syntax of the FORMATWIKI and CASE WHEN functions is standard for all the cases, only conditions, column names and colors are changed.

MudCo

@Stiltsoft support My 1st Table Transformer macro with

SELECT *,
DATEDIFF(day,T1.'Date of completion',"today")
AS 'Date difference'
FROM T*

works.

I wrapped this in another (external) table transformer and now I am able to call the new column sql created which is Date difference (as T1.Date difference). Now I want this column to be coloured as per the criteria:

1-14 as red i.e., "#CC0000 or "Red" as mentioned first condition of the code below
15-29 as amber "#FF9966" and anything>30 as green i.e.,"#00FF00"

I saw the post here as you suggested, and I tried to use the same logic into my code (removed IS's everywhere and corrected my missing CASE function, but it threw parse errors again.

My code now looks like this:

SELECT T1.'__',
CASE WHEN (T1.'Date difference') >=1 AND <=14
THEN
FORMATWIKI("{cell:bgColor=Red|align=center}" + T1.'Date difference' + "{cell}")
WHEN (T1.'Date difference') >=15 AND <=29
THEN
FORMATWIKI("{cell:bgColor=#FF9966|align=center}" + T1.'Date difference' + "{cell}")
ELSE
FORMATWIKI("{cell:bgColor=#00FF00|align=center}" + T1.'Date difference' + "{cell}")
END
AS 'Date difference'
FROM T*
FROM (SELECT '__',
CASE WHEN 'Date difference' IS NULL
THEN
"-"
ELSE
'Date difference'
END
AS 'Date difference' FROM T*)

The 2nd Select is, I am assuming, replacing an empty cell with "-". Now there are none, but there might be when we add data to rows/col. Missing values in Date difference column is not something that would exist because the dates would be a mandatory input...so sql would be automatically calculating the date difference in the background anyway. Not sure if the second select is completely necessary if the function is to fill the blank cells with "-"

But even with just the first select function, I am unable to get results

Welcome!

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

Quick Links

Find more posts tagged with

confluence

sql

cloud

confluence-cloud

filter

macro

script

table