How to sort a table created by several Excerpt Includes embedded in a Table Transformer?

Question

I have a table that is created using the Table Transformer with over a dozen Excerpt Includes that reference single row tables from as many different confluence pages.  The table generates as expected.  However the first column is a date column and we always want the table sorted by ascending dates... but it always displays in an order that seems related to the order I have the Excerpts Includes arranged (but not identical).  The dates in the Date column are also populated in all of the source tables using the Insert Date function in the format of dd M yy.  I have used the edit Table Transformer (TT) to set up the following:

1. In the Settings of TT, I've set the Date Format as dd M yy.

2. In the SQL query of TT, I've entered the following SQL command:

SELECT * FROM T*

ORDER BY 'Date' ASC

Every time we used this page we have to Click on the Date header to sort the column ascending.  Is there any way to make this sort on date ascending upon Page refresh?

Robin Wright · Answer

Wow! Wa! Wow!!! The SQL you shared above works!!! Thank you so much! I did have consistent date formate (dd M yy) so that working both with typed in dates and using the date insert from the calendar.  I'll spend some more time understanding your comments on the best practices above ... but my sort problem is resolved! :)

Stiltsoft support · Answer

The initial issue is that while using the standard macros you've got several separate tables, so when you use the

SELECT * FROM T*
ORDER BY 'Date' ASC

query, you try to sort the merged table that doesn't exist.

So, the correct query for this case is

SELECT * FROM (SELECT * FROM T*)
ORDER BY 'Date Contract will Expire' ASC

In the internal SELECT we merge all the tables into one report and then sort it.

But it is not a best practice: each time you need to collect a new table, you need to insert both Excerpt and Excerpt Include macros.

The switching to the Table Excerpt/Table Excerpt Include macros allows you to have only one Table Excerpt Include macro on the master page that collects all the Table Excerpts automatically.

So make sure that your macro structure is correct: you have one Table Excerpt Include macro (not several!) with the "Show as a report option" ticked inside the Table Transformer macro.

Then the simple query

SELECT * FROM T*
ORDER BY 'Date' ASC

will work as expected.

What concerns the date format, there may be an issue in mixing two similar formats:

* dd M yy stands for 07 Apr 2024
* d M yy stands for 7 Apr 2024

Maybe this helps your case.

Robin Wright · Answer

Hi @Stiltsoft support - Thank you so much for your suggestion.  I've tried it out (switching to Table Excerpt/Table Excerpt Include and ticking the "Show as a report option) and the resulting table is still not sorting correctly.  I'm going to experiment with the date format and see if that help at all. BTW, the SQL is definitely do something as if I switch from "ORDER BY 'Date' ASC" to "ORDER BY 'Date' DESC" the order of the rows does change ... it just that neither on produce the correct order.  Next I'm going to play with the date format ... but that will have to wait a day or two. :)