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*

[Stiltsoft support · Accepted solution]

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. 

[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

[Stiltsoft support]

If to talk about your internal SELECT:

SELECT '__',
CASE WHEN 'Date difference' IS NULL
THEN
"-"
ELSE
'Date difference'
END
AS 'Date difference' FROM T*

What do you mean by SELECT '__'? I think that you've taken this from the example but you don't have any columns with empty headers in your table.

Maybe you need to replace it by SELECT *? Like you select all your columns from the source table.

Then this abstract caught my eye:

AS 'Date difference'
FROM T*
FROM (SELECT '__',

The FROM T* doesn't belong here, your external SELECT grabs data from the internal SELECT. Please, refer to the example once more.

If to talk about the external SELECT, I also see T1.'__' - if you call a column but there is no such name then you'll get an error.

And in the example I've provided it was mentioned that you can't refer to T1.'Column name' in the external SELECT. The internal SELECT works with T1, so there you can type in T1.'Column_name' or just 'Column_name'. But for the external SELECT it will be 'Column_name' only (without the T1. part).

P.S. If there won't be any empty cells then you may drop the internal SELECT. Somehow I've got an idea that you want to color empty cells as well but they can't be colored unless they contain any symbol.

[Stiltsoft support]

And check your conditions as well:

you have (T1.'Date difference') >=1 AND <=14

and the very first example that you've found gives DATEDIFF(day,"today",'Dates') > "0" AND DATEDIFF(day,"today",'Dates') <= "180" and not DATEDIFF(day,"today",'Dates') > "0" AND <= "180"

As I've mentioned before, the syntax is standard, you should follow the examples.

[MudCo]

@Stiltsoft support  thanks for being patient with me and explaining the logic. 

I tried again  and it worked great, with a small hiccup explained below:

Here is my code that worked perfectly (given both Date of Issue and Date of completion cells have data in them or both are empty)

SELECT *,
CASE
WHEN ('Date of Issue') IS NULL AND ('Date of completion') IS NULL AND ('Days left from today') IS NULL
THEN
FORMATWIKI("{cell:bgColor=#FFFFF|align=center}" + ('Date of Issue') + ('Date of completion') + ('Days left from today') + "{cell}")
WHEN ('Days left from today') <=-1 AND ('Days left from today') >=-365
THEN
FORMATWIKI("{cell:bgColor=Red|align=center}" + 'Days left from today' + "{cell}")
WHEN ('Days left from today') >=1 AND ('Days left from today') <=14
THEN
FORMATWIKI("{cell:bgColor=Red|align=center}" + 'Days left from today' + "{cell}")
WHEN ('Days left from today') >=15 AND ('Days left from today') <=29
THEN
FORMATWIKI("{cell:bgColor=#FF9966|align=center}" + 'Days left from today' + "{cell}")
ELSE
FORMATWIKI("{cell:bgColor=#00FF00|align=center}" + 'Days left from today' + "{cell}")
END
AS 'Days left from today'
FROM T*

BUT, when I try to enter a date on Date of Issue and leave Date of completion empty, I get the strange value below. I understand it is a calculated cell. I assumed I already asked the code to consider the NULL values in all the 3 columns and to provide a result as a blank cell or clear coloured cell

[Stiltsoft support]

Hi @MudCo ,

No worries, once you get one case, other similar cases will look very simple.

So, you are correct that you get strange numbers because you calculate the 'Days left from today' column:

DATEDIFF(day,'Date of completion',"today")

No value in the 'Date of completion' column causes an error.

If you decide to calculate, for example, the date difference between 'Date of completion' and 'Date of Issue' columns, then both these columns should contain proper dates.

So, here is a workaround for the case: you may modify the query that goes to your internal Table Transformer macro.

SELECT *,
CASE
WHEN 'Date of Issue' IS NULL AND 'Date of completion' IS NULL
THEN "No dates"
WHEN 'Date of Issue' IS NULL
THEN "No Date of Issue"
WHEN 'Date of completion' IS NULL
THEN "No Date of completion"
ELSE DATEDIFF(day,'Date of completion',"today")
END
AS 'Days left from today'
FROM T*

Here we just check which fields are missing and mark them with a specific text (status). 

Later in the external Table transformer macro we will be able to color these cells (an empty cell can't be colored!):

SELECT *,
CASE
WHEN ('Days left from today') IS "No dates" OR ('Days left from today') IS "No Date of completion"
THEN FORMATWIKI("{cell:bgColor=#CCCCFF|align=center}" + 'Days left from today' + "{cell}")
WHEN ('Days left from today') IS "No Date of Issue"
THEN FORMATWIKI("{cell:bgColor=#CCCCCC|align=center}" + 'Days left from today' + "{cell}")
WHEN ('Days left from today') <=-1 AND ('Days left from today') >=-365
THEN FORMATWIKI("{cell:bgColor=Red|align=center}" + 'Days left from today' + "{cell}")
WHEN ('Days left from today') >=1 AND ('Days left from today') <=14
THEN FORMATWIKI("{cell:bgColor=Red|align=center}" + 'Days left from today' + "{cell}")
WHEN ('Days left from today') >=15 AND ('Days left from today') <=29
THEN FORMATWIKI("{cell:bgColor=#FF9966|align=center}" + 'Days left from today' + "{cell}")
ELSE FORMATWIKI("{cell:bgColor=#00FF00|align=center}" + 'Days left from today' + "{cell}")
END
AS 'Days left from today'
FROM T*

My example is a general one, for your specific case there is no need to check if the 'Date of Issue' contains data as you don't need it in the formula.

So, you may just leave this query for the internal Table Transformer macro:
SELECT *,
CASE
WHEN 'Date of completion' IS NULL
THEN ""
ELSE DATEDIFF(day,'Date of completion',"today")
END
AS 'Days left from today'
FROM T*

and get an empty cell instead of the strange error number.

In the external Table Transformer you just remove the bold part where we color the missing dates by statuses - as you don't have any statuses, you don't need to color by them. Your conditions will work with numbers and empty cells will remain as blank empty cells.

[MudCo]

@Stiltsoft support  I tried this on more tables with a much complex/larger data set. It worked perfectly! Now I understand how it reads blank cell. I always assumed it would just colour my blank cell either white or plain because I was giving that criteria. Can't thank you enough for your help. Much appreciated!