two or more columns sort

Enric_Herrera · March 8, 2016, 8:50am

Is there a way to sort a listbox by two, or more, columns?

For example a column is size, another is color, and a third is value, I want to sort by size, color, and value

Russ_Lunn · March 8, 2016, 10:04am

there are a few ways.

possibly the easiest is to put your data into an in memory sqllite table and then just select it based on the users choices.
then display that recordset in the list.

Eli_Ott · March 8, 2016, 10:48am

Implement the CompareRows event.

Enric_Herrera · March 8, 2016, 10:50am

And?

(It is already implemented this)

Eli_Ott · March 8, 2016, 11:10am

Compare by the value of the first column to sort by, if the two values are equal, compare the rows by the value of the second column.

Enric_Herrera · March 8, 2016, 2:09pm

Well, the question was “Is there a way to sort a listbox by two, or more, columns”?, doing “by hand” it’s always a solution, but I expected a better one. Thanks.

DaveS · March 8, 2016, 2:25pm

And Eli gave you that better option… look at COMPAREROWS
Implement your own over-ride and sort it however you like

Emile_Schwarz · March 8, 2016, 4:23pm

If you do not understand how to implement Eli answer, you’re still in front of a blackw-board (even if this one is white !). ;-

Eli_Ott · March 8, 2016, 6:51pm

For example you want to sort by column 1 ascending and then by column 2 descending and let’s say both columns contain only integers:

Event CompareRows( row1 As Integer, row2 As Integer, column As Integer, ByRef result As Integer ) As Boolean Select Case column Case 1 Dim value11 As Integer = Val(Me.Cell(row1, 1)) // column 1 Dim value12 As Integer = Val(Me.Cell(row2, 1)) // column 1 If value11 < value12 Then result = -1 ElseIf value11 > value12 Then result = 1 Else // the values from column 1 at row1 and row2 are identical, so compare the values from column 2 Dim value21 As Integer = Val(Me.Cell(row1, 2)) // column 2 Dim value22 As Integer = Val(Me.Cell(row2, 2)) // column 2 If value21 < value22 Then result = -1 ElseIf value21 > value22 Then result = 1 Else result = 0 End End Return True // Tell Xojo it should not sort automatically Else Return False // All other columns will be sorted automatically End End

Enric_Herrera · March 8, 2016, 7:06pm

Thank you, this kind of code is what we use to sort columns when they must by ordered as a number, but I would like some more flexible, where the user could decide how to see the set (list) ordered.
I think, as it seems that is not supported easily by Xojo, the better will be to create a SQLlite database from the list and then ORDER by the different fields chosen and pass the result to the list again.

Norman_P · March 8, 2016, 7:07pm

seems like what you need / want is “chainable” sorting functions so you could sometimes sort by one column, sometimes two, sometimes 3 etc

Enric_Herrera · March 8, 2016, 7:08pm

that is.

Eli_Ott · March 8, 2016, 8:47pm

The code was meant as an example…

Mark_Walsh · March 8, 2016, 10:31pm

Check to see if a modifier key is pressed in the HeaderPressed event. If the proper key is pressed (e.g. option/command), then add the selected column to an array and use that to determine which columns the user wants to sort on, and the order they chose to sort them (in the CompareRows event). If the modifier key isn’t pressed, then clear the array and add that column to the array to start a new sort order.

You could use a separate modifier key to allow the user to choose whether to sort each column ascending or descending.

Enric_Herrera · March 9, 2016, 5:54am

thanks to all, I posted looking for a missing simple solution like

mListBox.SortedColumn = “2,4,1” // sorted by column 2 and then 4 and then 1. Now, only an integer is passed to
mListBox.ColumnSortDirection(2 ) = SortDescending
mListBox.ColumnSortDirection(4 ) = SortAscending
mListBox.ColumnSortDirection(1) = SortAscending
mListBox.sort

Thanks all and specially to Eli for your interest, but even if the solutions offered are very good, I found it is easy to convert the list to SQLDatabase, order the set and populate the listbox with it.

Emile_Schwarz · March 9, 2016, 8:27am

Enric:

I love that creative idea (using SQL to gat what you want).

Peter_Job · March 10, 2016, 10:18am

I’ve been mucking about with this. I have tried to make a generic listbox with a database in a class that will do a two-column sort ( easily modified to more ). I have put it up at:

http://www.retroprograms.com/lbsorter.zip

RB source, Xojo source, fugly Win test binary, and 500 element sample data to import. If anyone could take a look I’d appreciate it with comments.

My SQL rating is at the level of rank amateur, please be warned.

I am interested as to how one would cater for rowicons and checkboxes, and how this sort-via-sql approach would work with larger data sets - would it be just too slow?

Thanks
Peter

Carlo_Rubini · March 10, 2016, 12:33pm

For a project of mine I ended up with this code, here slightly modified making cellTags into cell; so same error may be present:

Function CompareRows(row1 as Integer, row2 as Integer, column as Integer, ByRef result as Integer) As Boolean
const NSCaseInsensitiveSearch = 1
const NSNumericSearch = 64
const NSDiacriticInsensitiveSearch = 128
select case column
case 0
result = StrCompWithOptions(me.cell(row1,column) + ": " + me.cell(row1,1) + " " + me.cell(row1,2), me.cell(row2,column) + ": " + me.cell(row2,1) + " " + me.cell(row2,2), NSCaseInsensitiveSearch + NSNumericSearch + NSDiacriticInsensitiveSearch)
Return true
Case 1
if me.Cell(row1,2) <> “” then
if me.Cell(row1,2) = me.cell(row2,2) then
result = StrCompWithOptions(me.cell(row1,column), me.cell(row2,column), NSCaseInsensitiveSearch + NSNumericSearch + NSDiacriticInsensitiveSearch)
Else
result = StrCompWithOptions(me.cell(row1,column) + me.cell(row1,2), me.cell(row2,column) + me.cell(row2,2), NSCaseInsensitiveSearch + NSNumericSearch + NSDiacriticInsensitiveSearch)
end if
else
result = StrCompWithOptions(me.cell(row1,column), me.cell(row2,column), NSCaseInsensitiveSearch + NSNumericSearch + NSDiacriticInsensitiveSearch)
end if
Return true
case 2
result = StrCompWithOptions(me.cell(row1,column) + ": " +me.cell(row1,0) + " " + me.cell(row1,1), me.cell(row2,column) + ": " + me.cell(row2,0) + " " + me.cell(row2,1), NSCaseInsensitiveSearch + NSNumericSearch + NSDiacriticInsensitiveSearch)
Return True
case 3,4,5,6
result = StrCompWithOptions(me.cell(row1,column) + ": " +me.cell(row1,0) + " " + me.cell(row1,1) + " " + me.cell(row1,2), me.cell(row2,column) + ": " + me.cell(row2,0) + " " + me.cell(row2,1) + " " + me.celltag(row2,2), NSCaseInsensitiveSearch + NSNumericSearch + NSDiacriticInsensitiveSearch)
Return true
case 12
result = Sign(Val(me.Cell(row1, column)) - Val(me.Cell( row2, column)))
Return True
else
Return false
end Select
End Function

Peter_Job · March 11, 2016, 3:55am

[quote=252405:@Peter Job]I’ve been mucking about with this. I have tried to make a generic listbox with a database in a class that will do a two-column sort ( easily modified to more ). I have put it up at:

http://www.retroprograms.com/lbsorter.zip[/quote]

I re-uploaded this, hopefully with a quoting bug fixed.

Emile_Schwarz · March 11, 2016, 11:58am

I loaded yesterdays project and it worked fine.

It do not crash, but I do not had time to watch how the sorting is done using a specific testing file where I have many members of the same family (so identical family name) and of course, different First Names (If I recall correctly).

BTW: I tried the xojo project on OS X 10.11.3, El Capitan.