nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Table Transformer: advanced FORMATWIKI

Alex_6puje0

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

Accepted answers

Alex_6puje0

@Aliaksei Mikhailau {Stiltsoft} you mind looking at this? I see you are pretty good at using table transformers.

Thanks!!

Stiltsoft support

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:

Screenshot 2024-12-07 at 18.47.39.png

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

Screenshot 2024-12-07 at 18.48.03.png

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'

Screenshot 2024-12-07 at 18.48.20.png

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.

All comments

Alex_6puje0

@Aliaksei Mikhailau {Stiltsoft} you mind looking at this? I see you are pretty good at using table transformers.

Thanks!!

Stiltsoft support

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:

Screenshot 2024-12-07 at 18.47.39.png

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

Screenshot 2024-12-07 at 18.48.03.png

In the external Table Transformer we group our tickets:

Screenshot 2024-12-07 at 18.48.20.png

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.

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_6puje0

@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'

Screenshot 2024-12-07 at 22.34.41.png

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:

Screenshot 2024-12-07 at 22.38.05.png

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

Screenshot 2024-12-07 at 22.38.29.png

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'

Screenshot 2024-12-07 at 22.39.00.png

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

Alex_6puje0

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!!!

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

sql

filter

data-center

table

table-filter

table-filter-and-charts

confluence-data-center

table-transformer

table-filter-charts-spreadsheets

stiltsoft