B4J Tutorial [Pen&Paper] Automating ABMTable CRUD Functionality

Hi there

Well, this is not new. The ABMGenerator has been an inspiration to this, so I have just adjusted a few things for my liking. As you might know, MyMaterial.Show is a code generator for ABMaterial Apps with some limited components as per this post.

I was faced with having to create my ModalSheets, Tables and CRUD functionality for a variety of my tables. I liked the ABMGenerator so much as it does this perfectly, however the code generated by that is done externally and has to be pasted to your app. No harm with that. As part of MyMaterial.Show, I wanted that to be done within the automated script engine to produce the output without much effort.

These are my requirements:

1. No search functionality for the table (Can be turned on/off)
2. CRUD functionality (Create, Read, Update and Delete records)
3. Ability to hide the table footer where "Number of rows is shown"
4. Modal Sheets

MyMaterial.Show has never fully explored this functionality before, so there are some tricks of the trade that needed to be learned.

Figure 1: Output

output.png

Figure 2: AddEdit

AddEdit.png

Figure 3: Confirm

Confirm.png

Other credits: Thanks to the team behind ABMaterial and other ABMaterial users for very useful advise.
 
Last edited:

Mashiane

Expert
Licensed User
Longtime User
Creating the database table:

1. As a backend, I am using SQLite for development and can just upscale to MySQL after I have finalized everything. So I fired up my SQLite Manager and created a table with a couple of columns, id, numeric primary key which is autoincrement, HolidayDate a date type, HolidayName i.e. the name of the holiday, a varchar and also the Background color entity, also another varchar.

2. My database is linked to my Project on MyMaterial.Show project, so after creating the table, I did, Database > Test Connection and agreend to Document the database. I captured some few records first.

Figure 4

Holidays.png
 

Mashiane

Expert
Licensed User
Longtime User
Creating the frmHolidays Page inside MyMaterial.Show

1. After opening up my project, I created a new page, using Page > New Page.
2. I changed the Page Name to be frmHolidays and also checked Is Initial Page
3. I also changed the Page Title to be the project name
4. On the Navigation tab, I changed the NavBar Title to be Holidays
5. On the CRUD tab, I checked Has Table Crud and also checked Save Records to Session. I selected the Holiday table for DB table and Id for the Primary Key. Then back to the ABMPage tab and clicked Apply, this saved the page, created a navigation bar component and a grid row component.

Creating the ABMTable

1. Selected the newly created page, frmHolidays
2. On Designers > Components > ABMGenerator and set it up as shown below.
3. I have not specified the Search Input Caption because I do not want to have search functionality on this page. If selected this created a search input box and a search button
4. It's important for you to check Is ABMTable for this to work properly.

Figure 5

ABMGenerator.png

Figure 6

ABMGenerator1.png

Figure 7

ABMGenerator2.png
 
Last edited:

Mashiane

Expert
Licensed User
Longtime User
Adding Columns to the ABMTable

The tables for the ABMTable are derived from the database table Holidays, thus we need to create the respective columns as shown in Figure 1 above. To do this we need to use the Fields tab in the ABMGenerator.

The first field in the ABMGenerator is usually the primary key of the table with a GEN_NONE type. As there are 3 columns in the ABMTable, we need to create 3 extra columns for it. On the EditableListView custom control one uses the buttons to Add, Update, Delete, Clone each of the columns, the first column 01 is added by default. So we need to add column 02, 03 and 04. Why 02,03,04 and not anything else?

The sequence when using this custom control is important, we want the columns to show in the order they are added numerically, so the entries will always sorted. Below is the definition of each of these columns to be in the table. The Seq Field Name property is only used to store and sort the column order for this control. Note the Data Field that is used to link the ABMTable to the backend table.

Figure 8: Field 1

field1.png

Figure 9: Field 2

field2.png

Figure 10: Field 3

field3.png

Figure 11: Field 4

field4.png

Each time you add a column, the structure of the table is changed. After finishing adding the columns, you need to update the ABMGenerator by selecting the ABMGenerator tab and clicking Apply. This will create the respective components for the page and the tree structure for your page should look like this figure 12 below.

Figure 12: Tree Structure

treestructure.png
 
Last edited:

Mashiane

Expert
Licensed User
Longtime User
Closing Off

As noted above, the components created have the table name used i.e. Holidays in them. This is to differentiate should you have more than 1 modal sheet, 1 table for example in the same page. When you select the page frmHolidays in the tree, all of your code is now generated and you can just compile your app.

B4X:
Sub Class_Globals
    Private ws As WebSocket 'ignore
    ' will hold our page information
    Public page As ABMPage
    ' page theme
    Private theme As ABMTheme
    ' to access the constants
    Private ABM As ABMaterial 'ignore
    ' name of the page, must be the same as the class name (case sensitive!)
    Public Name As String = "frmHolidays"  '<-------------------------------------------------------- IMPORTANT
    Private ABMPageId As String = ""
    ' your own variables
    Dim Origin As String = "http://localhost:51045" '<--- IMPORTANT for youtube videos.  Set here your website address
    ' the download folder
    Public DownloadFolder As String = "/www/" & ABMShared.AppName & "/uploads/"
    Public DownloadMaxSize As String = 100*1024
    Private myToastId As Int
    Private dbAction As Map
    Private ActiveID As Int
    private IsNew As Boolean
    Private Filter As String
    Private LastSort As String
    Private FilterCount As String
    Private OldValue As String
    Dim CurrentValues As List
    Dim CurrentLabels As List
    Public MaxRows As Int = 10
End Sub
'Initializes the object. You can add parameters to this method if needed.
Public Sub Initialize()
    CurrentLabels.Initialize
    CurrentValues.Initialize
    ' build the local structure IMPORTANT!
    BuildPage
End Sub
Private Sub WebSocket_Connected (WebSocket1 As WebSocket)
    Log("Connected")
    ws = WebSocket1
    ABMPageId = ABM.GetPageID(page, Name,ws)
    Dim session As HttpSession = ABM.GetSession(ws, ABMShared.SessionMaxInactiveIntervalSeconds)
    'page specific authorization
    If session.GetAttribute2("frmHolidays", "false") = "true" Then
        If session.GetAttribute2("IsAuthorized", "") = "" Then
            ABMShared.NavigateToPage(ws, ABMPageId, "../")
            Return
        End If
    End If
    If ABMShared.NeedsAuthorization Then
        If session.GetAttribute2("IsAuthorized", "") = "" Then
            ABMShared.NavigateToPage(ws, ABMPageId, "../")
            Return
        End If
    End If
    ABM.UpdateFromCache(Me, ABMShared.CachedPages, ABMPageId, ws)
    If page.ComesFromPageCache Then
        ' refresh the page
        page.Refresh
        ' because we use ShowLoaderType=ABM.LOADER_TYPE_MANUAL
        page.FinishedLoading
    Else
        ' Prepare the page
        page.Prepare
        ' load the dynamic content
        ConnectPage
    End If
    Log(ABMPageId)
End Sub
Private Sub WebSocket_Disconnected()
    Log("Disconnected")
End Sub
Public Sub Page_ParseEvent(Params As Map)
    Dim eventName As String = Params.Get("eventname")
    Dim eventParams() As String = Regex.Split(",",Params.Get("eventparams"))
    If eventName = "beforeunload" Then
        Log("preparing for url refresh")
        ABM.RemoveMeFromCache(ABMShared.CachedPages, ABMPageId)
        Return
    End If
    If SubExists(Me, eventName) Then
        Params.Remove("eventname")
        Params.Remove("eventparams")
        Select Case Params.Size
        Case 0
            CallSub(Me, eventName)
        Case 1
            CallSub2(Me, eventName, Params.Get(eventParams(0)))
        Case 2
            If Params.get(eventParams(0)) = "abmistable" Then
                Dim PassedTables As List = ABM.ProcessTablesFromTargetName(Params.get(eventParams(1)))
                CallSub2(Me, eventName, PassedTables)
            Else
                CallSub3(Me, eventName, Params.Get(eventParams(0)), Params.Get(eventParams(1)))
            End If
        Case Else
            ' cannot be called diretly, to many param
            CallSub2(Me, eventName, Params)
        End Select
    End If
End Sub
public Sub BuildTheme()
    ' start with the base theme defined in ABMShared
    theme.Initialize("pagetheme")
    theme.AddABMTheme(ABMShared.MyTheme)
    ' add additional themes specific for this page
    theme.Page.BackColor = ABM.COLOR_TRANSPARENT
    theme.Page.BackColorIntensity = ABM.INTENSITY_NORMAL
    theme.Page.ConnectedIndicatorColor = ABM.COLOR_GREEN
    theme.Page.ConnectedIndicatorColorIntensity = ABM.INTENSITY_NORMAL
    theme.Page.DisconnectedIndicatorColor = ABM.COLOR_RED
    theme.Page.DisconnectedIndicatorColorIntensity = ABM.INTENSITY_NORMAL
    theme.Page.PlaceHolderColor = ABM.COLOR_GREY
    theme.Page.PlaceHolderColorIntensity = ABM.INTENSITY_NORMAL
End Sub
public Sub BuildPage()
    ' initialize the theme
    BuildTheme
    ' initialize this page using our theme
    page.InitializeWithTheme(Name, "/ws/" & ABMShared.AppName & "/" & Name, False, ABMShared.SessionMaxInactiveIntervalSeconds, theme)
    page.ShowLoader=True
    page.ShowLoaderType=ABM.LOADER_TYPE_MANUAL
    page.PageHTMLName = "frmHolidays.html"
    page.PageTitle = "DRPW"
    page.PageDescription = ""
    page.PageKeywords = ""
    page.PageSiteMapPriority = "0.50"
    page.PageSiteMapFrequency = ABM.SITEMAP_FREQ_MONTHLY
    'page.UseFontAwesome = False
    page.DisableBackButton = False
    page.UseGoogleAnalytics(ABMShared.TrackingID, Null) ' IMPORTANT Change this to your own TrackingID !!!!!!!
    page.GoogleMapsAPIExtras = ABMShared.GoogleMapsAPIExtras
    page.IsFixedFooter = False
    page.DisablePageReloadOnSwipeDown = True
    page.ShowConnectedIndicator = True
    page.PageLanguage = "en"
    page.SetFontStack("arial,sans-serif")
    page.AlwaysShowVerticalScrollBar = False
    ' adding a navigation bar
    Dim sbtopimg As ABMImage
    sbtopimg.Initialize(page, "sbtopimg", "../images/drpwlogo.png", 1)
    sbtopimg.IsCircular = False
    sbtopimg.SetFixedSize(279, 79)
    page.NavigationBar.Initialize(page, "navBar", ABM.SIDEBAR_MANUAL_ALWAYSHIDE, "Holidays", True, True, 400, 49, sbtopimg, ABM.COLLAPSE_ACCORDION, "drpw")
    page.NavigationBar.SideBarLogoHeight = 79
    page.NavigationBar.TopBarDropDownConstrainWidth = True
    ' you must add at least ONE dummy item if you want to add items to the topbar in ConnectNaviagationBar
    page.NavigationBar.AddTopItem("DUMMY", " ", "", "", False)
    ' you must add at least ONE dummy item if you want to add items to the sidebar
    page.NavigationBar.AddSideBarItem("DUMMY", " ", "", "")
    page.ShowGridInfo = False
    ' create the page grid
    page.AddRows(5, True, "").AddCells12(1, "")
    page.BuildGrid 'IMPORTANT once you loaded the complete grid AND before you start adding components
    ' add a error box template if the name is not entered
    page.AddModalSheetTemplate(ABMShared.BuildWrongInputModalSheet(page))
    ' add a modal sheet template for a yes no msgbox
    page.AddModalSheetTemplate(ABMShared.BuildMsgBoxYesNo(page))
    ' add a modal sheet template for a  msgbox
    page.AddModalSheetTemplate(ABMShared.BuildMsgBox(page))
    page.AddModalSheetTemplate(BuildModalSheetmsHolidays)
End Sub
Private Sub BuildModalSheetmsHolidays() As ABMModalSheet
    Dim msHolidays As ABMModalSheet
    msHolidays.Initialize(page, "msHolidays", True, ABM.MODALSHEET_TYPE_NORMAL, "")
    msHolidays.Size = ABM.MODALSHEET_SIZE_NORMAL
    msHolidays.IsDismissible = False
    msHolidays.IsTextSelectable = True
    msHolidays.Footer.AddRowsM(1, True, 0, 0, "").AddCellsOS(1, 0, 0, 0, 12, 12, 12, "")
    msHolidays.Header.AddRowsM(1, True, 0, 0, "").AddCellsOS(1, 0, 0, 0, 12, 12, 12, "")
    msHolidays.Content.AddRowsM(5, True, 0, 0, "").AddCellsOS(1, 0, 0, 0, 12, 12, 12, "")
    msHolidays.Header.BuildGrid  'IMPORTANT once you loaded the complete grid AND before you start adding components
    msHolidays.Content.BuildGrid 'IMPORTANT once you loaded the complete grid AND before you start adding components
    msHolidays.Footer.BuildGrid  'IMPORTANT once you loaded the complete grid AND before you start adding components
    'Add components to ModalSheet
    Dim dpHolidaysHolidayDate As ABMDateTimePicker
    Dim dpHolidaysHolidayDateDate As Long = DateTime.Now
    dpHolidaysHolidayDate.Initialize(page, "dpHolidaysHolidayDate", ABM.DATETIMEPICKER_TYPE_DATE, dpHolidaysHolidayDateDate, "Date", "")
    dpHolidaysHolidayDate.WeekText = "Week"
    dpHolidaysHolidayDate.TodayText = "Today"
    dpHolidaysHolidayDate.ReturnDateFormat = "YYYY-MM-DD"
    dpHolidaysHolidayDate.ReturnTimeFormat = ""
    dpHolidaysHolidayDate.Language = "en"
    dpHolidaysHolidayDate.FirstDayOfWeek = ABM.FIRSTDAYOFWEEK_SUNDAY
    dpHolidaysHolidayDate.PickText = "OK"
    dpHolidaysHolidayDate.CancelText = "Back"
    msHolidays.Content.Cell(2,1).AddComponent(dpHolidaysHolidayDate)
    Dim txtHolidaysHolidayName As ABMInput
    txtHolidaysHolidayName.Initialize(page, "txtHolidaysHolidayName", ABM.INPUT_TEXT, "Name", False, "")
    msHolidays.Content.Cell(2,1).AddComponent(txtHolidaysHolidayName)
    Dim txtHolidaysBackgroundColor As ABMInput
    txtHolidaysBackgroundColor.Initialize(page, "txtHolidaysBackgroundColor", ABM.INPUT_TEXT, "Background Color", False, "")
    txtHolidaysBackgroundColor.Text = "lightblue"
    msHolidays.Content.Cell(2,1).AddComponent(txtHolidaysBackgroundColor)
    Dim lblHolidays As ABMLabel
    lblHolidays.Initialize(page, "lblHolidays", "{NBSP}Add / Edit Holidays", ABM.SIZE_H5, False, "whitefc")
    msHolidays.Header.Cell(1,1).AddComponent(lblHolidays)
    Dim btnApplyHolidays As ABMButton
    btnApplyHolidays.InitializeFlat(page, "btnApplyHolidays", "", "", "Apply", "transparent")
    btnApplyHolidays.Size = ABM.BUTTONSIZE_NORMAL
    msHolidays.Footer.Cell(1,1).AddComponent(btnApplyHolidays)
    Dim btnCancelHolidays As ABMButton
    btnCancelHolidays.InitializeFlat(page, "btnCancelHolidays", "", "", "Cancel", "transparent")
    btnCancelHolidays.Size = ABM.BUTTONSIZE_NORMAL
    msHolidays.Footer.Cell(1,1).AddComponent(btnCancelHolidays)
    Return msHolidays
End Sub
Public Sub msHolidaysClear()
    Dim msHolidays As ABMModalSheet
    msHolidays = page.ModalSheet("msHolidays")
    Dim dpHolidaysHolidayDate As ABMDateTimePicker = msHolidays.Content.Component("dpHolidaysHolidayDate")
    Dim txtHolidaysHolidayName As ABMInput = msHolidays.Content.Component("txtHolidaysHolidayName")
    Dim txtHolidaysBackgroundColor As ABMInput = msHolidays.Content.Component("txtHolidaysBackgroundColor")
    dpHolidaysHolidayDate.SetDate(DateTime.Now)
    dpHolidaysHolidayDate.Refresh
    txtHolidaysHolidayName.Text = ""
    txtHolidaysHolidayName.Refresh
    txtHolidaysBackgroundColor.Text = "lightblue"
    txtHolidaysBackgroundColor.Refresh
End Sub
Public Sub msgyes_Clicked(Target As String)
    Dim msg As ABMModalSheet = page.ModalSheet("confirm")
    Dim msgyes As ABMButton = msg.Footer.Component("msgyes")
    dim strTag As String
    strTag = msgyes.Tag
    YesNoProcess(strTag)
    page.CloseModalSheet("confirm")
End Sub
Public Sub msgok_Clicked(Target As String)
    page.CloseModalSheet("msgbox")
End Sub
Public Sub msgno_Clicked(Target As String)
    page.CloseModalSheet("confirm")
End Sub
' clicked on the navigation bar
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
    End Select
    ABMShared.NavigateToPage(ws, ABMPageId, Value)
End Sub
Public Sub Page_FileUploaded(FileName As String, success As Boolean)
    page.ws.Flush ' IMPORTANT
End Sub
Public Sub Page_ToastClicked(ToastId As String, Action As String)
    page.DismissToast(ToastId)
End Sub
Public Sub Page_ToastDismissed(ToastId As String)
End Sub
Public Sub ConnectPage()
    'connect navigation bar
    ConnectNavigationBar
    Dim NowWH As String = ABM.GetBrowserWidthHeight(page)
    If NowWH <> "" And NowWH <> ";" Then
        Dim split() As String = Regex.Split(";", NowWH)
        Dim NewH As Int = split(1) - 350
        NewH = NewH / 50
        NewH = NewH / 5
        NewH = NewH * 5
        If NewH >= 10 Then
            MaxRows = NewH
        End If
    End If
    'add components for the page
    Dim HolidaysPager As ABMPagination
    HolidaysPager.Initialize(page, "HolidaysPager", 10, True, True, "")
    HolidaysPager.SetTotalNumberOfPages(10)
    page.Cell(3,1).AddComponent(HolidaysPager)
    Dim tblHolidays As ABMTable
    tblHolidays.Initialize(page, "tblHolidays", True, False, True, "tblTheme")
    tblHolidays.IsBordered = True
    tblHolidays.IsResponsive = True
    tblHolidays.IgnoreFormattingCodes = True
    tblHolidays.IsTextSelectable = True
    tblHolidays.SetFooter("Number of records: ",12,"")
    tblHolidays.SetHeaders(Array As String("ID", "Date", "Name", "Background Color", "Open/Edit", "Delete"))
    tblHolidays.SetHeaderThemes(Array As String("bg", "bg", "bg", "bg", "bgc", "bgc"))
    tblHolidays.SetHeaderHeights(Array As Int(0, 0, 0, 0, 48, 48))
    tblHolidays.SetColumnVisible(Array As Boolean(False, True, True, True, True, True))
    tblHolidays.SetColumnSortable(Array As Boolean(False, True, True, True, False, False))
    tblHolidays.SetColumnDataFields(Array As String("id", "HolidayDate", "HolidayName", "BackgroundColor", "", ""))
    page.Cell(4,1).AddComponent(tblHolidays)
    Dim frmHolidaysAddToTable As ABMActionButton
    frmHolidaysAddToTable.Initialize(page, "frmHolidaysAddToTable", "mdi-content-add", "", "bigblue")
    frmHolidaysAddToTable.MainButton.Size = ABM.BUTTONSIZE_LARGE
    page.AddActionButton(frmHolidaysAddToTable)
    AdminAccess
    page.Refresh ' IMPORTANT
    ' NEW, because we use ShowLoaderType=ABM.LOADER_TYPE_MANUAL
    page.FinishedLoading 'IMPORTANT
    page.RestoreNavigationBarPosition
    LoadHolidays(1)
End Sub
Public Sub Page_SignedOffSocialNetwork(Network As String, Extra As String)
    page.ws.Session.SetAttribute("UserID", "")
    page.ws.Session.SetAttribute("UserEmail", "")
    page.ws.Session.SetAttribute("authType", "")
    page.ws.Session.SetAttribute("authName", "")
    page.ws.Session.SetAttribute("IsAuthorized", "")
    ABMShared.NavigateToPage(ws,ABMPageId, "../")
End Sub
'An ABMPagination event has been fired
Public Sub HolidaysPager_PageChanged(OldPage As Int, NewPage As Int)
    LoadHolidays(NewPage)
End Sub
'An ABMActionButton has been clicked
Public Sub frmHolidaysAddToTable_Clicked(Target As String, SubTarget As String)
    If SubTarget = "" Then
        Select Case Target.tolowercase
        End Select
        ABMShared.SessionStorageSave(page, "action", "new")
        ABMShared.SessionStorageSave(page, "id", "-1")
        msHolidaysAddEditRecord
        Return
    End If
End Sub
'An ABMButton has been clicked
Public Sub btnApplyHolidays_Clicked(Target As String)
    msHolidaysCreateUpdate
    page.CloseModalSheet("msHolidays")
End Sub
'An ABMButton has been clicked
Public Sub btnCancelHolidays_Clicked(Target As String)
    page.CloseModalSheet("msHolidays")
End Sub
Private Sub LoadHolidays(fromPage As Int)
    'Lets get the component from the page.
    Dim tblHolidays As ABMTable = page.Component("tblHolidays")
    '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 sqlQry As String
    'Read arguments from LocalStorage (if any)
    Dim id As String = ABMShared.SessionStorageRead(page, "id")
    'Get the record linked to this record id
    Dim RecordJSON As String = ABMShared.SessionStorageRead(page, id)
    'Convert this record to a map from json
    Dim RecordMap As Map = ABMShared.Json2Map(RecordJSON)
    page.Pause
    'Let's define the qry string
    sqlQry = "select * from Holidays " & Filter & " " & LastSort & " LIMIT " & ((fromPage - 1) * 10) & ", 10"
    'Get connection from current pool if MySQL/MSSQL
    Dim SQL As SQL = ABMShared.SQLGet
    'Get the number of records
    Dim SQL_str As String
    SQL_str = "Select Count(id) As IDS FROM Holidays " & FilterCount
    Dim NumRecords As Int = ABMShared.SQLSelectSingleResult(SQL, SQL_str, Null)
    'Get the records as a list of maps from the db
    results = ABMShared.SQLExecuteMaps(SQL, sqlQry, Null)
    'Close the connection to the database
    ABMShared.SQLClose(SQL)
    If results.Size = 0 And fromPage > 1 Then
        'we are on a page without any lines
        fromPage = fromPage - 1
        LoadHolidays(fromPage)
        Return
    End If
    'set the table records
    tblHolidays.SetFooter("Number of records: " & NumRecords, 12, "")
    tblHolidays.Clear
    'Loop throught each record read and process it
    resTot = results.size - 1
    For resCnt = 0 to resTot
        'Get the record map
        resMap = results.get(resCnt)
        'Update each table row
        Dim rCellValues As List
        Dim rCellThemes As List
        rCellValues.Initialize
        rCellThemes.Initialize
        If resMap.GetDefault("id","") = "" Then
            rCellValues.Add("{NBSP}")
        Else
            rCellValues.Add(resMap.GetDefault("id",""))
        End If
        rCellThemes.Add("nocolor")
        If resMap.GetDefault("holidaydate","") = "" Then
            rCellValues.Add("{NBSP}")
        Else
            rCellValues.Add(resMap.GetDefault("holidaydate",""))
        End If
        rCellThemes.Add("nocolor")
        If resMap.GetDefault("holidayname","") = "" Then
            rCellValues.Add("{NBSP}")
        Else
            rCellValues.Add(resMap.GetDefault("holidayname",""))
        End If
        rCellThemes.Add("nocolor")
        If resMap.GetDefault("backgroundcolor","") = "" Then
            rCellValues.Add("{NBSP}")
        Else
            rCellValues.Add(resMap.GetDefault("backgroundcolor",""))
        End If
        rCellThemes.Add("nocolor")
        Dim btnEditHolidays As ABMButton
        btnEditHolidays.InitializeFloating(page, "btnEditHolidays", "mdi-action-visibility", "")
        rCellValues.Add(btnEditHolidays)
        rCellThemes.Add("openedit")
        Dim btnDeleteHolidays As ABMButton
        btnDeleteHolidays.InitializeFloating(page, "btnDeleteHolidays", "mdi-action-delete", "")
        rCellValues.Add(btnDeleteHolidays)
        rCellThemes.Add("openedit")
        'Add the row to the table
        tblHolidays.AddRow("id" & resCnt, rCellValues)
        tblHolidays.SetRowThemes(rCellThemes)
    Next
    'Update the paginating component
    Dim pager As ABMPagination = page.Component("HolidaysPager")
    If (NumRecords Mod MaxRows > 0) Or (NumRecords = 0) Then
        NumRecords = NumRecords/MaxRows + 1
    Else
        NumRecords = NumRecords/MaxRows
    End If
    pager.SetTotalNumberOfPages(NumRecords)
    pager.SetActivePage(fromPage)
    pager.Refresh
    tblHolidays.Refresh
    page.Resume
End Sub
'Code to handle the sorting of the table
Public Sub tblHolidays_SortChanged(DataField As String, Order As String)
    Select Case DataField
    Case "HolidayDate","HolidayName","BackgroundColor"
        LastSort = " ORDER BY " & DataField & " " & Order & ", id "
    Case Else
        LastSort = " ORDER BY id "
    End Select
    Dim pager As ABMPagination = page.Component("HolidaysPager")
    LoadHolidays(pager.GetActivePage())
End Sub
'Code to handle the search on the data
Public Sub DoSearchHolidays
    Dim txtSearch As ABMInput = page.Component("txtSearchHolidays")
    Filter = txtSearch.Text
    If Filter <> "" Then
        Filter = " WHERE HolidayDate LIKE '%" & Filter & "%' OR HolidayName LIKE '%" & Filter & "%' OR BackgroundColor LIKE '%" & Filter & "%' "
    Else
        Filter = ""
    End If
    'reload the table
    LoadHolidays(1)
End Sub
'Code to handle the search enter press event
Public Sub txtSearchHolidays_EnterPressed(value As String)
    DoSearchHolidays
End Sub
'Code to handle button clicks on table for edit/delete
Public Sub tblHolidays_Clicked(PassedRowsAndColumns As List)
    'Get the details of the cell being selected
    Dim tblCellInfo As ABMTableCell = PassedRowsAndColumns.Get(0)
    'Get the table being processed.
    Dim tblHolidays As ABMTable = page.Component(tblCellInfo.TableName)
    'Read the first column in the current row and assign value to ActiveID
    ActiveId = tblHolidays.GetString(tblCellInfo.Row, 0)
    Select Case tblCellInfo.Column
    Case 4
        'Edit a record
        ABMShared.SessionStorageSave(page, "action", "edit")
        ABMShared.SessionStorageSave(page, "id", ActiveId)
        msHolidaysAddEditRecord
    Case 5
        'Delete a record
        ABMShared.ShowYesNo(page,"Are you sure that you want to delete this record? You will not be able to undo your action. Continue to delete?", "DeleteHolidays", "CancelDeleteRecord")
    End Select
End Sub
Public Sub msHolidaysAddEditRecord()
    'Show progress dialog...
    Dim strAction As String
    Dim strID As String
    OldValue = ""
    'Read action from localstorage...
    strAction = ABMShared.SessionStorageRead(page, "action")
    'Read the ID from localstorage...
    strID = ABMShared.SessionStorageRead(page, "id")
    Select Case strAction
    Case "new"
        'clear the contents of the modal sheet component
        msHolidaysClear
        page.ShowModalSheet("msHolidays")
    Case "edit"
        'clear the controls of the page
        msHolidaysClear
        'read the record from the database.
        Dim SQL As SQL = ABMShared.SQLGet
        Dim Record As Map
        Record = ABMShared.SQLRecordRead(SQL,"Holidays", "id", strID)
        'Close the database connection...
        ABMShared.SQLClose(SQL)
        If Record.IsInitialized = True Then
            'The record has been found, update the modal sheet controls...
            msHolidaysSetContents(Record)
            page.ShowModalSheet("msHolidays")
        End If
    End Select
End Sub
Private Sub msHolidaysCreateUpdate()
    page.pause
    'define a map to hold the form contents
    Dim m As Map
    'read the modal sheet contents to a map
    m = msHolidaysGetContents
    'validate the form contents where required
    If msHolidaysValidate(m) = False Then
        page.resume
        Return
    End If
    'the form contents are ok, continue with the save
    Dim strAction As String
    Dim strID As String
    'determine the action we have been performing
    strAction = ABMShared.SessionStorageRead(page, "action")
    strID = ABMShared.SessionStorageRead(page, "id")
    Select Case strAction
    Case "new"
        Dim SQL As SQL
        'get the database connection
        Dim SQL As SQL = ABMShared.SQLGet
        'insert the record
        strID = ABMShared.SQLRecordInsert(SQL, "Holidays", m)
        If strID > 0 Then
            ABMShared.SessionStorageSave(page, "action", "edit")
            ABMShared.SessionStorageSave(page, "id", strID)
            myToastId = myToastId + 1
            page.ShowToast("toast" & myToastId, "toastgreen", "Record added successfully.", 3000)
            'Refresh the table
            Dim pager As ABMPagination = page.Component("HolidaysPager")
            LoadHolidays(pager.GetActivePage())
        Else
            myToastId = myToastId + 1
            page.ShowToast("toast" & myToastId, "toastred", "Record could not be added, please try again.", 3000)
        End If
        'Close the connection to the database
        ABMShared.SQLClose(SQL)
    Case "edit"
        Dim SQL As SQL
        'get the database connection
        Dim SQL As SQL = ABMShared.SQLGet
        If ABMShared.SQLRecordUpdate(SQL,"Holidays", m, "id", strID) = True Then
            myToastId = myToastId + 1
            page.ShowToast("toast" & myToastId, "toastgreen", "Record updated successfully.", 3000)
            'Refresh the table
            Dim pager As ABMPagination = page.Component("HolidaysPager")
            LoadHolidays(pager.GetActivePage())
        Else
            myToastId = myToastId + 1
            page.ShowToast("toast" & myToastId, "toastred", "Record could not be updated, please try again.", 3000)
        End If
        'Close the connection to the database
        ABMShared.SQLClose(SQL)
    End Select
    page.resume
End Sub
'Get the contents of the modal sheet input components and save to a map
Public Sub msHolidaysGetContents() As Map
    Dim pMap As Map
    pMap.Initialize
    Dim msHolidays As ABMModalSheet
    msHolidays = page.ModalSheet("msHolidays")
    Dim dpHolidaysHolidayDate As ABMDateTimePicker = msHolidays.Content.Component("dpHolidaysHolidayDate")
    Dim txtHolidaysHolidayName As ABMInput = msHolidays.Content.Component("txtHolidaysHolidayName")
    Dim txtHolidaysBackgroundColor As ABMInput = msHolidays.Content.Component("txtHolidaysBackgroundColor")
    Dim dpHolidaysHolidayDateContents As String
    Dim dpHolidaysHolidayDateDate As Long
    dpHolidaysHolidayDateDate = dpHolidaysHolidayDate.GetDate
    dpHolidaysHolidayDateContents = ABM.Util.ConvertToDateTimeString(dpHolidaysHolidayDateDate, "yyyy-MM-dd")
    pMap.put("holidaydate", dpHolidaysHolidayDateContents)
    pMap.put("holidayname", txtHolidaysHolidayName.Text)
    pMap.put("backgroundcolor", txtHolidaysBackgroundColor.Text)
    Return pMap
End Sub
'Get the contents of the modal sheet from GetContents and check validity
Public Sub msHolidaysValidate(gMap As Map) As Boolean
    Dim msHolidays As ABMModalSheet
    msHolidays = page.ModalSheet("msHolidays")
    Dim stxtHolidaysHolidayName As String
    stxtHolidaysHolidayName = gMap.get("holidayname")
    If stxtHolidaysHolidayName = "null" Then stxtHolidaysHolidayName = ""
    If stxtHolidaysHolidayName.Length = 0 Then
        ABMShared.ShowMsgBox(page,"Name cannot be blank. Please enter a value.")
        Dim txtHolidaysHolidayName As ABMInput = msHolidays.Content.Component("txtHolidaysHolidayName")
        txtHolidaysHolidayName.SetFocus
        Return False
    End If
    Dim stxtHolidaysBackgroundColor As String
    stxtHolidaysBackgroundColor = gMap.get("backgroundcolor")
    If stxtHolidaysBackgroundColor = "null" Then stxtHolidaysBackgroundColor = ""
    If stxtHolidaysBackgroundColor.Length = 0 Then
        ABMShared.ShowMsgBox(page,"Background Color cannot be blank. Please enter a value.")
        Dim txtHolidaysBackgroundColor As ABMInput = msHolidays.Content.Component("txtHolidaysBackgroundColor")
        txtHolidaysBackgroundColor.SetFocus
        Return False
    End If
    Return True
End Sub
'Set the contents of the modal page input components from map
Public Sub msHolidaysSetContents(pMap As Map)
    Dim msHolidays As ABMModalSheet
    msHolidays = page.ModalSheet("msHolidays")
    'Get the page components to update
    Dim dpHolidaysHolidayDate As ABMDateTimePicker = msHolidays.Content.Component("dpHolidaysHolidayDate")
    Dim txtHolidaysHolidayName As ABMInput = msHolidays.Content.Component("txtHolidaysHolidayName")
    Dim txtHolidaysBackgroundColor As ABMInput = msHolidays.Content.Component("txtHolidaysBackgroundColor")
    'Update computations
    'Assign content to component values
    Dim dpHolidaysHolidayDateContents As String
    If pMap.GetDefault("holidaydate",null) = null Then
        dpHolidaysHolidayDateContents = DateTime.Now
    Else
        dpHolidaysHolidayDateContents = pMap.get("holidaydate")
        dpHolidaysHolidayDateContents = ABM.Util.ConvertFromDateTimeString(dpHolidaysHolidayDateContents, "yyyy-MM-dd")
    End If
    dpHolidaysHolidayDate.SetDate(dpHolidaysHolidayDateContents)
    dpHolidaysHolidayDate.Refresh
    Dim txtHolidaysHolidayNameContents As String
    If pMap.GetDefault("holidayname",null) = null Then
        txtHolidaysHolidayName.Text = ""
    Else
        txtHolidaysHolidayNameContents = pMap.get("holidayname")
    End If
    txtHolidaysHolidayName.Text = txtHolidaysHolidayNameContents
    txtHolidaysHolidayName.Refresh
    Dim txtHolidaysBackgroundColorContents As String
    If pMap.GetDefault("backgroundcolor",null) = null Then
        txtHolidaysBackgroundColor.Text = "lightblue"
    Else
        txtHolidaysBackgroundColorContents = pMap.get("backgroundcolor")
    End If
    txtHolidaysBackgroundColor.Text = txtHolidaysBackgroundColorContents
    txtHolidaysBackgroundColor.Refresh
End Sub
Public Sub AdminAccess()
    'Get the usertype
    Dim UserType As String = ws.Session.GetAttribute2("UserType", "0")
End Sub
Private Sub YesNoProcess(Tag As String)
    Select case Tag
    Case "DeleteHolidays"
        Dim SQL As SQL
        'get the database connection
        Dim SQL As SQL = ABMShared.SQLGet
        Dim bDeleted As Boolean = ABMShared.SQLRecordDelete(SQL, "Holidays", "id", ActiveID)
        ABMShared.SQLClose(SQL)
        If bDeleted = True Then
            myToastId = myToastId + 1
            page.ShowToast("toast" & myToastId, "toastgreen", "Record deleted successfully.", 3000)
            'Refresh the table
            Dim pager As ABMPagination = page.Component("HolidaysPager")
            LoadHolidays(pager.GetActivePage())
        Else
            myToastId = myToastId + 1
            page.ShowToast("toast" & myToastId, "toastred", "Record could not be deleted, please try again.", 3000)
        End If
    End Select
End Sub
Public Sub ConnectNavigationBar()
    ' Clear the dummies we created in BuildNavigationBar
    page.NavigationBar.Clear
    'connect the items in the navigation bar
    'refresh the navigation bar
    page.NavigationBar.Refresh ' IMPORTANT
End Sub

By default, the ModalSheet page created will follow this methodology of creation.

B4X:
msHolidays.Footer.AddRowsM(1, True, 0, 0, "").AddCellsOS(1, 0, 0, 0, 12, 12, 12, "")
    msHolidays.Header.AddRowsM(1, True, 0, 0, "").AddCellsOS(1, 0, 0, 0, 12, 12, 12, "")
    msHolidays.Content.AddRowsM(5, True, 0, 0, "").AddCellsOS(1, 0, 0, 0, 12, 12, 12, "")

This is not cast in stone as you can simply change this by selecting the footerrowHolidays, headerrowHolidays and or contentrowHolidays to change the specs of your rows and columns

footerrow.png


So with just a simple few steps...

1. Database table creation
2. Documenting the database in MyMaterial.Show
3. Creating a new page and linking it with the backend datasource
4. Creating a ABMGenerator within the page
5. Adding the columns for your table

You have a fully functional ABMTable with crud functionality for Adding, Updating and Deleting your records.

To Do:

Add support for DropdownLists and other components and proper validation.

That's all folks..

PS: The latest version of MyMaterial.Show is available here
 
Last edited:

Harris

Expert
Licensed User
Longtime User
Like I stated previously @Mashiane , you are a machine.

Simple grids and modal sheets will benefit greatly from this.
More complex pages will require tweeking - yet the grunt work of a foundation has been generated for you - by YOU.

ilikeit.jpg
 
Top