[Solved] Google Like filtering+ cascaded filter+ multi select filter

[Pooja]

So I am trying to build a cascaded multiselect google like filtering. So far I have been successful in getting google like cascaded filter to work.

Also I have to set a default filter value which is working fine except that I also store the filter information in my session.

So now the issue is with the multi select. So I do get the multi select by enabling checkboxes on the Combo boxes in custom template.

Imports Telerik.Web.UI
Imports System.Data
Imports System.Data.SqlClient
Imports PTAV2.Data
Imports System.Linq
Imports System.Data.Linq
'Imports System.Linq.Dynamic
Imports System.Reflection
Imports System.ComponentModel

Public Class MyCustomFilteringColumn
    Inherits GridBoundColumn

    Public Shared ReadOnly Property ConnectionString() As String
        Get
            Return ConfigurationManager.ConnectionStrings("PTAv2ConnectionString").ConnectionString
        End Get
    End Property

    'RadGrid will call this method when it initializes the controls inside the filtering item cells
    Protected Overrides Sub SetupFilterControls(ByVal cell As TableCell)
        MyBase.SetupFilterControls(cell)
        cell.Controls.RemoveAt(0)
        Dim combo As New RadComboBox()
        combo.ID = ("RadComboBox1" & Convert.ToString(Me.UniqueName))
        combo.ShowToggleImage = False
        combo.Skin = "MetroRed"
        combo.EnableLoadOnDemand = True
        combo.AutoPostBack = True
        combo.MarkFirstMatch = True
        combo.Height = Unit.Pixel(100)
        combo.BackColor = Drawing.Color.LightGoldenrodYellow
        'combo.BorderColor = Drawing.Color.Red
        'combo.BorderWidth = Unit.Pixel(2)
        'combo.BorderStyle = BorderStyle.Double
        ' combo.CheckBoxes = True
        AddHandler combo.ItemsRequested, AddressOf Me.list_ItemsRequested
        AddHandler combo.SelectedIndexChanged, AddressOf Me.list_SelectedIndexChanged
        cell.Controls.AddAt(0, combo)
        cell.Controls.RemoveAt(1)
    End Sub

    'RadGrid will cal this method when the value should be set to the filtering input control(s)
    Protected Overrides Sub SetCurrentFilterValueToControl(ByVal cell As TableCell)
        MyBase.SetCurrentFilterValueToControl(cell)
        Dim combo As RadComboBox = DirectCast(cell.Controls(0), RadComboBox)
        If (Me.CurrentFilterValue <> String.Empty) Then
            combo.Text = Me.CurrentFilterValue
        End If
    End Sub

    'RadGrid will cal this method when the filtering value should be extracted from the filtering input control(s)
    Protected Overrides Function GetCurrentFilterValueFromControl(ByVal cell As TableCell) As String
        Dim combo As RadComboBox = DirectCast(cell.Controls(0), RadComboBox)
        Return combo.Text
    End Function

    Private Sub list_ItemsRequested(ByVal o As Object, ByVal e As RadComboBoxItemsRequestedEventArgs)




        DirectCast(o, RadComboBox).DataTextField = Me.DataField
        DirectCast(o, RadComboBox).DataValueField = Me.DataField
        Dim ProjectList As New DataTable
        ProjectList = ConvertToDataTable(LoadData())
        ProjectList.Select(PTAV2Session.FilterExpression)
        Dim view As DataView = New DataView(ProjectList)
        view.Sort = Convert.ToString(Me.UniqueName)


        view.RowFilter = PTAV2Session.FilterExpression
        Dim distinctValues As DataTable = view.ToTable(True, Convert.ToString(Me.UniqueName))

        DirectCast(o, RadComboBox).DataSource = distinctValues
        DirectCast(o, RadComboBox).DataBind()
    End Sub

    Private Sub list_SelectedIndexChanged(ByVal o As Object, ByVal e As RadComboBoxSelectedIndexChangedEventArgs)
        Dim filterItem As GridFilteringItem = DirectCast(DirectCast(o, RadComboBox).NamingContainer, GridFilteringItem)
        If (Me.UniqueName = "ProjectId" Or Me.UniqueName = "TotalApprovedCost") Then
            'this is filtering for integer column type
            filterItem.FireCommandEvent("Filter", New Pair("EqualTo", Me.UniqueName))
        Else

        End If
        'filtering for string column type
        filterItem.FireCommandEvent("Filter", New Pair("Contains", Me.UniqueName))
    End Sub

    Public Shared Function GetDataTable(ByVal query As String) As DataTable
        Dim conn As New SqlConnection(ConnectionString)
        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

    Protected Shared Function LoadData() As List(Of p_RetrieveProjectList_Result)
        Dim _iProjectRepository As New ProjectRepository
        Dim projectList As List(Of p_RetrieveProjectList_Result) = _iProjectRepository.RetrieveProjectList()

        Return projectList

    End Function

    Public Function ConvertToDataTable(Of T)(data As IList(Of T)) As DataTable
        Dim properties As PropertyDescriptorCollection = TypeDescriptor.GetProperties(GetType(T))
        Dim table As New DataTable()

        For Each prop As PropertyDescriptor In properties
            table.Columns.Add(prop.Name, If(Nullable.GetUnderlyingType(prop.PropertyType), prop.PropertyType))
        Next
        For Each item As T In data
            Dim row As DataRow = table.NewRow()
            For Each prop As PropertyDescriptor In properties
                row(prop.Name) = If(prop.GetValue(item), DBNull.Value)
            Next
            table.Rows.Add(row)
        Next
        Return table
    End Function
End Class

Also I have set EnableLinqExpressions = false in my code. So when I select multiple values from the dropdown, I have 2 issues
1) Sometimes the filter test says "All items selected" or "2 items selected" instead of the actual filter values
2) Since the EnableLinqExpressions=false the query is like this where xyz like "value1,value2" So should I write a routine to break these values separted by commas as individual filter values with an OR statement.

I also tried by enableing the linq expressions and then the query is like this where xyz.contains("value1,value2"). I both cases results do not return anything. 

Please help .

Also was trying the dynamic Linq library. It had it own share of issues. I could not get it to work either.

[Angel Petrov]

Hello Pooja,

We have a code library which demonstrates how to implement a similar scenario(you can find it by clicking here). Please review it and tell us if it serves you any good. In the example provided the EnableLinqExpressions is set to true. This means that the way the filter expressions are being build is different. On how to build your filter expression when EnableLinqExpressions is set to false please review this help article.

Now for the concrete questions that you asked.
   1) Based on the information provided it would be hard to tell why is the filter text set to the values you mention. We would need a project that we can debug.
   2) You should definitely break the values and use an OR statement to build the filter expression. The help article which I linked above should help you to achieve this.

If my suggestions do not help you in resolving the problem please open a formal support ticket with a project attach so we could give you a more precise answer.

All the best,
Angel Petrov
the Telerik team