Share My Creation TableView: Magical Inline Editing

Discussion in 'B4J Share Your Creations' started by Mashiane, Dec 22, 2017.

  1. Mashiane

    Mashiane Expert Licensed User

    Hi there

    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..

    Code:
    '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

    Export2Excel.png

    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.

    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 StringAs 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

    Code:
    Private lstRecords As EditableTV
    Then, add columns to the tableview that you will be processing, these indicate the type of column, the width, etc

    Code:
    '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...

    Code:
    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...

    Code:
    '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...

    Code:
    '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...

    Code:
    '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.
     
    Last edited: Apr 2, 2018
  2. micro

    micro Well-Known Member Licensed User

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

    Mashiane Expert Licensed User

    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.

    Code:
    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(0As 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
     
    joulongleu likes this.
  4. micro

    micro Well-Known Member Licensed User

    Thanks mashiane, now i try.
     
  5. micro

    micro Well-Known Member Licensed User

    Ok work, just a few corrections
    Code:
    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(0As 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
     
  6. Mashiane

    Mashiane Expert Licensed User

    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?
     
    joulongleu likes this.
  7. micro

    micro Well-Known Member Licensed User

    No, sure.
    I will try with more pages and with next and back button.

    You have an example already ready with the split into pages?
    Thanks
     
    joulongleu likes this.
  8. Mashiane

    Mashiane Expert Licensed User

    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.
     
    joulongleu likes this.
  9. roberto64

    roberto64 Active Member Licensed User

    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
     
  10. Mashiane

    Mashiane Expert Licensed User

    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.
     
  11. roberto64

    roberto64 Active Member Licensed User

    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
     

    Attached Files:

Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice