Blog

All Blog Posts  |  Next Post  |  Previous Post

Extend TMS WEB Core with JS Libraries with Andrew: Tabulator Part 5: Editing Data in Tabulator

Bookmarks: 

Tuesday, June 28, 2022


TMS Software Delphi  Components

In our continuing Tabulator adventures, last time out we barely scraped the surface when it comes to interactions.  We had a little side adventure into tooltips, potentially applicable to many TMS WEB Core projects.  And we got quite a bit further down the path of making our example project, Actorious, more useful. Grid-type controls are a natural fit for effectively organizing and displaying all kinds of data.  But in some cases, we'll actually want to give the user the ability to make changes to the data, either by editing it directly or by using other elements on the page.  In this article, we're going to primarily be looking at this special subcategory of interactions and how to get the most out of Tabulator when building editing interfaces into your TMS WEB Core projects.

Motivation.

As we discussed at the outset of this Tabulator miniseries, the basic concept behind it and similar JavaScript grids is that they ultimately have a bit of a self-centered view of the world. Meaning that they are, in effect, the entire package of data and UI elements.  As developers, we're basically connecting them up at a pretty high level, providing them with data and letting them do as much of the work as possible from that point on.  When it comes to editing data, this doesn't really change. Tabulator, in particular, has many options related to editing data, which we'll get into shortly.  The potential problem, though, is in keeping track of what it is doing in case you need to take those changes and apply them elsewhere, like in the actual data source that you're using. From a Delphi standpoint, it might help to think of a JavaScript grid as a disconnected TClientDataSet.  You're basically giving it a block of data.  And when it's done, you're getting a block of data (the changes) back.  What format that communication takes is entirely up to you, and we've got a few options, as usual. The mechanisms that you ultimately end up using will hopefully result in a very cohesive and highly integrated and performant product.  So let's have a look at some of those options.

Column Definitions. Again.

As with so many things in Tabulator, the easiest way to get started is by adding some options to the Tabulator column definitions.  By default, columns are not editable at all, but can easily be set to be editable.  The only question is what kind of editor do you want?  Let's start with a simple new example project, TabulatorEditor, and then work our way first through a few of the built-in editors.  Quick and easy.  The usual Project.html additions can be used to load the Tabulator library.  If you're using the JSDelivr CDN, it also offers the ability to combine many libraries into a single request.  We're going to end up using the latest version of Bootstrap, Luxon and FlatPickr, so why not combine them all.  Here's what it looks like.

    <!-- This is the combined request to JSDelivr -->
    <script src="https://cdn.jsdelivr.net/combine/npm/tabulator-tables@5,npm/luxon@2,npm/flatpickr@4,npm/bootstrap@5.2.0-beta1/dist/js/bootstrap.bundle.min.js"></script>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/combine/npm/tabulator-tables@5/dist/css/tabulator.min.css,npm/flatpickr@4/dist/flatpickr.min.css,npm/bootstrap@5.2.0-beta1/dist/css/bootstrap.min.css">

In our WebFormCreate procedure, we can then create a basic Tabulator setup similar to what we've done previously. In this case, there are just a bunch of different field data types, so we can play around with how the editors might work. The Tabulator Documentation has plenty of examples, so we're not going to cover all of them in detail.  But there are a few that are worth examining a little more closely. In the Delphi IDE, we'll start with a TWebHTMLDiv dropped onto the form, with the Name and ElementID properties set to "divTabulator" as usual.  As a small side-note, it is actually quite important that the ElementID property be set both for the Tabulator DIV and everything upstream from there.  So if you've got a Tabulator table embedded in a TWebPageControl, which is then embedded in a TWebPanel, which is itself also embedded in another TWebPageControl that is sitting inside  a TWebScrollbox.... Well, hopefully you get the idea.  All of those parent elements should have an ElementID explicitly set, from the Tabulator element all the way up to the form.  It might work fine without doing this, but certain odd behaviors might result - like column dragging won't work or something super-obscure.  Easily fixed, but best to get in the habit of doing that as we go along. This applies to other JS libraries as well, not just Tabulator. In any event, here's the initial table definition.

unit Unit1;

interface

uses
  System.SysUtils, System.Classes, JS, Web, WEBLib.Graphics, WEBLib.Controls,
  WEBLib.Forms, WEBLib.Dialogs, Vcl.Controls, WEBLib.WebCtrls;

type
  TForm1 = class(TWebForm)
    divTabulator: TWebHTMLDiv;
    procedure WebFormCreate(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    tabEdit: JSValue;
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.WebFormCreate(Sender: TObject);
begin

  asm
    var data = [{"ID":1, "NAME":"Franky",  "PLANET":"Mars",    "COLOR":"Red",         "DOB":"1922-02-02", "QUALITY":14.2,     "PROCESSED":false },
                {"ID":2, "NAME":"Billy",   "PLANET":"Venus",   "COLOR":"Blue",        "DOB":"1942-04-04", "QUALITY":110.0,    "PROCESSED":false },
                {"ID":3, "NAME":"Bobby",   "PLANET":"Mars",    "COLOR":"Green",       "DOB":"1962-06-06", "QUALITY":88,       "PROCESSED":false },
                {"ID":3, "NAME":"Andy",    "PLANET":"Jupiter", "COLOR":"Yellow",      "DOB":"1982-08-08", "QUALITY":24.22222, "PROCESSED":false },
                {"ID":3, "NAME":"Leonard", "PLANET":"Pluto",   "COLOR":"Perriwinkle", "DOB":"2002-10-10", "QUALITY":-7.5,     "PROCESSED":true  }];

    this.tabEdit = new Tabulator("#divTabulator", {
      layout: "fitColumns",
      movableColumns: true,
      resizeColumns: true,
      data: data,
      columns: [
        {title: "ID", field: "ID"},
        {title: "Name", field: "NAME"},
        {title: "Home", field: "PLANET"},
        {title: "Style", field: "COLOR"},
        {title: "Birthday", field: "DOB"},
        {title: "Quality", field: "QUALITY"},
        {title: "Ready", field: "PROCESSED"}
      ]
    });

  end;
end;

end.

In this example, we've also defined a form variable, tabEdit, which we'll be using to reference the table later.  With this basic setup in place, we've got a table that looks like the following.  No editing yet, but you can sort columns, rearrange the column order, and have a few basics like some column totals and counts. Don't pay any heed to that odd-looking sum - typical computer math at this point.  If you don't specify what you want, JavaScript tends to default to things you might not expect.  Try adding some leading zeroes to a number and see what happens!  In any event, we'll get to that in due course, and everything else is pretty much the same as we've covered previously.  
 
TMS Software Delphi  Components

TabulatorEditor Starting Point

Editing data, then, is a matter of supplying Tabulator with a bit of information about what kind of editing is expected for a particular column, via a column's editor property.  And, for most editors, there are also a number of editorParams that can be supplied, to refine things a little further.  If we wanted to be able to edit the NAME field, we can update its column definition like this.

{title: "Name", field: "NAME", editor: true}

Tabulator will figure out all its own that it is a text field, and thus give you a text editor that works the same way as a traditional INPUT field.  We can then add parameters to further constrain what someone might enter into this field.  Masks can be set here, for exmaple.  But we're only going to add a simple restriction, limiting the input to 20 characters. Also, while I'm generally onboard with Delphi code formatting conventions, things in JavaScript can get out of hand pretty quickly.  Seeing the nesting is important.  Seeing a bunch of rows with just a single closing } is perhaps less so.  So bear with me as we go through some of these examples.
 
        {title: "Name", field: "NAME",
          editor: true, editorParams: {
            elementAttributes:{
              maxlength:"20"
        }}},

TMS Software Delphi  Components  
Simple Text Input

By default, any changes are automatically accepted when leaving the field.  We'll look at that in more detail a bit later.  Naturally, as soon as the text is changed, nothing further has to be done in terms of updates for the table to use the data.  Sorting automatically picks up the changes. Column calculations are recalculated automatically. And to get the new value as a quick and simple approach, there is a cell callback function that can be added to the column definition.  Within that callback, a Delphi function can be invoked as well.  There is a small catch, though.  When Tabulator invokes these kinds of functions (or when using Tabulator events, or in any event-triggered JavaScript, really) the context sometimes changes.  So referencing Delphi code is a little confusing, and it is already plenty confusing to begin with.  So here, in JavaScript, we just define a variable that points at the Delphi function, before this scope changes, and then we can use that and not have to think about it too much. Because there are little bits and pieces, here's the whole thing again.  The function called is purely Delphi.  And the reference to it is created before the table is created.

unit Unit1;

interface

uses
  System.SysUtils, System.Classes, JS, Web, WEBLib.Graphics, WEBLib.Controls,
  WEBLib.Forms, WEBLib.Dialogs, Vcl.Controls, WEBLib.WebCtrls;

type
  TForm1 = class(TWebForm)
    divTabulator: TWebHTMLDiv;
    procedure WebFormCreate(Sender: TObject);
    procedure NameChange(ID:Integer; OldName: String; NewName: String);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;
  tabEdit: JSValue;

implementation

{$R *.dfm}

procedure TForm1.WebFormCreate(Sender: TObject);
begin

  asm
    // This is so we can use NameChange() later without having to worry about scope and context and all that fun stuff
    var NameChange = this.NameChange;

    var data = [{"ID":1, "NAME":"Franky",  "PLANET":"Mars",    "COLOR":"Red",         "DOB":"1922-02-02", "QUALITY":14.2,     "PROCESSED":false },
                {"ID":2, "NAME":"Billy",   "PLANET":"Venus",   "COLOR":"Blue",        "DOB":"1942-04-04", "QUALITY":110.0,    "PROCESSED":false },
                {"ID":3, "NAME":"Bobby",   "PLANET":"Mars",    "COLOR":"Green",       "DOB":"1962-06-06", "QUALITY":88,       "PROCESSED":false },
                {"ID":4, "NAME":"Andy",    "PLANET":"Jupiter", "COLOR":"Yellow",      "DOB":"1982-08-08", "QUALITY":24.22222, "PROCESSED":false },
                {"ID":5, "NAME":"Leonard", "PLANET":"Pluto",   "COLOR":"Perriwinkle", "DOB":"2002-10-10", "QUALITY":-7.5,     "PROCESSED":true  }];

    this.tabEdit = new Tabulator("#divTabulator", {
      data: data,
      layout: "fitColumns",
      movableColumns: true,
      columns: [
        {title: "ID", field: "ID", bottomCalc: "count"},
        {title: "Name", field: "NAME",
          editor: true, editorParams: {
            elementAttributes:{
              maxlength:"20"}},
          cellEdited: function(cell){
            NameChange(cell.getRow().getCell('ID').getValue(), cell.getInitialValue(), cell.getValue());
        }},
        {title: "Home", field: "PLANET"},
        {title: "Spacesuit Style", field: "COLOR"},
        {title: "Birthday", field: "DOB"},
        {title: "Quality", field: "QUALITY", editor:true, bottomCalc: "sum"},
        {title: "Ready", field: "PROCESSED"}
      ]
    });

    end;
end;

procedure TForm1.NameChange(ID:Integer; OldName: String; NewName: String);
begin
  console.log('ID#'+IntToStr(ID)+' Name changed from '+OldName+' to '+NewName);
end;

end.

Simple Lookup.

Editing text is easy enough, but often it is easier to pick elements from a list.  In its simplest form, there is a text field and we want the user to pick from a list of predefined text values. In our example, the next column has entries that are potential futuristic resident locations.  Nobody has to know we're storing values of 'Pluto' in a field called 'PLANET' because the user will never see it! Joking aside, we'd like this to be a list of choices.  One of Tabulator's very recent updates changed this functionality to use what what it now refers to as a "list" editor.  Which probably has the most options of any Tabulator feature that I can readily recall.  Good to have options.  But let's try some simpler variations first.  Adding the column definition with a few fixed choices is easy enough.

        {title: "Home", field: "PLANET",
          editor: "list", editorParams: {
            values: ['Mars', 'Venus', 'Jupiter', 'Mercury', 'Pluto', 'Saturn', 'Moon', 'Io','L2', '],
            sort: 'asc'
        }},

The layout of the popup that is used to present the chocies can be, as with everything in Tabulator, styled via CSS. Using the default style without changing any CSS gets us a pretty plain-looking but functional list to choose from.
 
TMS Software Delphi  Components

Tabulator Simple Lookup using Lists

Beyond simple lists, there are almost too many options for what else can be done here.  Lookup elements can be retrieved from another column, from elements existing in the current column, from a custom function, from a URL, from absurdly complex (but sometimes entirely necessary) structured item definitions, and on and on.  There are also options for selecting multiple values, clearing values, entering brand new values, whether or not values are required and more. And while I'd normally delve into more complex examples of some of these, really, I couldn't do a better job than what the documentation does in outlining the options available.  This essentially replaces a handful of our favorite Delphi-style controls.  Anything remotely related to a combobox or a lookupcombobox is readily available here.  In the above example, the field storing the data was a text field already.  But if it were a numeric field (Earth = 1, Moon = 2, etc.) the same "list" editor would be used with a different assortment of editorParams to get everything organized and in sync.  And the same cellEdited function we looked at earlier can be used to get the new data from the table when it is entered. 

Date Editor.

While there are numerous editors supported out-of-the-box, there is one conspicuously missing - a date editor.  Probably because everyone has their favorite date pickers already, so no need to reinvent a very complex wheel. Our date picker of choice happens to be FlatPickr of course. We can define a custom editor and then pass data back and forth to a flatpickr control that is instantiated just for this purpose.  Seems like that might be a lot of overhead, but in practice it works pretty well.  In our DOB field's column definition, we can just specify a custom editor by giving it a name (a function).  And then define the function, being sure to add the function before the Tabulator table definition, or it won't be able to find it.  First, let's setup the column definition for the editor.

        {title: "Birthday", field: "DOB", editor: dateEditor}

Then, the code for the custom editor.

    var dateEditor = (cell, onRendered, success, cancel, editorParams) => {
      var editor = document.createElement("input");
      editor.value = cell.getValue();

      var date = new Date();
      var minDate = new Date("2000-01-01");
      var maxDate = new Date(date.getFullYear(), date.getMonth() + 2, 0);

      var datepicker = flatpickr(editor, {
        minDate: minDate,
        maxDate: maxDate,
        weekNumbers: true,
        onClose: (selectedDates, dateStr, instance) => {
          success(dateStr);
          instance.destroy();
        },
      });
      onRendered(() => {
        editor.focus();
       });
      return editor;
    };

There's a lot going on  in this function.  The declaration shows what Tabulator is passing into it.  And the definition of the FlatPickr instance is something we've seen before.  Setting limits on the dates available, or even enabling/disabling individual dates or any of the other FlatPickr customizations can be handled just like any other FlatPickr instance.  The extra bits are to handle the lifecycle of the FlatPickr instance as it relates to Tabulator - creating and destroying it as needed by the editor function invocation.  And of course returning a new value via success(dateStr) if one is selected.

TMS Software Delphi  Components  
Tabulator using FlatPickr 

Theming for FlatPickr can be handled just as any normal FlatPickr theming would be done.  One might choose a much smaller layout here to have it serve more as a traditional lookup (as small as possible) but whatever fits into the overall theme would be best.  The latest iteration of the Actorious app shows a little more customization of FlatPickr in this way.  Nothing too fancy though.
  

Numeric Data.

Seems not that long ago that Tabulator didn't have an editor for numeric data.  Just an example of how it is currently under active development and continues to evolve in new and useful directions. For our Quality column, then, we're after a simple numeric editor. We can also format existing data (and the footer total) to show just one decimal place, to help cover for what perhaps might have been a less capable data entry system. Tabulator has options for the editor as we've covered to some degree, but there is also a suite of validation options as well.  These can be used to ensure that incoming data is properly formatted, with yet more options to determine how rigorous the data entry process is.  For example, at the table level, a "validationMode" can be set to manage whether any data can be accepted, whether it is just flagged as invalid, or whether more restrictive data entry rules are in place.  For our purposes, we're just looking to enter a numeric value, and then display them consistently, including in the footer.

        {title: "Quality", field: "QUALITY", validator:"float", editor:true, hozAlign: "right", bottomCalc: "sum",
          validator:"float",
          formatter: "money", formatterParams:{
            decimal: ".",
            thousand: ",",
            precision: 1},
          bottomCalcFormatter: "money", bottomCalcFormatterParams:{
            decimal: ".",
            thousand: ",",
            precision: 2
        }},

TMS Software Delphi  Components  
Numeric Input and Validation

Boolean Data.

There are plenty of situations where a boolean value is needed.  This might be a yes/no situation. Or true/false.  Or any of a number of other pairings.  In Tabulator, this kind of thing is referred to as a "tickCross" formatter (or editor).  If we set the formatter to use it, the editor will as well, just by setting editor to true.  A little formatting for the cell, and we've got a simple check mechanism ready to go.

        {title: "Ready", field: "PROCESSED", formatter:"tickCross",editor: true, hozAlign:"center"}
 
TMS Software Delphi  Components

Editing Boolean Fields

In practice, though, this is kind of kludgy as you have to click on the cell to edit it, then edit it (check or uncheck), then click somewhere else (or hit enter) to have the change take effect.  A nicer experience can be had by using a cellClick event and just changing the value directly in the table instead.  The look is exactly the same, but the experience is entirely different.  With this approach, you just need to click on the icon and it changes.  Much nicer.

        {title: "Ready", field: "PROCESSED", formatter:"tickCross", hozAlign:"center",
          cellClick:function(e,cell){
            cell.setValue(!cell.getValue());
        }}

In terms of styling, there are a number of ways to handle this.  CSS overrides are one way.  There is also the option of setting distinct values for true/false and having those values actually be the icons themselves.  Data coming into the table can be passed through a mutator function to set these as the default values.  And of course they can be styled using whatever icon set you'd like.  Also, all of this works the same way if you'd rather have two other values.  Like dogs/cats or something like that.  Yet more options cover off scenarios like whether a value is required or a non-existent value is acceptable (tri-state).

Moving Rows.

Another type of data that sometimes is easy to overlook is the row order.  Maybe the rows are sorted by name or by some other field.  But sometimes the data is the ordering itself.  Maybe it is a list of preferences, for example, and the ordering is the data that you're after. To help with this, there's a table-level option called 'movableRows' that can be enabled.  And to give you something to grab onto (n case all the fields are editable) there's an option for a column just for this purpose.  The initial part of our table definition can be updated like this.

    this.tabEdit = new Tabulator("#divTabulator", {
      data: data,
      layout: "fitColumns",
      validationMode:"highlight",
      movableColumns: true,   
      movableRows: true,
      columns: [
        {rowHandle:true, formatter:"handle", headerSort:false, frozen:true, width:30, minWidth:30},
        {title: "ID", field: "ID", bottomCalc: "count"},
        {title: "Name", field: "NAME",
      ...

Which gets us the following table.  Dragging the icon in the first column up or down reorders the rows.

TMS Software Delphi  Components  
Reordering Rows

In order to make use of this information, the ID field can be used, along with its position within the table.  This position then needs to be stored as a separate field elsewhere, and then used as the primary sort value for the data subsequently.  Also makes little sense to do this without limiting the sorting to that one column, whether it is visible or not. 

Just the Basics?

Yep.  This time, that's all we're out to cover.  A bunch of basics related to editing in Tabulator. Each one of the sections we've covered could easily be expanded into a full-length (and a substantial length at that) article.  So if that's of interest, by all means please post a comment about what you're after.  And there are other editors, like "stars", "range" or the progress bar editor, and no end to the possibilities with the custom editors. Generally, editing data is a less frequent occurrence than viewing data, and definitely a situation where simpler is better.  Combining formatters and editors in creative ways, and overriding the default behavior like we did with the checkboxes, or bringing in entirely new capabilities as we did with FlatPickr, there are plenty of ways to build very capable editing functions into your project. Next time out, we'll look at how to get all this data out of Tabulator and back into your database or other destination for storage.  And we'll also look at how to export data to various places, including XLS, PDF and CSV files, as well as to the clipboard.




Andrew Simard.




Andrew Simard


Bookmarks: 

This blog post has not received any comments yet.



Add a new comment

You will receive a confirmation mail with a link to validate your comment, please use a valid email address.
All fields are required.



All Blog Posts  |  Next Post  |  Previous Post