Search code examples
javascriptjsontabulator

Filter JSON Data In Custom Formatter

I am fairly certain that this is possible with Tabulator, however my JS knowledge is very limited.

I would like to filter the JSON data parsed to my table, so it can be summed together, providing a "calculation based" column. My data looks as follows:

{'id': '1' ,'date': "3/20" ,'type': 'API2' ,'volume': 14000 ,'price': 119000,'color': '#FFFFFF'},
{'id': '2' ,'date': "3/20" ,'type': 'API2' ,'volume': 500 ,'price': 119000,'color': '#FFFFFF'},
{'id': '3' ,'date': "4/20" ,'type': 'API2' ,'volume': 9000 ,'price': 129000,'color': '#FFFFFF'},
{'id': '4' ,'date': "3/20" ,'type': 'API4' ,'volume': 50000 ,'price': 119000 ,'color': '#FFFFFF'},
{'id': '5' ,'date': "3/20" ,'type': 'API4' ,'volume': 1000 ,'price': 109000 ,'color': '#FFFFFF'},
{'id': '6' ,'date': "4/20" ,'type': 'API4' ,'volume': 13000 ,'price': 119000 ,'color': '#FFFFFF'},
{'id': '7' ,'date': "3/20" ,'type': 'API6' ,'volume': 14000 ,'price': 119000 ,'color': '#FFFFFF'},

My columns (which are obviously incomplete) would look like:

{
    id: '1', 
    title: 'Volume',
    formatter: function(cell){
        //Requires Help
    },
    editor: '', headerTooltip: '', align: 'center', sorter: '', script_name: '', 
    script_parameter: '', headerFilter: '', headerFilterPlaceholder: '', 
    headerSort: false, frozen: false, resizable: false, minWidth: 35, 
    editorParams: {values:[]}
}

An example of how I would like this column to operate would be summing all of the volume values of all records which meet a particular criteria, perhaps type is 'API2', or date is '3/20' and so on.

Solution

  • I don't think a column of sums is all that desirable, I think what you would want is a row of sums at the top/bottom of the table. Either way, I will provide both ways for you to see.

    https://jsfiddle.net/s60qL1hw/ - Contains all examples below.

    Creating a column of sums.

    You need to first get the table data and then you need to create your filter/sum based on that.

    Here is an example formatter that creates a sum of the volumes where the type is 'API2'.

        formatter: function(cell) {
      const tableData = cell.getTable().getData();
      let mySum = 0;
      for (let i = 0; i < tableData.length; i++){
        if (tableData[i].type === 'API2'){
            mySum += tableData[i].volume;
        }
      }
      return mySum;
    }

    Tabulator column calcs

    Use the bottomCalc or topCalc option in your column definition. This is for the volume column.

      {
    title: "Volume",
    field: 'volume',
    bottomCalc: 'sum'
  }

    You can also use custom functions for the calculation. (I don't use the calcParams here, but you can see how those are used in the documentation.)

        bottomCalc: function (values, data, calcParams){
      let mySum = 0;
      for (let i = 0; i < values.length; i++){
        if (data[i].type === 'API2'){
            mySum += values[i];
        }
      }
      return mySum;
    }