Table Transformer: SQL query to split different handy statuses from one table cell up

[Paul Hiesleitner]

Hi, 

I am struggling with a creating a filter mechanism for different handy statuses in tables.

The idea is for customer meetings to keep track of certain topics to mark them with predefined handy statuses. On the one hand, the engineering discipline(s) concerned shall be marked and on the other hand the system(s) involved shall be tagged as well as the urgency/importance. 

I have a parent page, where I want to collect & filter the topics of the child pages. For every MoM a new page is created, which includes a Table with action points, description and a column with an extra table in every field, where I want to insert above mentioned handy statuses, that can be hidden. It should look something like this:

Now I would like to just bring together the the different statuses with the Date or topic name and create a large matrix so that I can filter for each System/discipline together or separately, which is something that does not work with e.g. the page property report macro (because then you can filter only for all statuses of one topic e.g. "Software & Electrics" or "Mechanics & Facility", which is sequence dependent as well...)

So therefore, I need to somehow split these fields apart, fill a large matrix with it and then might use it for the page properties report in the end. 

I gave it a try with the Table Transformer, however my SQL skills are quite limited and I have not managed to come up with an appropriate solution and stacking with other macros didn't work out as planned as well.

I would be very thankful for help and/or alternative solutions on how to address this problem properly. Maybe @Stiltsoft support  might be able to help me out!

Thank you very much in advance!

[Stiltsoft support · Accepted solution]

Hi @Paul Hiesleitner ,

Our advice would be to use simple tables, we mean without tables inside other table cells or smth.

Like when you start working with Excel, the first rule is to turn your data into a table: columns with headers that contain similar data (only text strings, only dates in the same format and so on). Then you'll be able to perform various calculations, create pivot tables and so on.

The same rule works for Confluence that has very limited tabular options by default.

So, as I see, you have the 'Topic' and 'Description' columns. Now you may add three other columns as 'System', 'Discipline' and 'Importance' and get rid from tables in tables.

Then your statuses may be filtered separately even if there are several statuses in one cell with the help of the Table Filter macro.

To collect your child tables to the parent page and create a combined report, you may use the Table Excerpt/Table Excerpt Include macros.

Hope this helps your case.

[Paul Hiesleitner]

I finally managed to realise my idea with a combination of Table Excerpt/Include, Table filter and Table transformer with a small SQL query, but it took me a bit to figure out how to properly stack them.

Thank you very much for your help!