Hi everyone, I try to get some values from Jira and consolidated them in a Table Transformer into Confluence. The table I get is look like this right now: And here is what I want to have as a final table: In a nutshell: * I want to create one line per Jira Version and group Summary and Topic related on this line * For the Topic column, I want to:* check all the Topics from my Jira issues to remove duplicates if they are some for the same Version line * Change their style by using Confluence status style I already managed the first one thanks to the following SQL request: SELECT T1.'Fix versions' AS 'Version', CONCAT_VIEW_AGGR(FORMATWIKI('Summary' , " \n")) AS 'Summary', CONCAT_VIEW_AGGR(FORMATWIKI('Topic' , " \n")) AS 'Topic' FROM T* GROUP BY T1.'Fix versions'; But I'm struggle with the Topic column. Any advice?

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; } } }

Merging and removing dropdown values from Jira with Table Transformer

Aurélien Thazet

Hi everyone,

I try to get some values from Jira and consolidated them in a Table Transformer into Confluence. The table I get is look like this right now:

And here is what I want to have as a final table:

In a nutshell:

I want to create one line per Jira Version and group Summary and Topic related on this line
For the Topic column, I want to:
- check all the Topics from my Jira issues to remove duplicates if they are some for the same Version line
- Change their style by using Confluence status style

I already managed the first one thanks to the following SQL request:

SELECT T1.'Fix versions' AS 'Version',
CONCAT_VIEW_AGGR(FORMATWIKI('Summary' , " \n")) AS 'Summary',
CONCAT_VIEW_AGGR(FORMATWIKI('Topic' , " \n")) AS 'Topic'
FROM T*
GROUP BY T1.'Fix versions';

But I'm struggle with the Topic column. Any advice?

Accepted answers

Stiltsoft support

Hi @Aurélien Thazet ,

Your SQL query is correct. If in the 'Topic' column coming from your Jira Issues macro you have statuses, then they will be grouped as statuses (with color lozenges). If you have comma-separated strings (plain text), then you'll receive grouped plain text.

With the help of the Table Transformer macro you are able to set statuses based on some condition. If your 'Topic' column had only one word per cell, we could check which word it is and set a correspondent status instead. But, as I see from your screenshots, there can be several different words per cell. So, this workaround won't do for the case.

All comments

Stiltsoft support

Hi @Aurélien Thazet ,

Aurélien Thazet

Hello,

Thanks for your swift reply. I could have several statuses in one cell indeed. Do you have any workaround for this?

Stiltsoft support

Not sure that the workaround is worth the end goal but you may try the following structure:

Mon 12-1.png

The same Jira output is repeated twice.

SELECT'Col 1',
CONCAT_VIEW_AGGR(FORMATWIKI('Col 2' , " \n")) AS 'Col 2'
FROM T*
GROUP BY 'Col 1'

Mon 12-2.png

SEARCH / AS @a EX('Col 3'->split(",")) /
RETURN(@a->'Col 1' AS 'Col 1', @a->'Col 2' AS 'Col 2', _ AS 'Col 3') FROM T*

Mon 12-3.png

SELECT *,
CASE
WHEN 'Col 3' LIKE "Done" THEN FORMATWIKI("{status:colour=Green|title=Done}")
WHEN 'Col 3' LIKE "New" THEN FORMATWIKI("{status:colour=Yellow|title=New}")
ELSE FORMATWIKI("{status:colour=Red|title=Fail}")
END AS 'Col 3'
FROM T*

Mon 12-4.png

SELECT'Col 1',
CONCAT_VIEW_AGGR(FORMATWIKI('Col 3' , " \n")) AS 'Col 3'
FROM T*
GROUP BY 'Col 1'

Mon 12-5.png

Here we choose the standard Lookup preset by the 'Col 1' column.

Mon 12-6.png

Aurélien Thazet

Thanks for the proposition. I tried to reproduced it and everything went fine until step 5. The tables aren't merging.

2024-06-03 15_45_30-Edit - Confluence schedule based on Jira Epic - Aurelien Confluence Playground -.png

I tried to OUTER JOIN them with the following SQL request:

SELECT T1.'Col 1',
T1.'Col 2',
T2.'Col 3'
FROM T*
OUTER JOIN T* ON T1.'Col 1' = T*.'Col 1'

But I got the below error:

TypeError: Cannot read properties of undefined (reading 'Col 1')

Welcome!

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

Quick Links

Find more posts tagged with

confluence

cloud

confluence-cloud

filter

table

table-filter

table-filter-and-charts

table-transformer

table-filter-charts-spreadsheets