Web Core and Tabulator

Here's a bit of a run-down of my experiences using Web Core with Tabulator.

Motivation: In previous posts, I was trying to figure out how to get data to flow from an XData service endpoint through to a Web Core XDataWebDataset, ultimately with the goal of displaying it in a grid for the user to view/edit. The last iteration of that effort can be found here.

Unfortunately, getting a grid to work with that dataset proved to be problematic. And even when I did get an FNCGrid to show the data, it was seemingly going to be a tough haul to get the grid to be more useful in terms of editing, sorting, grouping, filtering and so on. And that's before even trying to tackle things like theming or custom controls within the grid or having the ubiquitous navigator attached. I'm sure there are examples of many of these things, but it seemed to be an uphill battle right out of the gate, and it wasn't getting any easier. And as I'm working on an app this is a reincarnation of sorts of a VCL app that has 490+ cxGrids, this seemed to be a bridge too far.

Enter Tabulator. Not something I had used previously at all. It is basically a pure javascript grid with support for all the things I was after. Sorting, filtering, grouping. Custom controls within the grid. No navigator, but the means to wire one up. This then is a not-so-brief introduction to what I've done along with a little demo video showing what it looks like.

First up is the usual links to the CDN.

    <script src="https://cdn.jsdelivr.net/npm/tabulator-tables@latest/dist/js/tabulator.min.js"></script>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/tabulator-tables@latest/dist/css/tabulator.min.css">
    <script src="https://cdn.jsdelivr.net/npm/luxon@latest/build/global/luxon.min.js"></script>

(Side note: This is for Tabulator v5 which just very recently came out (and was one of the reasons for the delay in writing this post - some little issues needed to be ironed out by the developer). So in v5 they use luxon as a support library for dates, replacing the moment.js library used in the previous version)

Tabulator has a reasonably complex set of options that can be set to control its behaviour. For my purposes, I'm looking for typical Delphi-type grid functionality with a single row selected at a time and the ability to perform operations on a row-based sort of level. So to start with, I set up a bunch of global defaults that apply to all Tabulator tables anywhere in my project. Something like this. Must be defined before any Tabulator tables are created. I have this in the WebDataModuleCreate procedure of the DataModule that is loaded up first.

asm
  Tabulator.defaultOptions.layout = "fitColumns";
  Tabulator.defaultOptions.selectable = 1;
  Tabulator.defaultOptions.placeholder = "No Records Found";
  Tabulator.defaultOptions.index = "ID";
end;

Tabulator really likes having an ID column as the first column, so that's what I've done with all my tables. I've generally set this up so ID=1 is the first row in the table and ID=n is the last row in the table. Important for making the navigator work properly more than anything. In the queries that I generate on the server via FireDAC, it is a simple matter of just adding that as a column regardless of what the table has in it.

In DB2 this is easy enough. Other databases are either similar or can be coerced into something equivalent. Point being that it is just an arbitrary index and adding it in at query time for me is the simplest approach. Not so fun if I had to go and do this many times, but when starting out it is fine.

select
  row_number() over (order by some.column) ID,
  column1,
  column2, 
  etc.

Alright. So to make a simple table, I've just dropped a TWebHTMLDiv on a form. I've gotten into the habit of adding a separate DIV within the HTML settings for the form, though it might be redundant. In any event, setting the HTML property to something like this. Means I can adjust the WebHTMLDiv as I want and the table within it will stretch to fit. Something like that anyway.

<div id="gridSample" style="height:100%; width:100%;"></div>

In the Interface section of the form, I add a variable to reference the Tabulator table that I'm going to create.

var
  tabSample: JSValue;

Then in the WebFormCreate procedure, a simple table can be created like this.

asm 
  var tabSampleData = [
    { ID: 1, OPTION: "Accounts" },
    { ID: 2, OPTION: "Modules"  },
    { ID: 3, OPTION: "Features" },
    { ID: 4, OPTION: "Blocks"   },
    { ID: 5, OPTION: "History"  }
  ];
  this.tabSample = new Tabulator("#gridSample", {
    data: tabSampleData,
    columns: [
      {title: "ID",     field: "ID",      width:     70, visible: false },
      {title: "Option", field: "OPTION",  minWidth: 125, widthGrow: 1, bottomCalc: "count" }
      ]
  });
end;

So in this case, a table is created with a hidden column and an Option column that stretches to fit the width of the table. A count of entries at the table appears at the bottom. All good.

The fun starts when dealing with callbacks. I tried many things to try and figure out what to put in the callback functions to call a Delphi function within the same module as the form. "this", "$mod" and so on didn't seem to work. I nominate anyone at TMS to provide a clear description of what all the options might actually be here, both for accessing functions and variables. It could be because the forms I use are created at runtime. It could be because Tabulator itself is doing something atypical. Or it could be something else entirely. What I found from poring over the generated JS code is that it is possible to call functions explicitly using a fully qualifed function call. As I want these to be centrallized anyway, I just did that and added in a table identifier so I could tell what table was calling. And then use the Delphi code to in the DataModule to call back to the functions in the form. so in the code below, my DataModule called DM1 in CoreDataModule.pas is used.

For the simple table, the callbacks look something like this. This follows the above table definition.

    this.tabSample.on("rowClick", function(e,row){
      row.getTable().deselectRow();
      row.select();
    });
    this.tabSample.on("cellClick", function(e,cell){
      pas.CoreDataModule.DM1.TabCellClick(101,cell.getRow().getPosition(true), cell.getColumn().getElement());
    });

So in my DataModule, I have a TabCellClick function defined, and its expecting a parameter telling me what table it is, as well as what the row number is. I then use that to map back to #gridSample. Works perfectly well. So at the moment, the main thing I want to happen when a user clicks on a row is to update a navigator. It has buttons like First, Previous, Next and Last.

Note that Tabulator references rows (and columns) in a bunch of different ways. It can be the physical row in the table. Or the row being displayed currently. Or the row in the current filter. Or the row can reference the data rather than a row number. Fun times. Here, we're passing the position of the row in the table, and hopefully noting where it is in the current filter/sort.

So in the DataModule functions, we start with the TabCellClick function:

procedure TDM1.TabCellClick(Grid: Integer; Row: Integer; Column: String);
var
  rowselected: Integer;
  optionselected: String;
begin
  if (Grid = 101) then
  begin
    asm 
      var table = Tabulator.findTable("#gridSample")[0];
      if (table.getDataCount() > 0) {
        var rowselected = table.getRowFromPosition(Row,true);
        table.selectRow(rowselected);
        optionselected = row.getCell(table.getColumn('OPTION')).getValue();
      }
    end;
  end;
  UpdateButtons(Grid, rowselected);
end;

This obviously can be more generalized. Note that we have to lookup the name of the table all the time. Probably a way around that. The latest v5 apparently does a better job of helping to identify the table from the callback functions. But this seems to work and doesn't seem to slow it down.

The UpdateButtons procedure is also in the same DataModule, and all it is doing is enabling or disabling buttons depending on whether the row selected is first or last or somewhere inbetween.

procedure DM1.UpdateButtons(Grid: Integer; Row:Integer);
var 
 rowcount: Integer;
begin
  if (Grid = 101) then
  begin
    asm
      var table = Tabulator.findTable(TableName)[0];
      if (typeof table !== 'undefined') {
        if (table !== null) {
          if (table !== false) {
           rowcount = table.getDataCount();
          }
        }
      }
  end;

// Row and RowCount are then used to enable/disable the buttons for
// First, Previous, Last and Next functionality.  Nothing special about this
// as we know how many rows (may be zero) and what the current row is.
// Skipping this code for now.  

end;

One little detail is that if you define Delphi variables that are only used in JS code, you'll get a compiler hint. And no clear way how to turn it off. So what I've done is create some dummy procedures to call which gets rid of the hints. For example, in the above, rowcount is declared but if you don't use it in a Delphi block of code, you'll get a compiler hint.

procedure TDM1.PreventCompilerHint(S: string);               overload; begin end;
procedure TDM1.PreventCompilerHint(I: integer);              overload; begin end;
procedure TDM1.PreventCompilerHint(J: JSValue);              overload; begin end;
procedure TDM1.PreventCompilerHint(D: TDateTime);            overload; begin end;
procedure TDM1.PreventCompilerHint(B: TJSArrayBuffer);       overload; begin end;
procedure TDM1.PreventCompilerHint(X: TXDataClientResponse); overload; begin end;

Another compiler issue is that if you have a procedure, like TabCellClick, that is only ever called by JS, the Delphi compiler tends to optimize it out of existence. This may be an element in trying to figure out why $mod.delphifunctioncall didn't find it, but perhaps not. In any event, like the PreventCompilerHint dummy calls, I also added another one for keeping these functions around.

procedure TDM1.StopLinkerRemoval(P: Pointer);                          begin end;

And in the same WebDataMOduleCreate function I have calls like this:

StopLinkerRemoval( @TabCellClick );

Full credit goes to this StackOverflow Post for this idea.

Alright. All good so far. It is possible to do much, much more though. There are more callbacks for things related to editing, for example. And I've had great success with date pickers, lookup combo boxes and other things integrated into the Tabulator interface. I'm currently using it in a way where changes to rows generate XData service calls to write changes back to the database (while also changing it locally) with all kinds of error handling and tracing functions. And it is all working pretty well. This is really and truly just the tip of the iceberg.

So without further ado, here is a lttle video capture of all of this in action. Happy to provide more code samples and explanations to anyone interested. Date controls are implemented using FlatPickr and the HTML editor is using SunEditor and CodeMirror.

Tabulator Examples

2 Likes

Awesome information, @AndrewSimard. Thank you very much for all your latest contributions. I think they're really valuable, helps other users and show how flexible TMS Web Core is. And also of course is knowledge about HTML and Javascript libraries in general.

Happy to help! I think TMS Web Core is a pretty compelling tool already, and being able to toss any old JS library at it and be productive with it right away makes it exponentially more powerful.

2 Likes

Sure I am interested in code samples and examples
Venkatesh

No problem. Take a look at the video I posted and pickout a specific item and I can do a write-up about it. There are a lot of things going on in that video, so the more specific your topic choice is, the more useful my write-up can be, as it will be more focused.

This looks very interesting. When I use tables being populated from a webbroker app I have usually used DataTables | Table plug-in for jQuery) which is incredibly flexible. I was about to start looking at how I could use this with WebCore/XData, but might now look at Tabulator. Thanks Andrew.

It would be good if we could start wrapping these things as webcore components.

I don't really have an opinion on jQuery so whether a tool is built with or without it isn't much of a concern. I suppose I'd opt for one without it, all else being equal. I guess I've been around long enough to see standards come and go. And there's a pattern - something becomes wildly successful and soon enough there's a faction that rises up to say you don't need it. NoSQL comes to mind :roll_eyes:

In this case I looked very briefly at a few data table-type projects, but once Tabulator came up on the list it was pretty obvious that it was a good match for what I was after. And I'm just barely getting started with it! Still fiddling with the UI for filters. There's a bunch of menu stuff I didn't show (like picking the visible columns in the table) and sorting and so on. Pretty robust all around I'd say.

Is there anything in the video that you were curious about? I was going to write a more exhaustive post but got distracted and thought it would be 100 pages before I was even close to being done. So I thought maybe splitting it up would be good. Maybe I'll write a book in the same style as these Dr. Flick books :thinking:

For example, in my previous posts about getting data from XData into Web Core I wrote about how I setup the StreamFormat for different things like CSV, FireDAC, JSON and so on. Turns out that the JSON that comes out of that (not the FireDAC version but the simpler "batch" version) can be dumped directly into Tabulator as-is and it does a pretty good job of figuring out everything on its own.

Andrew.

2 Likes

Also, it would be handy if there was a wrapper but there are so many points of interaction it is hard to even imagine where to begin with that. Maybe that's actually more of a justfication for having a wrapper after all - just figuring out what all the events are, for example, is a daunting task.

1 Like

A small sample program which ties up xdata tabulator and the events will be good enough as a start. No need to reinvent the wheel. Can build based on that

Thanks

Alright, here's how the actual connection happens.

In this post I covered how I go about getting data from XData into Web Core in a variety of different formats that might be useful for different purposes. I was initially interested in the FireDAC variation as it brings with it a list of field defintions that are helpful in recreating the dataset locally within Web Core that matches the original dataset created on the XData server. The idea was to simply use that communications mechanism as a means to transport the dataset into the local application.

But another approach, that I use here with Tabulator, is to get the simpler JSON variant and load that into Tabulator. In the post above, you can see how the fields are defined in Tabulator. When you import JSON, any matching fields are linked to Tabulator through this mechanism. As mentioned, it is much more pleasant if your JSON contains an ID column first that is a simple ordering of the records, but not necessarily a requirement.

The step to connect the two is then something like this.

TDM1.LoadTabulator(Endpoint: String; TabulatorTable: String);
var
  Client:    TXDataWebClient;
  Response:  TXDataClientResponse;
  TableData: WideString;
begin

  // Assuming Endpoint is your XData endpoint and there is one parameter for StreamFormat
  // as per my previous example, and it is going to return a simple JSON.
  // TabulatorTable name would be something like "#gridSample" from the previous example.

  Client := TXDataWebClient.Create(nil);
  Client.Connection := //Connection to your XData Server
  Response := await(Client.RawInvokeAsync(Endpoint, ['JSON'])); 

  TableData := string(Response.Result);

  asm
    var table = Tabluator.findTable(TabulatorTable)[0];
    table.replaceData(JSON.parse(TableData));
  end;

  Client.Free;
  PreventCompilerHint(TableData):
end;

That's really all there is to it. Much more can be done in terms of error-checking or making the LoadTabulator function work with a wider array of endpoints with different parameters and so on, but the gist of it is the same. Maybe this can be improved to skip the JSON > String > JSON translation that happens, but this works as-is pretty well, even with many thousands of records. I think in the video one of the tables has nearly 3,000 records and it isn't really much of a delay at all.

1 Like

Thanks. Just one more question . How to you save the data back to XData

Saving data back is a bit more complex and depends largely on the level of detail that you want to work at and how often you want to perform that operation. In my case, It is at the field level, so whenever someone changes a value in a cell, the change is written back to XData right away. Perhaps overkill or perhaps too much traffic depending on the nature of your data. I also don't use Aurelius in my project unfortunately, so on the XData side, I have service functions that implement writing the change and reporting on whether that change was successful. I think Aurelius has automatic CRUD endpoints, so it might be easier to implement in that environment.

So to start with, Tabulator has callback functions for notifying about changes. In this case, it is the cellEdited function. There are other callback functions for indicating whether editing is even allowed for a particular cell, whether it is in the process of being edited and so on. But for now, the main callback function is this one. It is added at the same time as the other callback functions when the table is first created. And as before, I pass a paremeter to tell me what table is being updated.

asm
  this.tabSample = new Tabulator("#gridSample", {
    columns: [
      {title: "ID",   field: "ID",   width:     70, visible: false },
      {title: "Col1", field: "ONE",  minWidth: 125, widthGrow: 1, editor: "input" },
      {title: "Col2", field: "TWO",  minWidth: 125, widthGrow: 1, editor: "number" }
    ]
  });
  this.tabSample.on("cellEdited", function(cell){
    pas.CoreDataModule.DM1.TabCellSave(101,cell.getRow().getPosition(true),cell.getColumn().getElement());
  });
end;

The implementation of TabCellSave is then responsible for contacting the XData server and perofrming the update, and if it isn't successful, reverting the change in Tabulator. On the XData side, more as a way to not go crazy, I've set it up such that I pass a JSON value containing all the fields in the table that are potentially to be updated, and it performs an update or insert to update the values. The old values are also passed in case it is for an update operation and you're changing one of the values of the primary key (for example).

So the same function gets called for each field that is edited, and will get called multiple times in succession if multiple fields are edited for the same row. It returns either "Success" indicating that the values were written successfully to the database, or an error of some kind that can optionally be displayed or logged. In the case of an error (anything other than "Success") the changes made in Tabulator are reverted back to the original values loaded into it. Here is the Web Core side. The XData side is just like the other service operations for retrieving data, but instead perform an update or an insert using the same mechanism.

procedure TDM1.TabCellSave(Grid, Row: Integer; Column:String);
var
  Client:   TXDataWebClient;
  Response: TXDataClientResponse;
  Service: String;
  Operation: String;
  TableName: String;
  Parameter: Integer;
  ParameterJSON: TJSONObject;

  updatedrow: Integer;

  fld_ONE: String;
  fld_TWO: Integer;

  fld_OLD_ONE: String;
  fld_OLD_TWO: Integer;
begin

  // Different tables may be looking for different fields to update and different XData services
  // to call to perform the update functions.  This can be generallized to some degree.

  if (Grid = 101) then
  begin
    TableName := '#gridSample';
    Service := 'XData.ServiceName';
    Operation := 'UPDATE';

    asm 
      var table = Tabulator.findTable(TableName)[0];
      var updatedow = table.getRowFromPosition(Row,true);
      if (updatedrow !== null) {
        fld_ONE     = updatedrow.getCell(table.getColumn('ONE')).getValue();
        fld_TWO     = updatedrow.getCell(table.getColumn('TWO')).getValue();
        fld_OLD_ONE = updatedrow.getCell(table.getColumn('ONE')).getValue();
        fld_OLD_TWO = updatedrow.getCell(table.getColumn('TWO')).getInitialValue();
      };
    end;

    ParameterJSON := TJSONObject.Create;
    ParameterJSON.AddPair('ONE', fld_ONE );
    ParameterJSON.AddPair('ONE', fld_ONE );
    ParameterJSON.AddPair('OLD_ONE', fld_OLD_ONE );
    ParameterJSON.AddPair('OLD_TWO', fld_OLD_TWO );

    Client            := TXDataWebClient.Create(nil);
    Client.Connection := DM1.CarnivalConn;

    try
      Response := await(Client.RawInvokeAsync(Service,[Operation, ParameterJSON.ToString]));
    except on E: Exception do
      begin
        // something happened with XData call
      end;
    end;

    if (string(TJSObject(Response.Result)['value']) <> 'Success') then
    begin
      // Update operation failed so maybe user is notified explicitly
      // But regardless, the data in Tabulator is reverted to its pre-edited state

      asm
        var table = Tabulator.findTable(TableName)[0];
        var updatedrow = table.getRowFromPosition(Row,true);
        if (updatedrow !== null) {
          updatedrow.getCell(table.getColumn('ONE')).restoreInitialValue();
          updatedrow.getCell(table.getColumn('TWO')).restoreInitialValue();
         };
      end;
    end;

    ParameterJSON.Free;
    Client.Free;
  end;
end

This could no doubt be refined a bit with more error handling or visual updates for the user while the operation is being performed. This is very quick though, in the ballpark of 100ms so the user is likely not going to notice as they're busy editing away.

1 Like

Thanks. Got the general Idea. Will play around with these ideas

Glad to hear, let me know if you want any other examples of anything.

Another Tabulator tidbit.

When setting up the control that contains the Tabulator, be extra sure to set its ElementID to something. Altough it appears to work without this being set, it behaves oddly when trying to rearrange columns or change column widths. Not sure if the default IDs that get assigned aren't recognized by Tabulator for some reason, or what the issue is, but simply assigning them fixed the problem.

Another Tabulator Tidbit - Column Visibility Toggle.

Say you want to add a little popup menu to the column headers of a Tabulator table that allow the user to select what columns are visible. Not a problem. There is actually an example buried deep in their page of examples on how to do this here.

Rather trivial to do though. Basically just add a function ahead of the table definition, and add the extra element headerMenu:headerMenu to the column definitions. So one of the samples from above might look like this.

asm
  var headerMenu = function(){
    var menu = [];
    var columns = this.getColumns();

    for(let column of columns){

        //create checkbox element using font awesome icons
        let icon = document.createElement("i");
        icon.classList.add("fas");
        icon.classList.add(column.isVisible() ? "fa-check-square" : "fa-square");

        //build label
        let label = document.createElement("span");
        let title = document.createElement("span");

        title.textContent = " " + column.getDefinition().title;

        label.appendChild(icon);
        label.appendChild(title);

        //create menu item
        menu.push({
            label:label,
            action:function(e){
                //prevent menu closing
                e.stopPropagation();

                //toggle current column visibility
                column.toggle();

                //change menu item icon
                if(column.isVisible()){
                    icon.classList.remove("fa-square");
                    icon.classList.add("fa-check-square");
                }else{
                    icon.classList.remove("fa-check-square");
                    icon.classList.add("fa-square");
                }
            }
        });
    }
   return menu;
  };

  this.tabSample = new Tabulator("#gridSample", {
    columns: [
      {title: "ID",   field: "ID",   headerMenu:headerMenu, width:     70, visible: false },
      {title: "Col1", field: "ONE",  headerMenu:headerMenu, minWidth: 125, widthGrow: 1, editor: "input" },
      {title: "Col2", field: "TWO",  headerMenu:headerMenu, minWidth: 125, widthGrow: 1, editor: "number" }
    ]
  });

I'm not entirely sure how to define the headerMenu globally, but simply copying and pasting that block of code (or a condensed version without the comments and whitespace) before each table defintion and then updating the column definitions to include the headerMenu:headerMenu element does the trick. It is also quite easy to adjust the styling for the popup menu and its elements.

Oh, actually, not hard. Just include that block of code before the default options (see original post) and then you can also define the extra element globally as well:

    Tabulator.defaultOptions.columnDefaults = {minWidth: 50, headerMenu:headerMenu};

And voila! Column visibility across all Tabulator tables in your project!