Google like filtering in using Filter Template or GridBoundColumn

[Hiren]

Hi can anyone tell me how to implement google like filtering for gridboundcolumn's are using filtertemplate
i found an example which is similar to my requirement click here

i want to use common method and javascript and want to filter the record based on filter column selected, in above example its filtering for only 1 column [ City dropdown]. i want to use same concept for all the bound columns

[Shinu]

Hello Giri,

Check the following demo which implements similar functionality.
Grid / Filter Templates

Thanks,
Shinu.

[Hiren]

That is not what i want, check this link
http://demos.telerik.com/aspnet-ajax/controls/examples/integration/gridandcombo/defaultcs.aspx?product=grid

I want to implement same functionality for existing bound columns

[Pavlina]

Hi Giri,

You could achieve your goal, by using FilterTemplate with ComboBox and use the logic from google like filtering demo. Here you could find demo and here is the online documentation about FilterTemplate.

Kind regards,
Pavlina
the Telerik team

[Hiren]

Hi Pavlina,
I had tried that example but it not working as i expect. Here is my code which i had written
ASCX File

<%@ Control Language="C#" AutoEventWireup="true" CodeFile="GoogleFilterSample2.ascx.cs"
    Inherits="GoogleFilterSample2" %>
<div>
    <telerik:RadGrid runat="server" EnableLinqExpressions="false" AllowFilteringByColumn="true"
        ID="RadGridUserControl" GridLines="None" OnNeedDataSource="RadGridUserControl_NeedDataSource">
        <MasterTableView AutoGenerateColumns="False" DataKeyNames="EmployeeID">
            <RowIndicatorColumn>
                <HeaderStyle Width="20px"></HeaderStyle>
            </RowIndicatorColumn>
            <ExpandCollapseColumn>
                <HeaderStyle Width="20px"></HeaderStyle>
            </ExpandCollapseColumn>
            <Columns>
                <telerik:GridBoundColumn DataField="EmployeeID" DataType="System.Int32" HeaderText="EmployeeID"
                    ReadOnly="True" SortExpression="EmployeeID" UniqueName="EmployeeID" AllowFiltering="false">
                </telerik:GridBoundColumn>
                <telerik:GridBoundColumn DataField="Title" HeaderText="Title" SortExpression="Title"
                    UniqueName="Title">
                    <FilterTemplate>
                        <telerik:RadComboBox runat="server" ID="FilterCombo" AutoPostBack="true" ShowToggleImage="false"
                            MarkFirstMatch="true" OnClientSelectedIndexChanged="onClientSelectedIndexChangedTitle"
                            OnItemsRequested="FilterCombo_ItemsRequested">
                        </telerik:RadComboBox>
                    </FilterTemplate>
                </telerik:GridBoundColumn>
                <telerik:GridTemplateColumn DataField="LastName" HeaderText="LastName" SortExpression="LastName"
                    UniqueName="LastName">
                    <FilterTemplate>
                        <telerik:RadComboBox ID="RcbLastName" EnableLoadOnDemand="true" OnClientSelectedIndexChanged="onClientSelectedIndexChangedLN"
                            runat="server" OnItemsRequested="RcbLastName_ItemsRequested" ShowToggleImage="false"
                            MarkFirstMatch="true">
                        </telerik:RadComboBox>
                    </FilterTemplate>
                    <ItemTemplate>
                        <%# Eval("LastName")%>
                    </ItemTemplate>
                </telerik:GridTemplateColumn>
                <telerik:GridTemplateColumn DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName"
                    UniqueName="FirstName">
                    <FilterTemplate>
                        <telerik:RadComboBox ID="RcbFirstName" EnableLoadOnDemand="true" OnClientSelectedIndexChanged="onClientSelectedIndexChangedFN"
                            runat="server" OnItemsRequested="RcbFirstName_ItemsRequested" ShowToggleImage="false"
                            MarkFirstMatch="true">
                        </telerik:RadComboBox>
                    </FilterTemplate>
                    <ItemTemplate>
                        <%# Eval("FirstName")%>
                    </ItemTemplate>
                </telerik:GridTemplateColumn>
                <telerik:GridTemplateColumn DataField="TitleOfCourtesy" HeaderText="TitleOfCourtesy"
                    SortExpression="TitleOfCourtesy" UniqueName="TitleOfCourtesy">
                    <FilterTemplate>
                        <telerik:RadComboBox ID="RcbTitleOfCourtesy" EnableLoadOnDemand="true" OnClientSelectedIndexChanged="onClientSelectedIndexChangedTitleOfCourtesy"
                            runat="server" OnItemsRequested="RcbTitleOfCourtesy_ItemsRequested" ShowToggleImage="false"
                            MarkFirstMatch="true">
                        </telerik:RadComboBox>
                    </FilterTemplate>
                    <ItemTemplate>
                        <%# Eval("TitleOfCourtesy")%>
                    </ItemTemplate>
                </telerik:GridTemplateColumn>
                <telerik:GridTemplateColumn DataField="BirthDate" HeaderText="BirthDate" SortExpression="BirthDate"
                    UniqueName="BirthDate" DataType="System.DateTime">
                    <FilterTemplate>
                        <telerik:RadComboBox ID="RcbBirthDate" EnableLoadOnDemand="true" OnClientSelectedIndexChanged="onClientSelectedIndexChangedBirthDate"
                            runat="server" OnItemsRequested="RcbBirthDate_ItemsRequested" ShowToggleImage="false"
                            MarkFirstMatch="true">
                        </telerik:RadComboBox>
                    </FilterTemplate>
                    <ItemTemplate>
                        <%# Eval("BirthDate")%>
                    </ItemTemplate>
                </telerik:GridTemplateColumn>
                <telerik:GridBoundColumn DataField="HireDate" DataType="System.DateTime" HeaderText="HireDate"
                    SortExpression="HireDate" UniqueName="HireDate">
                </telerik:GridBoundColumn>
                <telerik:GridBoundColumn DataField="Address" HeaderText="Address" SortExpression="Address"
                    UniqueName="Address">
                </telerik:GridBoundColumn>
                <telerik:GridTemplateColumn DataField="City" HeaderText="City" SortExpression="City"
                    UniqueName="City">
                    <FilterTemplate>
                        <telerik:RadComboBox ID="RcbCity" EnableLoadOnDemand="true" OnClientSelectedIndexChanged="onClientSelectedIndexChangedCity"
                            runat="server" OnItemsRequested="RcbCity_ItemsRequested" ShowToggleImage="false"
                            MarkFirstMatch="true">
                        </telerik:RadComboBox>
                    </FilterTemplate>
                    <ItemTemplate>
                        <%# Eval("City")%>
                    </ItemTemplate>
                </telerik:GridTemplateColumn>
            </Columns>
        </MasterTableView>
    </telerik:RadGrid><br />
    <asp:Button ID="clrFilters" runat="server" Text="Clear Filter" OnClick="clrFilters_Click" />
    <telerik:RadCodeBlock ID="RadCodeBlock1" runat="server">

        <script type="text/javascript">
            function onClientSelectedIndexChangedCity(sender, eventArgs) {
                var value = eventArgs.get_item().get_value();
                $find("<%= RadGridUserControl.ClientID %>").get_masterTableView().filter("City", eventArgs.get_item().get_text(), "StartsWith");
            }
            function onClientSelectedIndexChangedLN(sender, eventArgs) {
                var value = eventArgs.get_item().get_value();
                $find("<%= RadGridUserControl.ClientID %>").get_masterTableView().filter("LastName", eventArgs.get_item().get_text(), "StartsWith");
            }
            function onClientSelectedIndexChangedFN(sender, eventArgs) {
                var value = eventArgs.get_item().get_value();
                $find("<%= RadGridUserControl.ClientID %>").get_masterTableView().filter("FirstName", eventArgs.get_item().get_text(), "StartsWith");
            }
            function onClientSelectedIndexChangedTitle(sender, eventArgs) {
                var value = eventArgs.get_item().get_value();
                $find("<%= RadGridUserControl.ClientID %>").get_masterTableView().filter("Title", eventArgs.get_item().get_text(), "StartsWith");
            }
            function onClientSelectedIndexChangedTitleOfCourtesy(sender, eventArgs) {
                var value = eventArgs.get_item().get_value();
                $find("<%= RadGridUserControl.ClientID %>").get_masterTableView().filter("TitleOfCourtesy", eventArgs.get_item().get_text(), "StartsWith");
            }
            function onClientSelectedIndexChangedBirthDate(sender, eventArgs) {
                var value = eventArgs.get_item().get_value();
                $find("<%= RadGridUserControl.ClientID %>").get_masterTableView().filter("BirthDate", eventArgs.get_item().get_text(), "StartsWith");
            }

        </script>

    </telerik:RadCodeBlock>
</div>

ASCX.cs file

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Telerik.Web.UI;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public partial class GoogleFilterSample2 : System.Web.UI.UserControl
{
    public DataTable dt;

    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void clrFilters_Click(object sender, System.EventArgs e)
    {
        ClearFilter();
    }

    private void ClearFilter()
    {
        foreach (GridColumn column in RadGridUserControl.MasterTableView.Columns)
        {
            column.CurrentFilterValue = string.Empty;
            column.CurrentFilterFunction = GridKnownFunction.NoFilter;
        }
        RadGridUserControl.MasterTableView.FilterExpression = string.Empty;
        RadGridUserControl.MasterTableView.Rebind();
    }

    protected void RcbCity_ItemsRequested(object o, RadComboBoxItemsRequestedEventArgs e)
    {
        ClearFilter();
        (o as RadComboBox).DataTextField = "City";
        (o as RadComboBox).DataTextField = "City";
        (o as RadComboBox).DataSource = GetDataTable("City", e.Text);
        (o as RadComboBox).DataBind();
    }

    protected void RcbLastName_ItemsRequested(object o, RadComboBoxItemsRequestedEventArgs e)
    {
        ClearFilter();
        (o as RadComboBox).DataTextField = "LastName";
        (o as RadComboBox).DataTextField = "LastName";
        (o as RadComboBox).DataSource = GetDataTable("LastName", e.Text);
        (o as RadComboBox).DataBind();
    }

    protected void RcbFirstName_ItemsRequested(object o, RadComboBoxItemsRequestedEventArgs e)
    {
        ClearFilter();
        (o as RadComboBox).DataTextField = "FirstName";
        (o as RadComboBox).DataTextField = "FirstName";
        (o as RadComboBox).DataSource = GetDataTable("FirstName", e.Text);
        (o as RadComboBox).DataBind();
    }
    protected void FilterCombo_ItemsRequested(object o, RadComboBoxItemsRequestedEventArgs e)
    {
        ClearFilter();
        (o as RadComboBox).DataTextField = "Title";
        (o as RadComboBox).DataTextField = "Title";
        (o as RadComboBox).DataSource = GetDataTable("Title", e.Text);
        (o as RadComboBox).DataBind();
    }
    protected void RcbTitleOfCourtesy_ItemsRequested(object o, RadComboBoxItemsRequestedEventArgs e)
    {
        ClearFilter();
        (o as RadComboBox).DataTextField = "TitleOfCourtesy";
        (o as RadComboBox).DataTextField = "TitleOfCourtesy";
        (o as RadComboBox).DataSource = GetDataTable("TitleOfCourtesy", e.Text);
        (o as RadComboBox).DataBind();
    }
    protected void RcbBirthDate_ItemsRequested(object o, RadComboBoxItemsRequestedEventArgs e)
    {
        ClearFilter();
        (o as RadComboBox).DataTextField = "BirthDate";
        (o as RadComboBox).DataTextField = "BirthDate";
        (o as RadComboBox).DataSource = GetDataTable("BirthDate", e.Text);
        (o as RadComboBox).DataBind();
    }
    public DataTable GetDataTable(string columnName, string searchString)
    {
        String myQuery = string.Empty;

        if (!columnName.Equals("All"))
            myQuery = "SELECT DISTINCT " + columnName + " FROM Employees WHERE " + columnName + " LIKE '" + searchString + "%'";
        else
            myQuery = "SELECT [EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [City] FROM [Employees]";

        String ConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        SqlConnection conn = new SqlConnection(ConnString);
        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = new SqlCommand(myQuery, conn);

        DataTable myDataTable = new DataTable();

        conn.Open();
        try
        {
            adapter.Fill(myDataTable);
        }
        finally
        {
            conn.Close();
        }
        return myDataTable;
    }
    protected void RadGridUserControl_NeedDataSource(object sender, GridNeedDataSourceEventArgs e)
    {
        dt = GetDataTable("All", string.Empty);
        RadGridUserControl.DataSource = dt;
    }
}

When i try to filter on one column it works properly, when i try to filter on second column after filtering 1st column it doesn't bind the data to grid. And let me know how to filter Other datatype like DateTime, Integer. Want Google Like filter. combo box should show "Loading" status message when i request any item from filtering combo.

Check the attached images.
Reply asap

[Pavlina]

Hello Giri,

Please find attached to this message a sample working project which handles the desired functionality. Give it a try and let me know if it works as you expected.

Kind regards,
Pavlina
the Telerik team

[Hiren]

Thanks for your valuable reply,
I want google like filtering like this

I want to apply single column filter.

I am using license version, can u tell me how to raise a ticket on this issue., i need to finish it up asap