B4J Tutorial [ABMaterial] Creating Dynamic ABMSideBarItems from Database Records At Runtime

Mashiane

Expert
Licensed User
Hi there

At times, you might be faced with creating dynamic ABMSideBarItems items from your pages from existing DB records. This was a challenge I was faced with in my current project. So sitting down I came up with a sketch, Figure 1, of what I needed to do, done with Evolus Pencil

Figure 1

ABMSideBarItem.png


Basically...

1. The Page needs to Load
2. A query should be ran to get the database records I need to show in the dynamic ABMSideBar(s), I use a custom component for each sidebar item.
3. Each side bar item based on each record should be created and loaded on the page.
4. Each side bar item should have a record id, a title and a subtitle, and image and
5. Each record read from the db should be saved in session storage for later retrieval. I needed this for related tables as I did not want to query the db again. For large databasets I wouldnt recommend this, but then my records are limited and will be anyway.

For this my page has of course a navigation bar created in the same old fashion, at the end, this is something I needed, Figure 2

Figure 2

DynamicSideBar1.png


Based on the Demo, In my ABMShared, I used a similar approach for the container...

B4X:
Public Sub BuildSideBarComponent(page As ABMPage, id As String, image As String, Title As String, Subtitle As String) As ABMContainer
    Dim ItemCont As ABMContainer
    ItemCont.Initialize(page, id, "")
    ItemCont.AddRowsM(1,False,0,0, "").AddCellsOSMP(1,0,0,0,3,3,3,4,0,0,0,"").AddCellsOSMP(1,0,0,0,9,9,9,4,0,0,0,"")
    ItemCont.AddRowsM(1,False,0,0, "").AddCells12(1, "")
    ItemCont.BuildGrid 'IMPORTANT once you loaded the complete grid AND before you start adding components
    Dim SubItemCont As ABMContainer
    SubItemCont.Initialize(page, id & "SubItemCont", "")
    SubItemCont.AddRowsM(1,False, 0,0,"").AddCells12MP(1,-6,0,0,0,"").AddCells12(1,"")
    SubItemCont.BuildGrid 'IMPORTANT once you loaded the complete grid AND before you start adding components
    ItemCont.Cell(1,2).AddComponent(SubItemCont)
    Dim img As ABMImage
    img.Initialize(page, id & "img", image, 1)
    img.SetFixedSize(48,48)
    img.IsCircular = True
    img.IsResponsive = True
    ItemCont.Cell(1,1).AddComponent(img)
    Dim lbl1 As ABMLabel
    lbl1.Initialize(page, id & "lbl1", Title, ABM.SIZE_H6, False, "lightblue")
    lbl1.VerticalAlign = True
    SubItemCont.Cell(1,1).AddComponent(lbl1)
    Dim lbl2 As ABMLabel
    lbl2.Initialize(page, id & "lbl2", Subtitle, ABM.SIZE_H6, False, "")
    lbl2.VerticalAlign = True
    SubItemCont.Cell(1,2).AddComponent(lbl2)
    Return ItemCont
End Sub
I updated my ConnectNavigationBar method to be...

B4X:
Public Sub ConnectNavigationBar()
    ' Clear the dummies we created in BuildNavigationBar
    page.NavigationBar.Clear
    'connect the items in the navigation bar
    page.NavigationBar.AddTopItemEx("UpdateRecord", "", "mdi-action-done", "", True, ABM.COLOR_GREEN, ABM.INTENSITY_NORMAL)
    page.NavigationBar.AddTopItem("GoBack", "", "mdi-image-navigate-before", "../frmIYMMenu/frmIYMMenu.html", False)
    RefreshOnLoad_programme
    'refresh the navigation bar
    page.NavigationBar.Refresh ' IMPORTANT
End Sub
The important part here is the RefreshOnLoad_programme method. Let's take a look at that method.

B4X:
Private Sub RefreshOnLoad_programme()
    'We will create each side bar needed on the load
    'Define list to store the results of the query
    Dim results As List
    Dim resCnt As Int
    Dim resTot As Int
    Dim resMap As Map
    Dim sTitles As StringBuilder
    Dim sDescriptions As StringBuilder
    'We have titles, description and image
    'variable to hold the primary key
    Dim strId As String
    'variables to title fields
    Dim strProgrammeName As String
    'variables to hold the description fields
    Dim stryear As String
    page.Pause
    'Get connection from current pool if MySQL/MSSQL
    Dim SQL As SQL = ABMShared.SQLGet
    'Get the records as a list of maps from the db
    results = ABMShared.SQLExecuteMaps(SQL,"select IYMAnalysisSet.id,[year],summary,ProgrammeName from IYMAnalysisSet join ProgrammesSet on IYMAnalysisSet.IYMAnalysis_Programmes = ProgrammesSet.id", Null)
    'Close the connection to the database
    ABMShared.SQLClose(SQL)
    'Loop throught each record read and process it
    resTot = results.size - 1
    For resCnt = 0 To resTot
        'Initialize the titles and descriptions and define them
        sTitles.Initialize
        sDescriptions.Initialize
        'Get the record map
        resMap = results.get(resCnt)
        'process the primary key fields
        strId = resMap.get("id")
        'Save record offline
        Dim resJSON As String = ABMShared.Map2Json(resMap)
        ABMShared.SessionStorageSave(page, strId, resJSON)
        strId = strId.Replace(CRLF,"{BR}")
        'process the title fields
        strProgrammeName = resMap.get("programmename")
        strProgrammeName = strProgrammeName.Replace(CRLF,"{BR}")
        sTitles.Append(strProgrammeName)
        'process the description fields
        'Find the output formats and process them
        stryear = resMap.get("year")
        stryear = stryear.Replace(CRLF,"{BR}")
        sDescriptions.Append(stryear)
        'add the item to the ABMSideBarItem
        page.NavigationBar.AddSideBarComponent(strId, ABMShared.BuildSideBarComponent(page, strId, "../images/trends.png", sTitles.ToString, sDescriptions.ToString),"../frmIYMUpdate/frmIYMUpdate.html")
        page.NavigationBar.AddSideBarDivider("")
    Next
    page.Resume
End Sub
As my source code is dynamic, the title field and description fields can be made up of a number of fields from the database, I used a stringbuilder for each of those.

I open the Database Connection, I'm using an SQLite db for now and scaling that is just changing the connection string and creating the same schema in MySQL, when that time comes.

As you can see, I return a list of maps of each record from the db based on my query, loop through each record and then run page.NavigationBar.AddSideBarComponent for each record to create each ABMSideBarItem. Remember, in my ConnectNavigationBar method, all the items were cleared first, that's IMPORTANT.

So each time my records change, as soon as I go to this page, new ABMSideBarItems will be created.

Now, to trap each ABMSideBarItem click event...

The Page_NavigationBarClicked method is your gateway to what happens when each element is selected. As you might be aware, this traps the sidebar and top button events when clicked.

B4X:
Public Sub Page_NavigationbarClicked(Action As String, Value As String)
    page.SaveNavigationBarPosition
    If Action = "LogOff" Then
        ABMShared.LogOff(page)
        Return
    End If
    Select Case Action.ToLowerCase
    Case "updaterecord"
        ExecuteUpdateRecord
        Return
    Case "goback"
        ExecuteGoBack
        Return
    Case Else
        ABMShared.SessionStorageSave(page, "action", "edit")
        ABMShared.SessionStorageSave(page, "id", Action)
        'We are editing a current record...
        AddEditRecord
        Return
    End Select
    ABMShared.NavigateToPage(ws, ABMPageId, Value)
End Sub
I have two TopButtons, one to go back to the previous page and another one to update the current active record. LogOff is just a built in, in case I have that method added anytime.

The trick here was that my Record IDs will be different and thus I couldnt code here what happens if record id 1, 2,3 are selected, thus the use of the "Case Else" statement.

So, when each record is selected from the ABMSideBarItem, I am saving the action to perform and the id of each record. The ID of each record is passed by the Action property of this event.

In this case, the AddEditRecord method is executed each time my item selection changes. See the Return call after AddEditRecord?? Without that the following lines of code would be executed, thus navigating to another page. I had missed that part a couple of times when I did this.

That's all folks.

PS: The next try will be to group these per year and use ChildSubItems instead.
 
Top