Ashok Raja's Blog
Grouping SharePoint List Item Rows Based on Multi Value Look Up Field
by Ashok Raja T 8. December 2012 03:01

This post explains you how to group SharePoint List items based on a multi value look up field in SharePoint 2013 and SharePoint 2010.This sample web part uses jQuery and jQuery DataTables Plug-in to group rows. Although SharePoint has options to display list items grouped by columns, it does not have any option to group look up columns that allow multiple selection.

The objective this blog post is to create a grid like the one shown below. This custom grid groups the list items based on the languages assigned for each and every employee. If you notice the below screen shot of out put data, the employees will be listed in all the language groups depending on how the languages are mapped to employees. image

I have created 2 SharePoint lists to explain this sample. One is a Language list, which contains a list of languages and the other is a list of Employees. I have added “Title” column of the language list as a look up column in Employee list with “Allow Multiple Values” enabled.

The below is the structure of both the lists.

Language List

image

Employee List

image

Employee List after entering some sample data

image

Markup for Web Part Design

 <SharePoint:ScriptLink ID="ScriptLink1" Name="~Site/Style Library/dt/Scripts/jquery.js" runat="server" />
 <SharePoint:ScriptLink ID="ScriptLink2" Name="~Site/Style Library/dt/Scripts/jquery.dataTables.min.js" runat="server" />
 <SharePoint:ScriptLink ID="ScriptLink3" Name="~Site/Style Library/dt/Scripts/jquery.dataTables.rowGrouping.js" runat="server" />
 <SharePoint:CssRegistration ID="CssRegistration1" Name="<% $SPUrl:~Site/Style Library/dt/Styles/datatable.css%>" runat="server" After="corev15.css" />
 <script type="text/javascript">
     $(document).ready(function () {
         $('#empList').dataTable().rowGrouping();
     });
 </script>
 <div id="EmployeeList" runat="server">
     <table id="empList" class="display" border="0" cellspacing="0" cellpadding="0">
         <thead>
             <tr>
                 <th>
                     Name
                 </th>
                 <th>
                     Language
                 </th>
                 <th>
                     City
                 </th>
             </tr>
         </thead>
         <tbody>
             <asp:Repeater runat="server" ID="EmpList">
                 <ItemTemplate>
                     <tr>
                         <td>
                             <%# DataBinder.Eval(Container.DataItem, "Language") %>
                         </td>
                         <td>
                             <%# DataBinder.Eval(Container.DataItem, "Name") %>
                         </td>
                         <td>
                             <%# DataBinder.Eval(Container.DataItem, "City") %>
                         </td>
                     </tr>
                 </ItemTemplate>
             </asp:Repeater>
         </tbody>
     </table>
 </div>

Note for SharePoint 2013 : Usage of Site and Site Collection URL token in SharePoint 2013 visual web part is different from SharePoint 2010. If you are creating the Web Part as a Sand Boxed solution Script Link control cannot be used to refer Java Script files . This post on referring Scripts and Style Sheet explains in detail about different available options to handle it in SharePoint 2013.

The Code

 using SFS.SharePoint.Helpers; 
 namespace SFS.Utilities.RowGrouping.WebParts.GroupRows
 {
     public partial class GroupRowsUserControl : UserControl
     {
     private const string SOURCE_LIST = "Employee";
     protected void Page_Load(object sender, EventArgs e)
     {
         if (!IsPostBack)
             BindData();
     }
 
     private void BindData()
     {
         try
         {
             List<Employee> Employees = ListOfEmployees();
             EmpList.DataSource = Employees;
             EmpList.DataBind();
         }
         catch (Exception Ex)
         {
         }
     }
 
     private List<Employee> ListOfEmployees()
     {
         List<Employee> Employees = new List<Employee>();
         try
         {
             SPList list = SPContext.Current.Web.Lists[SOURCE_LIST];
             SPListItemCollection Items = list.Items;
             int ItemCount = list.ItemCount;
             if (Items == null || Items.Count == 0)
                 return null;
             for (int i = 0; i < ItemCount; i++)
             {
                 SPListItem item = Items[i];
                 string[] Languages = item.GetValuesAsArray("Languages Known");
                 foreach (string Lang in Languages)
                 {
                     Employees.Add(new Employee
                     {
                         Name = item.Title,
                         Language = Lang,
                         City = item["City"].ToString()
                     });
                 }
             }
             return Employees;
         }
         catch
         {
             return null;
         }
     }
 }

The above code uses 2 different helper classes to parse and bind data to the repeater control. One is a Employee entity class which contains definition of Employee object. The second is the Look Up Data Parser class to parse lookup field values. To know more about this utility class , refer this article in which I have explained it  in a detailed manner.

Download Source Code
blog comments powered by Disqus