B4J Question <SOLVED>How to do a For/Next loop on a B4XTable

[Mikelgiles]

I have searched but have not found out how to loop through a B4XTable. I can do it using SQL commands but that seems kinda backwards. I am working with less than 100 records and need to do some calculations from a column and put the result in another column for each record and I really don't want to do it in SQL if I can do it directly in the B4XTable. I am assuming that any changes made in the table changes the in-memory sqlite DB doesn't it? I would think this function would be common so I think I must be missing something in the forum.

 

[emexes]

For small quantities of data, why not just keep the original list-of-rows source data, and work with that? Only need do .SetData if (visible?) data changes.

 

[Erel]

See the code snippet from v1.15 update: https://www.b4x.com/android/forum/t...ortable-searchable-customizable-table.102322/

It shows how to use the BuildQuery to get the currently displayed data. Set the parameter to True if you are only interested in the visible data.

 

[Mikelgiles]

For small quantities of data, why not just keep the original list-of-rows source data, and work with that? Only need do .SetData if (visible?) data changes.

The original list of rows may or may not be in the proper sequence to do the calculations. In order for the calculations to be correct they have to be calculated in the same order as the dates in the first column and those dates are likely to be modified by the user at any time. I do a sort at the beginning of sub where the calculations are done.

 

[Mikelgiles]

I ended up using the code below and it is faster than I thought it would be.

Sub CalcBal() As String
    Dim column As B4XTableColumn = B4XTableM.GetColumn("paydate")
    column.InternalSortMode= "ASC"
    Dim rs As ResultSet = B4XTableM.sql1.ExecQuery("SELECT * FROM data ORDER BY [c0]")
    Dim Bal As Double = rs.GetDouble("c10"),iBal As Int 
    Dim newAmt As Double
    Do While rs.NextRow
        If rs.GetString("c3").EndsWith ("CC") =False Then
            newAmt=rs.GetDouble ("c9")
            If newAmt=0 Then newAmt=rs.GetDouble("c7")
            Bal=Bal+newAmt
        End If   
        Log(rs.GetString("c5") & "-" & newAmt & "=" & Bal)
        iBal  =Bal
        B4XTableM.sql1.ExecNonQuery2("UPDATE data SET [c10] = ? WHERE [c5] = ?", Array As String(iBal, rs.GetString("c5")))
    Loop
    rs.Close
    B4XTableM.Refresh
End Sub

 

[Mikelgiles]

I had already made it work using SQL so I just left it as it was but I think I will modify my code to use
'GetColumn("State").SQLID)' instead of the sqlite column numbers to make the code more readable.

 

[emexes]

to make the code more readable.

Good stuff - sometimes I feel very alone in this regard ;-)

 

[Mikelgiles]

Good stuff - sometimes I feel very alone in this regard ;-)

I have always been terrible at it but I am attempting to improve!