Android Question Sorting a B4XTable on a column number

DALB

Active Member
Licensed User
Hello,

I'm trying to sort a B4XTable by a column with numbers.
I have define this column which name is "amount' like this:
column type:
B4XTable1.COLUMN_TYPE_NUMBERS

I try to sort the table on this column descending like this:
code for sorting:
Dim coln As B4XTableColumn = B4XTable1.GetColumn("amount")
coln.InternalSortMode= "DESC"
B4XTable1.Refresh

But the ordering result is like if the numbers were strings !!!

How to solve this ?

Thanks to the helpers who come here.
 

DALB

Active Member
Licensed User
The datas come from a sqlite table and this column 'amount" is also defined as NUMERIC.
 
Upvote 0

DALB

Active Member
Licensed User
with
loadind creating b4Xtable:
Dim sy As String="SELECT * FROM tachats ORDER BY montant DESC"
Sleep(0)
chargerLesValeursDansB4Xtable(sy)

where "chargerLesValeursDansB4Xtable(sy)" means creating and loading the table.

Works perfectly...
 
Upvote 0

DALB

Active Member
Licensed User
B4XTable1.CreateDataView( ? ) ?
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
B4XTable1.CreateDataView( ? ) ?
It is not clear from your post whether you are asking a question about CreateDataView or if you are making statement. So, I assume you are asking how to use CreateDtaView in your case. Here is an example:
B4X:
Dim coln As B4XTableColumn = B4XTable1.GetColumn("amount")
    B4XTable1.CreateDataView($"${coln.SQLID} > 50 "$   'Displays the table where amount column's data is higher than 50)
 
Upvote 0

DALB

Active Member
Licensed User
Hello Mahares, yes, I wish to know how to sort a column with ClearDataView, if it's possible, otherwise with an other command ?
 
Upvote 0

DALB

Active Member
Licensed User
are the words ASCENDING or DESCENDING possible in a syntax ?
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
are the words ASCENDING or DESCENDING possible in a syntax ?
Use ASC or DESC . The below will sort the table for you where the data of 1st col is bigger than 0 in descending order
B4X:
B4XTable1.CreateDataView($"c0 > 0 ORDER BY c0 DESC"$)   'c0 is 1st col, c1 is 2nd, etc
 
Last edited:
Upvote 0

DALB

Active Member
Licensed User
Thanks you, I was looking for the exact syntax. But, making it, the column is sorted as if it is filled with strings not with numbers.
For example, The result is:
90.0
9.99
65.63
190
11
105

Normally, because they are numbers I should find:
190
105
90.0
65.63
11
9.99

How to do this ?
 
Upvote 0

DALB

Active Member
Licensed User
Do have I to place my column (c6) at the first place ?( c6 replaces c0)
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
Do have I to place my column (c6) at the first place ?( c6 replaces c0)
If you assigned the column as numbers when you populated the table it should work. Like this:
B4XTable1.AddColumn("amount", B4XTable1.COLUMN_TYPE_NUMBERS) . You said you did in your first post. If that is the case, something is wrong somewhere else. Again, you are reluctant to post your project and/or code, so it is hard to help. c6 does not have to be the first.
Here is a complete working example that works:
B4X:
Private Sub B4XPage_Created (Root1 As B4XView)
    Root = Root1
    
    Root.LoadLayout("2")  'layout has B4XTable1
    Sleep(0)
    B4XTable1.AddColumn("Col 1", B4XTable1.COLUMN_TYPE_NUMBERS)
    B4XTable1.AddColumn("Col 2", B4XTable1.COLUMN_TYPE_NUMBERS)
    Dim data As List
    data.Initialize
    data.Add(Array(6.67, 123.62))
    data.Add(Array(190, 63.62))
    data.Add(Array(190, 1.78))
    data.Add(Array(90.1, 6.78))
    data.Add(Array(9.0, 16.78))
    data.Add(Array(506, 9.99))
    
    B4XTable1.SetData(data)
    B4XTable1.CreateDataView($"c1 > 0 ORDER BY c1 DESC"$)
    
'    B4XTable1.CreateDataView($"c0 > 0 ORDER BY c0 DESC"$)
    
''    'Another way:
'    Dim coln As B4XTableColumn = B4XTable1.GetColumn("Col 1 ")
'    B4XTable1.CreateDataView($"${coln.SQLID} > 0  ORDER BY ${coln.SQLID} DESC"$)
End Sub
 
Last edited:
Upvote 0

DALB

Active Member
Licensed User
Here are my codes:

defining the columns b4xtable:
Sub creerLesColonnesB4Xtable
'defining the columns
    Sleep(0)
    Dim B4XCol(NumbCol) As B4XTableColumn
    Dim ColName(NumbCol) As String =Array As String("amj","id","date", _
    "fournisseur","ville","affectation","montant","numfacture", _
    "modepaiement","numcheque","comptepayeur","titulaire","sousaffectation", _
    "note","codebarres","an","mois","jour")

    Dim ColWidth(NumbCol) As Int =Array As Int(60dip, 50dip,70dip,100dip,80dip, _
    100dip,70dip,90dip,60dip,60dip,80dip,100dip,100dip,100dip,80dip, _
    40dip,30dip,30dip)
   
    For i= 0 To NumbCol -1
        If (i<>0 Or i<>6 Or i<>15 Or i<>16 Or i<>17) Then
            B4XCol(i) = B4XTable1.AddColumn(ColName(i), B4XTable1.COLUMN_TYPE_TEXT)
        '        amj   montant  an     mois    jour
        else If (i=0 Or i=6 Or i=15 Or i=16 Or i=17) Then
            B4XCol(i) = B4XTable1.AddColumn(ColName(i), B4XTable1.COLUMN_TYPE_NUMBERS)
        End If
    Next
    For i= 0 To NumbCol -1
        B4XCol(i).Width=ColWidth(i)
    Next

End Sub

and

loading the rows:
Sub chargerLesValeursDansB4Xtable(synt0 As String)
'Loading the rows from SQLite
    Dim data As List
    data.Initialize
    Try
        Dim rs As ResultSet = Starter.sql1.ExecQuery(synt0)
        Log("synt0 = " & synt0)
        Log("rs.RowCount = " & rs.RowCount)
        nlig=rs.RowCount
        If nlig=0 Then
            ToastMessageShow("Pas de données dans la sélection choisie!",False)'no datas
            Return
        End If
        Dim t As  String
        Do While rs.NextRow
            Dim n As Int=0
            Dim row(18) As Object
            '-------------------
            t =rs.GetString("amj")
            row(n) = t'rs.Getint("amj")
            n=n+1
            row(n) = rs.GetInt("id"):n=n+1
            row(n) = rs.GetString("date"):n=n+1
            row(n) = rs.GetString("fournisseur"):n=n+1
            row(n) = rs.GetString("ville"):n=n+1
            row(n) = rs.GetString("affectation"):n=n+1
            row(n) = rs.Getdouble("montant"):n=n+1
            row(n) = rs.GetString("numfacture"):n=n+1
            row(n) = rs.GetString("modepaiement"):n=n+1
            row(n) = rs.GetString("numcheque"):n=n+1
            row(n) = rs.GetString("comptepayeur"):n=n+1
            row(n) = rs.GetString("titulaire"):n=n+1
            row(n) = rs.GetString("sousaffectation"):n=n+1
            row(n) = rs.GetString("note"):n=n+1
            row(n) = rs.GetString("codebarres"):n=n+1
            t=rs.Getint("an")
            row(n) = t'rs.Getint("an")
            n=n+1
            row(n) = rs.Getint("mois"):n=n+1
            row(n) = rs.Getint("jour")':n=n+1
            '-------------------
            If row(0) = Null Or row(0) = "" Then row(0) = ""
            If row(1) = Null Or row(1) = "" Then row(1) = ""
            If row(2) = Null Or row(2) = "" Then row(2) = ""
            If row(3) = Null Or row(3) = "" Then row(3) = ""
            If row(4) = Null Or row(4) = "" Then row(4) = ""
            If row(5) = Null Or row(5) = "" Then row(5) = ""
            If row(6) = Null Or row(6) = "" Then row(6) = ""
            If row(7) = Null Or row(7) = "" Then row(7) = ""
            If row(8) = Null Or row(8) = "" Then row(8) = ""
            If row(9) = Null Or row(9) = "" Then row(9) = ""
            If row(10) = Null Or row(10) = "" Then row(10) = ""
            If row(11) = Null Or row(11) = "" Then row(11) = ""
            If row(12) = Null Or row(12) = "" Then row(12) = ""
            If row(13) = Null Or row(13) = "" Then row(13) = ""
            If row(14) = Null Or row(14) = "" Then row(14) = ""
            If row(15) = Null Or row(15) = "" Then row(15) = ""
            If row(16) = Null Or row(16) = "" Then row(16) = ""
            If row(17) = Null Or row(17) = "" Then row(17) = ""
            data.Add(row)
        Loop
        NumberOfColumns =n
        rs.Close
        Sleep(0)
        B4XTable1.SetData(data)  
    Catch
        Log("pas possib', erreur")
    End Try
    rafraichirNombreDeLignes(synt0)'refresh the counter lines
   
End Sub

I'm looking for the wrong place I could have a mistake...
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
Here are my codes:
Change this:
B4X:
If (i<>0 Or i<>6 Or i<>15 Or i<>16 Or i<>17) Then
to this:
B4X:
If (i<>0 AND i<>6 AND i<>15 AND i<>16 AND i<>17) Then
Let us see what happens. Otherwise, it is too much code to go through. An attached project woould be easier to go through if you can reproduce it
 
Upvote 0

DALB

Active Member
Licensed User
Yes, it works fine !

condition test if loop:
For i= 0 To NumbCol -1
        'Sleep(0)
        'wait for (B4XCol(i).ColumnType) Complete (Unused As Boolean)
        If (i<>0 And i<>6 And i<>15 And i<>16 And i<>17) Then
            B4XCol(i) = B4XTable1.AddColumn(ColName(i), B4XTable1.COLUMN_TYPE_TEXT)
        '        amj   montant  an     mois    jour
        'else If (i<>0 And i<>6 And i<>15 And i<>16 And i<>17) Then
        else If (i=0 Or i=6 Or i=15 Or i=16 Or i=17) Then
            B4XCol(i) = B4XTable1.AddColumn(ColName(i), B4XTable1.COLUMN_TYPE_NUMBERS)
        End If
    Next
    For i= 0 To NumbCol -1
        B4XCol(i).Width=ColWidth(i)
    Next

I didn't make care about this test lines, but following your suggestion I saw my mistake.
Big thanks Mahares, next time I'll be carefull.
Often, I am stuck with ridiculous things. It's Sunday, I'll goto make a 'brain.Refresh' !
 
Upvote 0
Top