Android Question Sorting a B4XTable on a column number

DALB · Mar 19, 2023

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 · Mar 19, 2023

The datas come from a sqlite table and this column 'amount" is also defined as NUMERIC.

DALB · Mar 19, 2023

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...

DALB · Mar 19, 2023

B4XTable1.CreateDataView( ? ) ?

Mahares · Mar 19, 2023

DALB said:
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)

DALB · Mar 19, 2023

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

DALB · Mar 19, 2023

are the words ASCENDING or DESCENDING possible in a syntax ?

Mahares · Mar 19, 2023

DALB said:
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

DALB · Mar 19, 2023

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 ?

DALB · Mar 19, 2023

Do have I to place my column (c6) at the first place ?( c6 replaces c0)

Mahares · Mar 19, 2023

DALB said:
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

DALB · Mar 19, 2023

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...

Mahares · Mar 19, 2023

DALB said:
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

DALB · Mar 19, 2023

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' !

Android Question Sorting a B4XTable on a column number

DALB

Active Member

DALB

Active Member

DALB

Active Member

DALB

Active Member

Mahares

Expert

DALB

Active Member

DALB

Active Member

Mahares

Expert

DALB

Active Member

DALB

Active Member

Mahares

Expert

DALB

Active Member

Mahares

Expert

DALB

Active Member

Similar Threads