HTML Widget: How to add column filters and search capability to any jive table on an overview page.

Version 4

    The Problem

     

    Users need the ability to column-filter and search across large tables and also maintain the ability to edit the table.

    Formatted Text widgets do not permit JavaScript to be added to them and even if they did, the script would be stripped when a non-admin edited the table in the widget.

    A plugin such as Ryan Rutan's Managed Snippets Plugin is a possibility but will take some time to build.

    What can we do in the meantime?

     

     

    The Solution


    The answer I came up with was to house the JavaScript in an HTML widget stored at the bottom of the overview page that contains your formatted text widget and table(s). The script would scan its parent page for any tables containing the trigger text '{filter}' and inject the necessary html select elements and search box into the table which are coded to call the functions in the iframed HTML widget.

     

    Please let me know if you find any bugs or have suggestions for enhancements.

     

    table filter ani.gif

     

    Note that having the HTML widget visible and editable by the group admins posed another potential problem though in that they may attempt to edit the widget thus causing Jive to strip out the script. I therefore added a check that only allows editing of the HTML widget in groups for system administrators. Space admins can also edit the widget because they are permitted to save JavaScript.

    The widget will appear in groups like this for non-system admins:

     

    non-editable.PNG

     

     

    How to use

     

    1. Create a formatted text widget on your overview page and add your table.

    filter step 1.PNG

     

    2. Add a new line below each table heading and add the text {filter} to those columns you require a filter on.

    filter step 2.PNG

     

    3. For neatness, you might want to set the text colour of the {filter} text to be the same as the cell background so that its not visible when the table loads. Once complete, save your formatted text widget.

    filter step 3.PNG

     

    4. If you're adding this to a group or project overview page. have an admin create a HTML widget on your overview page and paste the table filter JavaScript into it. Choose to Hide the header and Border and Save it. Position the widget at the bottom of your overview page.

    filter step 4.PNG

     

    5. Publish your page and you should see the table filters and search box added to your table.

    filter step 5.PNG

     

     

    The JavaScript

     

    I include the full code although you might want to use the minified version. If your community is using the Manage Files capability, it would be better to save this as a .js file in an open space and refer to it via script tag.

    I'm sure my JS isn't as efficient as it could be but its hopefully good enough. I tend to go for readability over minimalist efficiency.

     

    <script>
        /* Add Table filters and search box to Jive tables
        Matt Dickens
        Usage:
        For use on Jive Space, Group or Project overview pages.
        Have an admin create an HTML widget at the bottom of your page and paste this code within script tags
        If this code is being added via a script tag, be sure to correctly terminate it and not self terminate the tag.
        Self terminating the script tag will result in the HTML widget not being iframed by Jive and the code failing.
        Any tables on the overview pages that have the text {filter} somewhere in them will be acted upon.
        Add {filter} to the column headings you require a filter to be added to. It is recommended you add this text on
        a new line and in the same colour text as the background of the table header so as to hide it.
        Save your table and your overview page to see fully functioning column filters added to your table.
        A search bar will also be added to the top left of each table.
      */
        var $;
        var frameid = window.frameElement.id; //Jive puts JS into an iframe so we'll need the frameid so we can instruct the selects to call the functions in this frame.
        runWhenJQueryIsLoaded();
    
    
        function runWhenJQueryIsLoaded() {
            if (window.parent.$j) {
                $ = window.parent.$j; //We'll use the parent's jQuery
                widgetEditability();
            } else {
                setTimeout(runWhenJQueryIsLoaded, 50);
            }
        }
    
    
        function widgetEditability() {
            var framenum = frameid.substring(12);
            if (window.parent.document.getElementById('jive-widgets-panel')) { // This overview page is being edited
                $('#jive-widgetframe_' + framenum).show(); //Unhide this HTML widget
                if(window.parent.containerType != "14"){ //This is a Group or Project overview page. Administrators of Spaces can save JavaScript.
                   $('#jive-widgetframe-title_' + framenum).text("THIS HTML WIDGET CONTAINS JAVASCRIPT FUNCTIONS. AS SUCH IT IS ONLY EDITABLE BY SYSTEM ADMINS.");
                   var restEndPoint = "/api/core/v3/admin/properties?count=1";
                   var data = $.ajax({type: "GET", url: restEndPoint, async: false}).responseText;
                   var jsonString = data.replace(/^throw [^;]*;/, '');
                   var json = JSON.parse(jsonString);
                   if (json.list) { //if system properties can be accessed, this must be an admin so we can allow the widget to be edited.
                       $('#jive-widgetframe-edit_' + framenum).show(); //Show this html widget's edit button
                   } else {
                       $('#jive-widgetframe-edit_' + framenum).hide(); //Hide this html widget's edit button
                   }
                }
            } else {
                $('#jive-widgetframe_' + framenum).hide() //Hide this whole html widget frame in read mode ...
                initialise() //and run the code.
            }
        }
    
    
        function initialise() {
            $('table:contains("{filter}")').each(function(t) {
                var tabid = '#f1lt3rHandle' + t;
                $(this).attr("id", tabid.substring(1)); //Give the table a unique id
                $(tabid + ' th').each(function(i) { //step through each of the column headings
                    if (i == 0) {
                        $(tabid + ' thead').append('<tr></tr>'); //add the additional row to house the drop downs
                    };
                    $(tabid + ' thead tr:nth-child(2)').append('<td></td>'); //add the cell to house the filter
                    var indx = parseInt($(this).index()) + 1;
                    if ($(this).text().indexOf("{filter}") != -1) { //If this is a column needing a filter
                        var FTLabel = $(tabid + " thead tr:nth-child(1) th:nth-child(" + indx + ")").text().replace("{filter}", ""); //set up the default value/label
                        $(tabid + " thead tr:nth-child(2) td:nth-child(" + indx + ")").html('<select id="' + tabid.substring(1) + '_select' + indx + '" class="sparkTblFilters" onkeyup="onchange();"><option value="^.">Select ' + FTLabel + '</option></select>').change(function(event) {
                            window.parent.document.getElementById(frameid).contentWindow.applyFilters(indx, tabid);
                        }); //Inject the filter into the DOM and set it to call the applyFilters function on change
                    }
                    //Copy the style from the column heading cell to this newly created cell.
                    $(tabid + " thead tr:nth-child(2) td:nth-child(" + indx + ")").attr("style", $(tabid + " thead tr:nth-child(1) th:nth-child(" + indx + ")").attr("style"));
                    $(this).text($(this).text().replace(/{filter}/g, "")); //Remove the {filter} text the user added to indicate a filter was needed.
                });
                updateOptions(tabid); //Calculate the options available in the filter
                //Now add the search bar and set it to filter the table as the users type.
                $(tabid).before($('<input type="text" id="' + tabid.substring(1) + '_search" style="margin-bottom:8px; text-indent: 5px" placeholder="Type to search">'));
                $(tabid + '_search').keyup(function(e) {
                    var code = e.keyCode || e.which;
                    if (code == 8 || code == 46) { //If backspace or delete key has been pressed, we're potentially increasing the number of matching rows
                        applyFilters('search', tabid); //so we need to make sure the filters are re-applied
                    } else { //we're only going to be reducing the number of rows so just ..
                        applySearch(tabid); //apply search
                    }
                });
            });
        }
    
    
        function applySearch(tableid) {
            querystring = $.trim($(tableid + '_search').val()); //what is the string being searched for
            if (querystring != "" && querystring != "Type to search") {
                var $rows = $(tableid + ' tbody tr:visible'); //get hold of all the visible rows
                var val = $.trim(querystring).replace(/ +/g, ' ').toLowerCase();
                $rows.filter(function() { //Filter the table rows down to those matching the query string
                    var text = $(this).text().replace(/\s+/g, ' ').toLowerCase();
                    return !~text.indexOf(val);
                }).hide();
                var styles = {
                    "border-color": "red",
                    "border-width": "2px"
                }; //make sure the user knows a search filter is being applied by making the search box red
            } else {
                var styles = {
                    "border-color": "gray",
                    "border-width": "1px"
                };
            }
            $(tableid + '_search').css(styles);
            updateOptions(tableid); //Calculate the options available in the filter
        }
    
    
        function applyFilters(col, tableid) {
            if (col == "search" || $(tableid + '_select' + col).val() == "^.") { //If this is a reset of this filter
                $(tableid + ' tbody tr').show(); //unhide all the table's rows
                applySearch(tableid);
            }
            $(tableid + ' .sparkTblFilters').each(function(i) { //Step through each of the filters in this table
                var styles = {
                    "border-color": "gray",
                    "border-width": "1px"
                };
                var filtervalue = $(this).val();
                i = i + 1;
                if (filtervalue != "^.") { //for those that have something selected
                    var styles = {
                        "border-color": "red",
                        "border-width": "2px"
                    };
                    $j(tableid + ' tbody tr:visible td:nth-child(' + index + ')').each(function() { //Step through each of the visible cells in this column
                        if ($j(this).html().indexOf('<p>')>-1) {
                             var match = false;
                             $j(this).find('p').each(function() { //Step through each paragraphs within the cell in this column
                               if ($j(this).text() == filtervalue) { //compare the cell's text to the filter value and ..
                                    match = true;
                               }
                             });
                             if(match == false){$j(this).parent().hide()}; //hide the cell if it matches.;
                             }else{
                                  if ($j(this).text() != filtervalue) { //compare the cell's text to the filter value and ..
                                    $j(this).parent().hide(); //hide the cell if it matches.
                                  }
                             }
                    });
                }
                $(this).css(styles);
            });
            updateOptions(tableid); //Calculate the options available in the filter
        }
    
    
        function updateOptions(tableid) {
                $j(tableid + ' .sparkTblFilters').each(function(i) { //Step through each of the filters in this table
                var selectedVal = $j(this).val();
                var items = [],
                labeloption = "",
                options = [];
                var index=$j(this).parent("td").index()+1;
                 $j(tableid + ' tbody tr:visible td:nth-child(' + index + ')').each(function() { //Step through each of the cells in this column
                     if ($j(this).html().indexOf('<p>')>-1) {
                         $j(this).find('p').each(function() { //Step through each of the cells in this column
                             if ($j.trim($j(this).text()) != "" && $j.inArray($j.trim($j(this).text()), items) == -1) { //if its not empty and not already in the array
                                 items.push($j.trim($j(this).text())); //add the value to the array
                             }
                         });
                     } else {
                         if ($j.trim($j(this).text()) != "" && $j.inArray($j.trim($j(this).text()), items) == -1) { //if its not empty and not already in the array
                             items.push($j.trim($j(this).text())); //add the value to the array
                         }
                     }
                });
                labeloption = $j(this).find('option:eq(0)').text();
                if (selectedVal != "^.") {
                    labeloption = labeloption.replace('Select ', 'Reset ');
                } else {
                    labeloption = labeloption.replace('Reset ', 'Select ');
                }
                labeloption = '<option value="^.">' + labeloption + '</option>';
                options.push(labeloption);
                $j.each(items.sort(), function(a, item) { //sort the array and step through it
                    options.push('<option value="' + item + '">' + item + '</option>') //construct the options and add them to an array
                });
                $j(this).empty().append(options.join());
                $j(this).val(selectedVal); //Make sure to select the value that was previously set
            });
    
        }
    </script>
    
    
    
    

     

    The specified item was not found.

    24/02/15 - Applied fix to address schoolboy error in previous version when cycling through columns values where I was using the index of the each loop of every column that had a filter rather than the index of the actual column. It therefore only worked as long as you didn't have any gaps in the columns that had a filter. i.e. columns1,2 and 3 having filters worked fine but having columns 1 and 3 or 2 and 3 or just 3 would fail to render the drop down list correctly.