Hi!
Is there a method to sort a WebListBox?
Yeah, load up each column into an array. SortWith on the array representing the column you wish to sort, set all the cells of the ListBox. I’ve implemented this in Studio Stable Web Essentials. Visit the demo, click “#2” then “List” in the top segment controls. The click on the headers to sort columns.
As of 2014r1, there is a HeaderClicked event, so you could do this yourself.
[quote=95253:@Greg O’Lone]As of 2014r1, there is a HeaderClicked event, so you could do this yourself.
[/quote]
Right, but clicking the headers doesn’t sort the WebListBox. And yes, he could do the sorting “himself” anyway. See the first two sentences of my reply. 
Okay!
This would be a big improvement for xojo in future, if its build in within the listbox-control 
I try to solve it with arrays (the could be very tricky) 
There is also a an example included with Xojo to show how to sort a WebListBox in this manner:
Examples/Web/Controls/SortableListBox
I wasn’t trying to be snotty. Just pointing out that the event was added to the regular WebListbox.
[quote=95257:@Lars Lehmann]Okay!
This would be a big improvement for xojo in future, if its build in within the listbox-control
I try to solve it with arrays (the could be very tricky) :D[/quote]
You could also requery if you’re using a database. My concern with using arrays is that now you’re storing the data in memory a second time.
I’d vote for a feature to auto-sort, but I didn’t notice that there was a header clicked event, so I appreciate knowing that.
Greg, I appreciate your info and would have even if it was snotty!
Yes auto-sort would be cool… In the mean time. Nice example.
However how would you represent the tables contents if it had more than one column?
An array of string arrays?
How do you declare that
@Brian O’Brien, did you by chance get an answer to your last question? I am wanting to do something quick using xojo web and the weblistbox not “easily” sorting could be a deal breaker for me. I have 4 columns (string, string, date, currency) that I need to be able to sort when the user clicks the header.
Re-query the DB is an option but an additional round trip that I am trying to prevent. The larger the table gets the slower the query gets when populating the weblistbox.
One way is by putting every column in different arrays and use sortwith.
Sort the other arrays to the one that corresponds to the header clicked.
If Header1(Arr1) is clicked…
Arr1.Sortwith(Arr2, Arr3, Arr4)There might be a better way but this is what popped up in my head right now
And then obviously put the data back in the listbox.
EDIT: I see now this was suggested earlier in this thread
I want to write a method, which sorts Listboxes with dynamicaly column counts.
[code]dim LST as weblistbox 'the Listbox to sort
dim HeaderIndex as Integer ’ the Index of the clicked header
Dim Cols() As Variant 'All ColumnValues in own Arrays
for i as Integer = 0 to lst.ColumnCount - 1
dim Arr() as string 'one column in an array
for a as integer = 0 to LST.RowCount - 1
Arr.Append(LST.Cell(i, a))
next
Cols.Append(Arr)
next[/code]
Now: how can I sort the Arrays?
We always have a database behind out WebListbox so we just requery the database with the proper sort order to match the column clicked on and its sort order.
We limit the amount of data coming back and do ‘paging’ so we don’t have this problem. You really don’t want more than a couple of hundred rows in your list boxes since they will get REALLY slow. If you look around the web, most web sites default lists to contain 50 or less rows at any given time. They give you the option of showing more but very few allow an unlimited amount of data to be shown.
I don’t want requery all the Data. I’m also not a fan of pagination, thats a UX-element from the nineties.
Additional, some data which is shown in a listbox don’t comes from a query, but were computed before.
Um…well, then you must be using different websites then I am since it still in use by many (most?). Seriously, Amazon, PayPal, eBay still paginate your results so it’s not a “nineties UX element.”
Well, then there’s not much else you can do but figure out how to create your own listbox sort. Sorry to have wasted your bandwidth.
It is not a question of fashion, it is a truly efficient way to manage an UI. Especially on the web. And especially for a WebListBox. If you look at the code in the browser Developer Tools, you will see that a WebListBox is entirely present in the page : all columns, all rows, even if you show only one row. That is a huge waste of traffic.
Paginating to send only the necessary rows to the UI is the only way to insure a real zippy UI.
I know it is very old fashion, but one should always think about the worst possible connection when designing a web app. Especially when over 50% of web connections is done by mobile devices possibly in cell data mode, and with the kind of rather slow browsers they have.
So there is no way to sort an array of arrays? If I write a method, that sort listboxes with custom columncount, I have to do it with a lot if-thens…
hmmm…
I’m subclassing WebListbox, so that I can sort this by column.
There are a few other things that need to be moved than just the cell contents.
for example there are tags and those tags should move with the contents of the current cell.
Must I figure out what properties ‘might’ be effected by the documentation OR is there a way by introspection to determine the properties of each cell and move the whole thing? Is there a Cell Object that I might use?
I modified David Cox 's function for sorting WebListBox (Link to conversation) so it can take into account RowTags and CellTags as well. It works well with Strings, dates and numbers. I use two Functions in a subclassed WebListbox . It surely can be optimized and by the way, let me know if you do so :). Hopefully it can be usefull for someone… So thanks again to David Cox for creating the main part of this code. Some comments are in french but you should find your way around…
Function 1 : fieldExistsInTable (db As SQLiteDatabase, tableName As String, fieldName As String) As Boolean
[code] Dim rsf As RecordSet
rsf = db.FieldSchema(tableName)
If rsf <> Nil And rsf.RecordCount > 0 Then
While Not rsf.EOF
If rsf.Field(“ColumnName”).StringValue = fieldName Then
Return True
End If
rsf.MoveNext
Wend
End If
Return False[/code]
Function 2 : SortListbox(sortColumn As Integer)
[code] Dim memoryDB As New SQLiteDatabase
Dim rec As New DatabaseRecord
Dim rs As RecordSet
Dim numericalColumn As Boolean = True
Dim tempSQL As String
Dim tempString As String
Dim sortAscending As Boolean = True
If Me = nil Then Return
If Me.ColumnCount < 1 Then Return 'no point sorting when no columns!
If Me.RowCount <= 1 Then Return 'no point sorting when no rows, or only one row!
If not memoryDB.Connect Then Return 'can’t access the database
'check If the column is already sorted by going down the column until we find two in descending order, otherwise exit And keep as ascending order.
// This fixes the issue when lots of values are the same.
For tempInt As Integer = 1 to Me.RowCount - 1
Dim b1 As Boolean
If Me.Cell(tempInt - 1, sortColumn).val = 0 Then b1 = True
dim s1 As String = Me.Cell(tempInt - 1, sortColumn)
dim s2 as String = Me.Cell(tempInt, sortColumn)
If s1 < s2 then b1 = True
Dim b2 as Boolean
Dim s3 As Double = ReplaceAll(ReplaceAll(ReplaceAll(Me.Cell(tempInt - 1, sortColumn), ",", ""), " ", ""), "-", "").val
Dim s4 As Double = ReplaceAll(ReplaceAll(ReplaceAll(Me.Cell(tempInt, sortColumn), ",", ""), " ", ""), "-", "").val
If s3 < s4 Then b2 = True
If (Me.Cell(tempInt - 1, sortColumn).val = 0 _
And Uppercase(Me.Cell(tempInt - 1, sortColumn)) < Uppercase(Me.Cell(tempInt, sortColumn))) _
Or ReplaceAll(ReplaceAll(ReplaceAll(Me.Cell(tempInt - 1, sortColumn), ",", ""), " ", ""), "-", "").val < _
ReplaceAll(ReplaceAll(ReplaceAll(Me.Cell(tempInt, sortColumn), ",", ""), " ", ""), "-", "").val Then
sortAscending = False
Exit For
End If
Next
'build an in-memory SQLite database to hold the column data
tempSQL = “CREATE TABLE sortTable (”
//Ajouter une colonne pour le rowTag
If Me.RowTag(0).StringValue <> “” Then
tempSQL = tempSQL + “myRowTag” + " TEXT COLLATE NOCASE, "
End If
For tempInt As Integer = 0 to Me.ColumnCount - 1
tempSQL = tempSQL + “myColumn” + str(tempInt) + " TEXT COLLATE NOCASE, "
//Ajouter le cellTag
If Me.CellTag(0, tempInt).StringValue <> "" Then
tempSQL = tempSQL + "myCellTag" + Str(tempInt) + " TEXT COLLATE NOCASE, "
End If
Next
tempSQL = tempSQL + “extraSortColumnWAD DOUBLE”
tempSQL = tempSQL + “)”
memoryDB.SQLExecute(tempSQL)
If memoryDB.Error Then Return 'not sure why we got an error, but leave the table alone
'place the WebListBox data into the database
For tempInt As Integer = 0 to Me.RowCount - 1
rec = New DatabaseRecord
For tempInt2 As Integer = 0 to Me.ColumnCount - 1
//Ajouter le RowTag
If fieldExistsInTable(memoryDB, "sortTable", "myRowTag") Then rec.Column("myRowTag") = Me.RowTag(tempInt)
//Ajouter le CellTag
If fieldExistsInTable(memoryDB, "sortTable", "myCellTag" + Str(tempInt2)) Then rec.Column("myCellTag" + Str(tempInt2)) = Me.CellTag(tempInt, tempInt2)
//Ajouter donne
rec.Column("myColumn" + str(tempInt2)) = Me.Cell(tempInt, tempInt2)
'check If the chosen column is full of only numerical data
If sortColumn = tempInt2 And numericalColumn Then
tempString = Uppercase(Me.Cell(tempInt, tempInt2))
tempString = ReplaceAll(tempString, ",", "") 'remove commas
tempString = ReplaceAll(tempString, " ", "") 'remove spaces
If tempString <> "" And tempString <> str(tempString.val) And tempString.val < 1000000 Then
numericalColumn = False 'check If what's left over is a number
End If
End If
Next
memoryDB.InsertRecord("sortTable", rec)
If memoryDB.Error Then Return 'not sure why we got an error, but leave the table alone
Next
'extract the sorted table from the database, wipe the table Then return the data
tempSQL = "SELECT * FROM sortTable "
If numericalColumn Then
memoryDB.SQLExecute(“UPDATE sortTable SET extraSortColumnWAD = REPLACE(REPLACE(myColumn” + str(sortColumn) + ", ’ ', ‘’), ‘,’, ‘’) ") 'convert the text to a number
If memoryDB.Error Then Return 'not sure why we got an error, but leave the table alone
tempSQL = tempSQL + "ORDER BY extraSortColumnWAD " + If(sortAscending, “ASC”, “DESC”)
Else
tempSQL = tempSQL + “ORDER BY myColumn” + str(sortColumn) + " " + If(sortAscending, “ASC”, “DESC”)
End If
rs = memoryDB.SQLSelect(tempSQL)
//If commonWAD.isDatabaseRecordSetErrorWAD(memoryDB, rs, False) Then Return
Me.DeleteAllRows
while not rs.EOF
Me.AddRow “”
Dim listColNum As Integer
For tempInt As Integer = 0 to rs.FieldCount -1 'don't place extraSortColumnWAD into the original WebListBox! et les colonnes des tags non plus!
//Ajout du RowTag
If rs.IdxField(tempInt + 1).Name = "myRowTag" Then
Me.RowTag(Me.LastIndex) = rs.IdxField(tempInt + 1).StringValue
End If
For listColNum = 0 To Me.ColumnCount -1
//Ajout du CellTag
Dim sName As String = "myCellTag" + Str(listColNum)
If rs.IdxField(tempInt + 1).Name = sName Then
Me.CellTag(Me.LastIndex, listColNum) = rs.IdxField(tempInt + 1).StringValue
End If
//Ajout des Donnes
sName = "myColumn" + Str(listColNum)
If rs.IdxField(tempInt + 1).Name = sName Then
Me.Cell(Me.LastIndex, listColNum) = rs.IdxField(tempInt + 1).StringValue
End If
Next listColNum
Next
rs.MoveNext
Wend
If Me.RowCount > 0 Then Me.ListIndex = 0 'highlight the first row[/code]
The SortListbox function is triggered in the HeaderPressed event handler of the WebListbox.