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_ · Accepted solution]

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 :) 

[larry joseph]

 thank you.  That worked.

[Marc -Devoteam-]

Hi @larry joseph 

Best is to contact support of the vendor of the 3rd party app. Tables Filter and Charts.

@Stiltsoft support I think your app is used, could you assist here?

[larry joseph]

Yes this is the Stilsoft Table Transformer.