Sorting a column that is not an attribute

Sorting a column that is not an attribute

  
I'm trying to sort through a column in a Table Records that is not a real attribute (from the Entities data table), it's a new column in the Table Records (not in any data table)
Does anyone have an idea on how to do it? I've tried numerous not so successfull ways.
Could you tell what ways you tried? That'll help us coming up with an answer :).
Iris -

Retreive the results from the query, then AFTER the query, use the SortRecordList command in the SortRecordList Extension (http://www.outsystems.com/forge/component/22/sortrecordlist/) to do the sorting.

J.Ja
Yes,
I also used shortrecordlist lis many time for attribute not from a entity and it works fine , also we can give multiple column name seprtaed by comma , for sorting.
Yeah, SortRecordList can be fine, but given the "List_SortColumn_GetOrderBy" tag of this post, I figured Íris wants to sort using the RichWidgets sortable columns. That's why I asked what she'd already tried.
Hi Íris,

If you are adding the column directly on the TableRecords and adding a calculated expression on it, you can't sort it unless you resort to some sort of javascript, but that just way too complex and error prone...
If you are using OutSystems Platform 9 you probably can add that column directly on your aggregate as a calculated column. This means that you should be able to sort it just like you sort any other column, in this case instead of using {Entity}.[Attribute] syntax you simply use the name of the column and that should work.

Cheers
André Vieira wrote:
Hi Íris,

If you are adding the column directly on the TableRecords and adding a calculated expression on it, you can't sort it unless you resort to some sort of javascript, but that just way too complex and error prone...
If you are using OutSystems Platform 9 you probably can add that column directly on your aggregate as a calculated column. This means that you should be able to sort it just like you sort any other column, in this case instead of using {Entity}.[Attribute] syntax you simply use the name of the column and that should work.

Cheers
 I've attempted to do it this way and get the error: Invalid column name 'ColumnName'.
Where ColumnName is the name of the calculated field.
 
Hi Braxton,

Can you perhaps share an eSpace that shows what exactly you are doing?

I get the same error as Iris. I cannot share an eSpace but i've included some screen shots.

I have a custom attribute in my regular query and want to use the rich widget List_Sort_Column to sort by the custom attribute. however when i click on the header to sort i get an error "invalid column name 'EventStatus'

thank you for any help.

Here's the 2nd screen shot
Fabian -

That's not going to work like that. It needs a two part identifier. Look at the expression in that column that displays EventStatus to figure out what the two part identifier is.

J.Ja

Hi Justin

Thank you for your reply. In the first screen shot i attached you can see the expression. I don't see how i can get a 2 part identifier. This expression doesn't come from a table. What would the first part be of the 2 part identifier?
Fabian -

Please click on the Expression for EventStatus in the Table to get the 2 part identifier.

See attached screenshot.

J.Ja

Hi J.Ja
I clicked the expression on the table. The Value is set to TblCommEventTable.List.Current.EventStatus. This is a 4 part identifier.
I tried to set the List_SortColumn Column attribute to a few values but none works
"TblCommEventTable.EventStatus"
"{TblCommEventTalbe}.[EventStatus]"
I get an error 'TblCommEventTable' found in 'Order By' parameter is an invalid Entity.
Kind of makes sense.

Any ideas what the proper 2 part identifier should be?
Thank you.

Ok so it ended up being a bug in the version we have 9.0.0.19 that has been fixed in version 9.0.1.9

Fixed dynamic order by to work with calculated attributes (#854718)
Fabian,

The version I am running is 9.0.1.9, and still encounter the Invalid column name error.

Sincerely,
Braxton

ok then it is fixed in 9.0.1.25 . we have that version on our sandbox and there the error doesn't occur.

Hi guys,

I'm trying to do the same but with GroupBy on the aggregate.

I tried with calculated attributes and it's fine but if i try to order by an attribute from the GroupBy it doesn't work.

Any tip?

Thanks!

Ps: i'm using p10


Mikael Serra wrote:

Hi guys,

I'm trying to do the same but with GroupBy on the aggregate.

I tried with calculated attributes and it's fine but if i try to order by an attribute from the GroupBy it doesn't work.

Any tip?

Thanks!

Ps: i'm using p10


Have the same request :)

Does not want to sort by my Group By column


Hi Mariaan,

Since various sorting options have been discussed here, could you detail what kind of sorting you are looking for?

Kilian Hekhuis wrote:

Hi Mariaan,

Since various sorting options have been discussed here, could you detail what kind of sorting you are looking for?

Hi Kilian,


Sorting on a Group By column.

The group by column name is "FirstName" so made my dynamic search as follows:

SyntaxEditor Code Snippet

List_SortColumn_GetOrderBy("FirstName")

and on the table List_SortColumn "FirstName" but not sorting in the preparation or on the click of the column name.


Thank you :)


Hi Mariaan,

Since I'm still not entirely sure what your functional requirements are, I'll explain the two methods of sorting on a calculated or grouped column.

First, the static way, is to add a Sort directly to the aggregate. In the aggregate, click the Sorting tab, and click "Add Sort". In the "Select Attribute" pop-up, select one of the calculated or grouped columns (shown at the top, indicated by a green, instead of a blue, attribute icon). Use this method if you only ever want a single way of sorting.

Secondly, the dynamic way, using the RichWidgets List_SortColumn_GetOrderBy and List_SortColumn_SetOrderBy actions. Both actions have, as their first parameter, the widget Id of the Table Records you plan to use the output of the query in. Note that you could as well use any other unique identifier if you don't have a Table Records widget associated with the query, as long as the Id you use for the List_SortColumn_GetOrderBy is the same as the Id you use for the List_SortColumn_SetOrderBy (I assume RichWidgets uses a session variable to store the orders). In the aggregate, click the Sorting tab, and click "Add Dynamic Sort". In the expression editor that opens, enter List_SortColumn_GetOrderBy(myId), where myId is the Id I explained above (typically TableRecords.Id). By default, GetOrderBy returns an empty string, so your query is unsorted. To sort, use SetOrderBy, specifying the Id, the column name (this is the name you gave your calculated or grouped column), and optionally the direction of sorting ("ASC" or "DESC"). Where in the code you use the SetOrderBy is up to you. You could use the RichWidget's List_SortColumn widget, that presumably implicitly also uses List_SortColumn_SetOrderBy. Or you could create a widget yourself.

I hope this explains it all, if not feel free to ask.

Mariaan Bosman wrote:

Kilian Hekhuis wrote:

Hi Mariaan,

Since various sorting options have been discussed here, could you detail what kind of sorting you are looking for?

Hi Kilian,


Sorting on a Group By column.

The group by column name is "FirstName" so made my dynamic search as follows:

SyntaxEditor Code Snippet

List_SortColumn_GetOrderBy("FirstName")

and on the table List_SortColumn "FirstName" but not sorting in the preparation or on the click of the column name.


Thank you :)



If you're having problems with the richwidget dynamic sort you could always use SortRecordList http://www.outsystems.com/forge/component/22/sortrecordlist/ and create your own ajax refresh action to sort using SortRecordList (This could also be done in the prep as well.)

Braxton wrote:

If you're having problems with the richwidget dynamic sort you could always use SortRecordList http://www.outsystems.com/forge/component/22/sortrecordlist/ and create your own ajax refresh action to sort using SortRecordList (This could also be done in the prep as well.)

In general, it's unwise to move on to different measures if something doesn't work, and you don't know why. Understanding why something doesn't work is very important. Giving up and using something that's not specifically suited for the task is bad advice, imho.

Secondly, starting with version 10 I'd use the built-in ListSort instead of SortRecordList, unless you want to sort on multiple columns at the same time. But again, I wouldn't go around sorting lists in memory unless you have a very specific use-case for it.




Kilian Hekhuis wrote:

Hi Mariaan,

Since I'm still not entirely sure what your functional requirements are, I'll explain the two methods of sorting on a calculated or grouped column.

First, the static way, is to add a Sort directly to the aggregate. In the aggregate, click the Sorting tab, and click "Add Sort". In the "Select Attribute" pop-up, select one of the calculated or grouped columns (shown at the top, indicated by a green, instead of a blue, attribute icon). Use this method if you only ever want a single way of sorting.

Secondly, the dynamic way, using the RichWidgets List_SortColumn_GetOrderBy and List_SortColumn_SetOrderBy actions. Both actions have, as their first parameter, the widget Id of the Table Records you plan to use the output of the query in. Note that you could as well use any other unique identifier if you don't have a Table Records widget associated with the query, as long as the Id you use for the List_SortColumn_GetOrderBy is the same as the Id you use for the List_SortColumn_SetOrderBy (I assume RichWidgets uses a session variable to store the orders). In the aggregate, click the Sorting tab, and click "Add Dynamic Sort". In the expression editor that opens, enter List_SortColumn_GetOrderBy(myId), where myId is the Id I explained above (typically TableRecords.Id). By default, GetOrderBy returns an empty string, so your query is unsorted. To sort, use SetOrderBy, specifying the Id, the column name (this is the name you gave your calculated or grouped column), and optionally the direction of sorting ("ASC" or "DESC"). Where in the code you use the SetOrderBy is up to you. You could use the RichWidget's List_SortColumn widget, that presumably implicitly also uses List_SortColumn_SetOrderBy. Or you could create a widget yourself.

I hope this explains it all, if not feel free to ask.


Thank you Killian

Hi Mariaan,

If you mean "thanks for the effort, but it's still not what I want" than I'd like to know if I can assist any further :).

If you mean "thanks that solved it for me", I'd want to ask you to mark my answer as the solution (and Like it :)) so the topic is marked as solved. 

Mariaan Bosman wrote:

Kilian Hekhuis wrote:

Hi Mariaan,

Since various sorting options have been discussed here, could you detail what kind of sorting you are looking for?

Hi Kilian,


Sorting on a Group By column.

The group by column name is "FirstName" so made my dynamic search as follows:

SyntaxEditor Code Snippet

List_SortColumn_GetOrderBy("FirstName")

and on the table List_SortColumn "FirstName" but not sorting in the preparation or on the click of the column name.


Thank you :)



Hi


Why not use an advanced query?



Rui Barbosa wrote:

Mariaan Bosman wrote:

Kilian Hekhuis wrote:

Hi Mariaan,

Since various sorting options have been discussed here, could you detail what kind of sorting you are looking for?

Hi Kilian,


Sorting on a Group By column.

The group by column name is "FirstName" so made my dynamic search as follows:

SyntaxEditor Code Snippet

List_SortColumn_GetOrderBy("FirstName")

and on the table List_SortColumn "FirstName" but not sorting in the preparation or on the click of the column name.


Thank you :)



Hi


Why not use an advanced query?



Hi Rui,  

Yes I can.  I actually had it like that originally, but then I need to maintain a structure with it and instead of just adding a new group by field if I want to add a new column.

I would think it would just be simple to add the column name as the sort by field.

For now I have just sorted it by the FirstName column.  Will change it to use a structure instead.