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

joivan_hedrick

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.



UPDATE BY Geoff Ross

Moved code to GitHub:
https://github.com/Cireson/Community_GridFilter

Tyson

We have this installed in our environment and our users love it. Thanks JV!

Adrian_Mataisz

With multiple pages will filter show content from all pages or just the page you are in?

joivan_hedrick

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_Nordqvist

Is it possible to get this working on Promoted Views? Great solution btw

joivan_hedrick

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_Keith

Great solution @joivan_hedrick !  If we could get this working for promoted views, that would be the greatest.

joivan_hedrick

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_Keith

@joivan_hedrick You're the man!  Thank you so much!

Alistair_Pleasant

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_hedrick

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_Pleasant

Thanks Joivan, do you have an idea as to when the update will be available?

joivan_hedrick

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_Rigelsford

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_hedrick

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_Hendricks

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:

var&nbsp;htmlGridViewElement&nbsp;=&nbsp;$(e.currentTarget).closest('[data-role=grid]');&nbsp;//two&nbsp;parents&nbsp;with&nbsp;a&nbsp;child&nbsp;'find'&nbsp;will&nbsp;account&nbsp;for&nbsp;both&nbsp;dashboards&nbsp;and&nbsp;widgets.// Change this (line 87):<br>var htmlGridViewElement = $(thisResetViewButtonParentDiv).parent().parent().find('[data-role=grid]'); //two parents with a child 'find' will account for both<br><br>// To this (line 87):<br>

Now it works great.  This is a terrific feature!

joivan_hedrick

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_Hendricks

Even better!  I'll update to your new version.

Mina_Saidi

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_hedrick

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_Saidi

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 ----------- */
/* ----------------------------------------------- */

Margarete_Jussen

Hi Joivan,
I tried your function in our test enviroment and the search works very well. The only problem I have is that I can not refresh the portal to see again all requests after deleting the filter entry. Could you help with this? Maybe I did something wrong.
Thank you very much
Margret

joivan_hedrick

Hi @Margarete_Jussen,
The grid filter should clear if the search box has focus and you press the escape key. This will only reset the filter without reloading the page. Alternatively, you can press the page's Reset View button to reset both the filters and grid's settings, and refresh the page. 

Margarete_Jussen

Thank you very much for the quick answer. Pressing esc works.

Adrian_Mataisz

Hi Joivan,

We are on version  9.3.8.2016  and the filter is not working for us.

Wendy_Craig

Same for us. It's been working fine until the most recent upgrade.

Adrian_Mataisz

I don't understand why this feature is not available from Cireson, it is very handy, how we can bring it back?

Wendy_Craig

Agreed. Our end users can't search, so the filter is the only meaningful way they have to narrow down their list of requests if they're looking for something.

joivan_hedrick

I've updated this with a couple changes, see the new download file at the top (2019.08.20_CustomGridViewFilter.zip). This should work correctly for OOB views and SQL dashboard views, but only somewhat for Promoted views from the console. The lack of promoted view functionality in this case is due to it being tricky to implement on the Kendo filter side, as well as promoted views making end-users sad at the lack of an efficiently sexy SQL view.

Gabriel_Lences

Hi @joivan_hedrick , just tested this out works great , however it doesn't work at all when there's a "group by" a certain column used. Can you adjust the code to work it while the tickets are grouped as well?

Otherwise, it's a really really nice addition! :)

Adrian_Mataisz

@joivan_hedrick I was going to implement the code as well to our live environment but we use Group by to sort WI as well.