How to update table and apply existing filters

Related searches

We are using the excellent Tabulator JS library. We poll our server at intervals to refresh the data in our table. We are trying to achieve the following behaviour when this happens.

  1. Update the existing data in the table
  2. Add any new rows that "Match" the current filters
  3. Remove any rows that have changed and don't match the filters
  4. Keep the existing scroll position
  5. Keep any selected rows.
  6. If the filters are removed show the latest data.

There are 3 methods to update Tabulator

http://tabulator.info/docs/4.5/update

  1. SetData (we use this when we first fetch)
  2. updateData
  3. updateOrAddData (we use this on repeated fetches but row edits don't get filtered out)
  4. replaceData

In the documents is mentions that 'replaceData' will update existing table data with out resetting the scroll position or selected rows, this does not seem to be the case. 'updateData' does update the data and leaves the scroll pos and selected rows as is, BUT it does not apply the filters, for example if a row is updated out of the scope of the current filter it still shows?

Our code right now:

if (replace) {
   table.updateOrAddData(data); //replaceData
} else {
   table.setData(data);
}

We need the table to update and meet our requirements in my above points 1-6. I would expect one of the methods listed (replace, update) would do this and in the docs it seems they should, but in our tests this fails. How can we achieve the desired results?

We are using version 4.5 downgraded from 4.4.

Edit:

So for example if we update the table with data and one of the rows in the table does not exist in the new data, that update does not persist. Unless we use setData which resets the scroll position, undoes all selected rows and so on.

You can either call manually Redraw

table.redraw(true); //trigger full rerender including all data and rows

or just use reactive data

reactiveData:true, //enable reactive data
data:tableData, //assign data array

Excel Filter: How to add, use and remove, Video: Apply Multiple Filters to Pivot Table Field. Report Filters are Not Dependent. Video: Change the Report Filter Layout. Change the Report� One way to apply a filter is to click the Filter menu on the Data tab. You can use the keyboard shortcut control + shift + L. Note that you can toggle the filter on and off. Another way to add a filter is to convert the data to an Excel table with the keyboard shortcut control T, which automatically applies a filter.

if I understand your OP, this is what you were looking for ?

var selected,visible;
var t = new Tabulator("#tabulator", {
    data:[
        {id:1,title:'One'},{id:2,title:'Two'},{id:3,title:'Three'},{id:4,title:'Four'},{id:5,title:'Five'},
        {id:11,title:'Eleven'},{id:12,title:'Twelve'},{id:13,title:'Thirteen'},{id:14,title:'Fourteen'},{id:15,title:'Fifteen'},
    ],
    height:"10em",
    selectable:true,
    columns:[ {title:'Title',field:'title',headerFilter:'input'}, ],
    initialHeaderFilter:[{field:'title',value:'e'}],
    dataLoading:function(d) { if (t) { selected = t.getSelectedData().map(o => o['id']); visible = t.getData('visible').map(o => o['id']); } },
    dataLoaded:function(d) { if (t) { t.selectRow(selected); t.scrollToRow(visible); } }
});
t.selectRow([3,11,15]);
t.scrollToRow(15);
setTimeout(() => {
    t.replaceData([
        {id:3,title:'Three'},{id:5,title:'Five'},{id:6,title:'Six'},{id:7,title:'Seven'},{id:8,title:'Eight'},{id:9,title:'Nine'},{id:10,title:'Ten'},
        {id:13,title:'Thirteen'},{id:15,title:'Fifteen'},{id:16,title:'Sixteen'},{id:17,title:'Seventeen'},{id:18,title:'Eighteen'},{id:19,title:'Nineteen'},{id:20,title:'Twenty'},
    ]);
},5000);

Excel Pivot Table Report Filter Tips and Tricks, You can determine filters applied in a Sheet directly through the Sheets API or existing filters on a given range; 3 Clear / remove all filters; 4 Get filtered rows� Filters in Pivot tables are not similar like filters in the tables or data we use, in pivot table filters we have two methods to use filters, one is by right click on the pivot table and we will find the filter option for the pivot table filter, another method is by using the filter options provided in the pivot table fields.

After much searching and looking through Tabulator 4.5 docs it seems that the table can not update existing data, re-apply filters, remove dead rows (those not in the new data from the server) from one of the update, replace methods. From what I can understand this can be achieved by the code I have written below, which uses the updateOrAddData method, the setFilter method and my own async function to loop the table and remove dead rows.

    // var data= new data from server - any array.
    // get the existing filters, update the data or add any new rows.
    var f = table.getFilters();
        table.updateOrAddData(data); //replaceData with server copy

        var keys = [],
            r = table.getRows();
        // make a list of all the  keys in our server data, this will make it faster to remove dead rows.
        data.forEach(function(item) { keys.push(item.id); });
        // remove any dead rows in the data that do not belong start at row 0
        removeDeadRows(0, function() {
            table.setFilter(f); //re-apply filters when finished.
        });

    // iterate the table test if each row is in our key list, if not remove it.
        function removeDeadRows(pos, cb) {
            if (!r[pos]) return cb();
            var c = pos + 1;
            try {
                if (keys.indexOf(r[pos].getData().id) > 0) return removeDeadRows(c, cb);
                r[pos].delete();
                removeDeadRows(c, cb);
            } catch (e) {
                console.log(e);
                cb();
            }
        }

Create, update & clear filters + Get filtered rows, If you are editing an existing filter, the table selection cannot be changed. you not apply functions to column names in parameterized row filter� The Refresh button will update your pivot table to reflect any changes in your existing data, such as any changes to our sales data due to customer returns. Using the Refresh button won't automatically pick up any new data in your table (unless you're using Excel's Table feature as the source for your pivot table - we'll come to that shortly).

Add or Edit Filter, You can use filters to display specific records in a form, report, query, or datasheet, or to print only certain records from a report, table, or query. By applying a filter,� Filter data from one worksheet to another dynamically in Excel. To finish this task, please do with the following step by step: 1.First, define a range name for the source data, please select the data range that you want to filter, and enter a range name into the Name Box, and then press Enter key, see screenshot:

Apply a filter to view select records in an Access database, How to apply the same filter to multiple pivot tables? Sometimes, you may create several pivot tables based on the same data source, and now you filter one pivot table and want other pivot tables are filtered with the same way as well, that means, you want to change multiple pivot table filters at once in Excel.

Turn the macro recorder on: Developer tab > Record Macro. Give the macro a name, choose where you want the code saved, and press OK. Apply one or more filters using the filter drop-down menus.

Comments
  • Thanks. The redraw does not fix the issue. I have not tried reactive data as this requires an addition module and makes the data-->table live. It seems more efficient to construct our own procedure to run the various processes to update the table in situ and without affecting filters, selected rows or scroll position. I have posted this code in my own answer. I feel this is the way to do it.
  • Actually, none of the use of the visible variable is required. You're right, replaceData() preserves scrolling place. but it doesn't preserve selectedData()
  • Also, at Point 6, you didn't say whether scrollingPos should be maintained when the filter is removed. If so, look at the dataFiltering and dataFiltered callbacks and use the same way.
  • Yes correct. Also if you have filters the new rows added will show on the table regardless of whether they match. I find it strange that Tabulator has 2/3 update methods but non seem to carry out the correct task of keeping the table in-sync with server data where new rows appear, old rows are removed, existing rows update. Without simply rebuilding the whole table. In my code/in my answer it manages to carry out that task.
  • Regarding the filters. Well that is not the issue. The table may or may not have a filter applied. Regardless when I update the data from the server I would expect the table to hold position, keep filters and keep selected rows. So if the user is part way in an operation, filters, selected rows or scroll are not lost.
  • I think the updateOrAddRow is the best match function. It fails to remove dead rows, and adds new rows regardless of what filters are set - Seems to me be a design fault or bug. Either way - In my example i run this as it keeps scroll POS and selected rows. Then I removed the dead rows, and them re-apply the filters. This seems to be the best methods I can find to get the desired result. What do you think?