How do I access table columns from T* (table transformer)?

[Scott Dyer]

So I have 1 main table which I'm using to join my other tables off of...

T1:

|| Ref || Name || Control Ref || Test Case Location || Last Tested || Testing Evidence || 

Then I have multiple tables that need to join onto that table to form the picture of what I want that look like:

T*
|| Control Ref || Test Description || Expected Result || Last Tested || Testing Evidence ||

The problem I have that I want to join them all and display their columns in a specific way:

|| Ref || Name || Control Ref || Test Description || Expected Result || Last Tested || Testing Evidence ||

Note: The last tested / testing evidence from T* are excerpt into T1 and thus are the same values 

If I use "Lookup" then it just tacks the T* columns at the end.. which doesn't work. 

And if I try and use T*.'Test Description' in the SQL query it falls over itself with a SyntaxError.

I can get it to work fine for just 2 tables joining each other. As soon as there are 2 or more of the second table types then it falls over. The problem is I can't specify:

SELECT T1.'Ref', T1.'Name', T1.'Control Ref', T2.'Test Description' ...

Because then T3, T4 etc don't get that column filled in

Is there a solution?

*edited for clearer column names*

A hacky solution is to label the T1 columns slightly different names (like a random character at the end) because the columns are duplicated, and then a table filter to hide the ones I don’t want. But it wouldn’t help if I actually wanted to place T1 columns back in the list after T* ones

[Stiltsoft support · Accepted solution]

Hi @Scott Dyer ,

If to put it simply, I understood that you have T1 table and several T* (T2, T3, T4, T5, etc.) tables. T1 is your main table, T* are your child tables with additional data that corresponds to the items in your T1 table. All the T* tables have the same structure (the same set of columns). The task is to lookup the main table and child tables.

So, I can suggest including an additional step for the case: merge your T* tables into one table. It will simplify the case, especially if the number of child tables may vary.

If you keep all the child tables on the same page, you may use an additional internal Table Transformer macro and its standard "Merge" preset.

If your child tables may come from other pages, you may use the Table Excerpt/Table Excerpt Include macro (just tick "Show as a report table" option inside the Table Excerpt Include macro and all your collected tables will be merged into one table automatically).

Now when you have just two tables, you may use another Table Transformer macro to lookup them by the unique column (it seems to be the 'Control Ref' column judging by your headers) and list the required columns in that specific order that you need. Seems that you already have a working SQL query for such case.

[Stiltsoft support]

Also paid attention on this phrase "if I actually wanted to place T1 columns back in the list after T* ones": if your 'Last tested' / 'Testing evidence' columns from the T1 and combined T* table contain different information and you want to show both variants, don't forget to rename them in your SQL query.

For example, smth like this:

SELECT
T1.'Last tested' AS 'Last tested from main table',
T2.'Last tested' AS 'Last tested from child tables'
FROM T1 OUTER JOIN T2 ON T1.'Control Ref' = T2.'Control Ref'

And the SQL query below won't work (there can't be the same columns' names in the output table of the Table Transformer macro):

SELECT
T1.'Last tested', T2.'Last tested'
FROM T1 OUTER JOIN T2 ON T1.'Control Ref' = T2.'Control Ref'

[Scott Dyer]

Thanks, for some reason I never thought to merge all the T* tables first but you’re right it would solve the issue.

Also knowing you can rename table columns with using “as” is very useful 👍🏻