Extracting information from 'Linked Issues' besides Issue Key using Table Transformer SQL

Question

Hi Community

I was wondering how can I extract more information from the 'Linked Issues' besides Issue Key.

I have created created linked Jira tickets with bits of information. In confluence I am using Jira macro to pull required fields and then Table Toolbox for rearrangement and editing. On of the fields is 'Linked Issues'. Some of the issues have multiple linked issues, so in order to quickly see what the summary of the ticket is, I am trying to find a way insert this info into the cell, where Linked Issue key is.

So in a nutshell, how can I achieve the following format: Issue Key | Summary for all linked issues, which would be displayed in a single cell?

@Katerina Kovriga _Stiltsoft_ you have helped some of the member of the community in similar domain. Could you please look int this?

Thank you very much

Aleksei

Stiltsoft support · Answer

Hi @Aleks Irinics , The Table Transformer macro considers each table as a mini database, so your 'headers' should be in a header row (to call them out in the SQL query) and not in the column as it is shown on the first screenshot. So, you may replicate the example from our documentation mentioned above with a slight change just to put summary in the same cell with Linked Issues keys: not CONCAT_VIEW_AGGR(FORMATWIKI(T2.'Key', " ")) AS 'Key - linked issues', CONCAT_VIEW_AGGR(FORMATWIKI(T2.'Summary', " ")) AS 'Summary - linked issues', but CONCAT_VIEW_AGGR(FORMATWIKI(T2.'Key', " | ", 'Summary', " ")) AS 'Key and Summary - linked issues', Please check the simplified example below: SELECT T1.'Key', CONCAT_VIEW_AGGR(FORMATWIKI(T2.'Key', " | ", 'Summary', " ")) AS 'Key and Summary - linked issues' FROM T1 LEFT JOIN T2 ON T1.'Linked Issues'->split(" , ")->indexOf(T2.'Key'::string) > -1 GROUP BY T1.'Key' Hope it helps.

Aleks Irinics · Answer

Hi!

Thanks for your reply. The above not necessarily do the job, as it combines two tables and shows linked issues with their summary in a separate cell.

Is there now a way to transfer 'Summary' information into the cell where the Issue Key is?

Basically I have an output table is of 2 columns, where 1st column is a 'header' and second column is information.

When call out for lined issues, it shows this:

But I am more after this format, where info is provided in single cell):

Hope the above makes sense and clarifies what I am after. :)

Stiltsoft support · Answer

Hi @Aleks Irinics ,

Please take a look at this part of our documentation (if the anchor didn't work, please navigate to the "Merging Jira Issue tables by linked issues and keys" section). The screenshots of the source and result tables are below the SQL query.

Here we show how to merge two Jira Issues tables:

* main table with linked issues in a separate column when they are given as a list with commas (several linked issues are in one cell, they belong to one issue)
* additional table with linked issues (each row(cell) is for every linked issue, for example, its key and summary)

Looks like the result table is exactly your case.

If you are somehow stuck with the query, you may refer to our support. Please attach the screenshots of your source Jira Issues tables (like we did for our example) and the page storage (menu ... -> View Storage Format). We'll recreate your macros (Table Toolbox with the Table Transformer inside or the Table Transformer itself if you wrap your Jira Issues macros into it directly) and look into the SQL query.