SPGridView with filtering by multiple columns and support of TaxonomyFieldMulti

In this post I would like to share implementation of advanced SPGridView control which has more features comparing with OTB SPGridView. As you probably now SPGridView control can be used in Sharepoint in order to display list-like data. I.e. with its header you can sort item, filter them – and it will work with OTB look and feel. However there is a lack of important features in OTB SPGridView: it supports filtering only by single column. Also if you use SPGridView for displaying some multi values field (e.g. TaxonomyFieldMulti or LookupMulti) – you will need to concatenate all values for single field into one string using some separator (e.g. string.Join(“,”, values)). The disadvantage of this approach is that from SPGridView point of view it will be single string value. As result in filter menu it will be displayed as single string.

Lets consider example: suppose that we have Language taxonomy field which allows multiple values (TaxonomyFieldMulti). It allows you to specify several languages: English, Finnish, Russian, Sweden. Suppose that we selected all of these 4 values for some list item. In our data source we concatenate these values into one string. Now lets see how it will be displayed in OTB SPGridView filter menu:

See that all values are shown as one filtering option. It means that when you will select this option you will get only those items which have absolutely the same value in Language field. Event if Language field of another item has the same set of Language but in different order (e.g. Finnish, English, Russian, Sweden) – it will be treat as different value and another filtering option will appear in menu. Now lets see how it looks like in OTB XsltListViewWebPart:

See that all options are presented as separate filtering option. That is our goal – to add this functionality in OTB SPGridView control.

But lets start with another problem: multi column filter. As I said above OTB SPGridView control supports filtering only by one column. I.e. you can apply filter on several columns simultaneously. This problem was solved by Erik Burger in his great blog post: Building A SPGridView Control – Part 4: Filtering Multiple Columns. Thanks to Erik for sharing his solution. It works successfully both for Sharepoint 2007 and 2010. However it has own problems: currently selected filter value is not shown in the filtering menu like it is shown in OTB SPGridView:

Well we are going to solve this problem as well.

Our solution will consist from 2 parts: server side control – inheritor of OTB SPGridView control and customized javascript aspgridview.js which overrides several functions from OTB spgridview.js. Actually I’m going to use solution from the Erik’s post – just will expand it for mentioned features. So you should definitely check his post first – it contains explanation how it works generally. And after that you can return to my post, because I won’t repeat whole explanation here.

So here is the code of changed SPGridViewProxy:

   1: using System;

   2: using System.Collections.Generic;

   3: using System.Linq;

   4: using System.Text;

   5: using System.Text.RegularExpressions;

   6: using Microsoft.SharePoint.WebControls;

   7: using Menu = Microsoft.SharePoint.WebControls.Menu;

   8: using System.Web.UI;

   9: using System.Reflection;

  10: using System.Web.UI.WebControls;

  11:  

  12: namespace CustomControls

  13: {

  14:     public class SPGridViewProxy : SPGridView, IPostBackEventHandler

  15:     {

  16:         // Contains names of fields splited by comma which may contain several values.

  17:         // E.g. some Taxonomy fields may have several values (Language = "Finnish,Russian").

  18:         // These fileds will be filtered using LIKE expression instead of "=" operation

  19:         public string MultiValuesFields { get; set; }

  20:  

  21:         protected override void OnLoad(EventArgs args)

  22:         {

  23:             base.OnLoad(args);

  24:             ScriptLink.Register(this.Page, "Fiskars/scripts/aspgridview.js", false);

  25:         }

  26:  

  27:         protected override void Render(System.Web.UI.HtmlTextWriter writer)

  28:         {

  29:             base.Attributes["callbackEventReference"] =

  30:               base.Attributes["callbackEventReference"].Replace("SPGridView_FilterCallbackHandler",

  31:                                                                  "ASPGridView_FilterCallbackHandler");

  32:  

  33:             MenuItemTemplate t = this.FindControl("SPGridViewFilterMenuTemplate").Controls[3] as MenuItemTemplate;

  34:             t.ClientOnClickScript = t.ClientOnClickScript.Replace("__ClearFilter__", "__ClearFilter__;%FIELDNAME%");

  35:  

  36:             if (this.HeaderRow != null)

  37:             {

  38:                 foreach (TableCell cell in this.HeaderRow.Cells)

  39:                 {

  40:                     Menu menu = (cell.Controls[0] as Menu);

  41:                     if (menu != null)

  42:                     {

  43:                         menu.ClientOnClickPreMenuOpen =

  44:                           menu.ClientOnClickPreMenuOpen.Replace("SPGridView_FilterPreMenuOpen",

  45:                                                                  "ASPGridView_FilterPreMenuOpen");

  46:                     }

  47:                 }

  48:             }

  49:  

  50:             base.Render(writer);

  51:         }

  52:  

  53:         protected new void DoFilterPostBackEventProcessing(string filterData)

  54:         {

  55:             string filterExpression = String.Empty;

  56:             if (filterData.StartsWith("__ClearFilter__"))

  57:             {

  58:                 string[] data = filterData.Split(';');

  59:                 string fieldName = data[1];

  60:  

  61:                 string oldFilterFieldName = base.Attributes["filterFieldName"];

  62:                 List<string> fieldNames = new List<string>(oldFilterFieldName.Split(';'));

  63:                 fieldNames.Remove(fieldName);

  64:                 base.Attributes["filterFieldName"] = String.Join(";", fieldNames.ToArray());

  65:  

  66:                 string oldFilterExpression = Convert.ToString(ViewState["FilterExpression"]);

  67:                 string[] filters = oldFilterExpression.Split(new string[] { " AND " }, StringSplitOptions.RemoveEmptyEntries);

  68:                 List<string> newFilters = new List<string>();

  69:                 foreach (string filter in filters)

  70:                 {

  71:                     if (!filter.Contains(String.Format("[{0}]", fieldName)))

  72:                     {

  73:                         newFilters.Add(filter);

  74:                     }

  75:                 }

  76:                 filterExpression = String.Join(" AND ", newFilters.ToArray());

  77:             }

  78:             else

  79:             {

  80:                 filterExpression = ExtractFilterExpression(filterData);

  81:             }

  82:  

  83:             ViewState["FilterExpression"] = filterExpression;

  84:             base.Attributes["filterExpression"] = filterExpression;

  85:  

  86:             if (filterExpression == null) { return; }

  87:  

  88:             DataSourceControl control = this.NamingContainer.FindControl(this.DataSourceID) as DataSourceControl;

  89:             PropertyInfo pi = control.GetType().GetProperty("FilterExpression");

  90:             pi.SetValue(control, filterExpression, null);

  91:  

  92:             base.RequiresDataBinding = true;

  93:         }

  94:  

  95:         private string ExtractFilterExpression(string filterData)

  96:         {

  97:             string[] data = filterData.Split(';');

  98:             string fieldName = data[0];

  99:             if (String.IsNullOrEmpty(fieldName))

 100:             {

 101:                 return null;

 102:             }

 103:  

 104:             string oldFilterFieldName = base.Attributes["filterFieldName"];

 105:             if (oldFilterFieldName != null)

 106:             {

 107:                 if (!oldFilterFieldName.Contains(fieldName))

 108:                 {

 109:                     base.Attributes["filterFieldName"] += ";" + fieldName;

 110:                 }

 111:             }

 112:             else

 113:             {

 114:                 base.Attributes.Add("filterFieldName", fieldName);

 115:             }

 116:  

 117:             string fieldValue = data[1].Replace("%3b", ";").Replace("%25", "%");

 118:  

 119:             string oldFilterExpression = Convert.ToString(ViewState["FilterExpression"]);

 120:             string newFilterExpression = "";

 121:             // if field is multi value we need to perform search using LIKE expression instead of "=" because

 122:             // multi values are displayed as single string

 123:             if (this.isMultipleValues(fieldName))

 124:             {

 125:                 newFilterExpression = String.Format("[{0}] LIKE '%{1}%'", fieldName, fieldValue);

 126:             }

 127:             else

 128:             {

 129:                 newFilterExpression = String.Format("[{0}] = '{1}'", fieldName, fieldValue);

 130:             }

 131:  

 132:             if (!String.IsNullOrEmpty(oldFilterExpression))

 133:             {

 134:                 if (oldFilterExpression.Contains(newFilterExpression))

 135:                 {

 136:                     newFilterExpression = oldFilterExpression;

 137:                 }

 138:                 // also we need to check that current filed name already exists in the old filter expression

 139:                 // with another filed value. We need to replace it with new value. Otherwise it will be possible to filter

 140:                 // within single column using several values. This is not OTB behavior. Sharepoint allows to select only one

 141:                 // field value per column

 142:                 else if (oldFilterExpression.Contains(string.Format("[{0}]", fieldName)))

 143:                 {

 144:                     string[] filters = oldFilterExpression.Split(new string[] { " AND " }, StringSplitOptions.RemoveEmptyEntries);

 145:                     filters = filters.Where(f => !f.Contains(string.Format("[{0}]", fieldName))).ToArray();

 146:                     if (filters.Length != 0)

 147:                     {

 148:                         newFilterExpression = String.Join(" AND ",

 149:                                                           new[] {String.Join(" AND ", filters), newFilterExpression});

 150:                     }

 151:                 }

 152:                 else

 153:                 {

 154:                     newFilterExpression = String.Join(" AND ",

 155:                                                   new [] { oldFilterExpression, newFilterExpression });

 156:                 }

 157:             }

 158:  

 159:             return newFilterExpression;

 160:         }

 161:  

 162:         private bool isMultipleValues(string fieldName)

 163:         {

 164:             try

 165:             {

 166:                 if (string.IsNullOrEmpty(this.MultiValuesFields))

 167:                 {

 168:                     return false;

 169:                 }

 170:                 var multiValuesFields = this.MultiValuesFields.Split(new[] {","}, StringSplitOptions.RemoveEmptyEntries);

 171:                 if (multiValuesFields.Length == 0)

 172:                 {

 173:                     return false;

 174:                 }

 175:                 return multiValuesFields.Any(t => t == fieldName);

 176:             }

 177:             catch

 178:             {

 179:                 return false;

 180:             }

 181:         }

 182:  

 183:         private string GetValueAfterPrefix(string value, string prefix)

 184:         {

 185:             if (String.IsNullOrEmpty(value) ||

 186:                  String.IsNullOrEmpty(prefix) ||

 187:                  !value.StartsWith(prefix))

 188:             {

 189:                 return null;

 190:             }

 191:  

 192:             if (prefix.Length < value.Length)

 193:             {

 194:                 return value.Substring(prefix.Length);

 195:             }

 196:  

 197:             return String.Empty;

 198:         }

 199:  

 200:         #region IPostBackEventHandler Members

 201:  

 202:         void IPostBackEventHandler.RaisePostBackEvent(string eventArgument)

 203:         {

 204:             string valueAfterPrefix = GetValueAfterPrefix(eventArgument, "__SPGridView__;");

 205:             if (valueAfterPrefix != null)

 206:             {

 207:                 string filterData = GetValueAfterPrefix(valueAfterPrefix, "__Sort__;");

 208:                 if (filterData == null)

 209:                 {

 210:                     filterData = GetValueAfterPrefix(valueAfterPrefix, "__Filter__;");

 211:                     if (filterData != null)

 212:                     {

 213:                         DoFilterPostBackEventProcessing(filterData);

 214:                     }

 215:                 }

 216:                 else

 217:                 {

 218:                     DoSortPostBackEventProcessing(filterData);

 219:                 }

 220:             }

 221:             else

 222:             {

 223:                 base.RaisePostBackEvent(eventArgument);

 224:             }

 225:         }

 226:  

 227:         #endregion

 228:     }

 229: }

I will describe only differences from the Erik’s post. First of all I added MultiValuesFields property (line 19) – using this property you can tell to custom SPGridViewProxy control what values are really multi values and should be split into tokens. For our case we will specify MultiValuesFields=”Language”. However you can specify as many fields as you need here separated by comma.

Next check the line 84:

   1: base.Attributes["filterExpression"] = filterExpression;

We need to store actual filter expression in the attribute as it will be needed on client side in javascript.

Another change is lines 120-130. Here we make actual trick for multiple values fields. We check whether or not current field name corresponds to a field with multiple values (using MultiValuesFields property mentioned above). If yes – then instead of simple equal expression (“=”) we will use LIKE expression. E.g. if we select Finnish language then filter expression will be: [Language] LIKE ‘%Finnish%’. So our list item with Language = “English,Finnish,Russian,Sweden” will match this filter.

Next change on lines 138-150. Here I added functionality which restricts number of selected filtering values for single column by one value. This is how OTB SPGridView and XsltListViewWebForms work. However it is not very hard to expand it to several values per column and if you will need it – you can implement it by yourself.

The last change is isMultipleValues() function (lines 162-181). It just check whether or not specified field name exists in MultiValuesFields property of the control.

Now lets check client side of the solution:

   1: function ASPGridView_FilterPreMenuOpen(gridViewClientId, templateClientId, menuClientId, dataFieldName, e) {

   2:     var gridView = document.getElementById(gridViewClientId);

   3:     var callbackEventReference = gridView.getAttribute("callbackEventReference");

   4:     var callbackArgumentPrefix = gridView.getAttribute("callbackArgumentPrefix");

   5:     var postbackEventReference = gridView.getAttribute("postbackEventReference");

   6:     var filterFieldName = gridView.getAttribute("filterFieldName");

   7:     var filterFieldValue = gridView.getAttribute("filterFieldValue");

   8:  

   9:     var filterCurrentlyOn = false;

  10:     if ((filterFieldName != null) &&

  11:         (filterFieldName.length > 0) &&

  12:         (filterFieldName.match(dataFieldName) != null)) 

  13:     {

  14:         filterCurrentlyOn = true;

  15:     }

  16:  

  17:     var menuTemplate = document.getElementById(templateClientId);

  18:     var menuLink = document.getElementById(menuClientId);

  19:     if ((menuLink.getAttribute("suppressBubbleIfPostback") != null) &&

  20:         (e != null) && (e.srcElement != null) && 

  21:         (e.srcElement.href != null) &&

  22:         (e.srcElement.href.substr(0, MMU_postbackPrefix.length) == MMU_postbackPrefix)) 

  23:     {

  24:         event.cancelBubble = true;

  25:         return;

  26:     }

  27:     SPGridView_FixupFilterValuesFromMenuTemplate(menuTemplate, filterCurrentlyOn);

  28:     var menuItem = CAMOpt(menuTemplate, L_Loading_Text, "null");

  29:     menuItem.setAttribute("isFilterItem", "true");

  30:     menuItem.setAttribute("enabled", "false");

  31:     SPGridView_CallbackContext = new Object();

  32:     SPGridView_CallbackContext.gridViewClientId = gridViewClientId;

  33:     SPGridView_CallbackContext.templateClientId = templateClientId;

  34:     SPGridView_CallbackContext.menuClientId = menuClientId;

  35:     SPGridView_CallbackContext.dataFieldName = dataFieldName;

  36:     callbackEventReference = callbackEventReference.replace(/__CALLBACKARGUMENT__/g, dataFieldName);

  37:     callbackEventReference = callbackEventReference.replace(/__CALLBACKCONTEXT__/g, gridViewClientId + ";" + templateClientId + ";" + menuClientId + ";" + dataFieldName);

  38:     eval(callbackEventReference);

  39: }

  40:  

  41: function Array_Contains(a, obj) {

  42:     for(var i = 0; i < a.length; i++) {

  43:         if(a[i] === obj) {

  44:             return true;

  45:         }

  46:     }

  47:     return false;

  48: }

  49:  

  50: function ASPGridView_IsNullOrUndefined(val) {

  51:     return (val == null || typeof(val) == 'undefined');

  52: }

  53:  

  54: function ASPGridView_IsValueInFilter(fieldName, fieldVal, filterExpression) {

  55:     if (ASPGridView_IsNullOrUndefined(fieldName) || ASPGridView_IsNullOrUndefined(fieldVal) ||

  56:         ASPGridView_IsNullOrUndefined(filterExpression)) {

  57:         return false;

  58:     }

  59:  

  60:     fieldName = fieldName.toLowerCase();

  61:     fieldVal = fieldVal.toLowerCase();

  62:     filterExpression = filterExpression.toLowerCase();

  63:  

  64:     var case1 = '[' + fieldName + "] = '" + fieldVal + "'";

  65:     var case2 = '[' + fieldName + "] like '%" + fieldVal + "%'";

  66:     return (filterExpression.indexOf(case1) != -1 || filterExpression.indexOf(case2) != -1);

  67: }

  68:  

  69: function ASPGridView_FilterCallbackHandler(result, context) {

  70:     var values = context.split(';');

  71:     if (values.length != 4) { alert("ERROR: ASPGridView_FilterCallbackHandler() - values.length !=4"); return; }

  72:     var gridViewClientId = values[0];

  73:     var templateClientId = values[1];

  74:     var menuClientId = values[2];

  75:     var dataFieldName = values[3];

  76:     var gridView = document.getElementById(gridViewClientId);

  77:     if (gridView == null) { alert("ERROR: ASPGridView_FilterCallbackHandler() - gridView==null"); return; }

  78:     var menuTemplate = document.getElementById(templateClientId);

  79:     if (menuTemplate == null) { alert("ERROR: ASPGridView_FilterCallbackHandler() - menuTemplate==null"); return; }

  80:     var menu = document.getElementById(menuClientId);

  81:     if (menu == null) { alert("ERROR: ASPGridView_FilterCallbackHandler() - menu==null"); return; }

  82:     var postbackEventReference = gridView.getAttribute("postbackEventReference");

  83:     if ((postbackEventReference == null) || (postbackEventReference.length <= 0)) { alert("ERROR: ASPGridView_FilterCallbackHandler() - postbackEventReference is null or empty"); return; }

  84:     var filterFieldName = gridView.getAttribute("filterFieldName");

  85:     var filterFieldValue = gridView.getAttribute("filterFieldValue");

  86:     var filterExpression = gridView.getAttribute("filterExpression");

  87:     

  88:     var filterCurrentlyOn = false;

  89:     if ((filterFieldName != null) &&

  90:         (filterFieldName.length > 0) &&

  91:         (filterFieldName.match(dataFieldName) != null)) {

  92:         filterCurrentlyOn = true;

  93:     }

  94:  

  95:     SPGridView_FixupFilterValuesFromMenuTemplate(menuTemplate, filterCurrentlyOn);

  96:     values = result.split(';');

  97:  

  98:     // collect unique values

  99:     var uniqueValues = new Array();

 100:     for (var valueIndex = 0; valueIndex < values.length; valueIndex++) {

 101:         var value = unescape(values[valueIndex]);

 102:         if (typeof (value) != 'undefined' && value != null) {

 103:             var multipleValues = value.split(','); // the same as used for joining TaxonomyFieldTypeMulti

 104:             if (typeof (multipleValues) != 'undefined' && multipleValues != null && multipleValues.length > 0) {

 105:                 for (var multiValueIndex = 0; multiValueIndex < multipleValues.length; multiValueIndex++) {

 106:                     var v = multipleValues[multiValueIndex];

 107:                     if (!Array_Contains(uniqueValues, v)) {

 108:                         uniqueValues.push(v);

 109:                     }

 110:                 }

 111:             }

 112:         }

 113:     }

 114:  

 115:     // create menu items only for unique values

 116:     for (var valueIndex = 0; valueIndex < uniqueValues.length; valueIndex++) {

 117:         var value = unescape(uniqueValues[valueIndex]);

 118:         var script = postbackEventReference.replace(/__POSTBACKARGUMENT__/g, dataFieldName + ";" + value.replace(/\\/g, "\\\\").replace(/\'/g, "\\'").replace(/%/g, "%25").replace(/;/g, "%3b"));

 119:         var newMenuItem = CAMOpt(menuTemplate, value, script);

 120:         newMenuItem.setAttribute("isFilterItem", "true");

 121:         if (value == filterFieldValue || ASPGridView_IsValueInFilter(dataFieldName, value, filterExpression)) {

 122:             newMenuItem.setAttribute("checked", "true");

 123:         }

 124:     }

 125:     HideMenu(menuTemplate);

 126:     MMU_Open(menuTemplate, menu);

 127: }

Lets also check changes comparing with Erik’s solution. I added 3 helper functions: Array_Contains(), ASPGridView_IsNullOrEmpty() and ASPGridView_IsValueInFilter() (see lines 41-58). I didn’t want to use jquery here in order to avoid introducing dependencies on external libraries and in order to keep things as much easier to use as possible. Check line 86:

   1: var filterExpression = gridView.getAttribute("filterExpression");

Remember that I told you above that we need to store whole filter expression in the attributes collection in order to use it in javascript.

In lines 98-112 we split all multi values fields into single array of unique values. These values will be used for filter menu in lines 115-124. Note that on the line 121 we use ASPGridView_IsValueInFilter() in order to check selected filter values in the menu.

That’s how it works. Again thanks to Erik for his solution. I also realize that my solution is not ideal – and it contains its own problems (e.g. in javascript we split unique values for all fields regardless of is this field multi value or not). Howhever it works for our case. And I think it can be useful for your cases as well. Anyway if you will find any problems in it I will appreciate if you will share it in this post’s comments. And I will double appreciate if you will share solution for it as well :).