How do you loop through all columns of a table and tell if it is currently visible or not

Dwayne Jarman

Hi,

I'm looking to loop through all the columns in a table. I want to be able to store the column name and or index so that I can create a table that can be checked. The plan is to allow the user to be able to dynamically toggle the columns on/off. When they like what they see, they click a link and then the database updates with the settings. All of the updating, storage, and looping through for column visibility on next click I believe I can acheive. I just need to loop through all the columns and tell if they are visible or not.

What I currently do is load about 50 columns to the table the hide the ones that the users don't see. We use the same table for about 50 processes. So the columns needed are different. In what I've learned, once you load them all then you need to click on the toggle links to hide those. I currently use links that the user could click on, but I click on those for the user with jquery. So the concept is to just loop through all the columns, if it is visible, who cares... but if they are hidden then I need to grab the data value and the index to store and retrieve in the database.

I hope this is making sense.

Dwayne Jarman

Hi All,

What an amazing tool. I figured it out and want to share if you need it.

        var hiddencols = '';
        $.each(table.columns().visible(), function(key, value) {
            //console.log(key, value);
            if(value==false){
                if(hiddencols==''){
                    hiddencols = key;
                } else {
                    hiddencols = hiddencols+','+key;
                }
            }
        });
        console.log('hiddencols: '+hiddencols);

Dwayne Jarman

In case you are interested, this is how I would show after pulling the string hiddencols from database:

            //After loaded from db
            var str_array = hiddencols.split(',');
            for(var i = 0; i < str_array.length; i++) { 
                var column; 
                //Set column by which table is visible              
                if($('#example').is(':visible')){
                    column = table.column( str_array[i] );
                } else {
                    column = table2.column( str_array[i] );
                }

                //Set the column as false
                column.visible( false );
            }

allan

Very nice - thanks for sharing your solution with us!

Allan

Dwayne Jarman

So after 2 days of working out the bugs, I build a bunch of functions that you could replace the ajax calls with your site.

This is a complete solution how I implement this in sharepoint 2010. We display about 70 columns of data that are needded for different users at different times. I use normal links within the page that call the data to display in the the table which I call quick filters. When the user clicks on the ID, I store the user's name, the page, the id clicked, then all the visible columns. When the user clicks on the quick filter again, then the settings are retrieved, all columns are displayed, and if there is data for that page and quick filter, then the columns that are on record are hidden. There is also a process to remove that data so that the hard coded standard columns that are required for those fields are avaialble. This might be when you have a set of fields you want the user to see, they set the ones they want to see, then they realize they need to revert back to the standard listing.

Please note I did remove specific list setting references here so you will need to look those up by googling to see the _vti_bin/ListData.svc/ for your site.

/*
//JavaScript
var currUser = 'Current User'; //Set by your process
var preferences = [];
//Note: currentquickfilterid is the holder to store the link ID the user clicked.
//table and table2 are global variables that store the table object for each table.  This allows checking to see if there is a table displayed.  On each call to show the table, all other table occurrences are released.
*/
//Get the preference for a specific page for a user and load it to a global variable.
function GetPreferences(){
    var page = location.href.split("/").slice(-1);
    var tc = $.ajax({
       url: thisbase +"?$filter=((Page eq '"+page+"')and(DN eq '"+currUser+"'))",
                method: "GET",
                headers: {
                    "Accept": "application/json; odata=verbose",
                    "Content-Type": "application/json; odata=verbose"
                }
    });
    tc.done(function (data, textStatus, jqXHR) {
      //make a local variable easy to reference of the results, there should only be one result so we are referencing the first array in the object
      var d = data.d.results;               
      if((d==null)||(d=='undefined')){
          console.log('No preferences found for '+currUser);
          return false;       
      }
       preferences = d;     
    }); //end trips
    tc.fail(function (jqXHR,textStatus,errorThrown){ 
        alert("Unable to get user's preferences: " + jqXHR.responseText); 
        closeDialogue();
    }); 

}

//Remove preferences for a particular id then reload the preference settings to the global variable to prevent having to reload the page.
function RemovePreferences(){
    var pageid = $('#currentquickfilterid').val();
    if(pageid==''){
        alert('The selected quick filter was not detected, therefore no action was taken.');    
        return false;
    }
    var page = location.href.split("/").slice(-1);
    var tc = $.ajax({
       url: thisbase +"?$filter=((Page eq '"+page+"')and(Indicator eq '"+pageid+"')and(DN eq '"+currUser+"'))",
                method: "GET",
                headers: {
                    "Accept": "application/json; odata=verbose",
                    "Content-Type": "application/json; odata=verbose"
                }
    });
    tc.done(function (data, textStatus, jqXHR) {
      //make a local variable easy to reference of the results, there should only be one result so we are referencing the first array in the object
      var d = data.d.results;               
      var site = thisbase.toLowerCase().replace('-training','');    
      var pref = {};
      var hiddencols = '';
        if(($('#example').is(':visible'))||($('#example2').is(':visible'))) {
            pref.Settings = hiddencols;
        } else {
            alert('no table is visible, action cancelled');
            closeDialogue();
            return false;
        }

        if((d[0]==null)||(typeof d[0]=='undefined')){
            alert('No stored prefrences for the '+$('#currentquickfilter').text()+' found.\r\nNo Action was taken with this Quick Filter.'); //.replace('Active Quick Filter: ','')+' Quick Filter                  
        }
        else {
            var preferenceURL = site + "(" + d[0].Id + ")";         
            var call = $.ajax({     
                url: preferenceURL,
                type: "POST", 
                headers: { 
                 "Accept": "application/json;odata=verbose", 
                 "X-Http-Method": "DELETE", 
                 "If-Match": "*" 
                } 
             });                
            call.done(function (data,textStatus, jqXHR){
               GetPreferences();
               alert('Your prefrences for the '+$('#currentquickfilter').text()+' have been cleared.\r\nYour preferences have been reloaded so they are available the next time you click this Quick Filter.'); //.replace('Active Quick Filter: ','')+' Quick Filter 
               closeDialogue(); 
            });
            call.fail(function (jqXHR,textStatus,errorThrown){ 
                alert("Error in removing preferece for quickfilter: " + jqXHR.responseText);
                closeDialogue(); 
            });     
        }           
    }); //end trips
    tc.fail(function (jqXHR,textStatus,errorThrown){ 
        alert("Unable to get user's preferences: " + jqXHR.responseText); 
        closeDialogue();
    }); 
}

//Save preferences then reload the preference settings to the global variable to prevent having to reload the page.
function SavePreferences(){
    var pageid = $('#currentquickfilterid').val();
    if(pageid==''){
        alert('The selected quick filter was not detected, therefore no action was taken.');    
        return false;
    }
    var page = location.href.split("/").slice(-1);
    var tc = $.ajax({
       url: thisbase +"$filter=((Page eq '"+page+"')and(Indicator eq '"+pageid+"')and(DN eq '"+currUser+"'))",
                method: "GET",
                headers: {
                    "Accept": "application/json; odata=verbose",
                    "Content-Type": "application/json; odata=verbose"
                }
    });
    tc.done(function (data, textStatus, jqXHR) {
      //make a local variable easy to reference of the results, there should only be one result so we are referencing the first array in the object
      var d = data.d.results;               
      var site = thisbase.toLowerCase().replace('-training','');    
      var beforeSendFunction = function (xhr) {
                       xhr.setRequestHeader("If-Match", "*");
                       // Using MERGE so that the entire entity doesn't need to be sent over the wire.
                       xhr.setRequestHeader("X-HTTP-Method", 'MERGE');
      };
      var pref = {};

      var hiddencols = '';
        if(($('#example').is(':visible'))||($('#example2').is(':visible'))) {
            var visibility = null;
            if($('#example').is(':visible')){
                visibility = table.columns().visible();
            } else {
                visibility = table2.columns().visible();
            }

            $.each(visibility, function(key, value) {
                //console.log(key, value);
                if(value==false){
                    //console.log(key);
                    if(hiddencols==''){
                        hiddencols = hiddencols+key;
                    } else {
                        hiddencols = hiddencols+','+key;
                    }
                    //console.log(hiddencols);
                }
            });
            //console.log('hiddencols: '+hiddencols);
            pref.Settings = hiddencols;
        } else {
            alert('no table is visible, action cancelled');
            closeDialogue();
            return false;
        }

        if((d[0]==null)||(typeof d[0]=='undefined')){
            //alert('new record');
            //stringify the data for posting
            pref.Page = location.href.split("/").slice(-1).toString();
            pref.Indicator = $('#currentquickfilterid').val();
            pref.DN = currUser;
            var u = JSON.stringify(pref);

            var call = $.ajax({ 
                type: "POST",
                contentType: "application/json; charset=utf-8",
                processData: false,
                headers: {"Accept": "application/json;odata=verbose", "X-RequestDigest": $("#__REQUESTDIGEST").val()},
                url: site,
                data: u,
                dataType: "json"
            });
        }
        else {
            //stringify the data for posting
            var u = JSON.stringify(pref);
            if(administrator){console.log('(AO) commodity add/update: '+u);}
            var call = $.ajax({ 
                type: "POST",
                contentType: "application/json; charset=utf-8",
                processData: false,
                beforeSend: beforeSendFunction,
                url: site + "(" + d[0].Id + ")",
                data: u,
                dataType: "json"        
            });                                    
        }
        call.done(function (data,textStatus, jqXHR){
            GetPreferences();
            alert('Your prefrences for the '+$('#currentquickfilter').text()+' have been saved.\r\nYour preferences have been reloaded so they are available the next time you click this Quick Filter.\r\nNote: By saving your preferred columns, you are accepting that the columns could change and you may need to reorganize which columns are preferred by you if any changes are made to the table displayed.'); //.replace('Active Quick Filter: ','')+' Quick Filter 
            closeDialogue();            
        });
        call.fail(function (jqXHR,textStatus,errorThrown){ 
            closeDialogue();
            alert("Error in updating commodity: " + jqXHR.responseText); 
        }); 

    }); //end trips
    tc.fail(function (jqXHR,textStatus,errorThrown){ 
        alert("Unable to get user's preferences: " + jqXHR.responseText); 
        closeDialogue();
    }); 
}

Dwayne Jarman

//This is the function where the string of columns that were retrieved from the data base are used to hide the columns that were previously configured by the user.
function hideColumns(hiddencols){
    var str_array = hiddencols.split(',');
    for(var i = 0; i < str_array.length; i++) { 
        var column; 
        //Set column by which table is visible              
        if($('#example').is(':visible')){
            column = table.column( str_array[i] );
        } else {
            column = table2.column( str_array[i] );
        }
        //Set the column as false
        column.visible( false );
    }
}


$('#togglefilter').click(function(e){
    //Checking to see if one of the tables is defined in the page
    if((typeof table=='undefined')&&(typeof table2=='undefined')){
        alert('No action taken as WITS cannot detect a table is loaded to the page.');
        return false;
    }
    //Checking to verify that one of the tables is visible allowing the looping through the columns the user selected
    if(($('#example').is(':visible'))||($('#example2').is(':visible'))) {
        showDialogue('Setting your preferences...');
        SavePreferences();      
    }

     return false; 
 });

 $('#togglefilter2').click(function(e){
    //Checking to see if one of the tables is defined in the page
    if((typeof table=='undefined')&&(typeof table2=='undefined')){
        alert('No action taken as WITS cannot detect a table is loaded to the page.');
        return false;
    }
    //Checking to verify that one of the tables is visible allowing the looping through the columns the user selected
    if(($('#example').is(':visible'))||($('#example2').is(':visible'))) {
        showDialogue('Setting your preferences...');
        RemovePreferences();    
    }         
     return false; 
 });


//I conditionally show the columns based on the ID passed to the function to load the data.  Here, I first evaluate if preferences from the global object preferences exist, and if they do use those and ignore the hard coded column visibility settings.  This is done within another function, but I'm providing it here so others can utilize the appraoch.


    var hidethesecolumns = '';
    var currenttable = [];
    //console.log('preferences.length: '+preferences.length);

    if(preferences.length>0){
        //console.log
        currenttable = preferences.filter(function (elem) { return elem.Indicator == stage.replace(' ','-') });if(currenttable.length>0){
            hidethesecolumns = currenttable[0].Settings;
            //console.log('settings available: '+currenttable[0].Settings);
        }
    }

    if((hidethesecolumns!='')){
        hideColumns(hidethesecolumns);
    }



<!-- HTML: Here is the beauty.  We display two links and the user has to just click one or the other to save or remoe their preferences -->

    <div style='float: right; margin-right: 5px;'>
        Columns: <a href="" id="togglefilter" alt="Remember Columns">Remember</a>&nbsp;|&nbsp;<a href="" id="togglefilter2" alt="Forget Columns">Forget</a>
    </div>