Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

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:

image.png

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

2 answers

1 accepted

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.

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. :-)

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.

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

Our team was very excited to get it!

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.

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence

👁‍🗨 Confluence Team planning dashboard_Atlympics 🌈

Hi Atlassian's, How is your journey with #Atlympics 2021 so far....excited! Me too, same excitement. Here's my typical team planning and vision dashboard which I used to share to my management and ...

36 views 1 3
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you