I have a listbox with multiple columns - one column has a date, another column a string.
Is there a way to sort by date column and then sort by string column so that the date becomes the sub-sort?
When I sort the listbox by date and then by string - it seems to lose the date order.
I used to use a database called Panorama that would do this and was wondering if the Xojo listbox could do this as well.
i think you need this event
Function CompareRows(row1 as Integer, row2 as Integer, column as Integer, ByRef result as Integer) Handles CompareRows as Boolean
End Function
and a compare method
https://documentation.xojo.com/api/language/sort.html
I’m not sure if this will work for me. I use the CompareRows function to sort the date column. The problem arises after when I click the header to sort the string column.
First I sort the listbox by date and I get something like this:
1/1/02 Jones, Mary
6/1/02 Smith, John
1/1/03 Jones, Mary
6/1/03 Smith, John
1/1/04 Jones, Mary
6/1/04 Smith, John
but when I click the header to sort the second column, I get something like this:
1/1/02 Jones, Mary
1/1/04 Jones, Mary
1/1/03 Jones, Mary
6/1/02 Smith, John
6/1/04 Smith, John
6/1/03 Smith, John
instead of this:
1/1/02 Jones, Mary
1/1/03 Jones, Mary
1/1/04 Jones, Mary
6/1/02 Smith, John
6/1/03 Smith, John
6/1/04 Smith, John
It seems like it forgets the first sort and just sorts the second column.
I don’t want to make this rigid because the user might click another column like invoice number and want to sort by invoice number and customer instead of date and customer.
Sorting on multiple columns is pretty tricky. If it were me, I’d set up an in-memory SQLite database and keep all the rows of data there. Then use SQLIte’s sorting to get the RowSet and repopulate the listbox.
is that not the same as you put both together.
date+str <=> date+string
the sort just need -1,0,1
How do you populate your ListBox ? From a Data Base ?
If so, maybe you can sort the DataBase directly at read time…
CompareRows(row1 as Integer, row2 as Integer, column as Integer, ByRef result as Integer)
if column = dateindex
my idea was
read date+next column from row1
read date+next column from row2
=CellValueAt(row1,column)
=CellValueAt(row1,column+1)
=CellValueAt(row2,column)
=CellValueAt(row2,column+1)
create two strings yyyymmddtext
result = CompareMethod(yyyymmddtext1,yyyymmddtext2)
return true
Something like this should work, but untested
Function CompareRows(row1 as Integer, row2 as Integer, column as Integer, ByRef result as Integer) Handles CompareRows as Boolean
Select Case column
Case 0 ' Date Column
If me.CellTagAt(row1, column) > me.CellTagAt(row2, column) Then
Return 1
ElseIf me.CellTagAt(row1, column) < me.CellTagAt(row2, column) Then
Return -1
Else
Return 0
End If
Case 1 ' Name Column
If me.CellTagAt(row1, column) > me.CellTagAt(row2, column) Then
Return 1
ElseIf me.CellTagAt(row1, column) < me.CellTagAt(row2, column) Then
Return -1
Else ' Same values so now compare on column 0 being the date column
If me.CellTagAt(row1, 0) > me.CellTagAt(row2, 0) Then
Return 1
ElseIf me.CellTagAt(row1, 0) < me.CellTagAt(row2, 0) Then
Return -1
Else
Return 0
End If
End If
End Select
End Functioni guess this byref result is the return value 
Return True if the returned Result parameter is accurate for sorting.
Return False if you want the Listbox to use the default lexicographic sorting of the column.
And that’s what you get for coding here 
Except return x should be result = x
Ooops - you beat me to it 
Corrected code
Function CompareRows(row1 as Integer, row2 as Integer, column as Integer, ByRef result as Integer) Handles CompareRows as Boolean
Select Case column
Case 0 ' Date Column
If me.CellTagAt(row1, column) > me.CellTagAt(row2, column) Then
result = 1
ElseIf me.CellTagAt(row1, column) < me.CellTagAt(row2, column) Then
result = -1
Else
result = 0
End If
Case 1 ' Name Column
If me.CellTagAt(row1, column) > me.CellTagAt(row2, column) Then
result = 1
ElseIf me.CellTagAt(row1, column) < me.CellTagAt(row2, column) Then
result = -1
Else ' Same values so now compare on column 0 being the date column
If me.CellTagAt(row1, 0) > me.CellTagAt(row2, 0) Then
result = 1
ElseIf me.CellTagAt(row1, 0) < me.CellTagAt(row2, 0) Then
result = -1
Else
result = 0
End If
End If
Case Else
Return False
End Select
Return True
End FunctionI do populate the listbox from the database. I was thinking once the listbox was populated, you should not try to make another call to the database (if possible).
This should work. I just have to keep track of which column was sorted last (maybe with a property of the window).
Then you are asking the list to sort by string.
The listbox itself has no concept of sorting using two fields.
You may be better adding two sorting controls, or a sorting priority, for the user.
Eg:
except CompareRows
Visually, the user can not see anything about multi-column sorting.
Its column A or column B
Arguably, performing a multi-column sort on the basis of a single column header click, would be unintuitive.
The posts so far suggest remembering ‘which was clicked first, and which was clicked second’
So what happens if they click again?
Separate , unambiguous controls remove the guesswork ,is all.
thats true, but it can showed in the header paint event.
This is exactly what I did using a dialog similar to yours and an in memory Sqlite database. I brought the records into Sqlite, and then did a select with the desired order(s) and repopulated the listbox. It will probably be something rarely used, however it was a great exercise if something similar is ever needed.
Thanks.
When I have to sort a ListBox on a date column (sorting the ListBox directly without having to re-query the Database), I add a non-visible column to the ListBox, and I store in it the SQLDate (yyyy-mm-dd), and then I sort the ListBox on this column (while the visible column shows the date in the SortDate format)
I mean, the ShortDate format