Table Transformer sql - multiple Filter Results macro

[Chii]

Hi, Could someone please help on this...

I'm adding a 2nd Table Transformer in my Confluence page.  

The Table Transformer contains 4 tables using "Filter Results" macro and I want to use sql to create a new table joining all 4. 

My query shows correct result in the Table Transformer preview,

SELECT 
T1.'Key', T1.'Business Analyst' BA, 
T4.'Assignee' PMO, 
T3.'Assignee' Designer, T2.'Assignee' 'UI/UX Designer'
FROM 
T1 LEFT JOIN T2 ON T1.'Key' IN T2.'Links' 
LEFT JOIN T3 ON T1.'Key' IN T3.'Links'
LEFT JOIN T4 ON T1.'Key' IN T4.'Links';

but when I save the page it shows an error:

"Error: Column does not exist: Business Analyst"

I think the Table IDs are changing whenever I do something in the query. 

I don't mind using less Filter Results, but I don't know how to write the sql for it. 

[Katerina Kovriga _Stiltsoft_]

Hi @Chii ,

Please define what macros you use inside the Table Transformer macro? It seems that they are not our macros and that they are not standard Confluence macros as well.

Maybe it is coming from this app from another vendor? https://botronsoft.atlassian.net/wiki/spaces/ILV/pages/1130594441/Visualize+in+Confluence#Filter-Results-Macro

Maybe smth is wrong with the compatibility of the macros.

You may use a simple SQL query (select the 'Business Analyst' column from the first Filter results macro and see if the column is returned well when the page is published).

And you may recreate your Filter Results tables manually (just create 3-4 row tables with similar headers and data) and test your SQL query on them (to make sure that it works correctly when the page is published).

Then raise a support request. Attach a page storage format (menu ... -> View Storage Format, if you don't see the option, ask your Confluence administrator to do it for you) and the output of each Filter Results macro (with visible headers). 

P.S. Ans check the version of the Table Filter and Charts for Confluence app beforehand - the current one is 8.6.2.

[Chii]

I originally used a

Jira Issue/Filter macro

and the SQL worked fine.  The problem is that this macro does not get the latest data if the editable page is not updated manually.  Unlike the Filter results macro which is updated automatically.

Confluence app version is: 

Confluence 7.12.5

I'm not an admin of Confluence so I'm not sure how to check the Table Filter version.

[Katerina Kovriga _Stiltsoft_]

Then it looks like the problem is really in the compatibility of the two macros from different vendors.

Ask your administrator which version of the Table Filter and Charts you use and contact our support team: they will try to check the performance and see what the issue may be.

[Katerina Kovriga _Stiltsoft_]

And one more question - when you type in T1... do you see all the required columns in the autocomplete hint?

The first column you refer to is T1.'Key', but the error points to the second column that is T1.'Business Analyst' - was it typed in manually or selected from the Table Transformer's autocomplete? Maybe there is a mistake or upper/lower case is messed up?

[Chii]

No, it is autocomplete, and it kind of works just that the "Filter Result" is somehow changing the key T1, T2, T3, T4 but the order of how it is put in Confluence edit mode is not changed at all.

I can use a different macro to get an automated result of the table but not sure which standard Confluence macro can do that.  Could you please advise?  Thank you!

[Katerina Kovriga _Stiltsoft_]

Can't think of anything besides the Jira Issues macro.

But you may still refer to our support - maybe we'll be able to help with the issue.

[Chii]

Ok, unfortunately the Jira issues macro doesn't update automatically. :(