Sunday, March 6, 2011

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:

image

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:

image

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:

image

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 :).

13 comments:

  1. Hi Alex,

    Brilliant! This is a great expansion on my posts that will no doubt help out many, many fellow developers. This is definitely something I will be incorporating into the ASPGridView in the future and if I run into any issues I know who to talk to ;)

    Best regards and keep it up!

    Erik

    ReplyDelete
  2. Hi Alex,
    Did you check your solution with enable Grouping, Filtering, sorting ? I have a problem with us because I have to set EnableViewState = False. And I found this solution is not work. I am working on Sharepoint 2010.
    Thanks

    ReplyDelete
  3. Hello Binh,
    this solution works with filtering and sorting. As you can see in the code there are some places which use ViewState. It means that it won't work with EnableViewState = false.

    ReplyDelete
  4. I think it's better to rewrite these places with ControlState, ControlState can't be disabled

    ReplyDelete
  5. I implemented the above solution, it is really helpful, however i ran into an issue i.e. clear filter option is getting disabled when filtering with more than 1 column.

    ReplyDelete
    Replies
    1. Hi Kumar.. have you found any solution of this?

      Delete
  6. Thanks for you and Erik for this work:-)

    ReplyDelete
  7. Thank You Alex and Erik for the wonderful solution.

    I would like to resolve below issues:
    1. Extend the existing functionality to allow multiple value filter on the same column too. Can you guide me how to proceed on that part? Ex. filter values by English and Russian.

    2. If for a particular filter there is are records, empty data template is shown. I would like to atleast see the header column rather than empty data template.

    3. If for a particular filter there is are records, empty data template is shown. If you click on back button of the browser, the filter stops working. Can you help me resolve this issue too?

    Regards,
    Jaimin

    ReplyDelete
  8. Jaimin hi,
    this almost 3 years old post and I don't remember all details there. However you have all source code and may experiment with it like you wish.

    ReplyDelete
  9. hi.. any suggestion on question raised by "Kumar Shobhit" because i am facing the same error.

    ReplyDelete
  10. Great Help!
    I used two code pieces from here -> base.Attributes["callbackEventReference"] and the .js file (match)
    Clear filter wouldn't have been possible without this post. Thanks a lot!! :)

    ReplyDelete
  11. Hi,
    Great post. Works like charm for single webpart.

    I added following code in Render method when two webparts are present on same page to pass ClientID to "ClientOnClickPreMenuOpen"

    menu.ClientOnClickPreMenuOpen =
    menu.ClientOnClickPreMenuOpen.Replace("%TEMPLATECLIENTID%",
    this.ClientID + "_SPGridViewFilterMenuTemplate");

    Also when you clear filter page index was reset in DoFilterPostBackEventProcessing method


    ReplyDelete