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

Scott Dyer_np7zge

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

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.