Android Question How do you use indexof of a list?

[Sergio Castellari]

Hello people,
I need to "search" a list for a certain piece of information. If it is found, I will overwrite said record from the list with new information. Otherwise, I add a new record to the list.
I have this code:

    ProgressDialogShow("Procesando...ESPERE...")
    Dim aRegistros, aCampos                                            As List
    Dim aCom()                                                                    As String
    Dim prove = lblProv.Text.SubString2(0,5)         As String
    Dim depo = lblDeposito.Text.SubString2(0,3) As String
    aRegistros.Initialize
    aCampos.Initialize
    'Consulto el STOCK existente por depositos
    Wait For (Main.jConex.GetRecord("select_stock", Null)) Complete (Respuesta As Map)
    If Respuesta.Get("Correcto") Then
        Dim rs     As DBResult
        Dim cStk,cArtDep,cCodArt,cNomArt,cCodDep,cCodPro,cUmd As String
        Dim nExiste As Double
        Dim nID As Int
        rs = Respuesta.Get("Datos")
    For Each row() As Object In rs.Rows
            cStk = row(13)                                                                                                                                'STOCKASOC
            cArtDep = Main.Func.AgregoCeros(row(0),6) & Main.Func.AgregoCeros(row(5),3)        'CODART+DEPOSITO
            cCodArt = Main.Func.AgregoCeros(row(0),6)                                                                            'CODART
            cNomArt = row(1)                                                                                                                            'NOMBRE
            cCodDep = row(6)                                                                                                                            'NOMDEP
            cCodPro = Main.Func.AgregoCeros(row(11),4) & "-" & row(12)                                        'CODPROV+NOMPROV
            cUmd = row(2)                                                                                                                                    'UMEDSTOCK
            nExiste = row(4)                                                                                                                            'EXISTENCIA
'     nID = -1
            nID = aRegistros.IndexOf(cArtDep)
            If nID = -1 Then    'Si NO encontro ningun CODART+DEPOSITO, agrego el registro
                aCampos.Initialize
                aCampos.Add(cArtDep)
                aCampos.Add(cCodArt)
                aCampos.Add(cNomArt)
                aCampos.Add(cUmd)
                aCampos.Add(nExiste)
                aCampos.Add(0)
                aCampos.Add(nExiste)
                aCampos.Add(cCodDep)
                aCampos.Add(cCodPro)
                aCampos.Add(cStk)
                aRegistros.Add(aCampos)
                Log("Cargo Articulo+Deposito: " & cArtDep)
            Else                            'Si lo encontró, actualizo existencia
                Dim aReg As List
                aReg = aRegistros.Get(nID)                'Obtengo el Registro a modificar
                aReg.Set(4,aReg.Get(4) + nExiste) 'Agrego existencia a Stk.DEP
                aReg.Set(6,aReg.Get(6) + nExiste) 'Agrego existencia a Stk.REAL
                aRegistros.Set(nID,aReg)                    'Actualizo el registro original con los nuevos valores
                Log("Actualizo Articulo+Deposito: " & cArtDep)
            End If
        Next
    Else
        ProgressDialogHide
         MsgboxAsync(Respuesta.Get("Mensaje"),"")
        Return
    End If

From the result of the query, obviously there are repeated records that need to be updated. Well, NEVER find a repeated record !!!! .. Obviously I have something wrongly encoded or misinterpreted.
What could be the reason that it does not work?

Greetings

 

[drgottjr]

the good news: list.indexof() works just fine.
the bad news: based on your current code, there is no way to know if cArtDep is in aRegistros
(since we don't know what is in aRegistros). we also don't know if it's in AgregoCeros either
(since we don't know what is in AgregoCeros).

in any case this line strikes me as suspicious:

            cArtDep = Main.Func.AgregoCeros(row(0),6) & Main.Func.AgregoCeros(row(5),3)        'CODART+DEPOSITO

you should log it to see what cArtDep actually is.

 

[Sergio Castellari]

the good news: list.indexof() works just fine.
the bad news: based on your current code, there is no way to know if cArtDep is in aRegistros
(since we don't know what is in aRegistros). we also don't know if it's in AgregoCeros either
(since we don't know what is in AgregoCeros).

in any case this line strikes me as suspicious:

            cArtDep = Main.Func.AgregoCeros(row(0),6) & Main.Func.AgregoCeros(row(5),3)        'CODART+DEPOSITO

you should log it to see what cArtDep actually is.

Hello @drgottjr

"AgregoCeros"

'*******************************************************************
' Agrega a un NUMERO Entero "nCant" de CEROS al principio.
'**************************************************** 10-09-2021 ***
'nNum------> Numero [Int] a formatear
'nCant-----> Cantidad de Ceros a Completar
Public Sub AgregoCeros (nNum As Int, nCant As Int)
    Dim cNum As String
    Formatter.Initialize
    Dim DefaultFormat As B4XFormatData = Formatter.GetDefaultFormat
    DefaultFormat.MaximumFractions = 0
    DefaultFormat.MinimumFractions = 0
    DefaultFormat.MinimumIntegers = nCant
    DefaultFormat.GroupingCharacter = ""       'Quita las (,) de los millares
    Formatter.GetDefaultFormat.IntegerPaddingChar = "0"
    cNum = Formatter.Format(nNum)
    Return cNum
End Sub

cArtDep if it is in aRegistros. I can see it in:

Log("Cargo Articulo+Deposito: " & cArtDep)

and then in:

Log("NO Encontro Pedido: " & cArtDep)

I can see that if it is loaded in aRegistros.

 

[drgottjr]

so you're telling me that if you simply open aRegistros and run:

dim pos as int = aRegistros.IndexOf("000001001")
log("pos: " & pos)

you will get -1?

 

[Sergio Castellari]

so you're telling me that if you simply open aRegistros and run:

dim pos as int = aRegistros.IndexOf("000001001")
log("pos: " & pos)

you will get -1?

Exactly. I get -1.
Doing more tests, I created a second list that only contains the key field but I add it as a loose data, not inside another list of fields ... and there if the search "indexof" worked ...
I do not think it is the correct solution, but as I told you before, he did not understand how it works.
This is the code that if it finds the field for which I want to search. You can even observe that the load of the registers changes to an array, but it did not find it either. instead for "aIndex" if it finds it.

Private Sub CargarStock
    ProgressDialogShow("Procesando...ESPERE...")
    Dim aRegistros, aIndex      As List
    Dim prove = lblProv.Text.SubString2(0,5)         As String
    Dim depo = lblDeposito.Text.SubString2(0,3) As String
    aRegistros.Initialize
    aIndex.Initialize
    'Consulto el STOCK existente por depositos
    Wait For (Main.jConex.GetRecord("select_stock", Null)) Complete (Respuesta As Map)
    If Respuesta.Get("Correcto") Then
        Dim rs     As DBResult
        Dim cStk,cArtDep,cCodArt,cNomArt,cCodDep,cCodPro,cUmd As String
        Dim nExiste As Double
        Dim nID As Int
        rs = Respuesta.Get("Datos")
    For Each row() As Object In rs.Rows
            cStk = row(13)   
            cArtDep = Main.Func.AgregoCeros(row(0),6) & Main.Func.AgregoCeros(row(5),3)        'CODART+DEPOSITO
            cCodArt = Main.Func.AgregoCeros(row(0),6)                                                                            'CODART
            cNomArt = row(1)                                                                                                                            'NOMBRE
            cCodDep = row(6)                                                                                                                            'NOMDEP
            cCodPro = Main.Func.AgregoCeros(row(11),4) & "-" & row(12)                                        'CODPROV+NOMPROV
            cUmd = row(2)                                                                                                                                    'UMEDSTOCK
            nExiste = row(4)                                                                                                                            'EXISTENCIA
            nID = aIndex.IndexOf(cArtDep)
            If nID = -1 Then    'Si NO encontro ningun CODART+DEPOSITO, agrego el registro
                aRegistros.Add(Array As String(cArtDep,cCodArt,cNomArt,cUmd,nExiste,0,nExiste,cCodDep,cCodPro,cStk))
                aIndex.Add(cArtDep)   
                Log("Cargo Articulo+Deposito: " & cArtDep & " --->" & nID)
            Else                            'Si lo encontró, actualizo existencia
                Dim aReg2() As String
                aReg2 = aRegistros.Get(nID)                    'Obtengo el Registro a modificar
                aReg2(4) = aReg2(4) - nExiste
                aReg2(6) = aReg2(6) - nExiste
                aRegistros.Set(nID,aReg2)                        'Actualizo el registro original con los nuevos valores
                Log("Actualizo Articulo+Deposito: " & cArtDep & " --->" & nID)
            End If
        Next
    Else
        ProgressDialogHide
         MsgboxAsync(Respuesta.Get("Mensaje"),"")
        Return
    End If
    'Consulto las Salidas por PEDIDOS pendientes (Ventas)
    Log("................................")
    Wait For (Main.jConex.GetRecord("select_pedidos", Null)) Complete (Respuesta As Map)
    If Respuesta.Get("Correcto") Then
        Dim rs     As DBResult
        Dim cStk,cArtDep,cCodArt,cNomArt,cCodDep,cCodPro,cUmd As String
        Dim nExiste As Double
        Dim nID As Int
        rs = Respuesta.Get("Datos")
    For Each row() As Object In rs.Rows
            cStk = row(13)                                                                                                                                'STOCKASOC
            cArtDep = Main.Func.AgregoCeros(row(0),6) & Main.Func.AgregoCeros(row(5),3)        'CODART+DEPOSITO
            cCodArt = Main.Func.AgregoCeros(row(0),6)                                                                            'CODART
            cNomArt = row(1)                                                                                                                            'NOMBRE
            cCodDep = row(6)                                                                                                                            'NOMDEP
            cCodPro = Main.Func.AgregoCeros(row(11),4) & "-" & row(12)                                        'CODPROV+NOMPROV
            cUmd = row(2)                                                                                                                                    'UMEDSTOCK
            nExiste = row(4)                                                                                                                            'EXISTENCIA
            nID = aIndex.IndexOf(cArtDep)
            If nID = -1 Then    'Si NO encontro ningun CODART+DEPOSITO, agrego el registro
                Log("NO Encontro Pedido: " & cArtDep & " --->nID2: " & nID)
                nExiste = nExiste * -1        'Agregado para los casos donde NO EXISTE carga previa del articulo...06-08-2021
                aRegistros.Add(Array As String(cArtDep,cCodArt,cNomArt,cUmd,0,nExiste,nExiste,cCodDep,cCodPro,cStk))
                aIndex.Add(cArtDep)   
            Else                            'Si lo encontró, actualizo existencia
                Dim aReg2() As String
                aReg2 = aRegistros.Get(nID)                    'Obtengo el Registro a modificar
                aReg2(5) = aReg2(5) - nExiste
                aReg2(6) = aReg2(6) - nExiste
                aRegistros.Set(nID,aReg2)                        'Actualizo el registro original con los nuevos valores
                Log("Actualizo PEDIDO: " & cArtDep & " --->nID2: " & nID)
            End If
        Next
        B4XTable1.SetData(aRegistros)                             
        SeteoColumna("Cod", "CENTER", "LEFT",0,-1,-1)
        SeteoColumna("Articulo", "CENTER", "LEFT",0,-1,-1)
        SeteoColumna("xDep", "CENTER", "RIGHT",3,3,-1)
        SeteoColumna("xPed", "CENTER", "RIGHT",3,3,-1)
        SeteoColumna("REAL", "CENTER", "RIGHT",3,3,-1)
        SeteoColumna("Deposito", "CENTER", "CENTER",0,-1,-1)
        SeteoColumna("Provedor", "CENTER", "LEFT",0,-1,-1)
        ProgressDialogHide
    Else
        ProgressDialogHide
         MsgboxAsync(Respuesta.Get("Mensaje"),"")
        Return
    End If
End Sub

Hopefully you can realize that I'm doing wrong, I thought I could search within "aRegistros"

 

[drgottjr]

then you need another list to serve as an index. every database provides indexes. whenever you add an item to your aRegistros list, you add a key to the index. so when you step through your rs results, you simply query the index for, eg, "000001001". if it's there, you move on. plus, if the index mirros aRegistros, you get the actual position within aRegistros where the record containing "000001001" is (should you want to access it). follow? if the item isn't found, you
1) create the record in aRegistros for it
2) create the record in your index with the key that points to aRegistros.
you could also do it with a map, but it's simpler with a list. plus you get the position in aRegistros for free.

 

[aeric]

I would suggest use SQL SELECT to find the record already existed (resultset.NextRow) then use if-else to UPDATE or INSERT the record.
List is not needed since you already use SQL resultset.

 

[udg]

SQL SELECT to find the record already existed (resultset.NextRow) then use if-else to UPDATE or INSERT the record.

Or Try the UPSERT way

UPSERT

I successfully used it on MySql and it's great. It has limitations and you really need to understand what you're doing, but it works really smooth.
Consider it as just another tool that you could (or not) use in your projects.

udg

 

[Sergio Castellari]

then you need another list to serve as an index. every database provides indexes. whenever you add an item to your aRegistros list, you add a key to the index. so when you step through your rs results, you simply query the index for, eg, "000001001". if it's there, you move on. plus, if the index mirros aRegistros, you get the actual position within aRegistros where the record containing "000001001" is (should you want to access it). follow? if the item isn't found, you
1) create the record in aRegistros for it
2) create the record in your index with the key that points to aRegistros.
you could also do it with a map, but it's simpler with a list. plus you get the position in aRegistros for free.

@drgottjr
Exactly, I ended up using a sublist that only contains the search "key" and there it WORKS !!!
Thank you very much for the clues provided, everything works.
It is a somewhat complex process, since 2 different tables are involved, which in turn are made up of several auxiliary tables. These are the SQL queries from the JRDC server:

#Consulta de STOCK x Deposito (para Inventario 1/2)
sql.select_stock=Select s.CODART, a.NOMBRE, a.UMEDSTOCK, a.PTOPEDIDO, SUM(s.EXISTENCIA) as EXISTENCIA, s.DEPOSITO, d.DESCRIP as NOMDEP, \
                 a.LINEA, l.DESCRIP as NOMLINEA, a.RUBRO, r.DESCRIP as NOMRUBRO, a.CODPROV, p.NOMBRE as NOMPROV, a.STOCKASOC, a.CODIFICADO \
                 From STOCK s \
                 Left Join ARTICULO a ON a.CODIGO=s.CODART \
                 Left Join LINEAS l   ON l.CODIGO=a.LINEA \
                 Left Join RUBROS r   ON r.CODIGO=a.RUBRO \
                 Left Join DEPOSITO d ON d.CODIGO=s.DEPOSITO \
                 Left Join PROVEDOR p on p.CODIGO=a.CODPROV \
                 Group by s.CODART,s.DEPOSITO \
                 Order by s.CODART
#Consulta de STOCK x Pedidos (para Inventario 2/2)
sql.select_pedidos=Select cu.CODART, a.NOMBRE, a.UMEDSTOCK, a.PTOPEDIDO, Sum(cu.CANTIDAD) as EXISTENCIA, cu.DEPOSITO, d.DESCRIP as NOMDEP, \
                   a.LINEA, l.DESCRIP as NOMLINEA, a.RUBRO, r.DESCRIP as NOMRUBRO, a.CODPROV, p.NOMBRE as NOMPROV, a.STOCKASOC, a.CODIFICADO \
                   From CABPEDCLI c \
                   Left Join CUEPEDCLI cu ON cu.PEDIDO=c.NROPEDIDO \
                   Left join ARTICULO a   ON a.CODIGO=cu.CODART \
                   Left Join LINEAS l     ON l.CODIGO=a.LINEA \
                   Left Join RUBROS r     ON r.CODIGO=a.RUBRO \
                   Left join DEPOSITO d   ON d.CODIGO=cu.DEPOSITO \
                   Left Join PROVEDOR p   ON p.CODIGO=a.CODPROV \
                   Where c.ESTADO='P' and cu.CONDICION='P' \
                   Group by cu.CODART,cu.DEPOSITO \
                   Order by cu.CODART

But it is not only the union of the tables, but I must carry out subsequent revision processes, record by records, since there are cases where there may be more than one associated record, which in turn can happen the same with the second SQL query. And all of this adds or subtracts columns of data to be displayed.
The important thing is that with the help of the forum, I achieved the goal.
This APP works online with my commercial management system (ERP), therefore the data provided must be exact and equal to the desktop system.
Every day I am more in love with B4X (it hurts my lack of knowledge, but I will improve!)
Here is the complete code of the process (including filters customized by the user):

Private Sub CargarStock
    ProgressDialogShow("Procesando...ESPERE...")
    Dim aRegistros,aIndex,aFinal      As List
    Dim prove = lblProv.Text.SubString2(0,5)         As String
    Dim depo = lblDeposito.Text.SubString2(0,3) As String
    aRegistros.Initialize
    aFinal.Initialize
    aIndex.Initialize
    'Consulto el STOCK existente por depositos
    Wait For (Main.jConex.GetRecord("select_stock", Null)) Complete (Respuesta As Map)
    If Respuesta.Get("Correcto") Then
        Dim rs     As DBResult
        Dim cStk,cArtDep,cCodArt,cNomArt,cCodDep,cCodPro,cUmd,xDepo,xProv As String
        Dim nExiste As Double
        Dim nID As Int
        rs = Respuesta.Get("Datos")
    For Each row() As Object In rs.Rows
            cStk = row(13)   
            cArtDep = Main.Func.AgregoCeros(row(0),6) & Main.Func.AgregoCeros(row(5),3)        'CODART+DEPOSITO
            cCodArt = row(0) & CRLF & row(14)                                                                                            'CODART+CODIFICADO
            cNomArt = row(1)                                                                                                                            'NOMBRE
            cCodDep = Main.Func.AgregoCeros(row(5),3) & "-" & row(6)                                            'DEPOSITO+NOMDEP
            cCodPro = Main.Func.AgregoCeros(row(11),5) & "-" & row(12)                                        'CODPROV+NOMPROV
            cUmd = row(2)                                                                                                                                    'UMEDSTOCK
            nExiste = row(4)                                                                                                                            'EXISTENCIA
            xDepo = Main.Func.AgregoCeros(row(5),3)                                                                                'DEPOSITO (Usado para filtro)
            xProv = Main.Func.AgregoCeros(row(11),5)                                                                            'CODPROV (Usado para filtro)
            nID = aIndex.IndexOf(cArtDep)
            If nID = -1 Then    'Si NO encontro ningun CODART+DEPOSITO, agrego el registro
                aRegistros.Add(Array As String(cArtDep,cCodArt,cNomArt,cUmd,nExiste,0,nExiste,cCodDep,cCodPro,cStk,xDepo,xProv))
                aIndex.Add(cArtDep)   
                Log("Cargo Articulo+Deposito: " & cArtDep & " --->" & nID)
            Else                            'Si lo encontró, actualizo existencia
                Dim aReg2() As String
                aReg2 = aRegistros.Get(nID)                    'Obtengo el Registro a modificar
                aReg2(4) = aReg2(4) - nExiste
                aReg2(6) = aReg2(6) - nExiste
                aRegistros.Set(nID,aReg2)                        'Actualizo el registro original con los nuevos valores
                Log("Actualizo Articulo+Deposito: " & cArtDep & " --->" & nID)
            End If
        Next
    Else
        ProgressDialogHide
         MsgboxAsync(Respuesta.Get("Mensaje"),"")
        Return
    End If
    'Consulto las Salidas por PEDIDOS pendientes (Ventas)
    Log("................................")
    Wait For (Main.jConex.GetRecord("select_pedidos", Null)) Complete (Respuesta As Map)
    If Respuesta.Get("Correcto") Then
        Dim rs     As DBResult
        Dim cStk,cArtDep,cCodArt,cNomArt,cCodDep,cCodPro,cUmd,xDepo,xProv As String
        Dim nExiste As Double
        Dim nID As Int
        rs = Respuesta.Get("Datos")
    For Each row() As Object In rs.Rows
            cStk = row(13)                                                                                                                                'STOCKASOC
            cArtDep = Main.Func.AgregoCeros(row(0),6) & Main.Func.AgregoCeros(row(5),3)        'CODART+DEPOSITO
            cCodArt = row(0) & CRLF & row(14)                                                                                        'CODART+CODIFICADO
            cNomArt = row(1)                                                                                                                            'NOMBRE
            cCodDep = Main.Func.AgregoCeros(row(5),3) & "-" & row(6)                                            'DEPOSITO+NOMDEP
            cCodPro = Main.Func.AgregoCeros(row(11),5) & "-" & row(12)                                        'CODPROV+NOMPROV
            cUmd = row(2)                                                                                                                                    'UMEDSTOCK
            nExiste = row(4)                                                                                                                            'EXISTENCIA
            xDepo = Main.Func.AgregoCeros(row(5),3)                                                                                'DEPOSITO (Usado para filtro)
            xProv = Main.Func.AgregoCeros(row(11),5)                                                                            'CODPROV (Usado para filtro)
            nID = aIndex.IndexOf(cArtDep)
            If nID = -1 Then    'Si NO encontro ningun CODART+DEPOSITO, agrego el registro
                Log("NO Encontro Pedido: " & cArtDep & " --->nID2: " & nID)
                nExiste = nExiste * -1        'Agregado para los casos donde NO EXISTE carga previa del articulo...06-08-2021
                aRegistros.Add(Array As String(cArtDep,cCodArt,cNomArt,cUmd,0,nExiste,nExiste,cCodDep,cCodPro,cStk,xDepo,xProv))
                aIndex.Add(cArtDep)   
            Else                            'Si lo encontró, actualizo existencia
                Dim aReg2() As String
                aReg2 = aRegistros.Get(nID)                    'Obtengo el Registro a modificar
                aReg2(5) = aReg2(5) - nExiste
                aReg2(6) = aReg2(6) - nExiste
                aRegistros.Set(nID,aReg2)                        'Actualizo el registro original con los nuevos valores
                Log("Actualizo PEDIDO: " & cArtDep & " --->nID2: " & nID)
            End If
        Next
        B4XTable1.SetData(aRegistros)                             
        Filtrar
        SeteoColumna("Cod", "CENTER", "LEFT",0,-1,-1)
        SeteoColumna("Articulo", "CENTER", "LEFT",0,-1,-1)
        SeteoColumna("xDep", "CENTER", "RIGHT",3,3,-1)
        SeteoColumna("xPed", "CENTER", "RIGHT",3,3,-1)
        SeteoColumna("REAL", "CENTER", "RIGHT",3,3,-1)
        SeteoColumna("Deposito", "CENTER", "CENTER",0,-1,-1)
        SeteoColumna("Provedor", "CENTER", "LEFT",0,-1,-1)
        B4XTable1.Refresh
        ProgressDialogHide
    Else
        ProgressDialogHide
         MsgboxAsync(Respuesta.Get("Mensaje"),"")
        Return
    End If
End Sub

Private Sub Filtrar
    Dim prove = lblProv.Text.SubString2(0,5)         As String
    Dim depo = lblDeposito.Text.SubString2(0,3) As String
    Dim cFiltrar = "c9 = " & $""S""$                                        As String
    If prove <> "00000" Then
        cFiltrar = cFiltrar & " and c11 = " & $"""$ & prove & $"""$
    End If
    If depo <> "000" Then
        cFiltrar = cFiltrar & " and c10 = " & $"""$ & depo & $"""$
    End If
    Log($"${Col_CodDepo.SQLID} > 0 or ${Col_CodProv.SQLID} > 0"$)   
    Log("Filtrado: " & cFiltrar)
    B4XTable1.CreateDataView(cFiltrar)
End Sub

Total greetings!

 

[Sergio Castellari]

I would suggest use SQL SELECT to find the record already existed (resultset.NextRow) then use if-else to UPDATE or INSERT the record.
List is not needed since you already use SQL resultset.

@aeric
Sir thank you for your suggestion. But it is not necessary in this case. It does not require modifying / editing any database records.
This procedure consists of a consultation of Stock by deposits, including orders pending invoicing and with the ability to filter combinations of Deposits and / or Suppliers.
All this information is in our ERP system and this APP must access and display this data on a mobile device in real time.
It is a somewhat complex query due to the business rules of the system itself.
The important thing is that with your help and suggestions I have achieved the goal.
B4X amazes me every day. JRDC2 is very fast to get inquiries, it has nothing to envy to our desktop application.
Thank you again, it is a pleasure to receive your contributions and suggestions!

Total greetings !!

Excuse my English, it is a translation of Google Translator

 

[Sergio Castellari]

Or Try the UPSERT way
UPSERT
I successfully used it on MySql and it's great. It has limitations and you really need to understand what you're doing, but it works really smooth.
Consider it as just another tool that you could (or not) use in your projects.

@udg
Hello!!! As I wrote @aeric, in this case no editing / modification of any records is required. It is an information query that is necessary to be able to filter something complex in different ways.
Thank you for your contribution!

A big hug to the distance!