Can I set a filter from one page to another DataTable?

arie0512 · February 3

Hello,

I have an overview page where they can see how many sub categories one person had, for example a person Joe Doe have an overview page which has said it had 8 requests.

I want to make from this number 8 a link to another existing DataTable (query.php) and open this page which this 8 records.

The query.php looks like this:

<div class="pb-5">
    <table id="table-request" class="table-responsive stripe hover row-border compact nowrap">
      <thead>
        <tr>
          <th>Clientnumber</th>
          <th>Name</th>
          <th>Product</th>          
        </tr>
      </thead>
      <tbody>          
      </tbody>  
    </table> 
  </div>

$(document).ready(function() {

new DataTable('#table-request', {
ajax: {
    url: '/crm/ajax/request-data.php',
    type: 'POST'
},
columns: [        
    { data: 'request.clientnumber' },        
    { data: 'request.name' },
    { data: 'request.product' }
],
columnDefs: [
  { targets: [0], className: 'dt-body-left'},
  { targets: [0], render: DataTable.render.number( '.', ',', 0 ) }
],      
order: [[0, 'asc']],
pagingType: 'full_numbers',
pageLength: 25, 
language: {
  url: 'https://cdn.datatables.net/plug-ins/1.11.5/i18n/nl-NL.json'
},          
search: {
  smart: false
} 
});

If the client has clientnumber 3, how can I make a link from the overview page which opens the query.php and filter out only clientnumber 3? So only 8 records are visible?

Kind regards,

Arie

kthorngren · February 3

You can use the DeepLink plugin to get the search string parameter and perform a search.

Alternatively you can use Javascript URLSearchParams to get the query parameters and use either search or column().search() as appropriate.

Kevin

arie0512 · February 4

Hi Kevin,

Tnx for pointing me the the Deeplink plugin.

I have now an URL on the overview page, query.php?search.search=3 which loads the query.php but unfortually it loads every record, not only clientnumber 3.

I have edit the query.php with this:

<script src="https://cdn.datatables.net/plug-ins/2.3.7/features/deepLink/dataTables.deepLink.min.js"></script>

$(document).ready(function() {

$('tabel-request').DataTable( $.fn.dataTable.ext.deepLink( [
    'search.search', 'order', 'displayStart'
]));  

new DataTable('#request', {
ajax: {
    url: '/crm/ajax/request-data.php',
    type: 'POST'
},
....
});

Any idea why I get all the records in stead of only clientnumber 3?

And is it possible to look into a certain column for clientnumber? Because in the other columns the number 3 can also exist. I only need to look into column 0.

Best regards,

allan · February 4

Are you able to link to the page so I can take a look at it please?

$('tabel-request').

Is it missing a # for an ID selector? How does it relate to the #request table? Are you displaying two tables on this results page?

And is it possible to look into a certain column for clientnumber?

Yes, you can perform a filter on a specific column. column().search() with the API or searchCols for initialisation.

I'd focus on getting the search information working first.

Allan

kthorngren · February 4

Any idea why I get all the records in stead of only clientnumber 3?

It doesn't look like you are using server side processing so if you want to only return certain records for the ajax request to /crm/ajax/request-data.php then you will probably want to use ajax.data to send the search parameters to the server and the server script will need to filter the results based on the sent search parameters. The deeplink plugin won't be useful for this case.

Kevin

arie0512 · February 11

Hello,

Thanks for getting back to me, I have made an page where you can see what must be done.

The overview page is https://pma.info/crm/overview.php?id=1x1759928991x68e6629f63f905srAdO8wqTWom4wavnu7SiUKLowQEAneAQfgkeb7

Here you see record number 1 (John Doe) with 8 requests.

Now I want to click on the 8 Requests button so it will open the requests.php page with only those 8 records shown in the table.

But when I click now on this button, all records of the requests.php will be shown (total of 12 in this demo).

as you can saee, I have in the url "?search.search=1" so what I want is that only the requests with clientnumber 1 are shown.

I want also that the search query is only for column 0 (clientnumber) because the number 1 can also be in another column.

The code in requests.php is:

new DataTable('#table-requests', {
ajax: {
    url: '/crm/ajax/requests-data.php',
    type: 'POST'
},
columns: [        
    { data: 'requests.clientnummer' },        
    { data: 'overview.achternaam' },   
    { data: 'requests.polisnummer' },
    { data: 'requests.verzekeringsproduct' },
    { data: 'requests.maatschappij' },
    { data: 'requests.ingangsdatum',
      render: function (data, type, row) {
        return moment(new Date(data).toString()).format('DD-MM-YYYY');
      }
    },
    { data: 'requests.einddatum',
      render: function (data, type, row) {
        return moment(new Date(data).toString()).format('DD-MM-YYYY');
      }
    },
    { data: 'requests.status' },  
    { data: 'requests.unieke_url' }        

], 
columnDefs: [
  { targets: [0], className: 'dt-body-left'},
  { targets: [0], render: DataTable.render.number( '.', ',', 0 ) },
  { visible: false, targets: [8] },
  { searchable: false, targets: [3,4,7] }
],      
order: [[0, 'asc']],
pagingType: 'full_numbers',
pageLength: 25, 
language: {
  url: 'https://cdn.datatables.net/plug-ins/1.11.5/i18n/nl-NL.json'
},          
search: {
  smart: false
} 
});

When I add this code above the new DataTable it will give an error about "Cannot reinitialise DataTable" which is logical.

$('#table-requests').DataTable( $.fn.dataTable.ext.deepLink( [
    'search.search', 'order', 'displayStart'
]));

How can I add this code so I can make an link to this page with only showing all the requests of John Doe but also make every record visible on the page https://pma.info/crm/requests.php

allan · February 11

You need to merge the object returned from deepLink with your standard initialisation.

Given that the search is the only thing you appear to want to use (despite order and displayStart also being listed in the array), I would suggest you simplify by removing the deepLink stuff and just use:

new URLSearchParams(location.search).get('search.search')

(or simply further and just use search as the query parameter rather than search.search).

Then you can do:

search: {
  smart: false,
  search:  new URLSearchParams(location.search).get('search.search')  || ''
}

Allan

arie0512 · February 12

Ok, I have remove the deeplink and just added the URLSearchParams.

search: {
  smart: false,
  search:  new URLSearchParams(location.search).get('search')
}

But if I go to this URL https://pma.info/crm/requests.php?search=34 I just want the search query to look only in column 0 and it must be the exact client number (so client 344 must not be shown)

Is that possible?

kthorngren · February 12

Use searchCols to define a column specific search. Use regex or exact instead of the default smart mode. See the DataTables.SearchOptions for details.

Kevin

arie0512 · February 13

I am sorry but still can't figure it out

I think I haven't clearly explain what I want to do.

I have a good working DataTable with a working search function.

What I want is that I can have an link to this DataTable page which shows me only some records.

So https://pma.info/crm/requests.php gives me all the records and https://pma.info/crm/requests.php?search=34 gives me only these records where exact the number 34 is in the first column.

Is that possible?

Best regards,

Arie

kthorngren · February 13

In a previous response I suggested using the ajax.data to send the search parameter to the server to fetch only those records matching the search parameter. If no search parameter is supplied the the ajax.data option can be used to send an empty value for that parameter. The server will use that parameter to filter the data sent back to the server via the ajax option.

Or, if you want to retrieve all of the data and have filtered at the client then column().search() in initComplete to filter the full data set at the client.

Kevin

kthorngren · February 13

Also I believe that when using searchCols and empty search string will result in all the rows being shown. You could try a Javascript ternary operator in searchCols. Something like this:

    searchCols: [
        { 
          search: new URLSearchParams(location.search).get('search') ? new URLSearchParams(location.search).get('search') : '',
          exact: true
        },
        null,
.....
    ]

Kevin

arie0512 · February 16

Hi Kevin,

Tnx a lot, it's working like a charm now!

First I was focussing on the DeepLink and forgot all about the searchCols option

Can I set a filter from one page to another DataTable?

Can I set a filter from one page to another DataTable?

Answers

Howdy, Stranger!

Categories

DataTables

Can I set a filter from one page to another DataTable?

Can I set a filter from one page to another DataTable?

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables