Table Transformer broken as soon as I add another row in one table

[Aurélien Thazet]

Hello everyone, 

I created a status report in a Confluence to gather information from several project pages (each page have several table with the same structure) using Table Excerpt Includes and Table Transformers. Here is the current architecture of my status report:

I'm using some SQL to merge tables. Here are my requests:

• Table Transformer for Table Excerpt Include 2

  SELECT * FROM T1 
  
  OUTER JOIN T* ON T1.'Page'=T*.'Page'

• Table Transformer for Table Excerpt Include 1 & 2

  SELECT T1.'Page' AS 'Project',
  
  T1.'Product status' AS 'Project status',
  
  T1.'Risk status' AS 'Risk status',
  
  T1.'Project status update' AS 'Project status update'
  
  FROM T1
  
  OUTER JOIN T* ON T1.'Page' = T*.'Page

My table worked pretty good so far but I found an edge case today. If someone is adding another line or column in one of the project page tables, the report breaks. And I don't understand why since I didn't select this new line/column in my SQL requests. 

For now I ask my colleagues not to modify the table in the project pages. But if there is a workaround I might be great.

Thanks for your time and your future help,

Aurélien

[Stiltsoft support · Accepted solution]

Hi @Aurélien Thazet ,

Please refer to our support portal and attach page storages of your master page and pages with the Table Excerpts (where your source tables are placed). We'll be able to replicate your pages with tables, macros and their settings and look into the issue.

To get the page storage, go to the upper right corner of the page -> menu ... -> View storage format. If you don't see the option, you may ask your Confluence administrator to do it for you.

[Aurélien Thazet]

Hello, 

Thanks for your reply. Unfortunately there is a lot of confidential information into these pages and my Confluence administrator, as well as my company, won't be agree to share them.

If you need more details to replicate the behaviour I have, please let me know

[Stiltsoft support]

The support portal is confidential, you'll be able to share your data there freely.

If some data is really sensitive (passwords, account credentials, etc.), you may replace it with some dummy data or smth but save the initial macros on the master page and on the page with the Table Excerpt. So that we'll be able to replicate the exact combination and all the settings.

[Aurélien Thazet]

Hello, 

I checked internally and my company don't want to share this kind of information. So, allow me to explain my current setting.

Structure

Basically, I have several project pages with key information that I consolidate in a table on a master page. Here is the hierarchy

ALPHA, BETA and CHARLY are my project pages and the parent page is the consolidation page.

Project pages configuration

Each project page is created from a template with the same information. The key information are displayed in a Table (1) which is itself embeded into a Table Excerpt (2). And all the Table Excerpts across project pages have the same Name thanks to the template, Product Overview here (3). 

I repeat the same process with all the information in my Project pages.

Consolidation page configuration

On my consolidation page, I have a Table Toolbox with the above hierarchy: A Table Transformer (1) which embeds a Table Excerpt Include (2) and another Table Transformer (3) which embeds a second Table Excerrpt Include (4).

The Table Excerpt Include 2 looks for an Excerpt called Project allocation and is configured like above.

The Table Excerpt Include 1 looks for an Excerpt called Product overview and is configured like above.

The SQL request for Table Transformer of Table Excerpt 2 is the one below.

SELECT * FROM T1 OUTER JOIN T* ON T1.'Page'=T*.'Page'

And the SQL request for Table Transformer of Table Excerpt 1 is the following one.

SELECT T1.'Page' AS 'Project',
T2.'POC' AS 'POC',
T1.'Product status' AS 'Project status',
T1.'Risk status' AS 'Risk status',
T1.'Project status update' AS 'Project status update'
FROM T1
OUTER JOIN T* ON T1.'Page' = T*.'Page'

And here is the final result. A beaufitful consolidation page ✨

The problem

So, this consolidation page is working pretty well so far.

But one day, one of my colleague decided to add a new row in her Product overview table (like in the example above).

And my consolidation page is totally broken.

Hope it helps to understand the configuration and reproduce it on your side.

[Stiltsoft support]

Do I understand correctly that your colleague added an extra row (and for your case it is not a "row", it's a new column - the table is oriented horizontally) to one of the tables that you collect by the The Table Excerpt Include 2?

On the screenshot of your macro I see that the "Show as a report table" checkbox is ticked - seems that here your structure is broken. How do you see your tables in the macro preview? If any of your collected tables differ from the others (extra columns, typos in the headings, etc.), they can't be merged into a combined table. You'll see separate tables.

The query for the Table Transformer of Table Excerpt 2 is also not clear. As I understood, you collect several similar tables with the help of the The Table Excerpt Include 2 macro, which tables you are trying to JOIN by the 'Page' column?

SELECT * FROM T1 OUTER JOIN T* ON T1.'Page'=T*.'Page'

If I got the case right, you collect your tables with the help of the  The Table Excerpt Include 2 macro, untick the "Show as a report table" checkbox and use the standard "Merge" preset in the Table Transformer of Table Excerpt 2.

SELECT * FROM T*

Then your tables will be collected into one big table even with the extra columns in some of the tables.

And what concerns the Table Transformer of Table Excerpt 1 query: if you don't rename your columns, you don't need the AS parts:

SELECT T1.'Page' AS 'Project',
T2.'POC',
T1.'Product status',
T1.'Risk status'',
T1.'Project status update'
FROM T1
OUTER JOIN T* ON T1.'Page' = T*.'Page'

[Aurélien Thazet]

Hello,

Thanks for your reply. Here are the answers to your questions:

1. No, my colleagues added a row on the table collected by the Table Excerpt Include 1.
2. I can see my tables correctly in my preview. There is just another column for the project BETA.
   
3. The Table Except Include 2 collects tables named Project allocation, which is basically a table with the name of people involved into the project. I'm joining Table Excerpt Include 1 and Table Excerpt Include 2 by the 'Page' column, because my tables are in the same page but in a different table.
   

Hope that helps

[Stiltsoft support]

Are the tables that you collect via the Table Excerpt Include 1 macro supposed to be collected as one big table?

On your screenshot I see that you have plenty of separate tables but the "Show as a report" option is ticked - it happens so because one of your tables contains an extra column.

In my previous reply I've mentioned that if you need to collect such tables into one big table, you should use the standard "Merge" preset in the Table Transformer macro.

The "Show as a report" option works only if your tables have the same structure.

[Aurélien Thazet]

Oh I understand. So, I unticked the Show as Report and moved my Table Excerpt Include 1 to another Table Transformer macro and use the standard Merge preset. 

Thank you very much for your help !