Count value (1 or 0) under a specific column with editor

Count value (1 or 0) under a specific column with editor

YoDavishYoDavish Posts: 123Questions: 46Answers: 3
edited July 2019 in Free community support

Hello,

I'm trying to get a count for value = "Yes" i.e "1" and eventually No as well (value = 0) under the 4 column, (the first column is 0, correct?) I've tried using the example below but get this error:

jquery-3.4.1.min.js:2 jQuery.Deferred exception: Cannot set property 'textContent' of null TypeError: Cannot set property 'textContent' of null

//global
var = tab

$(document).ready(function() {
setupDataTables(); --> uses editor and setups with ajax (tab is defined in here)
setupDTSearches(); --> enables search boxes per column
   
 var filteredData = tab
        .column(3)
        .data()
        .filter( function ( value, index ) {
            return value == 1 ? true : false;
        } );

        
    document.getElementById('result').textContent = filteredData.count();
    document.getElementById('table_filter').addEventListener('keypress', function() {
    document.getElementById('result').textContent = filteredData.count();
    });
});

//HTML

Marked Yes: Marked No:

What am I missing?

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

This question has an accepted answers - jump to answer

«1

Answers

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,735

    You filter() code is here:
    http://live.datatables.net/hibihele/1/edit

    I suspect it has to do with this line var = tab. If you are wanting to make a global variable it probably should look like this var tab;.

    Kevin

  • YoDavishYoDavish Posts: 123Questions: 46Answers: 3
    edited July 2019

    Opps that was a typo,

    However, I put in the code:

    var filteredData = tab
    .column(3)
    .data()
    .filter( function ( value, index ) {
    return value == 1 ? true : false;
    });
    console.log("COUNT: "+filteredData.count());

    but only get a count of "0", I've changed it to different columns but the value is always the same. I've updated the field as well to have several "1" values but no luck.

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,735

    Then we will need to see a test case replicating the issue. Please update my example or provide a new example showing the problem.

    Kevin

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    My guess would be this line:

    document.getElementById('result').textContent
    

    Is there an element with the ID of result? If there is, we really need a test case as Kevin said.

  • YoDavishYoDavish Posts: 123Questions: 46Answers: 3

    @kthorngren I updated the code with the link below.

    http://live.datatables.net/hibihele/2/edit

    Although, I'm using the ajax call to tableserver.php to load data. Under the "Completed" column, is where I want to get the count for both Yes and No values.

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,735

    You code is not running. You have a syntax error - look in the browser's console. The important thing with the test case is to have an example of your data so we can see what is happening. You can create a Javascript variable containing an example of your data then use the data option to fill the table with the data. Just grab a few rows from your JSON response and add it using data.

    Kevin

  • YoDavishYoDavish Posts: 123Questions: 46Answers: 3

    @kthorngren

    Ok I've updated it and added sample data, the editor files to run now.

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    You'll need to give a new link URL - the code changes slightly with each edit.

    C

  • YoDavishYoDavish Posts: 123Questions: 46Answers: 3
    edited July 2019

    @colin Ops sorry here it is below:

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

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,735
    edited July 2019

    Your example still wasn't running. I removed the Editor part because the files weren't loading and we don't need the Editor for the problem you are asking about. You tried adding the data option to the ajax option which won't work, I fixed that. Your columns were defined as objects but your data is an array structure so I removed the columns option. The filter() method is working with your data.
    http://live.datatables.net/zexizifu/1/edit

    Please update the test case to show the issue.

    Kevin

  • YoDavishYoDavish Posts: 123Questions: 46Answers: 3
    edited July 2019

    @kthorngren it still doesn't work for me. I think it's a timing issue, as I noticed this morning when I refreshed the page, the count comes up first before DataTables has completed the ajax call to load the data into the table. I managed to capture an image of this below. I put a SetTimeout to wait 3 seconds before trying the get perform the function that gets the count and this has resolved the issue. Unless there is a better solution?

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,735
    edited July 2019

    Good find! That makes sense. The ajax call is async so you will want to execute that code after Datatables has populated the data. If you need the information once after initialization you can put that code into initComplete. Or you can use drawCallback if you need to update the data each table draw. Notice the use of var api = this.api(); in the examples. You will use this instead of the tab variable to access the API for either callback.

    Kevin

  • YoDavishYoDavish Posts: 123Questions: 46Answers: 3

    @kthorngren sorry I'm a little lost on how to use the drawbackCall function correctly. I'm trying to use the function getCount that will display the count but I'm sure I'm doing something wrong.

    tab = $("#table").DataTable({
                ......
        "initComplete": function () {
            tab.order([3,"asc"]);
            tab.draw();
        },
        "drawCallback": function (){
            var api = this.api();
            console.log( api.rows( {page:'current'} ).data() );
            api.DataTable({
                getCount(3,1,"yCount");
                getCount(3,0,"nCount");
            });
        },
    
  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,735

    Not sure what getCount is doing but my guess is you want something more like this:

        "drawCallback": function (){
            var api = this.api();
            console.log( api.rows( {page:'current'} ).data() );
            var yesCount = getCount(3,1,"yCount");
            var noCount = getCount(3,0,"nCount");
        },
    

    Kevin

  • YoDavishYoDavish Posts: 123Questions: 46Answers: 3

    @kthorngren sorry I should have included that too

    function getCount(col,data,id){
    var filteredData = tab
    .column(col)
    .data()
    .filter( function ( value, index ) {
    return value == data ? true : false;
    });

    //console.log("COUNT: "+filteredData.count());
    document.getElementById(id).textContent = filteredData.count();
    document.getElementById('table_filter').addEventListener('keypress', function() {
    document.getElementById(id).textContent = filteredData.count()
    });
    

    }

    When I run the updated code I get this error:

    jquery-3.4.1.min.js:2 Uncaught TypeError: Cannot read property 'column' of undefined
    at getCount (main.js:212)
    at k.fn.init.drawCallback (main.js:158)
    at jquery.dataTables.min.js:76
    at Function.map (jquery-3.4.1.min.js:2)
    at r (jquery.dataTables.min.js:76)
    at P (jquery.dataTables.min.js:31)
    at T (jquery.dataTables.min.js:31)
    at ha (jquery.dataTables.min.js:48)
    at e (jquery.dataTables.min.js:93)
    at HTMLTableElement.<anonymous> (jquery.dataTables.min.js:93)

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,735

    Uncaught TypeError: Cannot read property 'column' of undefined

    That's due to Datatables is still initializing and the tab variable has not been assigned yet. This might work:

    function getCount(col,data,id){
    var filteredData = $("#table").DataTable()
    .column(col)
    .data()
    .filter( function ( value, index ) {
    return value == data ? true : false;
    });
    

    Kevin

  • YoDavishYoDavish Posts: 123Questions: 46Answers: 3
    edited July 2019

    @kthorngren yep that works. The count for the yes value is correct, however, the count for the No value is off by 300 or so. weird.

    ah it's getting the total count for all sources on the yes and no values, rather than the source = '[source]'.

  • YoDavishYoDavish Posts: 123Questions: 46Answers: 3
    edited July 2019

    Is there a way to add to the filter count, to count on a specific value in a column? I.E.

    total records
    Yes = 10
    No = 5

    Total records
    for Yes, source = 'A', the count is 9
    for No, source = 'A', the count is 2

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,735
    edited July 2019

    Not sure what source is but if its accessible in the getCount() function you can change the comparison in return value == data ? true : false; to meet your needs. Maybe something like return (value == data && source ==='A') ? true : false;

    Kevin

  • YoDavishYoDavish Posts: 123Questions: 46Answers: 3

    @kthorngren I think I accidentally deleted my last comment somehow.

    It's getting closer but it's still not working correctly. "source" is another column that I found out that I have to filter for.

    I found a suggestion in the forums to count the rows if the 2 values match example here:

    http://live.datatables.net/xurijubo/8/edit

    However, when I try to do this, the val is "Undefined" and the second counts is always 0.

    Below is my updated code:
    col = 3
    input = 1
    id = 'yCount'

      function getCount(col,input,id){
       var filteredData = $('#table').DataTable()
    .column(col)
    .data()
    .filter( function ( value, index ){
     return value == input ? true : false;
    });
    
    count = 0;
    
    filteredData.rows().data().each(function(val, i) {
        if (val[3] == 1 && val[14] === 'CoPath') {
          count++;
        }
    });
    
    console.log("COUNT:"+count);
    // document.getElementById(id).textContent = filteredData.count();
    // document.getElementById('table_filter').addEventListener('keypress', function() {
        // document.getElementById(id).textContent = filteredData.data().count()
    // });
    

    }

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,735

    My guess is you don't want to use the first filter but only the second. Also you wouldn't use filteredData.rows().data().each(function(val, i) {. You would use $('#table').DataTable().rows().data().each(function(val, i) {, like this:

    function getCount(col,input,id){
    
    count = 0;
     
    $('#table').DataTable().rows().data().each(function(val, i) {
        if (val[3] == 1 && val[14] === 'CoPath') {
          count++;
        }
    });
     
    console.log("COUNT:"+count);
    // document.getElementById(id).textContent = filteredData.count();
    // document.getElementById('table_filter').addEventListener('keypress', function() {
        // document.getElementById(id).textContent = filteredData.data().count()
    // });
    

    If this doesn't help then please update your test case so we can take a look.

    Kevin

  • YoDavishYoDavish Posts: 123Questions: 46Answers: 3
    edited July 2019

    Yea it still shows up undefined still:

    5243main.js:213 Completed : undefined Source : undefined

    I copied the updated code here (I'm using editor JS+CSS with Autofill, Key, and Select options):

    http://live.datatables.net/zexizifu/6/edit?js,console,output

    Minus the ajax call,
    disabled setupDTSearch() <--sets up the search boxes above each column
    disabled findGetParameter() <-- places URL source parameter into source column to filter off of

    However, in the link above, it does show the correct number and also shows the "val" with a value. I'm lost as to why? Possibly another timing issue?

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,735

    Lets try it and see. We can add the xhr event and some console log statements to see the order of events.
    http://live.datatables.net/cucumolo/1/edit

    Interesting the drawCallback is called before the ajax request completes. I did not know that. If those data points do not change then you can simply move it to initComplete. Otherwise I would use a global variable as flag to know when ajax request is compete and check that flag in drawCallback. For example:
    http://live.datatables.net/hucacefi/1/edit

    Also noticed this in your initComplete:

            "initComplete": function () {
              var api = this.api();
                api.order([3,"asc"]);
                api.draw();
            },
    

    Generally it would be recommended to use the order option instead of using the API in initComplete. I changed this in the second example.

    Kevin

  • YoDavishYoDavish Posts: 123Questions: 46Answers: 3

    @kthorngren hmm the console count still shows as undefined. I decided to console log the api under the drawCallback

            var api = this.api();
            console.log(api);
    

    and it shows nothing under "rows", but the data has been moved to
    context > 0 > aoData

    Is that normal?

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,735

    You aren't using var api = this.api(); in drawCallback. I believe aoData is a legacy object and it would be normal.

    Did you add the initialization flag as I suggested?

    Kevin

  • YoDavishYoDavish Posts: 123Questions: 46Answers: 3

    Yep, I updated the code below: everything should be a mirror except the ajax call was different

    http://live.datatables.net/hucacefi/3/edit?js,console,output

    I put a console.log in the getCount to log when it starts after the initialization has been set to true.

    Here is the intilization is false no get count, after ajax complete, intialization is set and we see getCount() started with json objects

    I open up the json object and see nothing in "rows"

    Here is the data under context > 0 > aoData

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,735

    Please post the getCount() function you have on your page. Maybe there is something slightly different causing the problem.

    The other thing you can try is to call it from initComplete.

    Kevin

  • YoDavishYoDavish Posts: 123Questions: 46Answers: 3
    edited July 2019

    I tried moving it to into initComplete and same results. Here is my getCount():

    function getCount(col,input,id){
    console.log("getCount started");
    //var filteredData =
    //console.log($('#table').DataTable().column(col).data().filter( function ( value, index ){ return value == input ? true : false;}).length);
    var filteredData = $('#table').DataTable();
    console.log(filteredData);

    count = 0;
    filteredData.rows().data().each(function(val, i) {
        //val shows as undefined here
        console.log("Completed : "+val[3]+" Source : "+ val[14]);
        if (val[3] === 1 && val[14] === 'CoPath') {
            count++;
        }
    });
    console.log("COUNT:"+count);
    
    // document.getElementById(id).textContent = filteredData.count();
    // document.getElementById('table_filter').addEventListener('keypress', function() {
        // document.getElementById(id).textContent = filteredData.data().count()
    // });
    

    }

    Also, I just noticed this too in the console that the XHR is loading after the "count" occurs?

    initialized: true
    getCount started
    main.js:231 COUNT:0
    jquery-3.4.1.min.js:2 XHR finished loading: GET "http://..../tableServer.php?_=......".

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,735

    I wen back to your original test case and found that you are using columns.data:

            columns: [
                { data: "AssignedTo" },
                { data: "Note" },
                { data: "ProblemReason" },
                { data: "Completed" },
                { data: "specnum_formatted" },
                { data: "specclass_id" },
                { data: "lastname" },
                { data: "firstname" },
                { data: "DOB" },
                { data: "accessiondate" },
                { data: "prettyprint_name" },
                { data: "code" },
                { data: "datetime_taken" },
                { data: "lookup_display" }
            ],
    

    You need to access the data using object notation not array notation.

    if (val[3] === 1 && val[14] === 'CoPath') {

    Should be:
    if (val.Completed === 1 && val.Source=== 'CoPath') {

    Took a guess that column 14 is { data: "Source" }.

    Kevin

  • YoDavishYoDavish Posts: 123Questions: 46Answers: 3

    Yep, this is the complete columns section:

        columns: [
            { data: "AssignedTo" },
            { data: "Note" },
            { data: "ProblemReason" },
            { data: "Completed" },
            { data: "specnum_formatted" },
            { data: "specclass_id" },
            { data: "lastname" },
            { data: "firstname" },
            { data: "DOB" },
            { data: "accessiondate" },
            { data: "prettyprint_name" },
            { data: "code" },
            { data: "datetime_taken" },
            { data: "lookup_display" },
            { data: "source" }
        ]
    

    I tried to add just the "Completed" to test it

    count = 0;
    filteredData.rows().data().each(function(val, i) {
        //val shows as undefined here
        console.log("Completed : "+val.Completed[3]);
        if (val.Completed[3] === 1) {
            count++;
        }
    });
    console.log("COUNT:"+count);
    

    But it still shows undefined. When I try to comment out the "columns section and run it gives me this error below:

    DataTables warning: table id=table - Requested unknown parameter '0' for row 0, column 0. For more information about this error, please see http://datatables.net/tn/4

This discussion has been closed.