Dynamic Table operation in Page Properties

[Tommy Strømme]

So, I have a template used for posting Planned work. Its inside the Page Properties and I have a nice dashboard that shows me the results.   As you can see I am using the Easy Dropdown macro. Also got the Table filter and Charts for Confluence macro installed. 

I would like the table to be more dynamic. So. let's say you choose "N.A." in one of the dropdowns then I would like the field below grey out. Or also I would like to know how I can have dynamic text / dropdowns appear in a cell based on the value of the dropdown. Been trying to use the table transformer, but have not quite succeeded. 

Any help greatly appreciated!

/Tommy

[Stiltsoft support · Accepted solution]

Hi @Tommy Strømme ,

If you need to color the cells (rows) of your Page Properties Report macro based on statuses, you may indeed use the Table Transformer macro and its CASE WHEN and FORMATWIKI functions.

You may check this example from our documentation https://docs.stiltsoft.com/tfac/cloud/advanced-table-cells-formatting-58426218.html#Advancedtablecellsformatting-Conditionalformatting

And similar Community threads: one, two, three.

Not quite get you request about the dropdowns, but if you mean the multi-level dependent dropdown we can suggest trying the Table Spreadsheet macro. Here is an example of a possible workaround.

[Tommy Strømme]

Thank You for you answer! I will have a look at the Table Transformer again.

About the dropdowns I apologize that I was not quite clear. What I mean was that if you are in a table, and then there is an "Easy DropDown" field called Scandinavian Countries,  you select "Norway" , then the next field is called "City" and the dropdown is now showing "Oslo, Bergen, Stavanger, Tromso". You change the First one to Sweden and the second changes to "Stockholm, Gothenburgh, Malmo" etc.. So you change which "Easy Dropdown" is shown depending on another dropdown. 

[Stiltsoft support]

Then I've got your idea about the dropdowns correctly: the Easy Dropdown is not our app, so we can't tell if they provide dependent dropdowns.

The Table Spreadsheet macro that I've mentioned above comes with the Table Filter and Charts for Confluence app but it is a fully functional spreadsheet right in Confluence, it is not about native Confluence tables.

What concerns the Table Transformer macro, it treats the Page Properties Report as a regular table, so, you may apply all the use cases described in our documentation to your report.

[Tommy Strømme]

Thanks, I got as far as this in he Table Transformer:

SELECT T1.*, FORMATWIKI(" {cell}", CASE WHEN T1.`Planned Work` = "N.A." THEN "Not Applicable" ELSE T1.`Planned Work` END, " {cell}") AS `Planned Work` FROM T1;

However, this overwrites the cells with "N.A." with Not Applicable rather then inserting it in the cell directly below which is what I am trying to get it to do. (fighting with GPT3 and 4 on the side here :-)) 

[Stiltsoft support]

While working with the Table Transformer macro you should understand that it treats your source table as a mini database, you refer to column headers and color cells (set statuses, etc.) accordingly to their contents.

As I see it, you collect your Page Properties as vertical tables. Your headers take the first column.

The same structure remains for your Page Properties Report macro:

Header 1 | A1 | B1

Header 2 | A2 | B2

Header 3 | A3 | B3

So, you wrap your Page Properties Report macro in the Table Transformer macro and transpose the source table:

Header 1 | Header 2 | Header 3

A1           | A2            | A3

B1           | B2            | B3

Now, you can use standard queries and color cells/insert values/etc. 

For example, you may write a condition if your 'Header 1' has cells with "A1" contents, then put some predefined values in the 'Header 2' column (or in a new virtual column if required).

After that you may transpose your result table back inside the Table Transformer macro settings.

In your example you refer to the 'Planned work' column, rewrite its contents based on a condition and save this column under the same work. Everything works as it should.