Searching on appended data in cells

silkspin

Is it possible to search on data that has been appended? I'm guessing it might be something to do with orthogonal data which I've used before to format and search on currency. I've haven't been able to make this work, but I have created a test case.

The scenario is when the cell is empty, I append some text to it. To make this example simple I've removed all instances of London from the office column, and when an empty cell is found "Blank" is added in its place. I would like the search filtering to show the columns containing "Blank" when that is searched on.

http://live.datatables.net/xotesiwi/1/

kthorngren

The columns.render is a better place for manipulating the row data, for example:
http://live.datatables.net/xonefuho/1/edit

Datatables won't know about the cell changes using $(td).append(' Blank'); with createdRow so it won't update its data cache.

Kevin

silkspin

That's brilliant @kthorngren. I was approaching it from the wrong angle so thank you!

silkspin

Hi again @kthorngren. I have a related question regarding sorting on the displayed data rather than source data. I've changed the replaced null data value to "Empty" which I would like to sort. In this case "Edinburgh" should appear before.

I thought this would work... http://live.datatables.net/yenupage/1/ but it doesn't.

kthorngren

The problem is you have a leading space, ie, " Empty". You will want to use "Empty" for the sort and possibly filter types. Like this:
http://live.datatables.net/bapehefu/1/edit

Kevin

silkspin

I can't believe I overlooked the space Kevin! I was too busy trying to get the conditional in the right order, which I also failed on! Thanks @kthorngren for fixing this. Your test case is a really good example of how Orthogonal data can be broken down to an even more refined level.

silkspin

Something else has come to light now. I thought that type === 'display' and type === 'filter' would've meant the drop down filters would show the replaced data but it just shows the original "" (null) value. Is a different method needed to show the updated values and be able to filter on them?

http://live.datatables.net/bapehefu/2/

kthorngren

To get the rendered data you will need to use cells().render() instead of column().data(). Here is the updated example:
http://live.datatables.net/bapehefu/3/edit

Note the use of column().index() as the cell() column index. Also created the api variable to access it inside the columns().every() loop.

Kevin

silkspin

Thanks Kevin. That does indeed show the replaced values in the select. The only problem with replacing the column().data() is that cascading filtering doesn't work now if say the "Position" column has filtering too. Does it have to be one or the other that I use or is there a way to combine them? I'm sorry I should've added column 1 to my test case.

kthorngren

You can use the selector-modifier of {search: "applied"} with cells() to loop through only the rows displayed. I was too lazy to build the cascading solution but you can see an example here:
http://live.datatables.net/bapehefu/4/edit

I just defaulted the table search to filter the table before initComplete.

Kevin

silkspin

Hi Kevin. I understand what your quick fix does, but I'm stuck as how the new cascading filter code would fit? I originally used Allan's code as a starting point and customised that, but now it looks like it needs to exist outside the new function that constructs the select. I've had a go but the different functions clash. I'll just remove multiple select filtering and stick with your version using one filter. Users will always be able to refine the search using the text box.

kthorngren

I adapted what I think is Allan's original example here replacing the buildSelect() function with your initComplete code:
http://live.datatables.net/bapehefu/5/edit

If you still have problems please update the test case or build an example showing the issue.

Kevin

silkspin

Hi Kevin. The previous version worked apart from when I tried to reset the filter by going to the top of the select dropdown. There was a bit more code at the end of Allan's version (http://live.datatables.net/gejojiqu/1/edit) which I've added and that appears to have fixed it, and the cascading is working now... http://live.datatables.net/bapehefu/7/edit

Thanks to you all the code is now in the correct order. Your much appreciated help with the original question about orthogonal data replacing null values in selects also means I'm now able to filter on null values that aren't an option if left blank.