Table filter and Content Report Table is sorting strangely

[Anders Kjöllerström]

I'm trying to get a list of pages with descending sort order. I've identified that this is not possible in the native Content Report Table. So I've placed a content report inside a Table filter, sorting it on Title. This is the sort order I get:

Notice that it is not sorted by Title, Creator or Modified date.

In the tree structure these pages sort just fine, but only ascending as per default. So there should not be an issue with the ASCII.

What is going on here? I'm the space admin.

thanks Anders

[Katerina Kovriga _Stiltsoft_ · Accepted solution]

Hi @Anders Kjöllerström ,

I've checked the Content Report Table macro in our test Server instance where its columns are sortable - the sorting goes exactly as described into your case: pages with plain names are sorted just fine, but Meeting notes are presented as they are in the Page Tree.

The Table Filter macro just catches the initial Confluence sorting and allows it to be set as a default one.

I can suggest using the following workaround for your case: wrap the Content Report Table macro into the Table Transformer macro and then into the Table Filter macro.

For Server and Data Center it can be done directly:

And for Cloud via the Table Toolbox macro:

Then we go to the Table Transformer macro and split the Title column to get only dates from the Meeting notes pages:

SELECT T1.'Title'->split(" Meeting notes ")->0 AS 'Modified Title for Sorting',
T1.'Title', T1.'Creator', T1.'Modified'
FROM T1

Then we go to the Table Filter macro and set the default sorting for the new column:

Now our Meeting notes pages are sorted by dates correctly.

The last step is to hide our additional column: Filters -> Hide Columns and Filtration -> Set filters' default values:

Hope this works.

[Alexander Bondarev]

@Katerina Kovriga _Stiltsoft_ , wow! I have to reproduce it! Thanks!

[Anders Kjöllerström]

@Katerina Kovriga _Stiltsoft_  Thank you so much!

Very helpful and easy to follow, even for a noob such as myself.

When I try, for some reason, the modified column only executes on some of the lines.

Some of the meeting notes pages were named programatically, some were named by typing manually. I cannot see that there is any difference in what and how characters  are used. Could this be it? 

I've assumed that the title is just a String, and that this could not affect the outcome...

[Katerina Kovriga _Stiltsoft_]

And will anything change if you delete the spaces from the query?

SELECT T1.'Title'->split(" Meeting notes ")->0 AS 'Modified Title for Sorting',
T1.'Title', T1.'Creator', T1.'Modified'
FROM T1

->

SELECT T1.'Title'->split("Meeting notes")->0 AS 'Modified Title for Sorting',
T1.'Title', T1.'Creator', T1.'Modified'
FROM T1

We'll see if the difference is in some invisible characters that differ from the regular spaces.

[Anders Kjöllerström]

Thanks!
Copy pasted the above, and no change.

I also tried to retitle one of the meetings that behave strangely. I copied the title from a meeting that behaves as expected, but still it doesn't trim the text.

[Katerina Kovriga _Stiltsoft_]

Oh, it is because you have "XXXX-XX-XX Demo meeting" for the second place marked on the screenshot.

This query

SELECT T1.'Title'->split("Meeting notes")->0 AS 'Modified Title for Sorting',
T1.'Title', T1.'Creator', T1.'Modified'
FROM T1

checks every page name for the Meeting notes phrase and leaves the left part with the date. If there is no such part, then the string is left as it was.

Let's try the following option: use this query for your Table Transformer

SELECT SUBSTRING(T1.'Title', 1, 10) AS 'Modified Title for Sorting',
T1.'Title', T1.'Creator', T1.'Modified'
FROM T1

Here we leave only dates from our Meeting notes or the first 10 symbols from any page name we have.

Then we move to the Table Filter macro and set two default sortings: for the "Modified Title for Sorting" (then we hide this column) and an additional one for the "Title" column because you have pages with regular names without dates:

[Anders Kjöllerström]

Yes! that does it, thank you so much Katerina!

I still don't really understand why we had to do a workaround for this, but that is another thing.

Again, thank you Katerina

all the best

anders