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

How to join three tables in order to extract the status of different sub-tasks in Table Transformer

Martin Röhricht

The epics in our JIRA project have several sub-tasks and we wish to display the sub-task's statuses along some other fields in a table view. Since this cannot be accomplished by JIRA's built-in filters, I came across Confluence's Table Transformer macro.

So the data looks basically as follows:

Table T3 (filter) epics:

+--------+------------------------+
|Key     | Sub-Tasks              |
+--------+------------------------+
| MCR-1  | MCR-10, MCR-11, MCR-12 |
+--------+------------------------+
| MCR-2  | MCR-20                 |
+--------+------------------------+
| MCR-3  | MCR-30, MCR-31         |
+--------+------------------------+

Table T1 (filter) for QM sub-tasks:

+--------+--------------+
|Key     | Status       |
+--------+--------------+
| MCR-10 | DONE         |
+--------+--------------+
| MCR-20 | NOT RELEVANT |
+--------+--------------+
| MCR-30 | OPEN         |
+--------+--------------+

Table T2 (filter) for E3 approvals:

+--------+--------------+
|Key     | Status       |
+--------+--------------+
| MCR-11 | DONE         |
+--------+--------------+
| MCR-31 | NOT RELEVANT |
+--------+--------------+

If I now add all three tables into the table transformer macro and edit the SQL query as follows:

SELECT T3.'Key' AS 'MCR-Key', 
    T1.'Status' AS 'QM Status',
    T2.'Status' AS 'E3 Approval'
FROM T3
    LEFT JOIN T1 ON (T1.'Key' IN T3.'Sub-Tasks')
    LEFT JOIN T2 ON (T2.'Key' IN T3.'Sub-Tasks')

then all rows are listed, but I get only the status of the first epic's sub-tasks:

So basically I would like to know how to join multiple tables with comma-separated values.

Accepted answers

Katerina Kovriga {Stiltsoft}

Hi @Martin Röhricht

Here I am with an update for your case.

Please check the version of your app - the current one is 7.6.1 and your own SQL query works just fine with it. We've tested it in our own instance and got the following result:

Thu 7-1.png

And you may also try the third variant:

Thu 7-2.png

SELECT T3.'Key' AS 'MCR-Key',
T1.'Status' AS 'QM Status',
T2.'Status' AS 'E3 Approval'
FROM T3
LEFT JOIN T1 ON (T3.'Sub-Tasks' LIKE "%" + T1.'Key' + "%")
LEFT JOIN T2 ON (T3.'Sub-Tasks' LIKE "%" + T2.'Key' + "%")

Thu 7-3.png

Hope this may help your case.

All comments

Katerina Kovriga {Stiltsoft}

Hi @Martin Röhricht ,

I can give you a not elegant but still working decision for you not to get stuck with the case (if our developers provide me with a better solution, I'll get in touch immediately).

Use the following combination of the Table Transformer macros and Jira tables:

Wed 1-1.png

The TT 1 and TT 2 are used to distinct your tables with the subtasks that belong to different fields.

TT 1:

Wed 1-2.png

SELECT T1.'Key' AS 'Key Subs',
T1.'Status' AS 'QM Status'
FROM T1

Wed 1-3.png

TT 2:

Wed 1-4.png

SELECT T1.'Key'AS 'Key Subs',
T1.'Status' AS 'E3 Status'
FROM T1

Wed 1-5.png

TT 3: Use the third TT to simply merge your tables.

Wed 1-6.png

TT 4: Use the fourth TT to combine the result table.

Wed 1-7.png

SELECT T1.'Key', T2.'Key Subs', T2.'QM Status', T2.'E3 Status' FROM T1
LEFT JOIN T2 ON T1.'Sub-Tasks'->indexOf(T2.'Key Subs') > -1

Wed 1-8.png

Now you'll be able to filter your table with the help of the Table Filter macro and count how many subtasks belong to this or that field and which statuses they have.

Katerina Kovriga {Stiltsoft}

Hi @Martin Röhricht

Here I am with an update for your case.

Please check the version of your app - the current one is 7.6.1 and your own SQL query works just fine with it. We've tested it in our own instance and got the following result:

Thu 7-1.png

And you may also try the third variant:

Thu 7-2.png

Thu 7-3.png

Hope this may help your case.

Martin Röhricht

Dear @Katerina Kovriga _Stiltsoft_,

thank you so much for your instant help. I really appreciate it and after fiddling around for literally five hours, I felt I had to ask more knowledgeable people. :-)

I just managed to find the solution for my problem some minutes ago. And it has nothing to do with the queries. These work perfectly fine. The reason I could not get the results stemmed from the fact, that I searched for the key's status but apparently we have four(!) different fields called "Status" in our company's JIRA. I was not aware of it and picked the wrong one. While the template for the other table I inserted used the right one, I thought it was due to the SQL statement, that I didn't get the results for my second table.

I definitely need to document this for my team mates, otherwise they will run into the same issue eventually.

Thanks again for your time. I really enjoy the table transformer tool -- it provides us the solution for something we searched for months. :-)

Katerina Kovriga {Stiltsoft}

I'm glad that you've solved the case and everything is working fine, thank you for reaching out!

Maybe you'll have several minutes to leave us a review about the Table Transformer macro and your cases - our developers will be thrilled to get one. Besides it is always useful for other customers.

Katerina Kovriga {Stiltsoft}

Hi @Martin Röhricht , thanks for the review!

Our team was very excited to get it!

Welcome!

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

Quick Links

Find more posts tagged with

confluence

server

confluence-server

table-transformer