Android Question How to Group results by a field in a listview?

Daniel44

Active Member
Licensed User
Hello, everybody!
I am developing an application to display medical appointments and the data is brought in json from my database, like this:

JSON:
[{"id_turno":"1","cal_id":"4","paciente_id":"31","dni":"112939","name":"WALKER","fecha":"2020-07-24","timeslot":"06:00-06:30","medico":"DOCTOR1","medico_id":"11","estado":"SIN CONFIRMAR"},{"id_turno":"2","cal_id":"5","paciente_id":"32","dni":"939","name":"CONERY","fecha":"2020-07-23","timeslot":"08:00-08:30","medico":"DOCTOR2","medico_id":"12","estado":"SIN CONFIRMAR"},{"id_turno":"3","cal_id":"5","paciente_id":"33","dni":"87333","name":"LOYD","fecha":"2020-08-13","timeslot":"11:00-11:30","medico":"DOCTOR2","medico_id":"12","estado":"SIN CONFIRMAR"},{"id_turno":"4","cal_id":"4","paciente_id":"34","dni":"3221","name":"SIMPSON","fecha":"2020-08-14","timeslot":"11:00-11:30","medico":"DOCTOR1","medico_id":"11","estado":"SIN CONFIRMAR"},{"id_turno":"5","cal_id":"6","paciente_id":"31","dni":"112939","name":"WALKER","fecha":"2020-09-04","timeslot":"18:30-19:15","medico":"DOCTOR3","medico_id":"13","estado":"SIN CONFIRMAR"},{"id_turno":"6","cal_id":"4","paciente_id":"32","dni":"939","name":"CONERY","fecha":"2020-09-02","timeslot":"10:00-10:30","medico":"DOCTOR1","medico_id":"11","estado":"SIN CONFIRMAR"},{"id_turno":"7","cal_id":"5","paciente_id":"33","dni":"87333","name":"LOYD","fecha":"2020-10-09","timeslot":"11:00-11:30","medico":"DOCTOR2","medico_id":"12","estado":"SIN CONFIRMAR"}]

I send all this data to a listview but it shows me for example doctor1 and his patient then doctor 2 and his patient then the same doctor again.

Here's my B4A script

B4X:
Sub ListarTurnos
    lsvTurnos.Clear
    lsvTurnos.SingleLineLayout.ItemHeight = 25dip
    lsvTurnos.SingleLineLayout.Label.TextSize = 16
    lsvTurnos.SingleLineLayout.Label.TextColor = Colors.White
    lsvTurnos.SingleLineLayout.Label.Gravity = Gravity.LEFT
    lsvTurnos.SingleLineLayout.Label.Gravity = Gravity.BOTTOM
    lsvTurnos.TwoLinesLayout.ItemHeight = 60dip
    lsvTurnos.TwoLinesLayout.Label.TextSize = 11
    lsvTurnos.TwoLinesLayout.Label.TextColor = Colors.ARGB(150, 255, 255, 255)
    lsvTurnos.TwoLinesLayout.Label.Gravity=Gravity.TOP
    lsvTurnos.TwoLinesLayout.SecondLabel.TextSize = 11
    lsvTurnos.TwoLinesLayout.SecondLabel.TextColor = Colors.ARGB(150, 255, 255, 255)
    lsvTurnos.TwoLinesLayout.SecondLabel.Gravity=Gravity.TOP
    
    Dim IDTURNO As Int
    Dim TurnosPacientes As HttpJob
    TurnosPacientes.Initialize("turnosinfo", Me)
    TurnosPacientes.Download(sSrcAPI)
    
    Wait For (TurnosPacientes) JobDone(TurnosPacientes As HttpJob)
    ProgressDialogHide
    
    If TurnosPacientes.Success Then
        Dim parser As JSONParser
        parser.Initialize(TurnosPacientes.GetString)
        Dim root As List = parser.NextArray
        
        For Each coolroot As Map In root
            Dim IDNUMBER As String  = coolroot.Get("dni")
            Dim LASTNAME As String  = coolroot.Get("name")
            Dim DATE As String = coolroot.Get("fecha")
            Dim TIMESLOT As String = coolroot.Get("timeslot")
            Dim DOCTOR As String = coolroot.Get("medico")
            Dim STATUS As String = coolroot.Get("estado")
            
            Dim info As String = "PACIENTE: " &  LASTNAME & _
                                        " | DNI : " & IDNUMBER & _
                                        " | FECHA: " & DATE & _
                                        " | HORA : " & TIMESLOT & _
                                        " | MEDICO: " & DOCTOR & _
                                        " | ESTADO: " & STATUS
            lsvTurnos.AddSingleLine(DOCTOR)
            lsvTurnos.AddTwoLines(info, "")
        Next
    End If
    TurnosPacientes.Release
End Sub

I would like to group them by doctor. I mean, for example doctor 1 and all his patients then doctor 2 and also all his patients etc. should I modify the json ? or should I modify the B4A script ? and in either case how could I do it? Thanks in advance
 

toby

Well-Known Member
Licensed User
Longtime User
Try following sql query:
B4X:
Select * from appointments order by dni
assuming the appointments are stored in appointments table and dni is the doctor ID
 
Upvote 0

Daniel44

Active Member
Licensed User
Try following sql query:
B4X:
Select * from appointments order by dni
assuming the appointments are stored in appointments table and dni is the doctor ID

Hey toby thank you for answering, well .. that it doesn't work, maybe I misunderstood with that solution (ORDER BY) the ids (DNI) keep repeating and that's what I don't want.
anyway thank you Toby
NOTE:
dni is patient's id. I'd like to group by DOCTOR (doctor's lastname) without repeating in the listview
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
I would like to group them by doctor. I mean, for example doctor 1 and all his patients then doctor 2 and also all his patients etc
Do you store the data in a SQLite database. If so, show us the SELECT statement you use.
If you are not using a SQLIte database and just want to sort the data after it is downloaded, you create 2 lists, then sort them , see below code:
B4X:
Dim parser As JSONParser
        parser.Initialize(str)
        Dim root As List = parser.NextArray
        Dim MyList,MyListDr  As List
        MyList.Initialize
        MyListDr.Initialize
      
        For Each coolroot As Map In root
            Dim IDNUMBER As String  = coolroot.Get("dni")
            Dim LASTNAME As String  = coolroot.Get("name")
            Dim DATE As String = coolroot.Get("fecha")
            Dim TIMESLOT As String = coolroot.Get("timeslot")
            Dim DOCTOR As String = coolroot.Get("medico")
            MyListDr.Add(DOCTOR)
            Dim STATUS As String = coolroot.Get("estado")         
            Dim info As String = "MEDICO: " & DOCTOR & _
            " ! PACIENTE: " &  LASTNAME & _
                                        " | DNI : " & IDNUMBER & _
                                        " | FECHA: " & DATE & _
                                        " | HORA : " & TIMESLOT &  _
                                        " | ESTADO: " & STATUS
            MyList.Add(info)
        Next      
        MyListDr.Sort(True)
        MyList.Sort(True)
        For i=0 To MyList.Size-1          
            lsvTurnos.AddSingleLine(MyListDr.Get(i))
            lsvTurnos.AddTwoLines(MyList.Get(i), "")
        Next
Note I changed the order for the DOCTOR to be able to sort with.
You are better off if you use a SQLite database and use xCustomListView. Listview is becoming obsolete.
 
Upvote 0

toby

Well-Known Member
Licensed User
Longtime User
B4X:
Select * from appointments order by name

B4X:
dim PrevLastName as string=""
     For Each coolroot As Map In root
            Dim IDNUMBER As String  = coolroot.Get("dni")
            Dim LASTNAME As String  = coolroot.Get("name")

            if LASTNAME=PrevLastName then
                LASTNAME=""
            END IF   
            Dim DATE As String = coolroot.Get("fecha")
            Dim TIMESLOT As String = coolroot.Get("timeslot")
            Dim DOCTOR As String = coolroot.Get("medico")
            Dim STATUS As String = coolroot.Get("estado")
            
            Dim info As String = "PACIENTE: " &  LASTNAME & _
                                        " | DNI : " & IDNUMBER & _
                                        " | FECHA: " & DATE & _
                                        " | HORA : " & TIMESLOT & _
                                        " | MEDICO: " & DOCTOR & _
                                        " | ESTADO: " & STATUS
            lsvTurnos.AddSingleLine(DOCTOR)
            lsvTurnos.AddTwoLines(info, "")
            
            PrevLastName = coolroot.Get("name")
        Next
 
Upvote 0

Daniel44

Active Member
Licensed User
Do you store the data in a SQLite database. If so, show us the SELECT statement you use.
If you are not using a SQLIte database and just want to sort the data after it is downloaded, you create 2 lists, then sort them , see below code:
B4X:
Dim parser As JSONParser
        parser.Initialize(str)
        Dim root As List = parser.NextArray
        Dim MyList,MyListDr  As List
        MyList.Initialize
        MyListDr.Initialize
    
        For Each coolroot As Map In root
            Dim IDNUMBER As String  = coolroot.Get("dni")
            Dim LASTNAME As String  = coolroot.Get("name")
            Dim DATE As String = coolroot.Get("fecha")
            Dim TIMESLOT As String = coolroot.Get("timeslot")
            Dim DOCTOR As String = coolroot.Get("medico")
            MyListDr.Add(DOCTOR)
            Dim STATUS As String = coolroot.Get("estado")       
            Dim info As String = "MEDICO: " & DOCTOR & _
            " ! PACIENTE: " &  LASTNAME & _
                                        " | DNI : " & IDNUMBER & _
                                        " | FECHA: " & DATE & _
                                        " | HORA : " & TIMESLOT &  _
                                        " | ESTADO: " & STATUS
            MyList.Add(info)
        Next    
        MyListDr.Sort(True)
        MyList.Sort(True)
        For i=0 To MyList.Size-1        
            lsvTurnos.AddSingleLine(MyListDr.Get(i))
            lsvTurnos.AddTwoLines(MyList.Get(i), "")
        Next
Note I changed the order for the DOCTOR to be able to sort with.
You are better off if you use a SQLite database and use xCustomListView. Listview is becoming obsolete.

Hey Mahares thank you for answering well I did as you indicated above. Here is:

B4X:
Wait For (TurnosPacientes) JobDone(TurnosPacientes As HttpJob)
    ProgressDialogHide
   
    If TurnosPacientes.Success Then
        Dim parser As JSONParser
        parser.Initialize(TurnosPacientes.GetString)
        Dim root As List = parser.NextArray
        Dim MyList,MyListDr  As List
        MyList.Initialize
        MyListDr.Initialize
       
        For Each coolroot As Map In root
            Dim IDNUMBER As String  = coolroot.Get("dni")
            Dim LASTNAME As String  = coolroot.Get("name")
            Dim DATE As String = coolroot.Get("fecha")
            Dim TIMESLOT As String = coolroot.Get("timeslot")
            Dim DOCTOR As String = coolroot.Get("medico")
            Dim STATUS As String = coolroot.Get("estado")
           
            Dim info As String = "MEDICO: " & DOCTOR & _
            " ! PACIENTE: " &  LASTNAME & _
                                        " | DNI : " & IDNUMBER & _
                                        " | FECHA: " & DATE & _
                                        " | HORA : " & TIMESLOT & _
                                        " | MEDICO: " & DOCTOR & _
                                        " | ESTADO: " & STATUS
            MyList.Add(info)
           
        Next
        MyListDr.Sort(True)
        MyList.Sort(True)
        For i=0 To MyList.Size-1
            lsvTurnos.AddSingleLine(MyListDr.Get(i))
            lsvTurnos.AddTwoLines(MyList.Get(i), "")
        Next
    End If
    TurnosPacientes.Release

I'm working with Mysql not Sqlite by a php file and here by a OkHttpUtils2. This code crashes:

B4X:
** Activity (main) Resume **
** Activity (main) Pause, UserClosed = false **
** Activity (turnos) Create, isFirst = true **
** Activity (turnos) Resume **
*** Service (httputils2service) Create ***
** Service (httputils2service) Start **
Error occurred on line: 86 (TURNOS)
java.lang.IndexOutOfBoundsException: Invalid index 0, size is 0
    at java.util.ArrayList.throwIndexOutOfBoundsException(ArrayList.java:255)
    at java.util.ArrayList.get(ArrayList.java:308)
    at anywheresoftware.b4a.objects.collections.List.Get(List.java:117)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:732)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:348)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:255)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:144)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:193)
    at anywheresoftware.b4a.shell.DebugResumableSub$RemoteResumableSub.resume(DebugResumableSub.java:22)
    at anywheresoftware.b4a.BA.checkAndRunWaitForEvent(BA.java:267)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:137)
    at anywheresoftware.b4a.BA$2.run(BA.java:387)
    at android.os.Handler.handleCallback(Handler.java:739)
    at android.os.Handler.dispatchMessage(Handler.java:95)
    at android.os.Looper.loop(Looper.java:148)
    at android.app.ActivityThread.main(ActivityThread.java:7406)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1230)

Line 86 ->lsvTurnos.AddSingleLine(MyListDr.Get(i))
 
Last edited:
Upvote 0

Mahares

Expert
Licensed User
Longtime User
I did as you indicated above.
Of course I could not use your download, did not have the download link, but I used the Json string and created a project for you. Unzip it and run it to see how the listview get sorted and displayed on the screen.
 

Attachments

  • Daniel.zip
    8.9 KB · Views: 205
Upvote 0

Daniel44

Active Member
Licensed User
Of course I could not use your download, did not have the download link, but I used the Json string and created a project for you. Unzip it and run it to see how the listview get sorted and displayed on the screen.

Hey Mahares you're so nice. Thank you so much. I run that project and shows me the same as toby indicated. The "title" (Doctor1, Doctor2 ) is still repeating.
I would like something like this in the listview:

Doctor1
CONERY","fecha":"2020-09-02","timeslot":"10:00-10:30"
SIMPSON","fecha":"2020-08-14","timeslot":"11:00-11:30"
WALKER","fecha":"2020-07-24","timeslot":"06:00-06:30

Doctor2
CONERY","fecha":"2020-07-23","timeslot":"08:00-08:30"
LOYD","fecha":"2020-10-09","timeslot":"11:00-11:30"
LOYD","fecha":"2020-08-13","timeslot":"11:00-11:30

Doctor3
WALKER","fecha":"2020-09-04","timeslot":"18:30-19:15"

My sql sentence is just "SELECT * from bookings" for that reason I asked if I should modify that sentence or the script in B4A , maybe both I don't know.
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
I would like something like this in the listview:
Your application is better suited for xCustomListView not listview because the listview can only show a maximum of 2 lines not 3 like you have. You also need to store the data in a SQLite on the device so you can group by doctor. When the data is downloaded as a json from your server, there is no order to it. If you really want to make it work, use xCustomListView. It will look much nicer than the way you are showing it now.
The other option is : jrdc2 server which I am not too familiar with.
 
Last edited:
Upvote 0

Mahares

Expert
Licensed User
Longtime User
I got it Mahares Thank you a lot
Here is the other option link where you connect to your MySQL, but still need an xCLV approach:
 
Upvote 0

Daniel44

Active Member
Licensed User
Here is the other option link where you connect to your MySQL, but still need an xCLV approach:
I could change the listview to the xcustomlistview but I'll need to know how to group this results anyway.
Jrdc .. I've never could understand that class. So difficult to use for me OkhttpUtils does it easier I think.
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
I could change the listview to the xcustomlistview but I'll need to know how to group this results anyway.
In that case, if you store the data you download into a SQLite database, it will not be difficult to run a query to display the data grouped by DOCTOR or any other field. You can start by creating your SQLite database and populate it, I am sure someone in the forum will be able to help you with the proper grouping query when you get stuck. As for the xCLV, there is a lot of information and code on the forum to help you.
something like this to create table:
B4X:
Dim strQuery As String ="CREATE TABLE IF NOT EXISTS mytable (MEDICO TEXT, DNI INTEGER, FECHA TEXT, HORA TEXT, ESTADO TEXT)"
SQL1.ExecNonQuery(strQuery)
Then INSERT the records inside the: For Each coolroot As Map In root loop
 
Upvote 0
Top