Filter from current row values

[Matt]

I have followed this sample:

http://demos.telerik.com/aspnet-ajax/grid/examples/programming/filteringtemplatecolumns/defaultcs.aspx

and created a column type to fitler with a radcombobox.  I added a DataSourceID to my column and when I set it the whole thing works correctly.

However I would like to add another option to have fitlering based on the items currently in the list.  I overrode the PrepareCell method and add the unique items as they go by:

   public override void PrepareCell(TableCell cell, GridItem item)
        {

            base.PrepareCell(cell, item);

            if (UseAvailableValuesForDataSource && item is GridDataItem)
            {
                if (rcBox.Items.FindItemByText(cell.Text) == null)
                {
                    rcBox.Items.Add(new RadComboBoxItem(cell.Text));

                }

            }

        }

This fills the combobox with the unique values, however when I get back into the  SetCurrentFilterValueToControl & GetCurrentFilterValueFromControl the value of the selected item is empty and so the filter doesn't get set.  I tried storing a list in ViewState and restoring from there but that didn't seem to work.  (For some reason my ViewState items wouldn't be restored either?)

Is there anyway to filter based on the currently displayed items instead of getting the full set back from the datasource?

Thanks,

[Radoslav]

Hi Matt,

The CustomFilteringColumn does not support the desired functionality concerning adding additional items to the RadComboBox. The SetCurrentFilterValueToControl method is called immediately after the SetupFilterControls. So if the user select and filter by the new added item, into the SetCurrentFilterValueToControl method you could get the "Object reference not set to an instance of an object" exception, because the selected item is not added into the combo.

To achieve the desired functionality you could try using the template columns with filter templates.Please check out the following online example which demonstrates using of filter templates:
http://demos.telerik.com/aspnet-ajax/grid/examples/programming/filtertemplate/defaultcs.aspx

I hope this helps.

Sincerely yours,
Radoslav
the Telerik team

[Matt]

I started with a template column and a Filter Template but didn't get anywhere.  I am using an objectdatasource as opposed to a sqldatasource in the example.  I see that in the code behind the select statement for each datasource is being modified to match the filter expression of the grid itself.

Since I'm using an objectdatasource I don't think I can do that(?).  I can get the list of all items from the datasource and I was able to grab the databound event for the combobox and make the list unique manually but then the filtering stopped working.  Any other possibilities when using an objectdatasource?

Thanks,

[Radoslav]

Hello Matt,

In order to modify the following example to use the ObjectDataSource instead of SqlDataSource you could try the following approach:
You could pass the empty parameter to the ObjectDataSource.SelectParameters collection:

<asp:ObjectDataSource runat="server" ID="ObjectDataSource2" SelectMethod="GetContactTitles" TypeName="Class1">
 <SelectParameters>
   <asp:ControlParameter ControlID="RadGrid1" Name="filterExpression" PropertyName="" Type="String" />
 </SelectParameters>
</asp:ObjectDataSource>

Into the code behind you could create method wich selects the date by filter expression parameter:

public static DataTable GetContactTitles(string filterExpression)
{
     if (filterExpression != null)
     {
         return GetDataTable("SELECT DISTINCT ContactTitle FROM Customers " + "WHERE " + filterExpression);
     }
     else
     {
         return GetDataTable("SELECT DISTINCT ContactTitle FROM Customers ");
     }
 }

Then on RadGrid.PreRender event handler you could pass the value to the ObjectDataSource. SelectParameters[filterExpression]:

ObjectDataSource2.SelectParameters["filterExpression"].DefaultValue =  RadGrid1.MasterTableView.FilterExpression.ToString();

Additionally I am sending you a simple example. Please check it out and let me know if it helps you.

Sincerely yours,
Radoslav
the Telerik team

[Matt]

This sample is simply changing SQL statements.  We are using an ObjectDataSource because our mid tier only returns objects as the model.  There is no SQL in our pages at all or anywhere really, its generated by Linq 2 SQL at the very back end.  This is hidden behind a repository pattern though so I don't have access to the L2S model directly either.

I was able to get the filter expression to the object data source and may go with some sort of solution that parses it on my own and then uses that to modify our LINQ statements.  

I appreciate your help,

Matt

[Kumar]

Hi,
I am working on a radgrid with a column that drops down values for choices. I have looked at the two ways for implementing this:
However, both examples are not helpful enough.
The Googlelike filtering-column method shows a generic code to create all of them in the same way.
Even the columntemplate example doesnt work.
On debugging, I am able to populate the dropdown with list of values in, radgrid_itemdatabound event. but the program flow forces the page, the grid, and the column to be refreshed all over, ie thru events Page_Load,radgrid_init, radgrid_itemcreating.
I also noticed the SelectedIndexChanged event doesnt trigger for either examples.
Seems like there is some problem with my code.
Please help.
thanks
Kumar.

[Radoslav]

Hi Kumar,

Based on the provided information is hard to determine what is causing the described issues on your end. Could you please send us a small runnable project which demonstrates the issue. You could open a formal support ticket from your Telerik account and attach a ZIP file there. Thus we will be able to we debug the project and provide you with more to-the-point answer.
Additionally you could check out the following online resources:
http://demos.telerik.com/aspnet-ajax/grid/examples/programming/filteringtemplatecolumns/defaultcs.aspx
http://demos.telerik.com/aspnet-ajax/grid/examples/programming/filtertemplate/defaultcs.aspx     
http://www.telerik.com/help/aspnet-ajax/grdfilteringwithdropdownlist.html
http://www.telerik.com/community/code-library/aspnet-ajax/grid/multi-selection-radcombobox-for-filtering-grid.aspx

Kind regards,
Radoslav
the Telerik team

[Kumar]

Hey Radoslav and Friends,
Thanks for replying. My reply is late I was off duty!

For the issue at hand already explained, I need a dropdownlist on the column header, for the user to filter rows using values in the column.I have already looked at the separate ways such as "Google-like filtering" and "Template Columns" in your samples section.

I zeroed in on the "Template Columns" option since, it promised the way to build filtering for a particular column.
The Google-like one showed a generic way to build all the columns - thus tightly coupling building the grid and filtering together.If you can show a sample where we can use the Google-like one to also enable filtering independant of building the columns, that would be great.

Next, even the sample for "Template Columns" filtering was would not work for me locally.
Firstly, I text-copied the same page-content and created them locally.
I had to sort out some setup errors which I got while running both in .NET 3.5 and .NET 2.0 envts.
Next, I had to move the binding logic to the backend.

Even so, the appearance/working is not quite the same as smooth as the one on your site.
Of course, I made few changes across the files to suit my requirements.
Also, I converted the csharp code for the template column with the dropdown.

I am not able to attach a zip file to this thread (could you explain why??) So, I am embedding the relevant code under given headings for your usage:
1.DefaultVB.aspx

<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="DefaultVB.aspx.vb"
Inherits="Telerik.GridExamplesCSharp.GeneralFeatures.Filtering.DefaultVB" %>

<%@ Register TagPrefix="custom" Namespace="Telerik.GridExamplesCS.Programming.FilteringTemplateColumns" Assembly="er333" %>
<%@ Register TagPrefix="telerik" Namespace="Telerik.Web.UI" Assembly="Telerik.Web.UI" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head id="Head1" runat="server">
</head>
<body class="BODY">
    <form runat="server" id="mainForm" method="post">
        <telerik:RadScriptManager ID="RadScriptManager1" runat="server" />
        <!-- content start -->
        <div class="bigModule">
        <div class="bigModuleBottom">
            <div class="title">Enter filter criteria by either:</div>
            <ul>
                <li>Typing in the header textboxes and choosing filter options from the context filter menu or</li>
                <li>Selecting an item from the dropdown list in the last column header</li>
                <li>Each filter pattern will be persisted successfully on subsequent user entries</li>
            </ul>
        </div></div>
        <telerik:RadAjaxManager ID="RadAjaxManager1" runat="server">
            <AjaxSettings>
                <telerik:AjaxSetting AjaxControlID="RadGrid1">
                    <UpdatedControls>
                        <telerik:AjaxUpdatedControl ControlID="RadGrid1" />
                    </UpdatedControls>
                </telerik:AjaxSetting>
            </AjaxSettings>
        </telerik:RadAjaxManager>
         <telerik:RadGrid ID="RadGrid1" Width="97%" AllowFilteringByColumn="True"
            AllowSorting="True" PageSize="12" ShowFooter="True" AllowPaging="True" runat="server"
            AutoGenerateColumns="False" GridLines="None" ShowStatusBar="true"  OnNeedDataSource="RadGrid1_NeedDataSource">
              <GroupingSettings CaseSensitive="false" />
            <MasterTableView EditMode="InPlace" AllowFilteringByColumn="True" ShowFooter="True"
                DataKeyNames="CustomerID" TableLayout="Fixed">
                <Columns>
                    <telerik:GridBoundColumn DataField="ContactName" HeaderText="Contact name">
                        <HeaderStyle Width="25%" />
                    </telerik:GridBoundColumn>
                    <telerik:GridBoundColumn DataField="ContactTitle" HeaderText="Contact title"
                     AutoPostBackOnFilter="true" CurrentFilterFunction="Contains" ShowFilterIcon="false">
                        <HeaderStyle Width="25%" />
                    </telerik:GridBoundColumn>
                    <telerik:GridTemplateColumn DataField="City" HeaderText="TemplateColumn City">
                        <HeaderStyle Width="25%" />
                        <ItemTemplate>
                            <%# Eval("City") %>
                        </ItemTemplate>
                    </telerik:GridTemplateColumn>
                    <custom:MyCustomFilteringColumn DataField="Country" FilterControlWidth="180px" HeaderText="Custom TemplateColumn Country">
                        <headerstyle width="25%" />
                        <itemtemplate>
                             <%# Eval("Country") %>
                        </itemtemplate>
                    </custom:MyCustomFilteringColumn>
                </Columns>
            </MasterTableView>
        </telerik:RadGrid>
        <br />
        <!-- content end -->
    </form>
</body>
</html>

2.DefaultVB.aspx.vb

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls
Imports Telerik.Web.UI
Imports Telerik.Web.Design
Imports System.Configuration

Namespace Telerik.GridExamplesCSharp.GeneralFeatures.Filtering
    Partial Public Class DefaultVB
        Inherits System.Web.UI.Page

        Private dt As DataTable

        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        End Sub

        Protected Sub RadGrid1_NeedDataSource(ByVal source As Object, ByVal e As GridNeedDataSourceEventArgs)
            'dt = GETTABLE_eLaw()
            dt = GetDataTable("SELECT ContactName, ContactTitle, City, Country FROM Customers")
            Me.RadGrid1.DataSource = dt
            dt=nothing
        End Sub

        Public Shared Function GetDataTable(ByVal query As String) As DataTable
            Dim ConnString As String = ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString
            Dim conn As New SqlConnection(ConnString)
            Dim adapter As New SqlDataAdapter()
            adapter.SelectCommand = New SqlCommand(query, conn)
            Dim myDataTable As New DataTable()
            conn.Open()
            Try
                adapter.Fill(myDataTable)
            Finally
                conn.Close()
            End Try
            Return myDataTable
        End Function
    End Class
End Namespace

3.DefaultVB.aspx.designer.vb

'------------------------------------------------------------------------------
' <auto-generated>
'     This code was generated by a tool.
'     Runtime Version:2.0.50727.3607
'
'     Changes to this file may cause incorrect behavior and will be lost if
'     the code is regenerated.
' </auto-generated>
'------------------------------------------------------------------------------

Option Strict Off
Option Explicit On


Namespace Telerik.GridExamplesCSharp.GeneralFeatures.Filtering
    Partial Public Class DefaultVB
        Protected WithEvents Head1 As System.Web.UI.HtmlControls.HtmlHead
        Protected WithEvents mainForm As System.Web.UI.HtmlControls.HtmlForm
        Protected WithEvents RadScriptManager1 As Telerik.Web.UI.RadScriptManager
        Protected WithEvents RadAjaxManager1 As Telerik.Web.UI.RadAjaxManager
        Protected WithEvents RadGrid1 As Telerik.Web.UI.RadGrid
    End Class
End Namespace

4.MyCustomFilteringColumn.vb

Imports Telerik.Web.UI
Imports System.Data
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Data.SqlClient
Imports System.Configuration


Namespace Telerik.GridExamplesCS.Programming.FilteringTemplateColumns
    Public Class MyCustomFilteringColumn
        Inherits GridTemplateColumn

        Public Function GetDataTable(ByVal queryString As String) As DataTable
            Dim ConnString As String = ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString
            Dim MySqlConnection As New SqlConnection(ConnString)
            Dim MySqlDataAdapter As New SqlDataAdapter()
            MySqlDataAdapter.SelectCommand = New SqlCommand(queryString, MySqlConnection)

            Dim myDataTable As New DataTable()
            MySqlConnection.Open()
            Try
                MySqlDataAdapter.Fill(myDataTable)
            Finally
                MySqlConnection.Close()
            End Try

            Return myDataTable
        End Function

        Protected Overrides Sub SetupFilterControls(ByVal cell As TableCell)
            Dim rcBox As New RadComboBox()
            rcBox.ID = "DropDownList1"
            rcBox.Width = Unit.Percentage(100)
            rcBox.AutoPostBack = True
            rcBox.DataTextField = Me.DataField
            rcBox.DataValueField = Me.DataField
            AddHandler rcBox.SelectedIndexChanged, AddressOf rcBox_SelectedIndexChanged
            Dim table As DataTable = GetDataTable(String.Format("SELECT DISTINCT {0} FROM {1}", Me.DataField, "Customers"))
            Dim row As DataRow = table.NewRow()
            row(Me.DataField) = ""
            table.Rows.InsertAt(row, 0)
            rcBox.DataSource = table
            cell.Controls.Add(rcBox)
        End Sub

        Protected Overrides Sub SetCurrentFilterValueToControl(ByVal cell As TableCell)
            If Not (Me.CurrentFilterValue = "") Then
                DirectCast(cell.Controls(0), RadComboBox).Items.FindItemByText(Me.CurrentFilterValue).Selected = True
            End If
        End Sub

        Protected Overrides Function GetCurrentFilterValueFromControl(ByVal cell As TableCell) As String
            Dim currentValue As String = DirectCast(cell.Controls(0), RadComboBox).SelectedItem.Value
            Me.CurrentFilterFunction = IIf((currentValue <> ""), GridKnownFunction.EqualTo, GridKnownFunction.NoFilter)
            Return currentValue
        End Function

        Private Sub rcBox_SelectedIndexChanged(ByVal sender As Object, ByVal e As Telerik.Web.UI.RadComboBoxSelectedIndexChangedEventArgs)
            DirectCast(DirectCast(sender, RadComboBox).Parent.Parent, GridFilteringItem).FireCommandEvent("Filter", New Pair())
        End Sub
    End Class
End Namespace

5.Web.Config

<?xml version="1.0"?><configuration>

    <appSettings/>
  <connectionStrings>
    <add name="NorthwindConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|Northwind.mdf;Integrated Security=True;User Instance=True"
     providerName="System.Data.SqlClient" />
  </connectionStrings>

    <system.web>
        <!--
            Set compilation debug="true" to insert debugging
            symbols into the compiled page. Because this
            affects performance, set this value to true only
            during development.

            Visual Basic options:
            Set strict="true" to disallow all data type conversions
            where data loss can occur.
            Set explicit="true" to force declaration of all variables.
        -->
        <compilation debug="true" strict="false" explicit="true">
            <assemblies>
                <add assembly="System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
                <add assembly="System.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
                <add assembly="System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/></assemblies></compilation>
        <pages>
            <namespaces>
                <clear/>
                <add namespace="System"/>
                <add namespace="System.Collections"/>
                <add namespace="System.Collections.Specialized"/>
                <add namespace="System.Configuration"/>
                <add namespace="System.Text"/>
                <add namespace="System.Text.RegularExpressions"/>
                <add namespace="System.Web"/>
                <add namespace="System.Web.Caching"/>
                <add namespace="System.Web.SessionState"/>
                <add namespace="System.Web.Security"/>
                <add namespace="System.Web.Profile"/>
                <add namespace="System.Web.UI"/>
                <add namespace="System.Web.UI.WebControls"/>
                <add namespace="System.Web.UI.WebControls.WebParts"/>
                <add namespace="System.Web.UI.HtmlControls"/>
            </namespaces>
        </pages>
        <!--
            The <authentication> section enables configuration
            of the security authentication mode used by
            ASP.NET to identify an incoming user.
        -->
        <authentication mode="Windows"/>
        <!--
            The <customErrors> section enables configuration
            of what to do if/when an unhandled error occurs
            during the execution of a request. Specifically,
            it enables developers to configure html error pages
            to be displayed in place of a error stack trace.

        <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
            <error statusCode="403" redirect="NoAccess.htm" />
            <error statusCode="404" redirect="FileNotFound.htm" />
        </customErrors>
        -->
  <httpRuntime maxRequestLength="102400" />
  <httpHandlers>
    <remove verb="*" path="*.asmx" />
    <add verb="*" path="*.asmx" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
    <add verb="*" path="*_AppService.axd" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
    <add verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" validate="false" />
    <add verb="*" path="Telerik.RadUploadProgressHandler.ashx" type="Telerik.Web.UI.Upload.RadUploadProgressHandler, Telerik.Web.UI" />
    <add verb="*" path="Telerik.Web.UI.DialogHandler.aspx" type="Telerik.Web.UI.DialogHandler, Telerik.Web.UI, Culture=neutral, PublicKeyToken=121fae78165ba3d4" />
    <add verb="*" path="Telerik.Web.UI.SpellCheckHandler.axd" type="Telerik.Web.UI.SpellCheckHandler, Telerik.Web.UI, Culture=neutral, PublicKeyToken=121fae78165ba3d4" />
    <add verb="*" path="ChartImage.axd" type="Telerik.Web.UI.ChartHttpHandler, Telerik.Web.UI, Culture=neutral, PublicKeyToken=121fae78165ba3d4" validate="false" />
    <add path="Telerik.Web.UI.WebResource.axd" verb="*" type="Telerik.Web.UI.WebResource, Telerik.Web.UI" validate="false" />
  </httpHandlers>
  <httpModules>
    <add name="RadCompression" type="Telerik.Web.UI.RadCompression" />
    <add name="RadUploadModule" type="Telerik.Web.UI.RadUploadHttpModule, Telerik.Web.UI" />
    <add name="ScriptModule" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
  </httpModules>
    </system.web>
  <!--<location path="Telerik.Web.UI.WebResource.axd">
      <system.web>
        <authorization>
          <allow users="*"/>
        </authorization>
      </system.web>
    </location>-->
</configuration>

I missed seeing the other samples you posted in the previous reply.
While checking my code, please pardon me if I may have overwritten/erased some vital code in your samples.

Finally, my explanation might have become an epic story.
I have been through a lot of trauma trying to figure a solution.
So I needed to come out without hesitation asking for help.
I thank you folks for being generous.

your friend,
Kumar.

[Kumar]

Hey guys
Sorry here are few more points I missed.
1.The Need Datasource query in my attached code should have "CustomerID" in the columnset.
Thus the correction:
            dt = GetDataTable("SELECT CustomerID, ContactName, ContactTitle, City, Country FROM Customers")
2.In your sample, you had two items in each combo entry- a picture for the flag of the Country and the Country Name.
3.I have attached a snapshot of how the sample looks on my system as opposed to the one in the demo Filtering template columns.

Kindly reply.
thanks again
Kumar.

[Radoslav]

Hi Kumar,

I am sending you a simple example, based on the posted code. However everything works as expected. Could you please check it out and let me know what differs in your case.

Additionally you could not send project to us into the forum thread. In order to sent us a simple project you need to open a formal support ticket from your Telerik account and attach a ZIP file there.

Looking forward for your reply.

Greetings,
Radoslav
the Telerik team

[Kumar]

Hello Radoslav,
Thanks a lot for your response.
I am happy to say that through your solution, I was able to resolve the requirement I specified.
With inspiring support from all of you, I will continue further development on Telerik Tools.
thanks
Kumar.

[Won]

Matt, did you ever find a solution for your issue? I'm facing the exact same issue as my data repository only returns custom objects (List<>, IQueryable<>, etc)