ColumnControl searchList not updating when table data changes

bg7 · July 23

I have a table that uses the ColumnControl searchList. When data is added, updated or removed from the table the search list contents aren't updating. In this example if you add a new entry with unique field values and check the search list dropdowns the new values don't appear.

https://live.datatables.net/dufeciyo/1/edit

Is there a way to get them to stay in sync with the data in the table?

Thanks.

Ben

allan · July 24

Hi Ben,

For local table editing, there actually isn't at the moment. Apologies that is an oversight on my part and I've made a note to add an API method to do that.

Currently, if the options are coming from Ajax, then they will be refreshed on each Ajax reload.

Allan

bg7 · July 24

Allan,

I have tables that are being updated automatically (polling for updates from the server) and also tables that are updated when the user uses the editor to add/update/delete data and those use the editor's ajax option. Neither one's ColumnControl searchList is updating when the table data updates. Is it not an option to have the ColumnControl searchList to listen for change events for the table's data and then update appropriately? Or am I missing something?

The reason I used the editor local data example is I don't know how to set up a live.datatables.net example with ajax calls to the server (that's what you're referring to, right?). Is that possible? I'd very much like to see a working example of this functionality to play around with and hopefully figure out what I'm doing wrong.

As an aside, I don't know if this would be feasible but it would be really cool if each one of the DataTables and Editor example pages had a link to automatically create a live.datatables.net page with all the appropriate plugins already included and the code from the example loaded in as well. It would be really handy for quickly creating working and reliable demos to submit with tickets as well as a place to play with live examples to experiment. Just a thought.

Thanks.

Ben

allan · July 24

Is it not an option to have the ColumnControl searchList to listen for change events for the table's data and then update appropriately?

There is no such event at the moment. It should be looking for new options on each Ajax request (source code), and if that isn't working I'd welcome a test case showing the problem.

There does need to be a new API that will trigger a refresh of the options though and that will be added for 1.1 I expect.

Unfortunately at the moment the live site doesn't have the option of interfacing with a backend for Editor.

it would be really cool if each one of the DataTables and Editor example pages had a link to automatically create a live.datatables.net page with all the appropriate plugins already included and the code from the example loaded in as well

I have pondered that in the past, but there always seems to be something more pressing to do! I think it is a neat idea.

Allan

bg7 · July 24

Allan,

Thanks for the link to the code. I'll check to make sure it's getting run. If that's not the issue I'm happy to put together a test case to share. That said, if live.datatables.net doesn't support ajax calls to the server how do I put together a test case if that's the setup where this should work (as opposed to standalone as I set up in the initial example)? I feel like I'm missing something.

Thanks.

Ben

kthorngren · July 24

That said, if live.datatables.net doesn't support ajax calls to the server how do I put together a test case

See this technote for options to use ajax loaded data. Maybe one of those will work for a test case.

Kevin

bg7 · July 24

Kevin,

Thanks for the tip. I tried to convert the standalone editor example over to use ajax.

https://live.datatables.net/kihiwava/1/edit

It loads the data but the editor portion is no longer working so I can't add/update/delete records. The editor buttons are still clickable but anything I do with them just vanishes (and the edit button doesn't actually load the row data). I had to remove the columns section of the datatable definition to get the ajax data to load - not sure if that's related. Am I missing a step?

Thanks.

Ben

kthorngren · July 24

Sorry I did miss the Editor discussion you and Allan had. Based on my understanding ColumnControl will only update with the Datatables ajax responses (like ajax.reload() I think) and not the Editor ajax used during edits.

Your test case has server side processing enabled. Is that what you are using in your solution? If so see the ColumnControl Server Side Processing docs.

If you are not using server side processing then on option might be to use ajax.reload() in submitSuccess or submitComplete. However this might be too much network data.

Another possibility is to refactor the custom API plugin created in this thread. I haven't dug into it but it might be possible to change the plugin to support and array of column indexes, loop through the column indexes and get the unique sorted column data, similar to this example, and update the options. Call this in submitSuccess instead of ajax.reload().

Allan's implementation of a new API for this might be different.

Kevin

bg7 · July 24

Kevin,

No worries. No, we're not using server side processing. Honestly I was just trying to find an example from the tech notes page that you linked to that I could clone and rework so it has both the Editor and the ColumnControl on it so I could see it work, compare it to I'm doing in our app, experiment with it and link to it to submit as a test case if appropriate. It's starting to sound like the better option would be to put a pin in it and wait for the 1.1 changes Allan mentioned that will add a way to trigger an update that will refresh the options lists.

I was picturing the options list automatically listening for a table update event so it could keep in sync behind the scenes but Allan said that event doesn't exist right now. Not sure if tying the 1.1 api addition to the 'draw' event would be a bad idea but presumably that would get the job done, though it might be a bigger hammer than is appropriate. I'm guessing ideally you'd want something more like an event for rows added/updated/deleted but without that I could experiment with tying it to the 'draw' on my end and see what happens, once it's available.

I just finished updating our app from DataTables 1.x to 2.x and one of the things that didn't work any more was custom column filters that I'd built that handed this functionality. Pretty happy to be rid of all that custom code and use ColumnControl instead and this is one of the last pieces I'd like to get working but it's not an emergency.

Thanks.

Ben

kthorngren · July 24

Oh boy, I started looking at the API plugin and its for SearchPanes not ColumnControl I've been confused all day. Nevermind my above comments. Probably best to wait for 1.1.

Kevin

kthorngren · July 24

so it has both the Editor and the ColumnControl on it so I could see it work

Here is a starter example:
https://live.datatables.net/himazura/1/edit

As noted above the edits can't be stored at the server so there is no Editor ajax option. It may or may not be useful for you to mess with other things.

Kevin

allan · July 25

I'm working on Editor 2.5 at the moment, and ColumnControl 1.1 is going to go hand-in-hand with that (to add server-side processing support to CC). So it should be sometime next month I add them .

Allan

bg7 · July 25

Allan,

Sounds great! Thanks!

Ben

allan · September 8

i've committed a change to add a new column().columnControl.searchList() method to ColumnControl which provides the ability to have it either refresh the list from the table's data, or you can pass in options. 1.1 should land soon with this change .

Allan

bg7 · September 10

Allan,

Very exciting! Just curious - do you have a ballpark target to keep an eye out for the 1.1 release?

Thanks.

Ben

allan · September 10

It's out . Dropped the release yesterday afternoon. Still to write the server-side processing documentation and a release blog post, but column().columnControl.searchList() does now exist .

Allan

bg7 · September 11

Allan,

I've loaded up the new release and I'm running into errors. I followed the example here:

https://datatables.net/reference/api/columns().columnControl.searchList()

And I updated our code to refresh the search list when the table gets updated:

// redraw the table
this.alertsDatatable.draw("full-hold");
// reload the column filters
this.alertsDatatable.columns().columnControl.searchList('refresh');

And I now get this:

This is where things appear to be going sideways:

I tried running the command for the individual columns instead of all the columns together and it fails for four of the five columns. The only one it didn't spit out an error for was a column where I defined the search list as fixed values.

Am I doing something wrong?

Thanks.

Ben

allan · September 11

Hi Ben,

That suggests to me that you might be calling it on a column which doesn't have a searchList.

Can you restrict the .columns( {selector} ) to just the columns which have searchList on them?

Allan

bg7 · September 11

Allan,

Ah, ok. I was (blindly) expecting it to loop through and refresh anything that had a search list and skip the rest. I can filter it, e.g. like this:

  this.gridDetailsDatatable
    .columns(['deviceName:name', 'datastreamName:name', 'sensor:name', 'displayUnits:name'])
    .columnControl.searchList('refresh');

Does this mean I'll need to specify the columns to refresh for each table or is there some sort of magic column filter I can use in every instance that will find all of the columns with search lists?

Thanks.

Ben

allan · September 11

In retrospect, I should perhaps allow it to work in cases where searchList isn't on a column. I've just committed a fix for that. That should make things easier .

Allan

bg7 · September 11

Allan,

Yeah, that should make it cleaner on my end for sure.

I'm used to pulling down releases from the download builder. Do you have a recommendation for how best to roll a change like the one you just pushed into the release I've currently got? Should I modify the unminified code by hand and then minify it myself or is there a better way?

I'm also trying to sort out how best to incorporate this change into my code. I created a common utility method (I'll ditch the columns param once I have your latest change):

static refreshColumnSearchList = _.debounce((datatable, columns) => {
  datatable.columns(columns).columnControl.searchList('refresh');
}, 10);

And then I tried to hook into changes triggered by editor updates:

this._notificationsEditor.on('create edit remove', () => {
  Common.refreshColumnSearchList(this._notificationsDatatable, ['enabled:name']);
});

It's picking up some but not all of the changes. In some cases I'm having to trigger it manually. I'm curious if this seems like a reasonable approach.

For what it's worth it would be very cool if down the road it were possible to toggle on/off having the ColumnControl automatically keep in sync with changes in the table. I'm admittedly having trouble thinking of a case where I wouldn't want that to happen and it would be fantastic if that were baked into DataTables. Just my two cents.

Thanks.

Ben

kthorngren · September 11

I checked the nightly code and the fix seems to be in the code:

DataTable.Api.registerPlural('columns().columnControl.searchList()', 'column().columnControl.searchList()', function (options) {
    return this.iterator('column', function (settings, idx) {
        var fn = settings.aoColumns[idx].columnControlSearchList;
        if (fn) {
            fn(options);
        }
    });
});

Change the URL to the non-minimized link, for example:
https://nightly.datatables.net/columncontrol/js/dataTables.columnControl.js

Temporarily replace the dataTables.columnControl.js you are currently loading, might need to use the Download Builder to create a new concatenated version without ColumnControl. Then once there is a new release se the Download Builder to generate a new concatenated build with the new ColumnControl.

@allan will need to address your other questions.

Kevin

allan · September 11

For what it's worth it would be very cool if down the road it were possible to toggle on/off having the ColumnControl automatically keep in sync with changes in the table.

Events such as "updatedData" and the like are definitely something that I'd like to add in future. The issue is going to be doing it in a performant way! I want to rewrite how the API operates before doing that.

If I could convince someone to drop $1 million on just letting me focus 100% on making DataTables better...

Allan

bg7 · 2:37PM

Kevin - thanks for the advice about rolling in updates. For the moment I just hacked the unminified code and pointed at that so I can test.

Allan,

I've got most of our app updated to keep the ColumnControl search lists in sync with changes to the tables. There are a couple stragglers I'll try to sort out today.

I did however notice that in pulling the latest a different issue has cropped up. The dates that are being displayed have changed from local time to GMT. This is the stuff from this ticket.

https://datatables.net/forums/discussion/comment/241654

The date data is timestamps and worked with the previous version of ColumnControl, meaning they displayed as local time, but now they're formatted as GMT. I tried stashing everything and switching over to the changes I made to use the ISO-8601 dates instead with the latest ColumnControl and weirdly that doesn't work either (they render as GMT), though I know you got it working in the example you sent me. No clue what's going on. Probably going to try to get the remaining ColumnControl search list updates working first and then take a look at the dates. Unfortunately I won't be able to use the search list updates if the dates are GMT because that really confuses our users.

As for the $1m, I'm afraid that's just a hair outside our budget - sorry.

Ben

allan · 2:59PM

As for the $1m, I'm afraid that's just a hair outside our budget - sorry.

Haha! I'd probably settle for 900k .

I thought I'd got the time zone stuff sorted, sorry. Are you able to update the test case for this? At the moment everything should just be time zone-less. Did you update DataTables core as well?

Allan

bg7 · 3:24PM

Allan,

Oh, only $900k? I'll go check under the couch cushions...

I typically use the download builder and grab the latest everything. That's what I did in this case and then added the fix you committed above by hand to the unminified version and pointed at that for the moment. Seemed like the easiest way to try stuff out. The side effect of this of course is I ended up with lots of changes beyond just ColumnControl updates. This is what I pulled down:

https://datatables.net/download/#dt/jszip-3.10.1/dt-2.3.4/e-2.5.0/b-3.2.5/b-colvis-3.2.5/b-html5-3.2.5/b-print-3.2.5/cc-1.1.0/date-1.6.0/fc-5.0.5/kt-2.12.1/rg-1.6.0/sc-2.4.3/sl-3.1.0/sr-1.4.2

When switching between that and the previous download I was using:

https://datatables.net/download/#dt/jszip-3.10.1/dt-2.3.2/e-2.4.3/b-3.2.4/b-colvis-3.2.4/b-html5-3.2.4/b-print-3.2.4/cc-1.0.7/date-1.5.6/fc-5.0.4/kt-2.12.1/rg-1.5.2/sc-2.4.3/sl-3.0.1/sr-1.4.1

the dates (using timestamps) go from rendering as local time (older release) to GMT (newer release). I haven't had a chance to try to reproduce it in an example yet. I was hoping to finish up the search list stuff first and then switch gears. Context-switching is a killer and as they say, multi-tasking is doing many things poorly. I'll see what I can put together asap.

Thanks.

Ben

bg7 · 5:32PM

Allan,

Got the rest of the ColumnControl search list updates working. Definitely excited about that!

The config I've been using for the dates (with timestamps) looks like this:

                {
                    name: "startTimeOfAlert",
                    data: "startTimeOfAlert",
                    render: DataTable.render.datetime(),
                    type: "datetime",
                    className: "dt-nowrap",
                    columnControl: [ 'order', ['search'], 'searchClear' ],
                },

With the previous release I was using it would render as local time and only show a date picker. Updating to the latest everything means it now renders as GMT but show both a date and time picker. I know you said it's best not to force the type but it has been working for us. If I force the type then the date/time picker shows up properly. If I don't force the type I get no date/time picker at all. If I try to tell the render about the type:

  render: DataTable.render.datetime('x', 'M/D/YYYY, h:mm:ss A', 'en'),

it renders as GMT. That won't work for us anyway because as we discussed previously the locale has to be specified but I thought I'd try it just to see what would happen.

Then I loaded the version I've got stashed where I've converted everything over to ISO-8601 dates and updated it to the latest DataTables release. Now dates are coming in like this:

startTimeOfAlert: "2025-09-12T17:16:06"

But being rendered as GMT:

It does show the date/time picker btw.

If I ditch setting the type and update the config to this:

                {
                    name: "startTimeOfAlert",
                    data: "startTimeOfAlert",
                    render: DataTable.render.datetime(),
                    className: "dt-nowrap",
                    columnControl: [ 'order', ['search'], 'searchClear' ],
                },

then the date/time picker goes away completely and it still renders as GMT.

I've tried experimenting with setting up an example and I can't see why I'm getting different results there. Using the nightly release, timestamps and forcing the type it doesn't render the date at all.

https://live.datatables.net/hukededi/6/edit

In that same example if I set the render to use the full params (commented out) and comment out the render without params and type then it renders the date but as GMT.

And then there's this example which I think is a clone of the one you put together which uses the ISO-8601 date and renders properly (local time).

https://live.datatables.net/kahizela/1/edit

I can't explain why I'm seeing GMT locally but it works in the online example. Definitely confused.

Thanks.

Ben

ColumnControl searchList not updating when table data changes

ColumnControl searchList not updating when table data changes

Answers

Howdy, Stranger!

Categories

DataTables

ColumnControl searchList not updating when table data changes

ColumnControl searchList not updating when table data changes

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables