Share My Creation TableView: Magical Inline Editing

Hi there

THIS PROJECT IS NO LONGER BEING MAINTAINED, A BETTER ALTERNATIVE EXISTS, SEARCH FOR B4XTABLE

MashPropertyBag Download

This is a series in an article of the MashPropertyBag...

02 April 2018 Update...

Just added functionality to export the contents to Excel using JPOI. It is as simple as..

'set report options
    Dim opt As ReportHeading = MashPOI.PoiSetHeading(True,fx.Colors.Yellow,"Arial",16,True)
    Dim rh As ReportHeading = MashPOI.PoiSetHeading(True,fx.Colors.Red,"Arial",10,False)
    'MashPOI.PoiExportList(File.DirApp,filePath,"",filePath,filePath,headings,opt,rh,True,rows)
    tbl.Export2Excel(File.DirApp,filePath,"",filePath,filePath,opt,rh,True)

Example based on the b4xgoodies MashOverViewCreator. listings

15 March 2018 Update

Download Location

The control has now been linked to an SQLite backend as demonstrated in this video below.


I will adding paging facility some time later... This has been simply been created with these few lines of code.

#Region Project Attributes
    #MainFormWidth: 1500
    #MainFormHeight: 700
#End Region

Sub Process_Globals
    Private fx As JFX
    Private MainForm As Form
    Private btnNewRecord As Button
    Private lstRecords As EditableTV
    Private btnDeleteAll As Button
    Private btnSaveAll As Button
    Private dbFile As String
    Private cSQL As SQL
    Private dbPath As String
End Sub

#AdditionalJar: MashPropertyBagRes
#AdditionalJar: sqlite-jdbc-3.7.2

Sub PrepareDB
    If File.Exists(File.DirApp,"contacts.db") = False Then
        File.Copy(File.DirAssets,"contacts.db",File.DirApp,"contacts.db")
    End If
    cSQL.InitializeSQLite(File.DirApp,"contacts.db",False)
    dbPath = File.Combine(File.DirApp,"contacts.db")
End Sub


Sub AppStart (Form1 As Form, Args() As String)
    MainForm = Form1
    MainForm.RootPane.LoadLayout("vTableEdit") 'Load the layout file.
    MainForm.Title = "My Contacts"
    PrepareDB
    'setup the table fields
    lstRecords.SetParentForm(MainForm)
    lstRecords.loadcss
    lstRecords.SetReportViewer
    'indicate required fieldsd
    lstRecords.ResetRequired
    lstRecords.AddRequired("firstname").AddRequired("lastname")
    'start: set database link IMPORTANT
    lstRecords.PrimaryKey = "id"
    lstRecords.SecondaryKey = "firstname"
    lstRecords.DataSource = "contacts"
    lstRecords.DataBase = dbPath
    lstRecords.HandleCrud = True
    'end : set database link
    lstRecords.AddLabel("id",30,"#","-1")   'IMPORTANT
    lstRecords.AddTextBox("firstname",200,"First Name","")
    lstRecords.AddTextBox("lastname",200,"Last Name","")
    lstRecords.AddComboBox("gender",100,"Gender",lstRecords.CreateList(",","Male,Female"),"Male")
    lstRecords.AddDatePicker("dob",200,"Date of Birth","2017-01-01")
    lstRecords.AddCheckBox("active",100,"Active","True")
    lstRecords.ShowUpdate = True
    lstRecords.ShowDelete = True
    lstRecords.ShowClone = True
    lstRecords.TabIndex = ""
    lstRecords.AlwaysTell = True
    lstRecords.RefreshColumns
    MainForm.Show
    lstRecords.SQLQuery = "select * from contacts order by firstname,lastname"
    lstRecords.LoadSQLData(Null)
End Sub

'Return true to allow the default exceptions handler to handle the uncaught exception.
Sub Application_Error (Error As Exception, StackTrace As String) As Boolean
    Return True
End Sub


'add an empty row to the table using default values
Sub btnNewRecord_Click
    lstRecords.AddRowEmpty
End Sub

'a record has been saved/updated, get row and its map
Sub lstRecords_Update(RowIndex As Int,Value As Map)
    Log("Update: " & Value)
End Sub

'a record has been deleted, get row and its map
Sub lstRecords_Delete(RowIndex As Int, Value As Map)
    Log("Delete: " & Value)
End Sub

'save all records
Sub btnSaveAll_Click
    lstRecords.save
End Sub

'delete all records
Sub btnDeleteAll_Click
    lstRecords.Delete
End Sub

Sub etvRecords_Clone (RowIndex As Int, Value As Map)
    'get the old id
    Dim oldid As String = Value.getdefault("oldid9999","")
    'get the new id
    Dim id As String = Value.GetDefault("id","")
    'remove oldid
End Sub

Old Stuff

Well, I have been planning to do this for a while now and the 'need' for it came crashing down me once more.


Why?

1. I just wanted to use normal controls like textboxes, comboboxes, checkboxes etc.
2. I also needed to get the saved/updated/deleted record as a map, remove it from the tableview etc easy peasy.
3. Flexibility of being able to do anything with it.

I first asked a question about this sometime ago and then left it there.. and developing my own propertybag here taught me a lot as a result this just extends that for row functionality.

This is just so exciting..

See the "Log" during the operation of my video example here, the records are returned as Maps that you can manipulate.

Reproduction:

Create a view in the designer and drop the CustomView : EditableLV
In class globals, define your EditableLV

Private lstRecords As EditableTV

Then, add columns to the tableview that you will be processing, these indicate the type of column, the width, etc

'setup the table fields
    lstRecords.SetParentForm(frm,True)
    lstRecords.SetReportViewer
    lstRecords.PrimaryKey = "id"        'important
    lstRecords.AddLabel("id",30,"#","-1")   'IMPORTANT
    lstRecords.AddTextBox("firstname",200,"First Name","")
    lstRecords.AddTextBox("lastname",200,"Last Name","")
    lstRecords.AddComboBox("gender",100,"Gender",jMash.CreateList(",","Male,Female"),"Male")
    lstRecords.AddDatePicker("dob",200,"Date of Birth","2017-01-01")
    'lstRecords.AddColorPicker("eyecolor",200,"Eye Color","")
    lstRecords.AddCheckBox("active",100,"Active","True")
    lstRecords.RefreshColumns
    'Load some example records to the tableview
    LoadRecords

Extra methods...

Sub LoadRecords()
    'lstRecords.AddRecords(lst) passing it a list of records from a db example from ExecuteMaps
    lstRecords.AddRowM(-1,CreateMap("id":1,"firstname":"Mashy","lastname":"Mbanga","dob":"1973-04-15","gender":"Male","active":"False","skill":"Programming"))
    lstRecords.AddRowM(-1,CreateMap("id":2,"firstname":"Ozzie","lastname":"Mbanga","dob":"2010-04-15","gender":"Male","active":"True","skill":"Programming"))
    lstRecords.AddRowM(-1,CreateMap("id":3,"firstname":"Orio","lastname":"Mbanga","dob":"2010-04-15","gender":"Female","active":"True","skill":"Programming"))
    lstRecords.AddRowM(-1,CreateMap("id":4,"firstname":"Ernesto","lastname":"Mbanga","dob":"2009-04-15","gender":"Female","active":"True","skill":"Programming"))
    'use recTot as a ficticious primary key
    recTot = lstRecords.recordcount
End Sub

From the example, I have a button to add new records to the TableView, this calls...

'add an empty row to the table using default values
Sub btnNewRecord_Click
    lstRecords.AddRowEmpty
End Sub

The tableview columns have been defined above with default values, so the AddRowEmpty methods takes from that and creates an empty row for you. Remember the id is specified as -1 default value, so automatically assumes a new record mode.

Clicking the Save image for a records calls this method...

'a record has been saved/updated, get row and its map
Sub lstRecords_Update(RowIndex As Int,Value As Map)
    Log("save action")
    Dim json As String = lstRecords.Map2Json(Value)
    Log(json)
    'if this was a new recors, update its primary key, you can get the primary key from a db and use that here.
    Dim lastID As Int = Value.Getdefault("id",-1)
    If lastID = -1 Then
        recTot = recTot + 1
        Value.Put("id",recTot)
    End If
    'update the id of the table
    lstRecords.UpdateRow(RowIndex,Value)
End Sub

and the delete call for each record...

'a record has been deleted, get row and its map
Sub lstRecords_Delete(RowIndex As Int, Value As Map)
    Dim resp As String = jMash.MsgBoxConfirm(frm,"Confirm Delete","Are you sure that you want to delete this record?")
    If resp = "no" Then Return
    'user wants to delete the record, do it
    lstRecords.RemoveRow(RowIndex)
    Log("delete action")
    Dim json As String = lstRecords.Map2Json(Value)
    Log(json)
End Sub

You can then manipulate the records as you please.

In summary

1. Load records from a datasource
2. Create new records and save them
3. Delete records

Magic!

PS: I will upload the source code asap so that you can customize to your needs e.g. theme etc.

 

[micro]

1. Load records from a datasource

Hi Mashiane thanks for your project.
How i can use editable Tableview with mysql table?
Practically load a table directly into your tableview.

 

[Mashiane]

There is no method to do that yet however with DBUtils, there is a ExecuteTableView method, you can tweak that for what you need.

1. With this control, you need to define the columns first and link those to the types of controls that you want and then
2. Load the data, the method above called LoadRecords assumes a map is passed for each record that you want to add, so your list of records can come from the ExecuteMaps which is a list with maps of records.

I have not tested this, but you can try this, it is however better to define the structure of the editableTV before you load the data. In this example below we assume that you will have textboxes for editing. As I am tryping this code here, it might not be beautified.

Public Sub ExecuteEditableTV(jSQL As SQL, Query As String, StringArgs() As String, Limit As Int, lstRecords As editableTV, parentForm as form, primaryKey as string)
lstRecords.SetParentForm(parentForm,True)
    lstRecords.SetReportViewer
    lstRecords.PrimaryKey = primaryKey
lstRecords.Items.Clear
    Dim cur As ResultSet
    Dim colName as string
    Dim fValue as string
    Dim records as List
    records.initialize
If StringArgs = Null Then 
        Dim StringArgs(0) As String
    End If
'get the columns and create the headers
cur = jSQL.ExecQuery2(Query, StringArgs)
    For i = 0 To cur.ColumnCount - 1
        colName = cur.GetColumnName(i)
        'assuming your records are all texts 
        lstRecords.AddTextBox(colname,200,colName,"")
    Next
'set the columns
lstRecords.RefreshColumns
'now load the data
Do While cur.NextRow
' each record has to be a map        
Dim res As Map
        res.Initialize 
        For col = 0 To cur.ColumnCount - 1
            colName = cur.GetColumnName(i)
            colName = colName.tolowercase
            fValue = cur.GetString2(col)
'you need to trap NULL values
res.Put(colName, fValue)
'update the records list with each record map
records.add(res)
        Next
        If Limit > 0 And records.Size >= Limit Then Exit
    Loop
    cur.Close
'add records to the editableTV
for each record as map in records
 lstRecords.AddRowM(-1,record)
next
End Sub

 

[micro]

Thanks mashiane, now i try.

 

[micro]

Ok work, just a few corrections

Public Sub ExecuteEditableTV(jSQL As SQL, Query As String, StringArgs() As String, Limit As Int, lstRec As EditableTV, fr As Form, primaryKey As String)
    lstRec.SetParentForm(fr,True)
    lstRec.SetReportViewer
    lstRec.PrimaryKey = primaryKey
    lstRec.Clear
    Dim cur As ResultSet
    Dim colName As String
    Dim fValue As String
    Dim records As List
    records.initialize
    If StringArgs = Null Then
        Dim StringArgs(0) As String
    End If
    'get the columns and create the headers
    cur = jSQL.ExecQuery2(Query, StringArgs)
    For i = 0 To cur.ColumnCount - 1
        colName = cur.GetColumnName(i)
        'assuming your records are all texts
        lstRec.AddTextBox(colName,200,colName,"")
    Next
    'set the columns
    lstRec.RefreshColumns
    'now load the data
    Do While cur.NextRow
        ' each record has to be a map
        Dim res As Map
        res.Initialize
        For col = 0 To cur.ColumnCount - 1
            colName = cur.GetColumnName(col)
            colName = colName.tolowercase
            fValue = cur.GetString2(col)
            'you need to trap NULL values
            res.Put(colName, fValue)
        Next
        'update the records list with each record map
        records.add(res)
        If Limit > 0 And records.Size >= Limit Then Exit
    Loop
    cur.Close
    'add records to the editableTV
    For Each record As Map In records
        lstRec.AddRowM(-1,record)
    Next
    lstRec.Refresh
End Sub

The problem is that it goes into OutOfMemoryError with a Table of 15 columns and around 4000 rows (some cells even empty)
java.lang.OutOfMemoryError: Java heap space
If I decrease the limit, (< 2600) works but is slow (almost 10 seconds).
You can improve it?
Thanks

 

[Mashiane]

Hi @micro , the memory issue is an known problem that I have been trying to attend to especially with large data sets. Dololo at the moment. 4000 rows is a lot of data!!! perhaps what you can try is to have a pager in your UI, perhaps just two buttons for prev and next page.

Split your records into pages e.g. 10 pages and each page show just the number of enough records. You can tweak your SQL calls to show starting from record A to record Z, then link the page to two buttons, go forward and go previous, running the sql call and clearing the tableview and reloading with those records.

For now, that is my suggestions. Remember, each textbox etc is created in memory during runtime. I am using this control in my Pen & Paper project and the slowness currently is due to this editable grid and I cant let it go well as it makes everything else smooth. Try the paging approach, I'm sure it will work. Do you need to load so much data at once?

 

[micro]

Do you need to load so much data at once?

No, sure.

Split your records into pages e.g. 10 pages and each page show just the number of enough records. You can tweak your SQL calls to show starting from record A to record Z, then link the page to two buttons, go forward and go previous, running the sql call and clearing the tableview and reloading with those records.

I will try with more pages and with next and back button.

You have an example already ready with the split into pages?
Thanks

 

[Mashiane]

You have an example already ready with the split into pages?
Thanks

Ok, do that, no not yet and now that you have highlighted it, when I make time, I'm sure I will also add it, not anytime soon though, sorry.

 

[roberto64]

Hello Mashiane, I wanted to tell you that I'm trying to make sure that within the editableTV there were 2 comboboxes but the first combobox inside selecting a data displays the 2 combobox and so to show the data.
regards

 

[Mashiane]

Hello Mashiane, I wanted to tell you that I'm trying to make sure that within the editableTV there were 2 comboboxes but the first combobox inside selecting a data displays the 2 combobox and so to show the data.
regards

You need to ensure that the comboboxes have unique names and are loaded with the different lists you want. Or else please upload a small app for me to check.

 

[roberto64]

Hello Mashiane, if you can do some calculations in the price column for quantities, the combos are unique, one opens the table telegoricevute and the other the table tTipocalibro, when I select in the table TelencReceived a certain rexord i opens the combo to select a record.
regards

 

[Mashiane]

@roberto64 , damn, I'm only noticing this now, 3 years later. Hey mate, the new B4XTable probably is more easier to use than this now. I needed this then. A lot has evolved here. All the best! Sorry I couldnt help then.