Android Question Parent/Child example

[aklisiewicz]

I trying to implement a bit more complex App and need some advice on how to approach this type of functionality. This is very common situation where we have a PARENT (Master) record and CHILD (Detail) records attached to it. For example Ivnoice/Order etc. The CRUD operations are based on relationships (JOINS). At this moment I need to start with the simple scenario (no editing). I just want to display data from the database.
As you see on the attached image there is one PARENT table and several CHILD tables. They are all related based on ID (PrimaryKey) from the ParentTable.

Based on this structure I need to implement a functionality presented on the screenshoot (from B4A Designer). When the parent record is displayed in the Header then automatically the ChildRecords will update and display below on the list. When the user selects different buttons the list will update and show records from ChildTables according to selected button.

Right below the header there are PREVIOUS / NEXT buttons, so the user can scroll through the data.
The tables are relatively small so there should not be a performance problem, and the data is stored in local SQLite file.

------------------------------------------------
I would appreciate an help or suggestions on how to approach this functionality.
- should I use Panels or Tabs ?
- how to pass ID from the ParentTable to Child records
- any issues with this type of design, etc ?


Arthur

 

[LucaMs]

If I understand correctly, you should take a cursor opened on the Parent table (for "previous" and "next"), then use its primary key as parameter in the query that fills the listview, using DBUtils.ExecuteListView (...)

(I did not understand buttons "Child1" ... you have more than 3 child tables)

 

[klaus]

- should I use Panels or Tabs ?

I would use Tables and activities.

- how to pass ID from the ParentTable to Child records

With process global variables.

- any issues with this type of design, etc ?

I would do it in another way.
- Show the parent table in a Table view in the child fields show the child content and not the key.
- A click on a cell selects the cell and the row. As soon as a row is selected you can add other functions for the selected row.or cell.
- A long click shows the child, table if there is one, in another activity.

 

[aklisiewicz]

As I stated I have several (about 17) Child tables related to Parent.

Klaus your concept would work perfectly with one child table, but I would like to eliminate amount of clicks as much as possible.
I this it PC not the phone , and I'm not limited with space I would probably put all Child tables on Tabs and make them "hot" - which means that selecting record in a Parent table would immediately refresh currently open Tab.
With this limited space, from the user perspective the most convenient method would be to use LongClick to SelectHighlighted recrd, then show buttons (at the bottom of the screen) to allow to display detail (Child1, Child2,...) tables.
I wonder if I should use buttons or tabs for that ?

 

[devlei]

Looking at your 1st image I would create 3 levels on an activity:

• 1st Level - Parent Table: Diseases (Use a Spinner with Previous & Next buttons on either side - I actually use a Button instead of the Spinner as it can be customised more in terms of looks and functionality)
• 2nd Level - Child Tables: Homeother, Images, Keywords etc (Use a ListView/CustomListView).
• 3rd Level - Items of the Child Table: Different images etc (Use a Listview/CustomListView).

Quick navigation occurs as follows:

• Changing the selected item of the Parent Table will display the Child Tables on Level 2.
• Clicking a Child table on Level 2 displays the items of that table on Level 3
• Clicking an item on Level 3 will open the Item in another activity.

See image attached. In my example, Levels 2 & 3 Listviews are summaries of that item/record. The selected item from Level 2 is displayed as a header for Level 3, because of ListView's limited functionality to show as highlighted. Three Levels are Flock (Parent), Grazings (Child) and Tallies (Items of Grazings).

 

[ggpanta]

I would use ULV which supports sections on the list and temp sql views to select on each master record. If your data is small doing the selects on the view during the row calculations would not be that expensive.

 

[aklisiewicz]

I would use ULV which supports sections on the list and temp sql views to select on each master record. If your data is small doing the selects on the view during the row calculations would not be that expensive.

this LIB looks great, but for now I need to start with something basic. I might update it later though. Thank you for pointing me to this Lib - look really great.
Arthur

 

[aklisiewicz]

Looking at your 1st image I would create 3 levels on an activity:

• 1st Level - Parent Table: Diseases (Use a Spinner with Previous & Next buttons on either side - I actually use a Button instead of the Spinner as it can be customised more in terms of looks and functionality)
• 2nd Level - Child Tables: Homeother, Images, Keywords etc (Use a ListView/CustomListView).
• 3rd Level - Items of the Child Table: Different images etc (Use a Listview/CustomListView).

Quick navigation occurs as follows:

• Changing the selected item of the Parent Table will display the Child Tables on Level 2.
• Clicking a Child table on Level 2 displays the items of that table on Level 3
• Clicking an item on Level 3 will open the Item in another activity.

See image attached. In my example, Levels 2 & 3 Listviews are summaries of that item/record. The selected item from Level 2 is displayed as a header for Level 3, because of ListView's limited functionality to show as highlighted. Three Levels are Flock (Parent), Grazings (Child) and Tallies (Items of Grazings).

View attachment 21892

this would probably work really nice if I have only 1 Parent 1 Child table. In my case I need to be able to access 17 Child tables, and then when the Child table is displayed I need another Activity to display details. Your design look good, but I see no room to access more than one child.
I have made it pretty simple and hopefully this would work.
The first screel will allow to select the ParentRecord, the 2nd will display details and allow to access ChildRecords. The only question is how to fit 17 buttons, so I thought perhaps accessing ChildRecords by the menu would work. I'm thinking about the device menu (when you press one below the screen, next to the Home button).

 

[aklisiewicz]

OK, I decided to implement it the way described above. I got it partially working but got stuck in some place...
I have the ListView working fine, then upon selecting the record another screen opens up showing the details and buttons to the Child Tables, i.e. Symptoms, BodySystems, BodyParts etc...
When I select BodySystems everything works fine and I'm able to list Child Records, however every other Child Activity fails. For example when I click on BodySystems the ListView shows blank.

Here is the code... which calls the DiseaseFormView (Activity) from the ListView

Sub ListView1_ItemClick (Position As Int, Value As Object)
  selectedDiseaseID = Value
  CallSubDelayed2(DiseaseFormView, "ShowForm", selectedDiseaseID)
End Sub

then the code (image: des2.png) to open child activity

Sub btnSymptoms_Click
        CallSubDelayed2(DiseaseSymptomsList, "ShowForm", selectedDiseaseID)
End Sub

this works

then little below a code to call another Child Activity

Sub btnBodySys_Click
    CallSubDelayed2(DiseaseBodySystems, "ShowForm", selectedDiseaseID)
End Sub

this calls the DiseaseBodySystems which doesn't work....



the code for the DiseaseSymptomsList activity that works (although it needs some adjustment)

Sub Globals
    'These global variables will be redeclared each time the activity is created.
    'These variables can only be accessed from this module.
    Dim SQL1 As SQL
    Dim QueryString2 As String
    Dim lblName As Label
    Dim lblSubname As Label
    Dim ListView1 As ListView
    Dim Cur As Cursor
    Dim SelectedRecordID As String
End Sub

Sub Activity_Create(FirstTime As Boolean)
    SQL1.Initialize(Main.dbFileDir, Main.dbFileName, True)
    Activity.LoadLayout("dSymptomsList")
End Sub

Sub ShowForm(RecordID As String)
    Activity.Title = "Disease - Symptoms"
    SelectedRecordID = SQL1.ExecQuerySingleResult("SELECT DiseaseID FROM dDISEASES WHERE DiseaseID="&RecordID)
    lblName.Text = SQL1.ExecQuerySingleResult("SELECT ComonName FROM dDISEASES WHERE DiseaseID="&RecordID)
    lblSubname.Text = SQL1.ExecQuerySingleResult("SELECT ComonSubname FROM dDISEASES WHERE DiseaseID="&RecordID)
  
    ListViewInit
    FillListView
End Sub

Sub FillListView
    ListView1.Clear
    Cur = SQL1.ExecQuery(QueryString2)
    For i = 0 To Cur.RowCount - 1
        Cur.Position = i
        ListView1.AddSingleLine(Cur.GetString("Symptom"))
    Next
'    cur.Close
End Sub

then here is the code for the one that does not

Sub Globals
    'These global variables will be redeclared each time the activity is created.
    'These variables can only be accessed from this module.
    Dim SQL1 As SQL
    Dim lblName As Label
    Dim lblSubname As Label
    Dim ListView1 As ListView
    Dim Cur2 As Cursor
    Dim lblID As Label
    Dim QueryString2 As String
    Dim SelectedRecordID As String
End Sub

Sub Activity_Create(FirstTime As Boolean)
    SQL1.Initialize(Main.dbFileDir, Main.dbFileName, True)
    Activity.LoadLayout("dBodySysList")
End Sub

Sub ShowForm(RecordID As String)
    Activity.Title = "Disease - Body Systems Affected"
    lblID.Text = RecordID
    SelectedRecordID = SQL1.ExecQuerySingleResult("SELECT DiseaseID FROM dDISEASES WHERE DiseaseID="&RecordID)
    lblName.Text = SQL1.ExecQuerySingleResult("SELECT ComonName FROM dDISEASES WHERE DiseaseID="&RecordID)
    lblSubname.Text = SQL1.ExecQuerySingleResult("SELECT ComonSubname FROM dDISEASES WHERE DiseaseID="&RecordID)
    ListViewInit
    FillListView
End Sub

Sub FillListView
    ListView1.Clear
    QueryString2 = "SELECT ID, DiseaseId, BodySystem FROM dBODYSYSATK WHERE DiseaseID="&SelectedRecordID
    Cur2 = SQL1.ExecQuery(QueryString2)
    For i = 0 To Cur2.RowCount - 1
        Cur2.Position = i
        ListView1.AddSingleLine(Cur2.GetString("BodySystem"))
    Next
'    cur.Close
End Sub

Similar code for other activities does not work as well, so only the first is working.
I noticed the ID columns in some of the child tables do not start from 1 (simply some records were deleted manually while testing), but this should not affect it.

Any ideas appreciated.
Arthur

 

[devlei]

I have had a quick look at your last two posts and understand your situation a bit better, and offer the following suggestions (take into account that I am a relative newbie myself):

1. Because your 1st level (Diseases) is such a long list the way you have done it in des1.png is exactly right with the search facility.
2. Clicking on an item on the above activity then opens des2.png, which I would do differently:

* Put the name, description on a panel that is vertically scrollable, but only occupies the top half of the screen.
* In the middle place a spinner (or a button - as explained in my previous post) where one of the child tables (symptoms, body parts etc) is the selected one. Solves your problem of not enough space for all the buttons.
* Place another vertical scrollable panel on which you display the details (des3.png or des4.png) depending on which child table is selected.
* For Landscape mode you could have the two panels next to each other instead of as described above.​

3. Alternatively, if you think that 2 above would be two cramped, you could have the top panel with name, description, then a listview on bottom half listing the child tables. Clicking on one of them would then open a 3rd activity displaying details of that child activity.
4. I am not sure why you are using CallSubDelayed, when StartActivity is far simpler. To pass the SelectedDiseaseID simply use a ProcessGlobal in your module for des1.png.

 

[DonManfred]

What EXACTLY do not work? What EXACTLY errors you get? Posting some parts of an app but not the complete doesnt help us to get a complete overview over the situation.

Do some debugging (should be your FIRST step if you run in errors or problems before asking in a forum)!
Insert LOG-commands in any place that could help to find what exactly not work.

->

Sub ShowForm(RecordID As String)
log("RecordID: "&RecordID)
    Activity.Title = "Disease - Body Systems Affected"
    lblID.Text = RecordID
    SelectedRecordID = SQL1.ExecQuerySingleResult("SELECT DiseaseID FROM dDISEASES WHERE DiseaseID="&RecordID)
log("SelectedRecordID : "&SelectedRecordID )
    lblName.Text = SQL1.ExecQuerySingleResult("SELECT ComonName FROM dDISEASES WHERE DiseaseID="&RecordID)
log(" lblName.Text: "& lblName.Text)
    lblSubname.Text = SQL1.ExecQuerySingleResult("SELECT ComonSubname FROM dDISEASES WHERE DiseaseID="&RecordID)
log("lblSubname.Text: "&lblSubname.Text)

and where this sub is called you should add logcommands too

 

[aklisiewicz]

devlei - I took your suggestion and did some changes. Now I have a screen looking like this (see image). This approach seems to work fine, but I had put child records instead on separate activities to gain enough room to display data. Now the only thing in this stage is to make the MEMO field scrollable. At this moment when I display the TEXT fiels the remaining text is hidden. How can I make it scrollable ?

Thanks - Arthur

 

[devlei]

Your Disease Details layout must not have Name, Subname & Description views on it. In their place, only the ScrollView (svInfo) which must only take up slightly less than half the vertical screen. Then you need another layout (diseaseinfo - or whatever you want to call it) that contains the Name Subname & Description which are all on a Panel (pnlInfo).
In your module you first load the Disease Details layout then the DiseaseInfo layout - something like this:

Activity.LoadLayout("diseasedetails")     
svInfo.Panel.LoadLayout("diseaseinfo")
svInfo.Panel.Height = pnlInfo.Height

Then your 'Choose' spinner moves up to the middle, and below it you have des3.png & des4.png. It might to safer to also put them on a Scrollview so that if they get bigger they can still fit in the bottom half.

Search the forum for more examples - I got this way of doing it from the Forum.

 

[DonManfred]

devlei - I took your suggestion and did some changes. Now I have a screen looking like this (see image). This approach seems to work fine, but I had put child records instead on separate activities to gain enough room to display data. Now the only thing in this stage is to make the MEMO field scrollable. At this moment when I display the TEXT fiels the remaining text is hidden. How can I make it scrollable ?

Thanks - Arthur

Do you need to edit the text? If not then you should use a Label with a ScrollView.
See here

 

[aklisiewicz]

some of my problems with data not showing up was due to this http://www.b4x.com/android/forum/threads/do-not-make-my-mistake-sqlite.36729/

I do not need to edit the data so will they you suggestions. I wish things like scrolling the text in label would be taken care of automatically.
Setting this should be the feature of Designer. B4A claims to be 4GL tool

Thanks