I would like to merge and transpose tables that are used as an excerpt on other pages

[Michèle Page]

Hi there,

I would like to do the following and I'm not sure if the approach is even possible.

Context:

• We are managing several Data Definitions in Confluence
  (1 Data Definition = 1 Confluence Page)
• Every Data Definition has several Single Point of Contacts / Links
  • Link (which is the same for every page)
  • Manager (who is the same for every page)
  • Owner (who differ on each page)
  • Steward (who differ on each page)
• I would like to have a centralized table to manage the SPOCs and Links and displaying the relevant information on the Data Definition pages using the Table Excerpt Include macro

Example of the the tables on the centralized page with the current solution:

Information that is included in all the pages as it's the same for all of them:

Owned by	Link
Name Manager	Name

Information that is only relevant for the respective page

First row is only shown on the centralized page.

Second and third row will be shown on the respective page.

Link to Data Definition 1
Name Owner	Name Owner 1
Name Steward	Name Steward 1
Link to Data Definition 2
Name Owner	Name Owner 2
Name Steward	Name Steward 2
Link to Data Definition 3
Name Owner	Name Owner 3
Name Steward	Name Owner 3
...

Example of the table on the Data Definition pages (e.g., Data Definition 1):

Owned by	Link (always the same table)
Name Manager	Name (always the same table)
Name Owner	Name (Name Owner Data Definition 1)
Name Steward	Name (Name Steward Data Definition 1)

I found a hacky solution, that's kind of working, see below.

✨ What would be an even better solution is, if the centralized page would provide the following overview:

Owned by	Link
Manager	Name

Data Definition	Name Owner	Name Steward
Link to Data Definition 1	Name Owner 1	Name Steward 1
Link to Data Definition 2	Name Owner 2	Name Steward 2
Link to Data Definition 3	Name Owner 3	Name Steward 3
...

Now the hacky solution is as follows.

Centralized page

Table Transformer with the following SQL (all tables have to be listed individually for the SQL to work):

SELECT * FROM T1
UNION ALL
SELECT * FROM T2
UNION ALL
SELECT * FROM T3
UNION ALL
SELECT * FROM T4

As we have already many Data Definitions (and many more to come), my SQL goes up to T42.

Also, I always need to include a "placeholder" row in order to be able to display the Link.

Data Definition pages

Is there a better way to do this? Or is there even a way to display the centralized page as mentioned under ✨?

Thank you for your ideas.

[Daniel Berežnoi · Accepted solution]

Hi Michèle,

This is Daniel from Stiltsoft, the creators of Table Filter, Charts, and Spreadsheets for Confluence. 

Could you please let me know whether you can add the links to the data definition right into the excerpts? If this is possible, then you can try the following setup.

The overview excerpt with common data is separated from the rest, as you wished. Using a single Table Transformer, it is possible to collect data from all excerpts into a single table. 

You see, the Table Transformer has an option to transpose the source tables.

By enabling this option, you can pivot the whole table, remove the unnecessary info and collect data from excerpts into the table you wish.

The result will look just as you wished.

Then, on the Data Definition pages, you can transpose source and result tables with Table Transformer, while removing the column with links, if you so wish.

Let me know if you have any questions.

Daniel

[Michèle Page]

Hi @Daniel Berežnoi ,

Thank you so much for your answer as well as the detailed information!

That's exactly what I was looking for and seems so much easier. The only reason why I didn't include the links in the excerpts was that I didn't know how to hide them. With the possibility of hiding them, including them in the excerpts is no issue at all!

How do you hide certain rows in an excerpt?

I used the Table Filter macro and restricted the view to only the first 2 rows (Show N rows if not filtered). That again seems like a rather hacky solution to me.

Again, thank you so much, that's definitely much easier than my solution and much better to maintain in the future.

[Nikita Kamai]

Hello @Michèle Page 

You can freely use our Table Filter's Hide Columns Filter and hide the links column once the table is transposed after the Table Transformer is in effect, simply wrap the config with Table Filter and use this filter type.

Please, let us know how it goes for you.

Best wishes,
Nikita

[Michèle Page]

Hi @Nikita Kamai 

Thanks for your fast answer.

As the table is now transposed, this information is no longer provided in a column but a row. So when I add the "Hide columns" filter, I can only select the columns (FPOC / Name). 

[Nikita Kamai]

@Michèle Page

Got you!

Please let me know if, in that case, you can add the Table Filter before the Transposing and use a Free Text Filter, where you can input something like Link to& as this seems to be the start of all your link cells; thus, you should be able to filter them out like this.

NB: Ensure the filter is in reverse mode (click the equation mark to display it crossed) to display all values apart from the filtered ones in a column.

Please let me know how it works for you.

Best wishes,
Nikita

[Mia Tamm _Simpleasyty_]

Hi @Michèle Page 

Thanks a lot for sharing your use case — it’s really interesting. I’d like to better understand your goal before suggesting any alternative approach.

• One quick question that came to mind: do you actually want to keep those separate mini-tables on the centralized page, or is the real objective to just have one single unified overview table?
• If the mini-tables are important, could you share a bit more about the reason or the benefit you see in displaying them like that?

I’m asking because sometimes a simpler setup (with just one data source and one unified table) can make things easier to maintain and much less repetitive than managing so many excerpts and SQL unions.

Curious to hear how you imagine the “ideal” page should look — would it be more of a clean overview table, or do you also need those individual blocks preserved?

Thanks again for opening the discussion!

— Mia Tamm 

[Michèle Page]

Hi @Mia Tamm _Simpleasyty_ 

Thank you for your answer as well!

I definitely agree that sometimes a simpler setup is the better way to go ;-)

My approach was definitely not easy to maintain.

Therefore, I was glad to see the suggestion of Daniel.

His solution was exactly what I was looking for:

• My centralized page has two functions:
  It should provide an overview of all Data Definition pages with Link to the page and Names of the Owner and Steward of the page, see picture above in Daniel's post.
• It also should be the single source of truth for the Names (as we publish the page using the Comala Workflow and names / responsibilities could change, we want to include that information with an excerpt)

I'm not entirely sure what you mean with mini-tables. The set up is now exactly as provided in the example of Daniel:

• Excerpt with Overview table
• Table Transformer with separate Excerpts for each Data Definition

Does that answer your questions?