Can I auto-populate values from one table to another table?

[Hannah Uribe]

I am creating project pages that roll-up to a report page using page properties and page properties report, table excerpt and table excerpt include, as well as table filter and table transformer. I am by no means an expert on using table transformer, but have been able to figure out how to get things to show up as desired on the report page.

On the report page there is a project status section (Status | Start Date | End Date | Risk | Latest Update) and a team workload section (Team Member | Start Date | End Date | Level of Effort) like this:

On the project pages I want to have a section to list team members separately from the overall project status details like this:

But the only way I have been able to figure out how to get the level of effort and dates for each team member to show up on the report page (and still get the project page to look the way I want) is to add those columns, copy and paste the values into each row, and then hide the columns, like this:

The problem with this solution is that when this is rolled out to others to create and maintain pages, it will not report correctly if they do not copy and paste to each row.

So can I auto-populate values from one table to another table? Or is there another way to do the team workload report without having those hidden columns?

[Stiltsoft support]

Hi @Hannah Uribe ,

As I see, you want to add the upper one-row table that is wrapped in the Page Properties macro and goes to the Page Properties Report on your master page to the lower table that is a one-column table containing the list of the Team Members for this specific project.

To join two tables, we need a key column to show the macro where we want to add our data:

Here I've added two simple 'Key' columns with the same contents: we add the same row to every teammate on the list.

As your first table goes to to the Page Properties Report macro, you may adjust its settings not to show the 'Key' column in the final report.

For the Table Transformer macro you may use the following query:

SELECT 'Team Member', 'Column 1', 'Column 4'
FROM T1
RIGHT JOIN T2 ON T1.'Key' = T2.'Key'

Here we join the table by the 'Key' column and list only that columns which we want to show on the page:

Hope it helps your case.