How to format status when concatenated with other data in a cell Table Transformer

larry joseph

I have a (sample date) table below.  I want to group by the milestone and show the status for a task in the same cell as the Due date.  I have the SQL below.  But when it combines the status and due date the status formatting is lost and it just shows as plain text.  I looked into using a case statement and manually formatting the status with a Case Statement and {status:colour=Green|title=" + T1.'Status' + "}" but I don't see a way to know the status color or to accommodate status values which are variable

 

Desired Result

with row for each milestone and the columns, status color/value dynamic based on the data.

 

Current SQL

SELECT T1.'Milestone',
FORMATWIKI(T1.'Status' + " \n " + FORMATDATE(T1.'Due Date')) AS 'Current Status',
T1.'Task'
FROM T1
PIVOT (MAX('Current Status') for 'Task')

 

Sample Data Table

Anna Mitina (Stiltsoft)

Hi @larry joseph,

Thank you for your question.

I'm Anna from the Table Filter, Charts & Spreadsheets for Confluence app which the Table Transformer macro belongs to.

Try changing the following part of the SQL:

FORMATWIKI(T1.'Status' + " \n " + FORMATDATE(T1.'Due Date'))

Namely, change the "+" to ",". 

Let me know if it helps