Table Filter Macro using Variable as Input

[Emily Berg]

I have several pages that I need to create that reference the same table excerpt.  Within the header of the Confluence page I would like to include the inputs for filter the table (so I don't have to edit the macro for each page).  Is there way to reference a variable within a Filter Table macro?  In this example, I would like to filter the table based on "Unit #" column.  And my input is "Alpha-2".

[Katerina Kovriga {Stiltsoft} · Accepted solution]

Hi @Emily Berg ,

If I got your case right, you reuse the source table and wrap every Table Excerpt Include macro into the Table Filter macro to filter by different columns and values (so you'll get a kind of a dashboard).

Unfortunately, we don't have any automatic rules to assign filters/values to the Table Filter macro based on additional tables or smth similar.

[Emily Berg]

Hi @Katerina Kovriga {Stiltsoft} ,

Okay fair enough.  How about if I wrap the Table Excerpt into a Table Transformer Macro and "filter" the table that way - could I use a page variable to reference in the SQL query to extract a line(s) of interest?

Emily

[Katerina Kovriga {Stiltsoft}]

Hi @Emily Berg ,

The Table Transformer macro can use a page variable in its SQL query:

SELECT T1.* FROM T1
WHERE T1.'Name' = (SELECT T2.'Variable' FROM T2)

The big source table can be reused and inserted via the Table Excerpt Include macro. But you'll still need to wrap your reused table into the Table Transformer and create a mini-table with a different value for all your pages.

It seems that it is not faster than manually filtering you table with the Table Filter macro.

Have I got your case right? You need to have different values for every page?

If you want to show the same filtered table on all of your pages, then you'll need to set your filter once and wrap both your source table and the Table Filter macro on top of it into the Table Excerpt macro.

[Emily Berg]

Thank you @Katerina Kovriga {Stiltsoft} ,

Yes, this is what I was going for, but I was hoping to be able to reference a variable outside of the table transformer macro - like maybe within the page's properties macro?  I am trying to prevent an error when typing text and then having to select the proper filter (duplicate entries).

Emily

[Katerina Kovriga {Stiltsoft}]

You may create several mini-tables with variables and wrap every one into the Table Excerpt with a corresponding name.

Then you'll be able to include it into the Table Transformer macro via the Table Excerpt Include macro.

[Emily Berg]

@Katerina Kovriga {Stiltsoft} 

Thank you for this comment - it allowed me to be a bit more creative about how I approach this.  I found a solution that works beautifully, using multiple table excerpts across various pages.  Awesome, thank you!

Emily

[Katerina Kovriga {Stiltsoft}]

And thank you for reaching out!

[Emily Berg]

@Katerina Kovriga {Stiltsoft}  - Hello!

I am now using this in a different use case and am wondering about accessing the column of interest as a variable too.

With something like this...

SELECT * FROM T1
WHERE (SELECT T2.'Column' FROM T2) = (SELECT T2.'Variable' FROM T2)

Thank you for your assistance!

Emily

[Katerina Kovriga {Stiltsoft}]

Hi @Emily Berg , I'm afraid it is not possible to introduce a column name as a variable.

[Emily Berg]

Thank you for the fast response @Katerina Kovriga {Stiltsoft} !

[Anna Szabó]

Hi @Emily Berg you can try to transpose the table with a Table Transfo macro fisrt, then after the filtering transfpose it back.

@Katerina Kovriga {Stiltsoft} Thank you for the great answer! Do you know if there is a way to use it for multiple Variables on the same table? Like when I add an extra line on the Variable table (want to filter on B and C too) it only filters on the first one.

[Katerina Kovriga {Stiltsoft}]

Hi @Anna Szabó ,

You may add additional columns for your extra variables to the second table:

Here is your modified SQL query:

SELECT T1.* FROM T1
WHERE T1.'Name' = (SELECT T2.'Variable 1' FROM T2)
OR T1.'Name' = (SELECT T2.'Variable 2' FROM T2)

And here is the result:

Hope this helps your case.

[Anna Szabó]

@Katerina Kovriga {Stiltsoft} It answers my question! Thank you for the unbeliavebly quick answer!!!