columns().cache() does not return column data when datatables uses ajax

jasoncaditrack

I have successfully implemented Datatables to display single database tables, complete with column filters.
Originally, I passed the table data as an array of JS Objects using the data: option in the initialization object ( I retrieved the data from server using js fetch()/promise), . Everything worked correctly.

I subsequently replaced the data:option in the initialization object with the ajax:option, to retreive data from the server.
The table continues to load perfectly, but now the column filters are empty, i.e. no longer show the set of unique column values for filtering.

My initial diagnosis is that somehow the table data from data:option that WAS available, is no longer available from cache() method, simply because I changed from data: to ajax:option in the initialization object.

Why would ajax data successfully load the table, but not be available to populate the column filter options?

The code below sets up the column filter select/options, and appears to be the problem, because the

                .cache( 'search' )table.columns.cache() 

no longer seems to return any values when using ajax:

  /* Set up the search input elements at the footer of the table*/
        var colid = 1;
        lotsTable.columns( '.select-filter' ).every( function () {
           $( 'th' ).addClass('small');
            var that = this;
            // Create the select list and search operation
            var select = $('<select />')
                .appendTo(
                    this.footer()
                )
                .on( 'change', function () {
                    that
                        .search( $(this).val() )
                        .draw();
                } );

   /* Get the search data for the first column and add to the select / option list*/
            select.append('<option  value="" selected>Search..</option>');
            select.attr('id',colid);
            colid++;

            this
                .cache( 'search' )
                .sort()
                .unique()
                .each( function ( d ) {
                    select.append( $('<option value="'+d+'">'+d+'</option>') );
                    select.attr('placeholder', 'enter seach terms here');
                    select.addClass('footerfilter');
                } );
        } );

In case it is relevant, I show below the table initialization, with the data: option commented out, and replaced with ajax:

    // DataTables initialisation
        var lotsTable = $('#lotstable').DataTable( {
          dom: '<if>t<B<"clear"><pl>>',
          buttons: [
            {
              text: 'Clear Search',
              className: 'btn btn-success',
              action: function ( e, dt, node, config ) {
                dt
                .search( '' )
                .columns().search( '' )
                .draw();
                //$('.footfiller > option').eq(5).attr('selected','selected');
                //$('#2').val($('#2 option:first').val());
                $('.footerfilter').val($('.footerfilter option:first').val());
              },
            },
            {
              extend: 'copy',
              text: '<u>C</u>opy',
              key: {
                  key: 'c',
                  altKey: true
              },
              className: 'btn btn-success'
            },
            {
              extend: 'excel',
              text: 'e<u>X</u>cel',
              key: {
                  key: 'x',
                  altKey: true
              },
              className: 'btn btn-success'
            },
            {
              extend: 'pdf',
              orientation: 'landscape',
              footer: 'true',
              text: '<u>P</u>df',
              key: {
                  key: 'p',
                  altKey: true
              },
              className: 'btn btn-success',
            }
          ],
          fixedHeader: true,
          scrollY: 650,
          lengthMenu: [ 25, 50, 100,10000 ],
          pageLength:  50,
          paging: true,
/*
          !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
          data: lotsData,
          !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
*/
            ajax: {
              url: apiHttpURL,
              dataSrc: 'assocResponse',
          },

          columns: [
                    { data: 'hold',
              render: function(data,type,row){
                if ( type === 'display' ) {
                  return row.status+' '+ row.comment;
                }else{
                  return row.status;
                }
              },
              orderable: false,
              className: 'check-background select-filter'
            },

            { data: 'lot_alpha',
              render: function(data,type,row){
                if ( type === 'order' ) {
                  return row.lot_number;
                }else{
                  return data;
                }
              },
              className: 'text-center font-weight-bolder select-filter'
            },

            { data: 'item_name',
              orderData: [ 2, 7,6 ],
              className: 'small  select-filter'
            },

            { data: 'item_num',
              orderData: [ 3, 7 ,6],
              className: 'select-filter'
            },

            { data: 'lot_expiry',
              orderData: [4, 3, 6 ],
              className: 'text-center select-filter'
            },

            { data: 'src_type' ,
              orderData: [ 5, 7,6 ,2],
              className: 'text-center font-weight-bolder select-filter'
            },

            { data: 'work_order' ,
              render: function(data,type,row){return '(wo#:'+row.wo_num+') '+ row.src_number},
              className: 'select-filter'
            },

            { data: 'src_date' ,
              className: 'text-center select-filter'
            }
      ],
          order: [[1, 'desc']],
        } );

allan

http://live.datatables.net/futawero/1/edit

Looks okay to me. Do note that you must wait for the Ajax data to load before you can access it.

Beyond that, if this doesn't help, please link to a test case showing the issue.

Allan

jasoncaditrack

Thanks!
The problem was exactly as you described - the code to create the select/option elements was running before the ascyn ajax option had completed retrieving data.

I needed to include the column filter build in the table initialization , under the Initialisation complete callback "initComplete".