B4J Question [B4XTable] How To Sort Multiple Columns

[cklester]

I want to sort a B4XTable on multiple columns.

For example, say I have "State" names in one column, and "City" names in the other. When I click on the "State" names column, I'd like it to sort the states, then sort the cities within those states.

It seems like I should be able to manipulate the internal SQLite data with something like:

    SELECT * FROM data SORT BY States ASC, Cities ASC

What code goes inside the tbl_MyCitiesTable_HeaderClicked (ColumnId As String) event sub?

And how do I get the sorted data back into the table?

 

[cklester]

This didn't work:

Private Sub tbl_TableOfAssets_HeaderClicked (ColumnId As String)
    Dim col As B4XTableColumn = tbl_TableOfAssets.GetColumn(ColumnId)
    'if not clicked c0...
    Dim sql As String = $"SELECT * FROM data ORDER BY ${col.SQLID}, c0"$
    tbl_TableOfAssets.sql1.ExecNonQuery(sql)
End Sub

...but maybe I'm close?!

 

[Erel]

Your code doesn't do anything. The query results will not have any effect on the table.

Make all columns unsortable and:

Sub B4XTable1_HeaderClicked (ColumnId As String)
    Dim col As B4XTableColumn = B4XTable1.GetColumn(ColumnId)
    B4XTable1.CreateDataView($"1=1 ORDER BY ${col.SQLID}, c0"$)
End Sub

 

[cklester]

Make all columns unsortable and:

Sub B4XTable1_HeaderClicked (ColumnId As String)
    Dim col As B4XTableColumn = B4XTable1.GetColumn(ColumnId)
    B4XTable1.CreateDataView($"1=1 ORDER BY ${col.SQLID}, c0"$)
End Sub

How can I reverse it, like the standard behavior? Click once, it's ASC. Click again, it's DESC.

And is it possible to put the sort direction arrow back up as well? If a column is unsortable, does that mean the sort direction character will not appear?

I've tried the below, but I'm not sure how to populate string Direction nor to get the arrow showing properly.

Sub tbl_TableOfAssets_HeaderClicked (ColumnId As String)
    Dim col As B4XTableColumn = tbl_TableOfAssets.GetColumn(ColumnId)
    Dim Direction As String
    
    tbl_TableOfAssets.CreateDataView($"1=1 ORDER BY ${col.SQLID} ${Direction}, c0 ASC"$)

    Sleep(50)   
    If tbl_TableOfAssets.lblSort.Text = Chr(0xF0DE) Then
        tbl_TableOfAssets.lblSort.Text = Chr(0xF0DD)
    Else
        tbl_TableOfAssets.lblSort.Text = Chr(0xF0DE)
    End If
End Sub

 

[Erel]

You will need to do some work for this. It might be easier to modify B4XTable sort and implement it in the class directly.

And is it possible to put the sort direction arrow back up as well? If a column is unsortable, does that mean the sort direction character will not appear?

True.