Table Transformer: advanced FORMATWIKI

[Alex]

I’m using the below SQL in a Table transformer Macro to group epic information (in table 1) and subtasks information (in table 2 - an epic might have more than one subtask).   

Note: Table 2 is surrounded by another table transformer (transformer inside transformer); this transformer makes the font of the DUE field red if past due, or Green if date is in the future. 

The issue I’m having is when using the below sql seems like the CONCAT_VIEW_AGGR is somehow removing the formatting to the font color I have set for DUE. 

Any ideas?  I tried adding a FORMATWIKI around T2.DUE (as seen below) but it keeps removing the font color.

 The documentation from Stiltsoft on advanced transformation does not cover these type of scenarios. 

SQL BEING USED:

SELECT

T1. 'Key' AS 'Epic Key', T1. 'Epic name / Lead', T1. 'Status' AS 'Epic Status', T1. 'Epic Labels',

CONCAT_VIEW_AGGR(FORMATWIKI (T2. 'Subtask Status', T2. 'Subtak Details', " \n", "*Due*: ", FORMATWIKI (T2. 'Due'), " \n","--")) AS 'Subtask Details'

FROM T1 JOIN T2 ON T1. 'Sub-Tasks'->indexOf (T2. 'Key')>-1

GROUP BY T1. 'Key',T1. 'Epic name / Lead', T1. 'Status', T1.’Epic Labels

[Stiltsoft support · Accepted solution]

Hi @Alex ,

If you need any help from the vendor, you may refer to our support portal that is confidential.

Meanwhile, we couldn't reproduce the issue. As we see, you've used this example from our documentation but have added an additional internal Table Transformer macro to color your T2 conditionally.

Here is a simplified example of the case:

In the internal Table Transformer we color our statuses conditionally:

SELECT *,
CASE WHEN T1.'Status' LIKE "Go"
THEN FORMATWIKI("{color:green}", T1.'Status', "{color}")
ELSE FORMATWIKI("{color:red}", T1.'Status', "{color}")
END
AS 'Status'
FROM T1

In the external Table Transformer we group our tickets:

SELECT T1.'Key',T1.'Summary',
CONCAT_VIEW_AGGR(FORMATWIKI(T2.'Key', " \n")) AS 'Key - linked issues',
CONCAT_VIEW_AGGR(FORMATWIKI(T2.'Status', " \n")) AS 'Status - linked issues'
FROM T1 LEFT JOIN T2 ON T1.'Linked Issues'->split(" , ")->indexOf(T2.'Key'::string) > -1
GROUP BY T1.'Key', T1.'Summary'

As you can see, the statuses in the last column were grouped as well but the colors remained as they were after the conditional formatting.

So, you may check out this simplified example and see if it works for you. Then you may check the internal Table Transformer macro from the original case and make sure that your current dates are really red and green.

If the issue persists, check the version of your app - the current one is 12.2.0, please update if required.

If nothing helps, you may refer to your support portal for further investigation.

[Alex]

@Stiltsoft_support / @stiltsoft
Thank you for the quick response and good understanding of the issue.  
In the example provided  both columns of the second table are kept as a separate column (did not combined both columns Key and Status into one column).  The  SQL  I provided does combines multiple columns into one within the AGGR parameters:

My SQL:

CONCAT_VIEW_AGGR(FORMATWIKI (T2. 'Subtask Status', T2. 'Subtak Details', " \n", "*Due*: ", FORMATWIKI (T2. 'Due'), " \n","--")) AS 'Subtask Details'

Can you confirm if even in that scenario the font color would remain?  

see sample attached (the date is in fact red in when only looking at my second table), but the color disappears when combined columns are used:

[Stiltsoft support]

It doesn't matter if you aggregate cells within the same column or several columns at once:

SELECT T1.'Key',T1.'Summary',
CONCAT_VIEW_AGGR(FORMATWIKI(T2.'Key', " \n", "Status is:", T2.'Status')) AS 'Key - linked issues',
CONCAT_VIEW_AGGR(FORMATWIKI(T2.'Status', " \n")) AS 'Status - linked issues'
FROM T1 LEFT JOIN T2 ON T1.'Linked Issues'->split(" , ")->indexOf(T2.'Key'::string) > -1
GROUP BY T1.'Key', T1.'Summary'

As you can see, I've added statuses to the third column - the colors remain as they are.

Also I've taken the live Jira Issues macro as T2:

SELECT *,
CASE WHEN T1.'Created' IS "Dec 06, 2024"
THEN FORMATWIKI("{color:green}", T1.'Created', "{color}")
ELSE FORMATWIKI("{color:red}", T1.'Created', "{color}")
END
AS 'Created'
FROM T1

SELECT T1.'Key',T1.'Summary',
CONCAT_VIEW_AGGR(FORMATWIKI(T2.'Key', " \n", "Date is:", T2.'Created')) AS 'New column'
FROM T1 LEFT JOIN T2 ON T1.'Linked Issues'->split(" , ")->indexOf(T2.'Key'::string) > -1
GROUP BY T1.'Key', T1.'Summary'

So, as you can see, it also doesn't matter if you work with manually created or macro generated tables.

[Alex]

Thank you so much! I will go back and see the parameters of the formatwiki I have to format the color in my T2, I’m pretty sure I’m using “cell” and not “color” so I will try with Color and hope that does the trick. 

Thank you again!!!

[Alex]

Confirmed, I was using CELL parameter versus the COLOR Parameter within the FORMATWIKI of the Table driving the font color.   

Thank you for providing a clear example on how to accomplish this!!

[Alex · Accepted solution]

@Alexey Mikhaylov _Stiltsoft_   you mind looking at this?  I see you are pretty good at using table transformers. 

Thanks!!