Custom Grid View Filter - search all grid columns in a view

joivan_hedrickjoivan_hedrick Cireson Consultant Advanced IT Monkey ✭✭✭
edited August 24 in Cireson Uploads
Purpose: Allows quick searching of any text that may appear in a grid. After placing this script in customspace, it will add a textbox to the OOB work item views, allowing you to search all columns using minimal effort.

Tips and tricks: 
1. This script will set a filter on all columns. You can still set additional filters against a column the normal way, using hte column header.
2. When you want to clear all filters, you can press the escape key whenever the search textbox has focus.Alternatively, you can click on the Reset View button.



Download the attached zip file below:

Comments

  • TysonTyson Member IT Monkey ✭
    We have this installed in our environment and our users love it. Thanks JV!
  • Adrian_MataiszAdrian_Mataisz Customer Advanced IT Monkey ✭✭✭
    With multiple pages will filter show content from all pages or just the page you are in?
  • joivan_hedrickjoivan_hedrick Cireson Consultant Advanced IT Monkey ✭✭✭
    It will show results from all pages. 
    This does still respect the the GridViewRecordCap setting. For example, if your settings have the item GridViewRecordCap set to 2500 items, and your current view shows the maximum set number of 2500 items, then this search will only search those 2500 items in the grid (across all pages). 
  • Peter_NordqvistPeter_Nordqvist Customer Adept IT Monkey ✭✭
    Is it possible to get this working on Promoted Views? Great solution btw :)
  • joivan_hedrickjoivan_hedrick Cireson Consultant Advanced IT Monkey ✭✭✭
    Aye, it is possible to make it work for promoted views, but would require a little bit of debugging. 

    I initially had some issues getting the filter to work consistently with promoted views. For any one row, it would only return a result if all string columns in that one row had non-null data. If at least one column was null, then that row would be omitted from the search results whether it was a valid match or not. It's probably an awkward filter configuration that I overlooked, but I haven't pursued it yet. 
  • JD_KeithJD_Keith Customer IT Monkey ✭
    Great solution @joivan_hedrick !  If we could get this working for promoted views, that would be the greatest.
  • joivan_hedrickjoivan_hedrick Cireson Consultant Advanced IT Monkey ✭✭✭
    Done! The newly-attached file in the initial post will now work with all promoted views, as well as OOB views, saved query views, and dashboard views. 
  • JD_KeithJD_Keith Customer IT Monkey ✭
    @joivan_hedrick You're the man!  Thank you so much!
  • Alistair_PleasantAlistair_Pleasant Customer IT Monkey ✭
    For some reason I'm having issues with the filter box not showing on IE but only when open within the My Work page! Works perfectly well in Chrome & Firefox!!!

    Has anyone else experienced this?
  • joivan_hedrickjoivan_hedrick Cireson Consultant Advanced IT Monkey ✭✭✭
    While testing in Portal v8 it seems that in IE, the page title loads without simultaneously loading the kendo grid. Almost definitely a timing issue, where IE simply loads the elements a few ms slower. One quick fix would be to move the observer.disconnect() line into the above if statement.

    I have a couple other v8 fixes in my list as well, like removing this filter from OData dashboards. I'll address this IE issue too when I make the update. 
  • Alistair_PleasantAlistair_Pleasant Customer IT Monkey ✭
    Thanks Joivan, do you have an idea as to when the update will be available?
  • joivan_hedrickjoivan_hedrick Cireson Consultant Advanced IT Monkey ✭✭✭
    Nay, I don't yet have a time frame for updating this quite yet. However, using the legacy version should work for you in the meantime. It loads on a ~tacky timer which works, but has a delay before appearing on the page. 
  • Dean_RigelsfordDean_Rigelsford Customer IT Monkey ✭
    Hi Joivan,

    This is excellent!

    Not sure if anyone else has brought it up, having some issues with the font of the label "Filter Results" in IE, looks to be reverting to Times New Roman instead of using Segoe UI, can't replicate the problem in Chrome, the Segoe font is working perfectly.

    IE:





    Chrome:



    Thanks! 
    Dean
  • joivan_hedrickjoivan_hedrick Cireson Consultant Advanced IT Monkey ✭✭✭
    I've uplaoded version 8.8.1.2016. 
     - This version works with Portal v8.8.0.2016, as well as older Portal versions. Tested in Chrome, Edge, IE11, and Firefox. Not tested in IE9+10. There is no legacy version this time around.
     - This version now works with multiple grids if they exist on the same page, instead of previously only targeting the first grid.
     - @Alistair_Pleasant this version fixes the timing issue in IE with MyWork, as well as other obscurely intermittent timing issues.
     - @Dean_Rigelsford this version fixes the IE font issue with the grid search text.
  • Tom_HendricksTom_Hendricks Customer Super IT Monkey ✭✭✭✭✭
    Nothing happened after typing a search term and pressing enter when first added, but it was easy to tweak.  I am unsure if one of my other customizations is interfering, so I'll just share and see if it helps anyone else:

    // Change this (line 87):
    var htmlGridViewElement = $(thisResetViewButtonParentDiv).parent().parent().find('[data-role=grid]'); //two parents with a child 'find' will account for both

    // To this (line 87):

    var htmlGridViewElement = $(e.currentTarget).closest('[data-role=grid]'); //two parents with a child 'find' will account for both dashboards and widgets.

    Now it works great.  This is a terrific feature!
  • joivan_hedrickjoivan_hedrick Cireson Consultant Advanced IT Monkey ✭✭✭
    Thanks for sharing @Tom_Hendricks . I've incorporated this change into the newly uploaded version. I couldn't fully reproduce your issue but included both checks in the new version, as well as a multiple-grid class check up on line 31. 
  • Tom_HendricksTom_Hendricks Customer Super IT Monkey ✭✭✭✭✭
    Even better!  I'll update to your new version.
  • Mina_SaidiMina_Saidi Customer IT Monkey ✭

    Hi All,

    I have upgraded to the V8.9 and lost my customization of the grid view filter which we were using to filter tickets based on affected users location. I came across this post and used the above code by @jovian_Hedrick. I placed the code in our customspace and am using the below getscript from our custom.js to load.

    // Load Customisations for Custom GridViewFilter
    $.getScript("/CustomSpace/GridViewFilter/custom_GridViewFilter.js");

    The code now loads successfully but it doesn't pick up the affected users location although the office is set in AD. we really need this function and it was working in the previous version. is there a way to update the code to show us the location of the affected user.

    our affected users would appear as below, I hope it attaches the image :)


    any ideas?



  • joivan_hedrickjoivan_hedrick Cireson Consultant Advanced IT Monkey ✭✭✭
    Hi @Mina_Saidi

    It looks like the picture didn't come through. And I'm not sure I understand the issue that you are having. This grid view filter will search all columns within any grid. If the user's department field is in the grid, it will search that field too. However, it doesn't search any fields that are not in the grid. Can you try attaching your screenshot (instead of inserting an image), as well as the previous version of the grid view filter that you were using? 
  • Mina_SaidiMina_Saidi Customer IT Monkey ✭

    hi @joivan_hedrick ;

    Thanks for your reply. i cant seem to attach the screenshot as well. 

    How ever the code above  is working with the new version of the portal (thank you) and the user location field that was missing has now been found through a SQL SP database change that needed to be done for the location to appear in the grid. 

    its hard to explain without screenshots but i hope its clear. 

    Below is the previous code we were using for the grid filter which is currently in the older version of the portal but not working with v8.9how ever the one in this thread works.

    /* ----------------------------------------------- */
    /* -------------- Custom Grid Search ------------- */
    /* ----------------------------------------------- */

    $(document).ready(function () {
    if (session.user.Analyst == 1){
     var url = window.location.href;
     if(url.indexOf("/View/") == -1 && url.indexOf("/Page/") == -1 )
     {
      // Loosely verify we are on a page with workitems, not using hte GetProjection API call.
      return;
     }
     
     var mainPageNode = document.getElementById('main_wrapper');
     
     // create an observer instance
     var observer = new MutationObserver(function(mutations) {
      
      if ($("#customgridfilter").length > 0) { //It was already added once from somewhere. Fixes an IE multi-observer bug.
       observer.disconnect();
       return;
      }
      
      //The page changed. See if our title exists. If it does, then our gridview should also exist.
      var titleElement = $(".page_title"); //The title "usually" exists. But not on dashboard pages.
      var dashboardElement = $(".dashboard"); //The dashboard always exists for...pages with dashboards.
      //If this page has a gridview, then it also exists as soon as either the title or dashboard div does.
      
            if (titleElement.length > 0 || dashboardElement.length > 0) { //An element with class of page_title exists.
       var gridElement = $('.k-grid-header') // Get the grid object
       if (gridElement.length > 0) {
        fnAddCustomFilterInputBoxToPage();
       }
       
       //We are done observing.
                observer.disconnect();
            }
      
     });
     
     // configure the observer and start the instance.
     var observerConfig = { attributes: true, childList: true, subtree: true, characterData: true };
     observer.observe(mainPageNode, observerConfig);
     

     function fnAddCustomFilterInputBoxToPage() {
      
      var shouldPrependText = false;
      var isPromotedView = false; //controls filtering options.
      
      var resetViewButtonParentDiv = $(".btn-clear-grid-filters").first().parent();
      if (resetViewButtonParentDiv.length > 0) {
       //We shouldn't use this for a promoted view. Only OOB views.
       var url = window.location.href;
       if (url.indexOf("/c5161e06-2378-4b44-aa89-5600e2d3b9d8") == -1 &&
        url.indexOf("/9a06b0af-2910-4783-a857-ba9e0ccb711a") == -1 && url.indexOf("/cca5abda-6803-4833-accd-d59a43e2d2cf") == -1 &&
        url.indexOf("/f94d43eb-eb42-4957-8c48-95b9b903c631") == -1 && url.indexOf("/62f452d5-66b5-429b-b2b1-b32d5562092b") == -1
        )
       {
        //console.log("Custom Grid View filter - detected promoted view.");
        isPromotedView = true;
       }
      }
      else{
       //There was no button with this grid? Try a widget view then.
       resetViewButtonParentDiv = $("a:contains('Reset State')").first().parent().parent(); //Add another parent.
       shouldPrependText = true;
      }
      
      if (resetViewButtonParentDiv.length == 0) {
       console.log("Found a grid view, but not a reset button. Cannot show search filter in this scenario.");
       return;
      }
      
      var strHtmlToAdd = "<div class='customgridsearch' style='margin-left: 10px; margin-top: 10px; display:inline-block;'>" +
              "<div style='color:#000; font-family:'400 18px/22px',?'Segoe UI',?'Open Sans',?Helvetica,?sans-serif; display:inline-block; width: 400px;'>" +
               "<span>Filter Results:&nbsp;</span>" + "<input type='text' id='customgridfilter' placeholder='Type filter and press enter...' style='color: #000000; width: 200px;' />" +
              "</div>" +
             "</div>"
      
      console.log("Appending grid view filter");
      if (shouldPrependText) {
       $(resetViewButtonParentDiv).prepend(strHtmlToAdd);
      }
      else{
       $(resetViewButtonParentDiv).append(strHtmlToAdd);
      }
      

      $("#customgridfilter").on('keydown', function(e){
       if(e.which == 13) {
        //filter our resultset.
        var strFilterText = $("#customgridfilter").val().trim();
        if (strFilterText.length > 0) {
         fnApplyCustomFilterToGridDataSource(strFilterText, isPromotedView);
        }
       }
       else if (e.which == 27) {//escape
        $("#customgridfilter").val("");
        fnRemoveCustomFiltersOnGridResultSet();
       }
      });
     }
     
     
     function fnApplyCustomFilterToGridDataSource(strFilterValue, isPromotedView) {
      var gridElement = $('[data-role=grid]'); // Get the grid div element
      var kendoGridElement = gridElement.data('kendoGrid'); //...as a kendo widget
      var datasource = kendoGridElement.dataSource;

      var criteriaFilterObjects = []; //empty, for now...
      
      var firstColumn = ""

      for(var i = 0; i < kendoGridElement.columns.length; i++) {
      //for(var i = 0; i < kendoGridElement.columns.length-10; i++) {
       if (kendoGridElement.columns[i].field == "Icon" || kendoGridElement.columns[i].field == "NumericId" ||
         kendoGridElement.columns[i].field == "BaseId" || kendoGridElement.columns[i].field == "ParentWorkItemId")
        continue;
       if (firstColumn == "") {
        //Set the first real column, since we need any column to work with for our kendo datasource OR filter later.
        firstColumn = kendoGridElement.columns[i].field;
       }
       
       if (kendoGridElement.columns[i].DataType == "string" || kendoGridElement.columns[i].DataType == "anchor" ||
        (kendoGridElement.columns[i].DataType == undefined && kendoGridElement.columns[i].field.indexOf("Key") == -1)
          )
       { //when using a filter, the "contains" operator only works with strings, or else it throws a server 500 error.
        //an undefined datatype is a SQL table view from custom SQL source, It still chokes on integers. And we can't guess the type from the column from here?
        
        var newSingleFilterObject = { field: kendoGridElement.columns[i].field, operator: "contains", value: strFilterValue }
        criteriaFilterObjects.push(newSingleFilterObject); //add it to our criteriaFilterObjects
       }
      }

      //kendogridview datasources don't allow us to use an OR directly on them. So instead, do an AND, with a nested OR.
      //In the case, the first valid column is not null, AND any other column contains the filtered data.
      
      var masterFilter = { logic: "and",
              filters: [{
              logic:"or",
              filters: criteriaFilterObjects
             },
             { field : firstColumn, operator : "neq", value: ""} ] //isnotnull doesn't work with this kendo filter?
           }
      //nestedFilterWithOrColumns = criteriaFilterObjects[2];
      //Set our new filter.
      if (isPromotedView) {
       //In some promoted views, the kendo filter is broken. If column 6 has a null value, then the OR clause removes that entire result, even if another column matches :(.
       //This is bypassed by using client-side filtering.
       datasource.options.serverFiltering = false
       datasource.options.serverPaging = false
      }
      datasource.filter(masterFilter);
     }
     
     function fnRemoveCustomFiltersOnGridResultSet() {
      var gridElement = $('[data-role=grid]'); // Get the grid div element
      var kendoGridElement = gridElement.data('kendoGrid'); //...as a kendo widget
      var datasource = kendoGridElement.dataSource;
      datasource.filter([]);
     }
    }
    });
    /* ----------------------------------------------- */
    /* ------------ End Custom Grid Search ----------- */
    /* ----------------------------------------------- */







Sign In or Register to comment.