Freeze First Row in ConfiForms TableView macro

[davin]

Hello,

Is there a way to freeze the first row which includes all the Column Headers and also the sorting options in ConfiForms TableView? Basically I don't want it to disappear when scrolling down as we might still need to refer to which column is what. 

I usually use the Table Filter Macro with the ConfiForms Table View macro wrapped inside of it to freeze the first row but the Table View macro breaks everytime I have to sort data that is probably stored in a different format in ConfiForms.
Example - Masked Autonumbers and Vote counts cannot be correctly sorted using the Table Filter macro. Is there a way to fix this and continue using the Table Filter macro as it provides a lot of added functionalities and also my team is used to using it.

[Katerina Kovriga _Stiltsoft_ · Accepted solution]

Hi @davin , the Table Filter and Charts for Confluence app is our add-on, and we always recommend it for freezing rows/columns of the ConfiForms macros (as you are doing now as a workaround).

The Table Filter macro also provides sorting - you may also try to apply it to your Table View macro.

[davin]

Hi Katerina,

That is what I have been using for all my ConfiForm tables and want to stick to using it too as it has a lot of useful functionalities (kudos to that) and my team has grown accustomed to using it.

But when my ConfiForm uses specific data types like I mentioned, Masked Autonumber and Voting counts for example, the sorting functionality breaks and gives incorrect results. Please see below screenshots - 

• Masked Autonumber field in ConfiForms sorted in Ascending order using Table Filter macro. There are other records like M-1, M-3, M-16, etc which are not in the correct order as you can see because it seems to sort the data based on every digit individually. Which means this is how it sorts in ascending order - 1, 100, 1000, 2, 200, 2000, 3, 300, 3000

 

• Voting field count sorted in Descending order but again it follows the above logic of sorting thus giving incorrect results which means 2>19  -
  

[davin]

@Alex Medved _ConfiForms_ Can you confirm if the Table Enhancer macro accounts for this problem and correctly sorts data as it should?

[Katerina Kovriga _Stiltsoft_]

We can suggest a workaround for the first case: wrap your ConfiForm macro into the Table Transformer macro and then into the Table Filter macro.

Use the following SQL query for the Table Transformer:

SELECT *,
T1.'Process #'->split("-")->0 AS 'M',
T1.'Process #'->split("-")->1 AS 'Number'
FROM T*

It will split your "M-XX" into two columns (M and some number), so you'll be able to sort them properly later:

[davin]

But this won't solve the problem. As Alex mentioned, the problem arises from sorting the fields as text and not numbers. So even after splitting, the number field will be sorted as 12<194<2<200 in Ascending order.

So, is there a way to force a column to be sorted as a number?

[Katerina Kovriga _Stiltsoft_]

And have you tried the Table Transformer? 

When I check the sorting using a manually created table and the Table Filter macro, here is my result:

After the Table Transformer macro the result is different:

[davin]

But this breaks up the field into two parts and displays them that way. Which is not what we would want as that specific structure is important.

[Katerina Kovriga _Stiltsoft_]

You'll be able to hide the unwanted columns:

This example is for the classic panel view but you can also hide columns via the header filters view as well.

Besides sometimes Table Transformer may help even without any splitting - for example, for my manually created table sorting works right if I simply wrap my table into the Transformer.

[davin]

Hi Katerina,

I believe you are failing to understand the root problem I'm facing.

To put it simply, I have 3 columns and I want to be able to sort the data based on which field I choose by just clicking on the Column Headers. 

In your solution, if I split and then hide them, how can I access the column header to sort correctly? It can be used for default sorting but not for interactive sorting where the user would choose which column to sort by.

Also, for sorting without splitting using the Transformer as you mentioned, what is the SQL Query that you have used? Or any specific settings?

[Katerina Kovriga _Stiltsoft_]

Here is how the default Confluence sorting into headers works for me:

The first variant is wrong - it's a table wrapped into the Table Filter macro (without it's own sorting). 

The second variant seems to be right - it's a table wrapped into the Table Transformer macro and then into the Table Filter macro.

I don't use any specific settings for the Table Transformer - just insert the macro and put a table inside its body (I don't change the default SQL - it remains "SELECT * FROM T*").

And I don't use any sorting inside the Table Filter macro - just click the standard arrows in the table headers and it seems to be working.

[davin]

No idea how but this did it.

Thank you so much Katerina. Appreciate your patience and skill in solving my problem and teaching me something new on the way!

Can you explain the logic behind what the Table Transformer does actually in this case that the Table Filter can't do on its own? I mean we obviously aren't using the Table Transformer for merging tables or anything but rather just formatting the data for the Table Filter to correctly sort. How does that happen?

Again, thank you so much!

[Katerina Kovriga _Stiltsoft_]

The Table Transformer macro, we can say so, creates its own virtual table and the native sorting is applied to it by the app.

Without the Table Transformer (with the Table Filter macro or without it) Confluence applies its own native sorting with a text logic that is not suitable for your case.

The Table Filter macro only "catches" the existing sorting logic and allow you to set the default column and rule. 

So, I'm glad that I could help and thank you for reaching out!

[davin]

Understood. That's great. Will keep in mind for future use.
Also, should be helpful to have this explanation documented somewhere I believe.
Have a good day!

[Katerina Kovriga _Stiltsoft_]

Hi @davin ,

As it seems to me, you are a power-user both of Confluence and our app. Maybe you'll be able to talk to us via Zoom about your current uses cases, needs, any ideas you have, etc?

Please book a suitable time slot here - we'll be happy to meet you in person.)

[davin]

Absolutely! Would love to help improve anyway I can. 
Talk to you soon! :)

[davin]

Hello @Katerina Kovriga _Stiltsoft_ 

I recently realised this particular setup has a weird bug.

As per our discussion, this is how I have set up the table view of the ConfiForm Table data to properly sort numbers rendered in text format by ConfiForms -

But now, this does not register inline edits anymore. Everytime I make an inline edit in this view and click on the blue tick icon to save the change, it disappears on refreshing the page.

We caught this a little late as it shows in the table right after saving but the next time when we visited the page, the information had disappeared again. Lost a lot of important updates because of this. On further scrutiny I realised this is solely because of the Table Transformer macro as removing it from the middle fixes this problem. But then I'm back to square one with the sorting problem. So right now its like prioritizing which feature is more important for us - sorting data correctly or having inline edit functionality.

Can you please check and let me know if there's any way to fix this?

[Katerina Kovriga _Stiltsoft_]

Hi @davin , please raise a support request here.

Attach the page storage format (upper right corner > menu ... > View page storage), and we'll try to look into the issue.

[davin]

@Katerina Kovriga _Stiltsoft_ Can't find the "Page Storage Format" option in my menu but I have submitted the request. Thank you as always.

[Alex Medved _ConfiForms_]

Hi

This might help https://wiki.vertuna.com/display/CONFIFORMS/Using+Table+Enhancer+plugin+together+with+ConfiForms+TableView+to+render+sticky+header+for+the+table 

Alex

[davin]

Kind of how the Table Filter macro works, which we already have added to our Confluence instance. Is there no way to achieve sticky headers with the TableView macro and the Table Filter macro? Adding a new macro to our instance (even if free) is a long process which I don't find worth it for a basic functionality which we already have in an existing macro. This is a pretty common requirement for tables with headers imo.

[Alex Medved _ConfiForms_]

Same page explains a plugin-free approach

[davin]

Do not have access to custom scripts.

[Alex Medved _ConfiForms_]

I see. What I can say is that the "online sorting" works correctly, as the actual data you are trying to sort (when rendered in the table cell) is a text, not numeric

And is sorted as text.

[davin]

So, is there no way to sort these fields correctly and also have sticky headers on them? Because I will need an additional macro for sticky headers but that will sort them as text leading to incorrect sorting.

Please suggest a workaround.