B4J Library [BANanoVueMaterial]: The first complete opensource VueJS UX based framework for BANano

Mashiane · Jul 9, 2020

DataTable - Persisting changes when input elements change on the table.

Assuming... we have loaded these fields to a table from a database, how does one persist the changes made to a database when...

1. A switch value changes.
2. A checkbox value changes
3. A star value changes or
4. A progress value changes.

We added a switch that was linked to a database table column named "active"

B4X:

dtvuetifyattributes.AddSwitch("active", "On Sub Signature")

The field on the database table is named "active", so when this value changes to true/false, we want to update the database.

So when ever a value changes, just like edit / delete, an event is fired. The table name is "dtvuetifyattributes" and the field name is "active", so this event is fired.

B4X:

Sub dtvuetifyattributes_active(item As Map)

And what is passed to it is the current record being processed.

To back-track a little, we added these columns to the table.

B4X:

dtvuetifyattributes.AddIconView("icon", "Icon")
    dtvuetifyattributes.AddColumn1("name", "Name", "text",0,False,"start")
    dtvuetifyattributes.AddColumn1("type", "Type", "text",0,False,"start")
    dtvuetifyattributes.AddColumn1("default", "Default", "text",0,False,"start")
    dtvuetifyattributes.AddColumn1("description", "Description", "text",0,False,"start")
    dtvuetifyattributes.AddSwitch("onsub", "On Sub")
    dtvuetifyattributes.AddSwitch("active", "On Sub Signature")

These are linked to a database table and our original query to select the records from the db was.

B4X:

Dim rsAttributes As BANanoAlaSQLE
    db.OpenWait("bvmdesigner", "bvmdesigner")
    rsAttributes.Initialize("vuetifyattributes", "name")
    Dim qw As Map = CreateMap()
    qw.put("component", menuID)
    rsAttributes.SelectWhere(Array("*"), qw, Array("="), Array("name"))
    rsAttributes.result = db.ExecuteWait(rsAttributes.query, rsAttributes.args)
    rsAttributes.FromJSON
    'save records to state
    vm.SetData("vuetifyattributes", rsAttributes.Result)
    'update the data table records
    dtvuetifyattributes.SetDataSource(rsAttributes.Result)
    dtvuetifyattributes.SetPage("1")

So back to what happens when a switch changes. The event below is fired, receiving key value pairs for the record being processed.

B4X:

Sub dtvuetifyattributes_active(item As Map)
    'get the key
    Dim skey As String = item.get("key")
    'update the database
    db.OpenWait("bvmdesigner", "bvmdesigner")
    Dim compSQL As BANanoAlaSQLE
    compSQL.Initialize("vuetifyattributes", "key")
    compSQL.Update1(item, skey)
    compSQL.result = db.ExecuteWait(compSQL.query, compSQL.args)
    compSQL.FromJSON
End Sub

We read the key column / field value (this is not displayed on the table but is part of the fields in the table)
We open the table we want to process and pass it the record saved on item using the key.

Mashiane · Jul 9, 2020

Core 4.17 is out!

Vuetify updated to V2.3.4
Added the above functionality to tables.
Updated Core Demo Data-Table examples

Enjoy!

Mashiane · Jul 15, 2020

Upcoming Changes to the Designer

One of the advantages of using the designer is because of CRUD functionality. This means you after you link your backend (SQLite, MySQL, MSSQL) you can then easily create user interfaces based on your database schema for CRUD to your database of choice.

Before, each time one changed a property in the property bag, the stage reflected the new changes. This has not worked optimally for some instances so this is a manual event now. As soon as you click the refresh button in the toolbar, your stage is refreshed with your changes.

Handling database schemas.

When designing forms based on database schemas, at times you need to define everything about the form easily. The "Schema" section has been updated for one to be able to do some mandane task using the switches.

For example cust_no.

We don't want this field to be listed on the data-table, its not required and it should be available on the dialog input screen but invisible.

For the cust_name, we want this to show on the data-table etc.

The changes ones makes with on the schema are saved and once the stage is "refreshed", the controls will be created and shown on the stage.

Color coded buttons

For data-tables, the edit and delete and other action buttons will now come color coded. We have added this to the designer to enhance your UI and your generated source code for your data-aware app comes with this built in. As an example this code will be generated...

B4X:

dtAgents.SetEdit(True)
    dtAgents.SetDelete(True)
    dtAgents.SetIconDimensions1("edit", "24px", "success", "80")
    dtAgents.SetIconDimensions1("delete", "24px", "error", "80")

Case Sensitivity

JavaScript is case sensitive, thus anything in the designer is case sensitive, especially the JSON returned from your database. When importing databases, its important that the field names in your tables are all LOWERCASE and does not have special characters like -, _ etc., if this is not the case, you can activate this feature each time you run a select from your tables.

The generated SelectAll_??? code by the designer now adds an extra method call to the SelectAll_?? method. This is

B4X:

rsAgents.result = vue.MapKeysLowerCaseList(rsAgents.result)

NB: YOU CAN COMMENT THIS LINE OUT IF ALL THE FIELDS IN YOUR BACKEND-TABLE ARE LOWERCASE.

Ta!

Mashiane · Jul 15, 2020

Core 4.18 Now available.

Enjoy!

Mashiane · Jul 15, 2020

This is rather interesting...

Mashiane · Jul 15, 2020

Mashiane said:
Core 4.18 Now available.

A bug was discovered that makes data-table saves on property bag not to update when imported from the database.

Please re-download

Ta!

Mashiane · Jul 17, 2020

Upcoming Updates - Data-table inline editing with the edit-dialog

One can also use these on their apps.

1. Add columns to the data-table as you would.

B4X:

dtschema.AddColumn("key", "Name")
    dtschema.AddColumn("title", "Label")
    dtschema.AddColumn("collength", "Length")
    dtschema.AddColumn("coldatatype", "Field Type")
    dtschema.AddColumn("colrow", "R")
    dtschema.AddColumn("colcolumn", "C")

2. Add the edit dialog to the columns one needs to be edited

B4X:

dtschema.AddEditDialog("title",False)
    dtschema.AddEditDialog("collength",False)
    dtschema.AddEditDialog("colrow",False)
    dtschema.AddEditDialog("colcolumn",False)

3. Add events to the data-table (These are compulsory)

B4X:

Sub dtschema_save(item As Map)
    Log("save")
    Log(item)
End Sub

Sub dtschema_cancel(item As Map)
    Log("cancel")
    Log(item)
End Sub

Sub dtschema_open
 
End Sub

Sub dtschema_close

End Sub

Ta!

Mashiane · Jul 17, 2020

A combo box / auto complete can also be added..

In this example we add a column for the foreign table column..

Add the columns

B4X:

dtschema.AddColumn("colforeigntable", "ForeignTable")
    dtschema.AddColumn("colforeignkey", "ForeignKey")
    dtschema.AddColumn("colforeignvalue", "ForeignValue")

Add the combo selectors - These are activated once you click on the row column

B4X:

dtschema.AddEditDialogCombo("colforeigntable",False,"tablenames","tablename","tablename",False)
    dtschema.AddEditDialogCombo("colforeignkey",False,"fields","key","key",False)
    dtschema.AddEditDialogCombo("colforeignvalue",False,"fields","key","key",False)

For the foreign table, the data source should be a list of maps saved with state name "tablenames", the key field will be a map key named tablename with also the display value.

This works the same as the NewSelectCombo / NewSelectAutoComplete component.

In this example, when we change the value in the foreign selection, we want to update the "fields" state where the other combos will get their content from.

B4X:

'change the foreign table, update the other 2 columns
Sub dtschema_colforeigntable_change(item As Map)
    'get the table name to process
    Dim scolforeigntable As String = item.get("colforeigntable")
    If scolforeigntable = "" Then Return
    '
    Dim prjSQL As BANanoAlaSQLE
    db.OpenWait("bvmdesigner", "bvmdesigner")
    prjSQL.Initialize("tables", "tablename")
    prjSQL.Read(scolforeigntable)
    prjSQL.Result = db.ExecuteWait(prjSQL.query, prjSQL.args)
    prjSQL.fromJSON
    '
    Dim Result As List = prjSQL.Result
    Dim rec As Map = Result.Get(0)
    Dim fieldsJSON As String = rec.get("fields")
    Dim fields As List = BANano.FromJson(fieldsJSON)
    vm.setdata("fields", fields)
End Sub

In this example, we read a field from a BANanoSQL table, this contains a JSON string with field properties. We convert this into a list and save it.

The colforeignkey and colforeignvalue columns above will get their contents from the new list.

Ta!

Mashiane · Jul 18, 2020

Core 4.20 out with this important update.

Mashiane · Jul 19, 2020

Hooo haaaa!!!

One of the important things when importing databases to the designer is to ensure that all date fields are actually text fields. The PHP prepared statements are only using these bound parameters

/* Bind parameters. Types: s = string, i = integer, d = double, b = blob */, you need to convert your date data type to string for all dates in the database.

So here we highlight the date-time fields after the import with a red background to indicate an error.

Reproduction

You create the table a normal way, then

B4X:

dtschema.SetItemClass("checkDateTime")

The above code tells the app that for each item, apply this callback. For each item we check a particular field and if that field matches, we return the class to use.

B4X:

'mark row as red if we have dates
Sub checkDateTime(item As Map) As String
    Dim scoldatatype As String = item.get("coldatatype")
    Select Case scoldatatype
    Case "date", "datetime"
        Return "red"
    End Select
End Sub

Ta!

PS: Will be added in the next update.

Mashiane · Jul 19, 2020

Handling Data-Table Date Displays

The recommended way to save dates is using a varchar data type for your table in yyyy-mm-dd format. This makes it easy to sort and search.

However you might want to display dates differently in your data-table.

With this code for example

B4X:

dtInsurancePolicies.SetColumnDateFormat("startdate", "dd/MM/yyyy")

The output date shows 22/01/2020 for example whilst its saved as 2020-01-22 in your backend.

Ta!

Mashiane · Jul 21, 2020

Demonstrating how inline editing with a text area works.

NB: To persist your changes, you need to save the content.

In this example of the designer, we are specifying the items that will be used by the combo box, but these will be sourced from a key,value field. So we specify the keys separated by , and values separated by , also.

You add the column the normal way to the data-table and then also add this.

B4X:

dt.AddColumn("colkeys", "Item Keys (,)")

Then

B4X:

dt.AddEditDialogTextArea("colkeys", True)

Ta

Mashiane · Jul 23, 2020

BANanoVuetifyDesigner - Importing Database Table Field Descriptions From Excel

NB: It's better just to follow this approach discussed here as it eliminates errors

B4J Library - [BANanoVueMaterial]: The first complete opensource VueJS UX based framework for BANano

DataTable - Persisting changes when input elements change on the table. Assuming... we have loaded these fields to a table from a database, how does one persist the changes made to a database when... 1. A switch value changes. 2. A checkbox value changes 3. A star value changes or 4. A...

www.b4x.com

Core 4.23 is also available, This includes the latest version of Vuetify 2.3.5.

Ta!

Mashiane · Jul 23, 2020

Data-Table Column Visibility Toggler

Excited that I will be shooting 2 birds with one stone when it comes to his component.

Its a data-table column visibility toggler, which I will easily also adopt for reporting choosing. As its based on a list, I can add icons and other things on it.

To add this to your own data-table, include this code just before the data-table is added to the parent. It has to be added after all columns and actions are added for it to work.

In this example, the table is added to a container, so we add the column chooser before that. It does all the related magic of picking up the headers and other things from the data-table.

B4X:

dtschema.SetColumnChooser(True)    '
    contSchema.AddControl(dtschema.DataTable, dtschema.tostring, 1, 1, 0, 0, 0, 0, 12, 12, 12, 12)

You can explore it now on your projects by downloading the code from the first post.

Mashiane · Jul 28, 2020

The next version of BANanoVueMaterial features a duplicate field checker.

Check this out..

Mashiane · Jul 29, 2020

The next version of BANanoVuetify 4.25 features functionality to Export and Import database tables schema as JSON layouts.

These JSON layouts are useful when you might want someone else to update the database table layout, e.g. field title, field row and column position, field control type. Once updated, whoever is administering your database can save then export this database tables json layout for further use.

Watch this video here to understand what this means:

Mashiane · Jul 29, 2020

BANanoVuetify 4.25 is out, this includes the latest version of the Vuetify Library 2.3.6 that was updated 2 days ago.

Download from the 1st post.

Ta!

PS: I am adding data-table "Totals" row and will be available with the next version. One will be able to show row count / row sum per data-table headers with this.

swamisantosh · Jul 29, 2020

Mashiane said:
BANanoVuetify 4.25 is out, this includes the latest version of the Vuetify Library 2.3.6 that was updated 2 days ago.

Download from the 1st post.

Ta!

PS: I am adding data-table "Totals" row and will be available with the next version. One will be able to show row count / row sum per data-table headers with this.

By providing data-table Totals feature , data-table can be used as reporting tool.provision for exporting to excel,pdf will be excellent addon

Mashiane · Jul 29, 2020

IMPORTANT NOTICE: Explaining the Database Schema Layout

:You might want to set your browser to 80% on smaller screens

A maintenance update of the schema layout has been done and its available now at , https://github.com/Mashiane/BANanoVuetify

Database Schema Layouts enables one to pre-define database tables before these are converted to UI via the convert to data-table command.

What we do is the following:

1. For each field in the database table for the selected column, give it a title.
2. Define its control type e.g. combo, date, email etc.
3. Define its relationship between other tables
4. Define the boolean attributes using switches
5. Define the matrix location of the component, e.g. Row and Column.
Etc.

Mode 1 - Editable Content

There are 2 toolbars for this screen. 1 is t control the saving, export (JSON) and import (JSON) of the database schema (AS A WHOLE). If you import, all your previously defined changes about the database tables WILL BE OVERWRITTEN, be careful there.

The 2nd toolbar, is to control what you see on the table. You have 3 modes, 1. editable content, 2. switches and 3. relationships.

Available Modes (table headers change depending on the mode you have selected)

Mode 1 - Editable content (these are details like title, row, column etc)

To change details, you click the row and column to edit, an edit dialog appears and then you update press enter to save your changes. The default mode when you select a table to process is this mode as indicate above.

Mode 2 - Switches (these are for all boolean attributes)

Here you turn attributes ON / OFF

Mode 3 - relationships

With this mode one indicates the relationship between the field being processes with other tables. There is only a 1 to 1 definition relationship allowed.

Here you select the foreign table, this allows you to select the foreign key and value.

Remember, save your work, back it up by exporting it to JSON if you need to,

Ta!

Mashiane · Aug 10, 2020

Core 4.25 has been updated with Vuetify 2.3.8

Download

B4J Library [BANanoVueMaterial]: The first complete opensource VueJS UX based framework for BANano

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Member

Expert

Expert

Similar Threads