Dropdown Multiple select with Search and Select All like Excel

makimax

Hello,
After several searches I did not find an example with a filter like Excel. These posts show examples with a built-in multiple select but it's not very compact and not compatible with hidden columns by responsive (class ="none")
https://datatables.net/forums/discussion/49676/
https://datatables.net/forums/discussion/47864
https://datatables.net/forums/discussion/47655/

I'm trying to do this externally to have more space for the "pills" and filter on columns hidden by responsive.
I reused the part already proposed in the each example and tried to adapt it to a single column : https://live.datatables.net/vixoxohu/1/edit

The ideal would be a built-in filter like Excel

I'm sorry to ask the same question about Excel type filters but these posts are several years old, perhaps that has evolved.

kthorngren

The example you linked to has a couple issues. First the selector used for the select2 change event is incorrect so the event handler doesn't fire. I added <select id="mySelect2" to match the selector used.

Second is you have this:

      //search for the option(s) selected
      column.column(2).search( val ? val : '', true, false ).draw();

The column should be table for the Datatable API, for example:

      //search for the option(s) selected
      table.column(2).search( val ? val : '', true, false ).draw();

Updated example:
https://live.datatables.net/vixoxohu/2/edit

This example is similar but uses checkboxes:
https://live.datatables.net/vipifute/1/edit

I don't remember anyone creating an example like your screenshot. I think between these two examples you could refactor the code to build a list of checkbox selections, like the screenshot, to perform a search of multiple selections. I would be a matter building the proper HTML.

Also there are the SearchPanes and SearchBuilder extensions that you might find interesting.

Kevin

makimax

Thank you Kevin, you are everywhere and always there to help me! THANKS !
I updated with table.column instead of column.colmun, https://live.datatables.net/monovogi/1/edit

And it's true that it works well! For the id, in fact i confused class= and id=, thank you

I had seen the SearchBuilder, it is very powerful but not very ergonomic for daily use.
The SearchPanes like this official example : https://datatables.net/extensions/searchpanes/examples/initialisation/ajaxOptions.html correspond to my needs but it is a simple select and not a multiple select. And it would have to have select All option and collapse automatically, it would become like the Excel filter externally.

kthorngren

Each SearchPane is a Datatable using the Select extension. By default you can select multiple ites by using ctrl-click or shift-click. You can change the selection style as shown in this example. You can use searchPanes.dtOpts to change this or other Datatables settings for all SearchPanes or columns.searchPanes.dtOpts for specific columns.

Kevin

makimax

Thank you Kevin, i've tried and it's good. The result with your help : https://live.datatables.net/lebilehe/1/edit
If panes are datatables, how can i strippe, compact or hover them ? an exemple like this : https://cdn.datatables.net/plug-ins/preview/searchPane/index.html?fbclid=IwAR18751XuYols2EA6asPIFhVkeWH4LG14fKuSLVCo9_RoKeKBaKYPhQzF4k

Is it possible to expand / collapse the SearhPane zone ? Not into modal.

kthorngren

Wow, that's an old example

 * @summary     SearchPane
 * @description Search Panes for DataTables columns
 * @version     0.0.1
 * @author      SpryMedia Ltd (www.sprymedia.co.uk)
 * @copyright   Copyright 2017 SpryMedia Ltd.

Looks like the first example and it uses unordered lists instead of Datatables. Anyway I don't believe there is anything built into SearchPanes to apply classes to the HTML tables for stripping, hovering, etc. One option might be to use initComplete to apply the desired classes. Using searchPanes.container() as part of the selector you can apply the display class, assuming default Datatables styling, for stripping and hover:

      initComplete: function () {
        $('div.dt-scroll-body table.dataTable', this.api().searchPanes.container()).addClass('display')
      }

https://live.datatables.net/lebilehe/2/edit

Using the same searchPanes.container() in a jQuery selector you could toggle the container's visibility using jQuery hide() and show(), for example:

$( table.searchPanes.container() ).hide()

Kevin

makimax

Thanks kevin. It's perfect. I used hide / show searchPanes container like you ask me (SearchPanes placed into top1 layout), controled with buttons into Top layout. This part is ok but there is two search input.

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

kthorngren

The default layout is"

{
    topStart: 'pageLength',
    topEnd: 'search',
    bottomStart: 'info',
    bottomEnd: 'paging'
}

You set this:

            top1End: {
                search: {
                    placeholder: 'Type search here'
                }
            },

The topEnd: 'search' default setting is still applied. Use topEnd: null to remove it.
https://live.datatables.net/cewuyazi/2/edit

Kevin

makimax

Thanks Kevin, I was looking for it in my layout but so it has the default one which was always present. THANKS.
Maybe I should ask another Question because everything is resolved on this subject.

SearchPanes offers the data as it is present in the table. What can i do for Tags search for example (list each tag individually). The data must come from an other source ?

example : https://live.datatables.net/jimiyado/1/edit

allan

See this example for how to handle array data like that (assuming it is an array and being rendered as a tag string).

Allan

allan

p.s. Looks like you might be missing the "Tags" th in the footer from that screenshot.

makimax

Thanks allan !

makimax

Please, is there a way to hide "Clear All" button of SeachPanes but keep the clear button of each individual pane (cross button) ?

I put clear: false into top: { searchPanes: { and the Clear All is hidded.
I tried to hade clear: true into columnDefs: [ { searchPanes: { to show individual button but no sucess.

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

kthorngren

I tried to hade clear: true into columnDefs: [ { searchPanes: { to show individual button

There is no option for that.

is there a way to hide "Clear All" button of SeachPanes

You can use jQuery to hide the button. Right click the button to find an appropriate selector to use, for example: .dtsp-clearAll. Hide the button using initComplete. Updated example:
https://live.datatables.net/faloselo/1/edit

Kevin

makimax

Thank you Kevin, I am not a programmer at all and I appreciate your teaching and the explanations which allow me to really understand.

kthorngren

Glad to help out. Its nice to help someone who tries first and provides test cases

Kevin

makimax

Hi,
1) Since i replaced the language json file, the custom button to hide/collapse the SearchPanes use the traduction and no longer use the text written into the function
$('.spToggle').text(this.i18n('searchPanes.collapse', {0: 'Filtres (0)', _: 'Filtres (%d)'}, count));
Button with name : 'Filtre' : https://live.datatables.net/faloselo/1/edit
Button with name written in the traduction file : https://live.datatables.net/bovovifo/1/edit
It's written in the french json but in this case i still want 'Filtre'

2) My datatable and searchpanes work perfectly so now I'm trying to integrate it into my project. The data no longer comes from HTML but from JSON and AJAX.
On first load after cleaning the browser cache, the datatable and searchpanes work fine.
But then when I refresh the page, then the datatable is complete, but the searchpanes are empty.
I don't know how to post an example of my project since this uses a database.

kthorngren

I'm not sure about the first question. Maybe @allan can answer it.

Do you have stateSave enabled? If yes, maybe the previous filters are applied.

This technote provides options for using ajax data in the live.datatables.net environment.

Kevin

makimax

stateSave is disabled. the search panes are empty as soon as I refresh the page. however I have a fetch data function which calls a php which executes a request and transmits the response in json. The datatable is complete but only the searchpanes are empty.

With the proposed technology, I managed to make a simple model with the txt data as in the official example
https://live.datatables.net/piyagayo/1/edit

and another test model with the fetch function but instead of leaving the php with the request, I replaced it with the address of the ajax array.txt (I left the original code with the php call in comments).
I think that here the fetch is not working because the variable names may not be correct.
https://live.datatables.net/pewocami/1/edit

kthorngren

Since you are changing the table data you need to use searchPanes.rebuildPane() to have searchPanes update its data. Updated example:
https://live.datatables.net/pewocami/4/edit

Note that the response is JSON not text so set the dateType to json. Since the test case is using array data you can simply use rows.add() to add all the rows without looping through them.

Kevin

makimax

Thanks Kevin, In fact it's simpler but why do the filters at the columns cloned headers don't find the data for the select.
Now that there are the search panes, it's not really useful anymore because it's a single select and not a multiple select, I might remove that part.
Anthony

kthorngren

You are populating them in initComplete which only runs once. You can move that code into a function and call it after you add the row data.

Kevin

makimax

I tried to move into a function called 'filtercolumns' and call it at the end of the fetchData function but the argument is not good.
I tried to call it into the initComplete to check it and it works with 'this' argument (but normal with the same problem, data are no populated)
https://live.datatables.net/ciqinefo/1/edit
I know I'm not good at all

kthorngren

You still need var api = this.api(); in iniComplete for the other code. Just pass it to filtercolumns() like this:

``` initComplete: function () {

      var api = this.api();

      filtercolumns(api);

Use this to get the API in the `fetchData()` function:

```js
filtercolumns($('#myTable').DataTable());

The table variable isn't accessible within that function resulting in this error:

Uncaught TypeError: table.DataTable is not a function

Updated test case:
https://live.datatables.net/zesorata/1/edit

Kevin

makimax

I understand the correction and the clear explanations. Thanks a lot.
Finally the function is called a first time on initialization with 'api' as argument because at this moment 'this' corresponds to $('#myTable').DataTable()
And a second time by the fetchdata function but using the full name of the datatable $('#myTable').DataTable() because this is impossible here, and table.DataTable() is not recognized.
So why in this fetch function do the others work well like
table.clear().draw(); table.rows.add(response.data); table.draw(); table.searchPanes.rebuildPane();table.

kthorngren

Good question - I wasn't paying close attention Do this instead:

filtercolumns(table);

The variable table contains the API so only it needs to be passed to the function:
https://live.datatables.net/qakelibe/1/edit

Kevin

makimax

Thanks kevin.

I applied everything to my project without using AJAX for the moment with a table generated by a php foreach loop. Each "td" is written by a php value from a mysql request <?=$myArray['data']?> . The table is ok but the searchpanes offers me too many filters although I defined targets: [0, 1] it gives me 5 filters so 3 other filters in addition. The only way is to replace the php value to each of these with simple text.
If I reverse the positions of the "td", the problem remains at the same position and offers me this new "td".
Without php values the searchpanes is good, but with php values everything works but I have more filters than I have configured. It is impossible for me to offer a test in this configuration.

So I tried to reuse the code without searchpanes, but with manual external filters. I reused the select2 multiple but it doesn't work well. Because of Regex I think but I tried otherwise but without success.
For example, i need to find tags like "System" into System Architect" : https://live.datatables.net/tuvosowi/1/edit

kthorngren

SearchPanes uses a threshold calculation to determine if the pane should be shown or not. You can read about the threshold here. You can use columns.searchPanes.show to force showing or hiding panes regardless of the threshold calculation. See this example. I think you will want to hide the panes you don't want shown.

For example, i need to find tags like "System" into System Architect"

Are you saying you want to split the options list so there is an option for System and another for Architect? If yes then possibly refactor this code into two loops:

                column.data().unique().sort().each( function ( d, j ) {
                    select.append( '<option value="'+d+'">'+d+'</option>' );
                } );

The first loops through the column data and splits at the space, ie '" "'. The result is concatenated onto an array of all results, ie ['Developer', 'System', 'Architect', ....]. The second loop will build the unique sorted options for the select.

Or are you wanting the select to have System Architect then search for both System and Architect. Take a look at the new 2.0 function option of column().search(). Here is a quick example:
https://live.datatables.net/soqehiga/1/edit

It simulates selecting System Architect, splits the selected value then determines if one of the strings is in the column data.

Kevin

makimax

In the SearchPanes version,
I had defined show:true, target 0. I didn't think it was necessary to set the others to false if the targets were set to "true". After adding show: false to all other columns it's OK. Thanks a lot!

In the external Select2 version
I updated my test case with a tags column to better understand my problem. the select multiple offers the same list as the tags into pills but if I select one it does not work and does not find any results. https://live.datatables.net/xoqetase/1/edit
I was trying to take the example of System Architect as if each one is a "tag"

kthorngren

Remove the regex begin string ^ and end string $ tokens from the regex expression in this code:

      var data = $.map( $(this).select2('data'), function( value, key ) {
        return value.text ? '^' + $.fn.dataTable.util.escapeRegex(value.text) + '$' : null;

With this the search string will look like ^tag 1$. This will find tag 1 if its the only option available.

Datatables strips the HTML tags for the filter operation, leaving just the text. Something like 1 tag 2. Removing the tokens to look like this:

      var data = $.map( $(this).select2('data'), function( value, key ) {
        return value.text ? $.fn.dataTable.util.escapeRegex(value.text) : null;

Will allow for searching each tag regardless of where it is located in the string. The search string will look like this: 1|tag 2.

https://live.datatables.net/xoqetase/2/edit

Kevin

makimax

In this example, if i select "tag 3" + "tag 4" + "tag 9" (in this order or another) the search must find only Brielle Williamson but the result is empty
https://live.datatables.net/qocepisi/1/edit