Hi there
The ABMTable examples provide functionality to Open/Edit and Delete functionalities of the current record. I wanted more functionality like Cloning a record and Drilling Down details of the current records based on the entry that you select (click on).
So I tweaked my table loading functionality to include two extra buttons, 1 to drill down and see payments linked to the selected record and nother to clone the active record in question...
From above, btnDetailProcurementPlan provided functionality to drill down based on the selected record and btnCopyProcurementPlan clones the current records.
Thus, to trap these events...
So the msProcurementPlanCopyRecord method reads the record from the database and creates a copy of it taking into consideration other functionalities that need to be executed, like incrementing a particular field in the table and updating particular tables using a foreign key with 'live' totals based on current database records.
Then the drilldown methods, from the selected record, a header is defined for the ABMNavigation bar and navigation to the payments screen ensured, showing payments that are related to the previsouly selected record only. As you have noted, the expenditure for 5 is 30K, drilling down on this will provide payments in relation to that amount as depicted below.
That's all folks..
The ABMTable examples provide functionality to Open/Edit and Delete functionalities of the current record. I wanted more functionality like Cloning a record and Drilling Down details of the current records based on the entry that you select (click on).
So I tweaked my table loading functionality to include two extra buttons, 1 to drill down and see payments linked to the selected record and nother to clone the active record in question...
B4X:
Private Sub LoadProcurementPlan(fromPage As Int)
'Lets get the component from the page.
Dim tblProcPlan As ABMTable = page.Component("tblProcPlan")
CurrentLabels.Initialize
CurrentValues.Initialize
'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, "ProcurementPlanid")
'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 ProcurementPlan order by SeqNumber " & 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 ProcurementPlan " & 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
LoadProcurementPlan(fromPage)
Return
End If
tblProcPlan.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
Dim id As String = resMap.GetDefault("id", "")
If id = "" Then
rCellValues.Add("{NBSP}")
Else
rCellValues.Add(id)
End If
'Add theme to the cell
rCellThemes.Add("nocolor")
Dim SeqNumber As String = resMap.GetDefault("seqnumber", "1")
If SeqNumber = "" Then
rCellValues.Add("{NBSP}")
Else
rCellValues.Add(SeqNumber)
End If
'Add theme to the cell
rCellThemes.Add("nocolor")
Dim FinYear As String = resMap.GetDefault("finyear", "2016/17")
If FinYear = "" Then
rCellValues.Add("{NBSP}")
Else
rCellValues.Add(FinYear)
End If
'Add theme to the cell
rCellThemes.Add("nocolor")
Dim Programme As String = resMap.GetDefault("programme", "")
ABMShared.SessionStorageSave(page, "programme", Programme)
If Programme = "" Then
rCellValues.Add("{NBSP}")
Else
Dim SQL_str As String
SQL_str = "Select Number As Outcome FROM ProgrammesSet Where Id = ?"
Programme = ABMShared.SQLSelectSingleResult(SQL, SQL_str, Array As String(Programme))
rCellValues.Add(Programme)
End If
'Add theme to the cell
rCellThemes.Add("nocolor")
Dim ClassOfGoods As String = resMap.GetDefault("classofgoods", "")
ABMShared.SessionStorageSave(page, "classofgoods", ClassOfGoods)
If ClassOfGoods = "" Then
rCellValues.Add("{NBSP}")
Else
Dim SQL_str As String
SQL_str = "Select Description As Outcome FROM ClassOfGoods Where id = ?"
ClassOfGoods = ABMShared.SQLSelectSingleResult(SQL, SQL_str, Array As String(ClassOfGoods))
rCellValues.Add(ClassOfGoods)
End If
'Add theme to the cell
rCellThemes.Add("nocolor")
Dim Description As String = resMap.GetDefault("description", "")
If Description = "" Then
rCellValues.Add("{NBSP}")
Else
rCellValues.Add(Description)
End If
'Add theme to the cell
rCellThemes.Add("nocolor")
Dim Resource As String = resMap.GetDefault("resource", "")
ABMShared.SessionStorageSave(page, "resource", Resource)
If Resource = "" Then
rCellValues.Add("{NBSP}")
Else
Dim SQL_str As String
SQL_str = "Select ResourceName As Outcome FROM Resources Where id = ?"
Resource = ABMShared.SQLSelectSingleResult(SQL, SQL_str, Array As String(Resource))
rCellValues.Add(Resource)
End If
'Add theme to the cell
rCellThemes.Add("nocolor")
Dim Status As String = resMap.GetDefault("status", "")
ABMShared.SessionStorageSave(page, "status", Status)
If Status = "" Then
rCellValues.Add("{NBSP}")
Else
Dim SQL_str As String
SQL_str = "Select Description As Outcome FROM ProcurementStatus Where id = ?"
Status = ABMShared.SQLSelectSingleResult(SQL, SQL_str, Array As String(Status))
rCellValues.Add(Status)
End If
'Add theme to the cell
rCellThemes.Add("nocolor")
Dim Budget As String = resMap.GetDefault("budget", "0.00")
If Budget = "" Then
rCellValues.Add("{NBSP}")
Else
'*** Start output format
Budget = ABMShared.makemoney(Budget)
'*** End output format
rCellValues.Add(Budget)
End If
'Add theme to the cell
rCellThemes.Add("nocolorr")
Dim Expenditure As String = resMap.GetDefault("expenditure", "0.00")
If Expenditure = "" Then
rCellValues.Add("{NBSP}")
Else
'*** Start output format
Expenditure = ABMShared.makemoney(Expenditure)
'*** End output format
rCellValues.Add(Expenditure)
End If
'Add theme to the cell
rCellThemes.Add("nocolorr")
Dim AdvertDate As String = resMap.GetDefault("advertdate", "DateTime.Now")
If AdvertDate = "" Then
rCellValues.Add("{NBSP}")
Else
rCellValues.Add(AdvertDate)
End If
'Add theme to the cell
rCellThemes.Add("nocolor")
Dim CompletionDate As String = resMap.GetDefault("completiondate", "DateTime.Now")
If CompletionDate = "" Then
rCellValues.Add("{NBSP}")
Else
rCellValues.Add(CompletionDate)
End If
'Add theme to the cell
rCellThemes.Add("nocolor")
Dim Comment As String = resMap.GetDefault("comment", "")
If Comment = "" Then
rCellValues.Add("{NBSP}")
Else
rCellValues.Add(Comment)
End If
'Add theme to the cell
rCellThemes.Add("nocolor")
Dim btnEditProcurementPlan As ABMButton
btnEditProcurementPlan.InitializeFloating(page, "btnEditProcurementPlan", "mdi-action-visibility", "")
rCellValues.Add(btnEditProcurementPlan)
rCellThemes.Add("openedit")
Dim btnDetailProcurementPlan As ABMButton
btnDetailProcurementPlan.InitializeFloating(page, "btnDetailProcurementPlan", "mdi-action-list", "")
rCellValues.Add(btnDetailProcurementPlan)
rCellThemes.Add("openedit")
Dim btnCopyProcurementPlan As ABMButton
btnCopyProcurementPlan.InitializeFloating(page, "btnCopyProcurementPlan", "mdi-content-content-copy", "")
rCellValues.Add(btnCopyProcurementPlan)
rCellThemes.Add("openedit")
Dim btnDeleteProcurementPlan As ABMButton
btnDeleteProcurementPlan.InitializeFloating(page, "btnDeleteProcurementPlan", "mdi-action-delete", "")
rCellValues.Add(btnDeleteProcurementPlan)
rCellThemes.Add("openedit")
'Add the row to the table
tblProcPlan.AddRow("id" & resCnt, rCellValues)
tblProcPlan.SetRowThemes(rCellThemes)
Next
'Update the paginating component
Dim pager As ABMPagination = page.Component("ProcurementPlanPager")
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
tblProcPlan.Refresh
page.Resume
End SubFrom above, btnDetailProcurementPlan provided functionality to drill down based on the selected record and btnCopyProcurementPlan clones the current records.
Thus, to trap these events...
B4X:
Public Sub tblProcPlan_Clicked(PassedRowsAndColumns As List)
ABMShared.SessionStorageSave(page, "header", "")
'Get the details of the cell being selected
Dim tblCellInfo As ABMTableCell = PassedRowsAndColumns.Get(0)
'Get the table being processed.
Dim tblProcPlan As ABMTable = page.Component(tblCellInfo.TableName)
'Read the first column in the current row and assign value to ActiveID
ActiveID = tblProcPlan.GetString(tblCellInfo.Row, 0)
Select Case tblCellInfo.Column
Case 13
'Edit a record
ABMShared.SessionStorageSave(page, "action", "edit")
ABMShared.SessionStorageSave(page, "ProcurementPlanid", ActiveID)
msProcurementPlanAddEditRecord
Case 15
'Clone a record
ABMShared.SessionStorageSave(page, "action", "copy")
ABMShared.SessionStorageSave(page, "ProcurementPlanid", ActiveID)
msProcurementPlanCopyRecord
Case 14
'Drill down on a record
Dim sbHeader As StringBuilder
sbHeader.Initialize
Dim SeqNumber As String = tblProcPlan.GetString(tblCellInfo.Row, 1)
sbHeader.Append(SeqNumber)
sbHeader.Append("-")
Dim FinYear As String = tblProcPlan.GetString(tblCellInfo.Row, 2)
sbHeader.Append(FinYear)
ABMShared.SessionStorageSave(page, "header", sbHeader.ToString)
ABMShared.SessionStorageSave(page, "action", "detail")
ABMShared.SessionStorageSave(page, "ProcurementPlanid", ActiveID)
ABMShared.SessionStorageSave(page, "procpayid", ActiveID)
ABMShared.NavigateToPage(ws, ABMPageId, "../frmPayments/frmPayments.html")
Case 16
'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", "DeleteProcurementPlan", "CancelDeleteRecord")
End Select
End SubSo the msProcurementPlanCopyRecord method reads the record from the database and creates a copy of it taking into consideration other functionalities that need to be executed, like incrementing a particular field in the table and updating particular tables using a foreign key with 'live' totals based on current database records.
B4X:
Public Sub msProcurementPlanCopyRecord()
'Show progress dialog...
Dim ProcurementPlanid As String
'Read the ID from localstorage...
ProcurementPlanid = ABMShared.SessionStorageRead(page, "ProcurementPlanid")
'read the record from the database.
Dim SQL As SQL = ABMShared.SQLGet
Dim m As Map
m = ABMShared.SQLRecordRead(SQL,"ProcurementPlan", "id", ProcurementPlanid)
'remove the primary key column
m.Remove("id")
Dim AutoQrySeqNumber As String = "SELECT MAX(SeqNumber) FROM ProcurementPlan"
Dim AutoSeqNumber As Long = ABMShared.SQLSelectSingleResult(SQL, AutoQrySeqNumber, Null)
AutoSeqNumber = AutoSeqNumber + 1
m.put("seqnumber",AutoSeqNumber)
'save the record to the database and get new primary key value
ProcurementPlanid = ABMShared.SQLRecordInsert(SQL, "ProcurementPlan", m)
' the new key has been added
Dim xProgramme As String = ABMShared.SessionStorageRead(page, "programme")
Dim fProgramme As String
Dim QryProgramme As String
QryProgramme = "SELECT SUM(Budget) FROM ProcurementPlan WHERE Programme = ?"
fProgramme = ABMShared.SQLSelectSingleResult(SQL, QryProgramme, Array As String(xProgramme))
'update the foreign table
QryProgramme = "UPDATE ProgrammesSet SET ppBudget = " & fProgramme & " WHERE Id = " & xProgramme
SQL.ExecNonQuery(QryProgramme)
Dim xProgramme As String = ABMShared.SessionStorageRead(page, "programme")
Dim fProgramme As String
Dim QryProgramme As String
QryProgramme = "SELECT SUM(Expenditure) FROM ProcurementPlan WHERE Programme = ?"
fProgramme = ABMShared.SQLSelectSingleResult(SQL, QryProgramme, Array As String(xProgramme))
'update the foreign table
QryProgramme = "UPDATE ProgrammesSet SET ppExpenditure = " & fProgramme & " WHERE Id = " & xProgramme
SQL.ExecNonQuery(QryProgramme)
'Close the database connection...
ABMShared.SQLClose(SQL)
If ProcurementPlanid > 0 Then
ABMShared.SessionStorageSave(page, "action", "edit")
ABMShared.SessionStorageSave(page, "ProcurementPlanid", ProcurementPlanid)
myToastId = myToastId + 1
page.ShowToast("toast" & myToastId, "toastgreen", "Record copied successfully.", 3000)
' open the record for editing
msProcurementPlanAddEditRecord
Else
myToastId = myToastId + 1
page.ShowToast("toast" & myToastId, "toastred", "Record could not be copied, please try again.", 3000)
End If
End SubThen the drilldown methods, from the selected record, a header is defined for the ABMNavigation bar and navigation to the payments screen ensured, showing payments that are related to the previsouly selected record only. As you have noted, the expenditure for 5 is 30K, drilling down on this will provide payments in relation to that amount as depicted below.
That's all folks..
