1. Home
  2. Docs
  3. Data Publisher
  4. Advanced settings

Advanced settings

The plugin uses jQuery DataTables to publish a table on a web page. The jQuery DataTables library has a lot of configurable options, which allow users of the library to change the look and feel of a DataTable.

The plugin uses a set of default options. To allow plugin users to overwrite these default options or add their own, the Data Publisher contains a Table options (advanced) column. Options entered in this column:

  • will be added to the default plugin options, or
  • overwrite the default plugin options.

Valid JSON

Your advanced settings MUST BE valid JSON!

If you enter invalid JSON, the plugin will discard your advanced settings and use the default. The plugin supports JSON validation for this column. This feature can be disabled (how to disable to JSON validator…). Be aware, even if you disable the JSON validator your advanced settings still must be valid JSON (use an online validator instead).

Β» What is JSON?

Some examples

Define in which order areas are shown:

  "dom": "iptlfr"

Starting from version 5.1.2 the user needs to press the ENTER key to start a search. Add the following option to perform a search while the user is typing. This was the default behavior in earlier versions and puts a heavier load on your server!

  "wpda_search_force_enter": false

Load all rows in the browser on page load:

CAREFULL – This increases your page load time! DO NOT use this with large tables!

  "serverSide": false

Remember table state:

  "stateSave": true

The killToken option removes the token check which is added to each request by default to prevent unauthorized usage of publication data sources:

  "killToken": true

Add a placeholder to the search box:

  "language": { "search": "_INPUT_", "searchPlaceholder": "Search..." }

Add an icon to the search box:

  "language": { "search": "_INPUT_", "searchPlaceholder": "🔍" }

Change the list of shown entries (lengthMenu = items shown in list, pageLength = default);

  "lengthMenu": [ 10, 20, 30, 50, 100 ], 
  "pageLength": 30

Turn off paging, load all data at one, set scroll bars:

  "paging": false,
  "scrollY": "150px", 
  "scrollCollapse": true,

Overwrite some plugin defaults:

  "fixedColumns": true, 
  "fixedHeader": true

JavaScript functions (more complex)

The following code auto updates a table each 10 seconds:

  "initComplete": "function(settings, json) { setInterval(function() { jQuery('#'+settings.sTableId).DataTable().ajax.reload(null,false) }, 10000) }"

Add javascript function which sets color depending on content:

  "fnRowCallback": "function(row, data, index) { if (data[1]=='Z 800 E ABS') { jQuery(row).find('td:eq(1)').css('color','black'); } else { jQuery(row).find('td:eq(1)').css('color','green'); } }" 

In this example, I’m using a javascript function. Please notice, that the function is written between double quotes. This is to maintain valid JSON. The value of property fnRowCallback is a string, which contains the function code.

Function fnRowCallback checks the value of column Brand Type. If the type is Z 800 E ABS, the text color is set to black. Otherwise it is set to green. You can see this code in action in real time below.

Revised JavaScript example

Charles Godwin advised to revise the example above to make it easier to understand, less error prone, and debuggable in the browser developer. In his revision the javascript code is added to an external function. The Code Manager is used to add the external javascript function to the web page. The revised advanced table options of your publication looks like this:

  "fnRowCallback": "function(row, data, index) { setColor(row, data, index); }" 

Use the Code Manager to add javascript function setColor to your web page:

function setColor(row, data, index) {
    if (data[1] == 'Z 800 E ABS') {
        jQuery(row).find('td:eq(1)').css('color', 'black');
    } else {
        jQuery(row).find('td:eq(1)').css('color', 'green');

Much cleaner! Thank you Charles! πŸ™‚

BrandBrand TypeColorPhotoAttachmentsPriceLicence PlateCategoryFuel TypeMileageEngine CapacityNo Cylinders
BrandBrand TypeColorPhotoAttachmentsPriceLicence PlateCategoryFuel TypeMileageEngine CapacityNo Cylinders

Custom renderers (even more complex)

WP Data Access allows plugin users to write their own responsive renderers. A renderer in it’s simplest form could look like this:

	"responsive": {
		"details": { "renderer": "function(api, rowIdx, columns) { let render_method = jQuery.fn.dataTable.Responsive.renderer.tableAll(); return render_method(api, rowIdx, columns.filter(column => column.hidden && column.data)); }" }

This example is a one liner that hides all empty responsive columns. I strongly advise to write a separate javascript renderer function using the Code Manager or another tool which allows you to add custom javascript functions to your web pages. The rewritten function above could look like this:

function my_renderer(api, rowIdx, columns) {
	let render_method = jQuery.fn.dataTable.Responsive.renderer.tableAll();
	return render_method(api, rowIdx, columns.filter(column => column.hidden && column.data));

The advanced option can then be rewritten like this:

	"responsive": {
		"renderer": "function(api, rowIdx, columns) { my_renderer(api, rowIdx, columns); }" 

Thanks again to my old friend Charles! πŸ™‚

Available options

jQuery DataTable options are very powerful. I haven’t tested them all. There are just too many combinations.

Here is a link to the jQuery DataTables options page:

The plugin installs extension Responsive by default. To use this extension just select Responsive from the dropdown list for column Output. If you want to use other extensions, you need to add them manually. After that you should be able to use extension options in the same way. Just add them to column Table options (advanced).

Addtional extensions can be found here:


Have you written some interesting JSON to handle specific or complex tasks? Please share them here! Add a message below. Other plugin users might benefit from your experience.

Was this article helpful to you? Yes 1 No

36 Replies to “Advanced settings”

  1. Hi, thanks for the great plugin, first of all. πŸ™‚

    I am trying to use the DataTables Buttons extension, but can’t get it to work. I went here to get the CSS and JS: https://datatables.net/download/release and tried to add them via header tags or via wp_enqueue. Neither way has worked so far and I’m seeing errors like “ReferenceError: jQuery is not defined” in the Browser console. So I guess I’m doing it wrong. :/

    I would appreciate any help or suggestions.

  2. Hi Michael,

    This looks like a loading sequence issue. The DataTables files are registered via the admin_enqueue_scripts hook , but they are enqueued only when they are need and just before a table is added to a page. If you add the button files via the admin_enqueue_scripts hook, they are enqueued before the DataTables and responsive files which results in an error.

    To fix this issue, you can use filter wpda_wpdataaccess_prepare. The solutions could look something like this:

    public function add_datatable_buttons( $html, $schema_name, $table_name, $pub_id, $columns, $table_settings ) {
    wp_enqueue_script( ‘jquery_datatables_buttons’ );
    wp_enqueue_style( ‘jquery_datatables_buttons’ );
    add_filter( ‘wpda_wpdataaccess_prepare’, ‘add_datatable_buttons’, 10, 6 );

    Presuming you registered the style and script via the admin_enqueue_scripts hook. More information about filter wpda_wpdataaccess_prepare can be found here:

    Hope this helps. If you succeed and your table is on a public page, I would appreciate if you could add a link. πŸ™‚ This is certainly an interesting topic for other plugin users as well.

    Best regards,

    1. Hi Peter, thanks for the help! Using the filter, I finally got the button to show. So… progress. πŸ˜€

      However, when I actually click it, I get “TypeError: e.action.call is not a function”. This is what I put in the Table Options: {“text”:”My Button”, “action”:”function(){alert(‘Click!’);}” } If I remove the “action” attribute or give it an empty string (“”) as value, the error disappears… but no function gets called either, of course. ^^

      This is the example I used as a template: https://datatables.net/extensions/buttons/examples/initialisation/custom

      Could it be that the String value for “action” is not correctly interpreted as a function by Data Tables? But I have to put a String here or else WPDA complains. What to do? πŸ˜€

  3. Hi Michael,

    Sorry, I’m getting the same results! This seems to be scope issue, but I can’t find a solution. There are some refrences to working examples, but they are no longer available. Maybe you can drop your question on the jQuery DataTables forum? If you find a solution, I would be greatful if you can share it.

    Best regards,

    1. No luck so far and I haven’t yet found anything helpful in the DT forum either. But I found some other things that do not work and are maybe related:

      1) Setting other callbacks actually gives similar errors. E.g, “rowCallback” or “drawCallback” will give me “TypeError: b.fn.apply is not a function jquery.dataTables.min.js:89:373”. This seems like the same issue, but unrelated to the buttons extension. Is the above example for “fnRowCallback” still working for you, Peter?

      2) I am unable to manipulate the Data Table object using the API. I added some custom JS to tun on document.ready:

      var table = null;
      if ( $.fn.DataTable.isDataTable( ‘#tableID’ ) ) {
      table = $(‘#tableID’).DataTable();

      if(table !== null){
      console.log(‘Doing API calls’);
      table.button().add(1, {text:’bar’});
      table.buttons().action( function(){console.log(‘Click’);} );

      None of the API calls seem to have any effect at all, however. I’ll keep searching a little, but I feel like I am at a dead end. :/

      1. Hi Michael,

        1. The fnRowCallback callback is still working. If you scroll up a little, you can see the bike demo. If scroll up a little more, you can also see the code.

        2. I don’t think these settings have any effect after initialization. You need to add these options on initialization.

        Can you send me the code? Maybe I can help!? If you prefer to send it in private you can use the contactform below.

        Best regards,

        1. Ok yeah, stupid question. Obviously they are still working. m( And they are for me now, too. Don’t know why they didn’t before…

          So I found a workaround. This is what I put in my Table Options:
          “initComplete”: “function() { addBtnAction(); }”

          addBtnAction then is defiend in some custom JS file and essentially calls: jQuery(“#tableID”).DataTable().button(0).action( function(){alert(‘bar’);} );

          If I put the “function() {…}” in double quotes here, I get the “not a function” error again. This feels pretty hacky, but at this point, I think, I’ll just roll with it. ^^”

          1. Great you fixed it! And thank you for sharing your solution! πŸ™‚

            I wasn’t aware of the initComplete function, but I image this will be of interest for other plugin users as well.


  4. hi Peter,

    i have some question
    1. how to ordering data? i try Default order/by not working.
    2. how to limit view. i use “pageLength”:5, but not working.
    3. how to hide/remove tfoot on table
    4. how to show only one last record.

    thank you

    1. hi Peter,
      i answear my own question.. lol

      1. you must set “Allow ordering?” to Yes and than add : 1,desc
      2. you must ser “Allow paging?” to Yes and than add “pageLength”:5, to Table Option
      3. I use CSS to remove tfoot
      4. I dont know how… lol

      Thank you

      1. Hi buzzing,

        Great styling! πŸ™‚

        And how good you are answering your own questions! πŸ˜‰ Regarding question 3, if you want to hide specific areas or want to change the sequence in which areas are shown, please check out option “dom”:

        Best regards,

  5. Hi Peter,

    Thank you for the most amazing plug-in, videos and documentation.

    I lack the in-depth knowledge. In your example above. How would you go about formating the price of the bikes to currency? You refer to installing extensions from datatables.net and there is a currency conversion plug-in. I can not work the javascript/json portion in Table options (advanced) to do it. Any help will be much appreciated.

  6. Hi Peter, thanks for the great plugin, first of all.

    There is anyway to make published table refresh without any user action (if there was an new row)?


  7. Hi Pedro,

    If you want the page to be refresh only when table data was added or changed, you’ll need to add server and client site logic. You might want to have a look at websockets if you want to do something like this.

    Alternatively you might do a periodic page update. Here is an example of a refresh every 30 second. Much easier! πŸ™‚

    Good luck!

  8. Amazing plugin… thanks for investing your time in this Peter. Bravo!

    I’m trying to remove the tfoot, and following the directions does not do it for me.

    The following ‘Table Options’ do not work:

    “fixedHeader”: [{
    “header”: true,
    “footer”: false

    “fixedHeader”: {
    “header”: true,
    “footer”: false

    “fixedHeader.footer”: false

    “fixedFooter”: false

    “header”: true,
    “footer”: false

    1. Welcome Daz! πŸ™‚

      There is no parameter to remove the tfoot element from the table. This is part of the plugin, not jQuery DataTables. So, you cannot remove it from the table options column. There are two ways to remove it though:
      (1) Change the plugin code
      (2) Using javascript

      If you want to change the plugin code:
      Open file WPDataAccess\DataTables\WPDA_Data_Tables and remove line 216 (presuming you are on version 3.1.1). Disadvantage is you have to edit the code after every update.

      Using javascript, add this javascript code to your webpage:
      jQuery(document).ready(function() { jQuery(‘#your_table_id tfoot’).remove(); });

      Hope this helps,

  9. How do I limit my table to only display the top 10 rows after sorting by a column? I don’t want the end-user to see multiple pages or be able to view the rest of the data.

    I have tried a few WHERE clauses and advanced options but no luck.
    Table name: ‘rankme’
    Column I wanted sorted DESC: ‘Score’ [column 1 in my setup]

    SELECT TOP 10 * FROM rankme;
    LIMIT 10;
    LIMIT 10 FROM rankme;

    1. I got this working if I put it in the WHERE clause to display only the top Score, but I need the top 10 values…
      Score = (SELECT max(Score) FROM rankme)

      1. Great you solved it Sean! Another way to limit your output is to add these options to your advanced settings:
        {“pageLength”: 10,”dom”: “t”}
        With pageLength set to 10 only 10 rows are shown. Setting dom to t removes the navigation elements and will unable the possibility to navigate to the next page. Might be helpful with your next publication… πŸ™‚

  10. Hi Peter, thanks for this great plugin.

    Could I insert an independent search field for each column?

    Thanks for help!

    1. Hi Javier,

      You have two option to add independent search fields:
      (1) Install the full text search add-on which allows you to add search fields with a few mouse clicks
      (2) Add the necessary logic yourself

      Option (1) will be available soon. I planned to release the add-on in June, but that’s going to be July. For option (2) you need to have PHP and JS skills. Filter wpda_wpdataaccess_prepare allows you to add your own jQuery DataTable options to your publication. You can find the documentation here:

      You will also need the documentation on this page:

      Please start here to get an idea of the search logic:

      After that you can combine your filter and search logic and add your own search fields. This can be a challenge! πŸ™‚

      Best regards,

  11. Hello sir,
    i made a table that displays the result of a MYSQL View (or table), but i need to add some filter using javascript variables or php variables,
    i tried the “fnRowCallback” option, using this logic:
    if (ligne_value==myvalue) => show
    else => hide
    but this affects only the frontend, and makes the paging a little confusing ( page1 => 5 lines ; page 2 => 10 lines….) because it’s filtered after displaying, not before.

    is there any solution to filter the MYSQL request using Javascript/PHP variables instead of %USERID% or %USER%

    1. Hi Salah,

      There are two ways to add a filter to a publication:

      (1) Use the Data Publisher to add a default where clause

      The Data Publisher contains an advanced section which allows plugin users to add a default where clause. The where clause is added to the publication at run time. You must use the Data Publisher to add a where clause. For security reasons this is not possible for table based publications.

      (2) Add parameters filter_field_name and filter_field_value to your shortcode

      This works for both, Data Publisher and table based publications. Here is the documentation:
      Please scroll down to the filter examples.

      Best regards,

      1. Hello,
        thank you very much,

        another question please :
        how can i add Export buttons to the Data Publisher Data Table,
        i did use the Datatable option : “buttons” like this :
        “buttons” : “[‘copy’,’csv’,’pdf’]”
        but nothing happend

  12. Hello Peter,

    Using this function I am able to color the full row using 1 argument, but how do i use 2 arguments / 2* “else”? (I am not a common Java script user).

    “fnRowCallback”: “function(row, data, index) { if (data[2]==’Orange’) { jQuery(row).find(‘td’).css(‘backgroundColor’,’orange’); } else { if (data[2]==’Red’) { jQuery(row).find(‘td’).css(‘backgroundColor’,’red’); } else { jQuery(row).find(‘td’).css(‘backgroundColor’,’#d3d3d3′); } }”

    kind regards, Erwin

    1. Hi Erwin,

      Looks like you are just missing a } to close your function. Can you add the URL of your publication if adding a } to the end of your function does not help? You can use the contactform if your prefer to send it in private.

      Best regards,

    1. Hi Ali,

      You can use CSS to right align a column. Every td element in the Data Publisher has a CSS class which contains the column name. Suppose your column name is employee_salary, you can right align that column by adding this CSS to your web page:

      td.employee_salary { text-align: right; }

      Hope this helps,

  13. Nice plugin !
    But I need advice – I have a table with two columns – “href” and “title”. I need to overwrite the output of the first column and hide the second. It should be like this using the table options:

    “columnDefs” : [
    { “visible”: false, “targets”: [ 1 ] },
    { “render”: “function( data, type, row ) { return ‘‘ + row[1] + ‘‘; }”,
    “targets”: [ 1 ]

    But how do I insert double quotes inside a function that is specified as a double-quoted string?

    ‘ does not work …

    1. Hi petr,

      Interesting question… πŸ™‚ Looks lik you are right! Double quotes do not work. I was able to reproduce and solve this error. Unfortunately I cannot release an update immediately as there are a number of outstanding changes. So it will be available with the next version. Hope you don’t mind. If you need a quick update for development I can upload an update, but I cannot garuantee it is bug free. Thank you for reporting!!

      Good to read you like the plugin πŸ™‚

  14. Hi Peter,

    I’m using fnRowCallback function to correctly filter data in the row, however I need to filter the data in the “child” class that’s below the row. It appears the code example only allows changes if the data is in the row and not in the expanded section below the row (if you click on the expand or compress toggle button). Is there functionality that allows changes to that child data?

    Thank you!

    1. Hi Cory,

      Interesting question! The child data is added to a child table. You could use jQuery to access the child table (class=wpda-child-table). You’ll need to write more javascript of course, so you might consider to put your javascript code into a separate function. You can use the Code Manager (https://wordpress.org/plugins/code-manager/) to add a javascript function to your page. Does that help?

      Sorry for letting you wait so long. Due to personal cirsumstances I was not able to answer your question earlier.

      Best regards,

      1. Thank you very much Peter, this does help! And thank you for the plugin recommendation, I was just thinking if there was a plugin that existed like Code Manager!

Leave a Reply

Your email address will not be published.