Table transformer conditional logic to color code rows

Question

I have a source table with columns 'Version', 'Supported until', 'Target' and 'Comments'.

I want to use a table transformer to conditionally color rows, based on the value of the 'Supported until' date, in relation to the current date.

I need a red background color for any rows where the 'Supported until' date (entered via the calendar control) is less than the current date.

Where the 'Supported until' date <=30 days ahead of the current date, I need the row color to be amber.

For rows where the 'Supported until' date is between 31 and <=60 days ahead of the current date, then I want to give the whole row a background colour of green.

For any other rows, which will have a 'Supported until' date of >60 days from the current date, then the background color just needs to be a default white.

How do I use the FormatWiki function to do this most easily and concisely, also coloring the row with the designated color, whether the respective 'Target' and 'Comments' columns contain data or not (these are optional fields, as well as 'Version'). Or is there a better way of doing the same?

Stiltsoft support · Answer

Yes, the previous comment somehow wasn't visible.

What concerns the row level formatting, your SQL query is fully correct: you can apply formatting only column by column.

The limitation goes to the Table Transformer macro and its internal logic: the macro treats the source table as a mini SQL database. So, there is no such definition as "rows", the macro sees only a range of columns and can refer to the headers.

Green, Desmond · Answer

Strange, my earlier comment disappeared! In any case, I solved the problem like this:

SELECT 
FORMATWIKI("{cell:bgColor=" + bgColor + "}", T1.'Platform', "{cell}") AS 'Platform',
FORMATWIKI("{cell:bgColor=" + bgColor + "}", T1.'Version(s) in use', "{cell}") AS 'Version(s) in use',
FORMATWIKI("{cell:bgColor=" + bgColor + "}", T1.'Supported until', "{cell}") AS 'Supported until',
FORMATWIKI("{cell:bgColor=" + bgColor + "}", T1.'Target version', "{cell}") AS 'Target version',
FORMATWIKI("{cell:bgColor=" + bgColor + "}", T1.'Latest version available', "{cell}") AS 'Latest version available',
FORMATWIKI("{cell:bgColor=" + bgColor + "}", T1.'Plan', "{cell}") AS 'Plan',
FORMATWIKI("{cell:bgColor=" + bgColor + "}", T1.'Context', "{cell}") AS 'Context'
FROM (
SELECT 
CASE 
WHEN DATEDIFF(day, GETDATE(), T1.'Supported until') <= 0 THEN "#f8d6d6"
WHEN DATEDIFF(day, GETDATE(), T1.'Supported until') BETWEEN 1 AND 90 THEN "#fbe9c6"
WHEN DATEDIFF(day, GETDATE(), T1.'Supported until') BETWEEN 90 AND 180 THEN "#fffae6"
ELSE "#ffffff"
END AS bgColor,
T1.*
FROM T*
) AS T1
ORDER BY T1.'Supported until'

I hoped I could apply a background color at row level, rather than in a repeated and verbose (per column) manner. Where there are null values in columns, those cells do also get the intended background color.

Green, Desmond · Answer

I have null values in my data and did not need to use the '-' approach. The code I pasted in my last answer colors relevant cells, whether they have data or are null.