Table transformer conditional logic to color code rows

[Green_ Desmond]

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?

[Marc - Devoteam]

Hi @Green_ Desmond 

I think you have the Table and Charts app installed as you mention formatwiki function.

There documentation has information on this, see here

I hope this helps you in your required direction.

You could also reach out to their support in case, you might need more help.

[Green_ Desmond]

That doesn't really help. I had already looked at that documentation and it didn't explain how to retain existing formats particularly well (differences between the comma (,) and plus sign (+) when using FormatWiki, in how that retains or transforms existing content. I also asked how to format at row level, rather than having to apply the format for every column (where there might be numerous columns).

I kept the example in my question simple, for the purposes of illustration, with the number of columns in the table. But my real world example is as per the below, with quite a few more columns. The table transform code that I've since got working to color code the rows (column wise) according to apply some logic based on the 'Supported until' value.

This retains the existing formating in the source table (includes calendar control based dates, links, etc.), as well as setting an appropriate new background color. As I say, I was looking for a way to do the same at row level to avoid the repetition and verbose code.

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'

[Stiltsoft support]

And you may also browse the related questions about the formatting:

• https://community.atlassian.com/t5/Confluence-questions/Conditional-formatting-Background-color-Simple-SQL-help/qaq-p/2045063 - here is depicted how to insert "-" into empty cells and then color the background (empty cells can't be colored)
• https://community.atlassian.com/t5/Confluence-questions/Conditional-Formatting-for-Confluence-tables/qaq-p/1568284 - here you may see how the DATEDIFF(day,"today",'Dates') function works

To color the whole row, you just repeat the query for each column from your table.

If somehow you are stuck, please refer to our support and attach the page storage of your page (upper right corner -> menu ... -> View storage format). We'll recreate your source table and your current SQL query and help you with the case.

[Green_ Desmond]

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.

[Green_ Desmond]

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.

[Stiltsoft support]

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.