Adding a custom filter function

[Arif]

Hi,

I am trying to add a new filter function to implement the "IN" SQL function. Users will specify a list of values that they want to filter on and then select the "In" item from the filter menu for a given column. I have done the following so far:

1. Added a new RadMenuItem to the GradFilterMenu in Page_Init() and set its Text and Value properties to "In"
2. Added an event handler for the GradFilterMenu's ItemClick event
3. Added an event handler for the ItemCommand event.

When I debug the code, I get the following error:

Microsoft JScript runtime error: Sys.ArgumentException: 'In' is not a valid value for enum Telerik.Web.UI.GridFilterFunction.
Parameter name: value

Not exactly sure how to proceed from here. Could you please help?

[Arif]

Just to add that I am aware a user-defined filter function can be implemented using the "Custom" filter function, but I'm trying to explore if it is possible to add more than one custom function. In order to achieve this, it appears that the GridFilterFunction enum may need to be tinkered with.

Any insight into how to do this or directions to where I can find this information would be much appreciated.

Thanks.

[Arif]

Anyone?

[Mira]

Hello Arif,

I have followed your scenario and prepared a sample project for you implementing it.
You can find it attached to this message.

Please take a look at it and let me know whether it helps.

Kind regards,
Mira
the Telerik team

[Arif]

Hi Mira,

Thanks for your reply. Unfortunately, I've run into an issue (I'm not using the code you sent me yet) on which I'd like your help. Here's the scenario.

1. The "Custom" filter has been modifed to implement the "IN" functionality.
2. The user specifies a space delimited list of values (e.g. 20 30 40) in the filter textbox for Column A and selects the In option from the filter dropdown list (the Custom text has been renamed to In).
3. Code in the ItemCommand event handler constructs the proper filter expression ([Column A] IN (20, 30, 40)) and appends it to the FilterExpression (Grid.MasterTableView.FilterExpression).
4. When the page returns after the postback, the data in the grid is filtered as expected.
5. Now the user tries to set a filter on another column in the grid and causes a postback.
6. When I check the FilterExpression on the server, the expression that was constructed in 3. above is lost. Instead, the FilterExpression displays "20 30 40 AND <other filters>".

Do filter expressions for Custon filters need to be reconstructed on every postback? If so, what is the best place to do this? I'm not sure where the FilterExpression is actually populated with the previous (pre-postback) filter values (it's somewhere in between Page_Init() and Page_Load().

[Mira]

Hello Arif,

I have followed tour scenario and prepared a sample project implementing it. You can find it attached to this message.

Please take a look at it and let me know whether it helps.

Sincerely yours,
Mira
the Telerik team

[Arif]

Mira,

Thanks for the code. I had exactly the same implementation except that the e.Canceled = true statement had been commented out. Below are some queries based on my observations. Could you please clarify these for me?

1. Could you please explain exactly what canceling of a command (setting the e.Cancel property for the GridCommandEventArgs object) means?
2. Now that I have uncommented the statement e.Canceled = true, the proper expression for the IN filter is being retained in the FilterExpression when posting back. However, when the page returns, the filter selection for [Column A] changes from IN to NoFilter. I presume that one consequence of setting e.Canceled = true is that the filter selection that raised the ItemCommand event in the first place is lost, and the filter selection defaults to NoFilter. How do I get the filter to retain the original selection (in this case, the IN selection)?

[Iana Tsolova]

Hi Arif,

Straight to your questions:
1. When e.Canceled is set to true, the default command is canceled, and the default filters will not be applied. Also, in case the particular command invokes implicit grid rebind, the grid will not be rebound. That is why in order the custom filters to be applied, you need to rebind the grid explicitly.
2. To overcome that issue, try explicitly setting the column CurrentFilterFunction and CurrentFilterValue.

Check it out and let me know how it goes.

Kind regards,
Iana
the Telerik team

[Arif]

Iana,

Thanks for your feedback. Seeting the CurrentFilterFunction and CurrentFilterValue appears to get the job done. However, for custom filters, the filter value has to be the entire query, i.e., ([Column A] IN (1, 2 ,3)). This is what appears in the filter textbox for Column A, which kind of defeats the whole purpose because I could have had the exact same result by selecting the Custom filter and writing any valid SQL query to be executed on that column in the textbox without handling the ItemCommand event.

I'm trying to determine if I can create a "new" GridKnownFunction which would behave exactly like the other known functions without the user having to type in entire SQL queries in the filter textboxes. In other words, if I can create a known function called "In", all the user needs to do is specify a list of values, e.g., (1, 2, 3) in the filter textbox and the code will construct the proper SQL ([Column A] IN (1, 2, 3)) in the background like the EqualTo, NotEqualTo, etc. functions would do.

So, to summarise:

1. Using a Custom function requires the entire custom SQL expression to be specified in the filter textbox.
2. The above is not required when using a GridKnownFunction.

Is it possible to create a new GridKnownFunction? If not, is it possible to achieve functionality similar to a GridKnownFunction without actually creating one?

Thanks.

[Mira]

Hello Arif,

Your observations are indeed right.
The "Custom" filter function is to be used to introduce a filter function that is not included in the default list, or to allow multiple columns with the same data type to have different filtering menus.
To see how to replace the items in the filtering menu with your own custom options, please take a look at this help topic.

I hope this helps.

Best wishes,
Mira
the Telerik team

[Arif]

Hi Mira,

Thanks for your reply. If you look at the very first post in this series, you will find that I was attempting to do exactly what you have suggested. However, I'm having the following issues:

1. If I specify a value in the filter text box and then select a filter function from the dropdown, the
filterMenu_ItemClick(object sender, RadMenuEventArgs e) event handler is not executed and I get the following error:

Microsoft JScript runtime error: Sys.ArgumentException: 'In' is not a valid value for enum Telerik.Web.UI.GridFilterFunction.
Parameter name: value

2. If no value is specified in the filter text box and then a filter function selected from the dropdown, the event handler above is invoked, but e.Item.Attributes["columnUniqueName"] is null (in fact, e.Item.Attributes.Count = 0)

Can you tell me where I might be going wrong?

Thanks.

[Mira]

Hello Arif,

Straight to your questions:

1. I suggest that you use the "Custom" filter function to introduce a filter function that is not included in the default list. You can change the Custom text of the menu item to In with the following code:
   

   protected void RadGrid1_Init(object sender, EventArgs e)
   {
       GridFilterMenu menu = RadGrid1.FilterMenu;
       menu.Items.FindItemByText("Custom").Text = "In";
   }

2. I tried to replicate the issue which you described, but to no avail - please take a look at this video. If the problem persists, please send us a small working project, demonstrating your full setup and showing the unwanted behavior.
   We will examine it locally and get back to you.

Greetings,
Mira
the Telerik team

[Arif]

May be I'm missing something very subtle here. I looked at the video you sent me and implemented the code as explained. What I observed is that new values for the In filter on the same column appends to the FilterExpression rather than replacing the previous filter. For example:

1. Filter: [Column A] In (1, 2, 3); FilterExpression: (Other filters) AND ([Column A] IN (1, 2, 3))
2. Filter: [Column A] In (4, 5); FilterExpression: (Other filters) AND ([Column A] IN (1, 2, 3)) AND ([Column A] IN (4, 5))
3. Filter: [Column A] In (2, 6); FilterExpression: (Other filters) AND ([Column A] IN (1, 2, 3)) AND ([Column A] IN (4, 5)) AND ([Column A] IN (2, 6))

How can this be prevented?

[Pavlina]

Hi Arif,

Based on the supplied information, it is hard to determine what is causing the unwanted behavior. In order to investigate the problem further, it will be best if you open a formal support ticket, and provide a small working project, which can be tested locally, in order to supply further information.

Greetings,
Pavlina
the Telerik team

[Arif]

Thanks for your help guys. I will try and put together a sample project for you to investigate.

In the interim, I have managed to get the In filter to work by doing the following:

1. Using the Custom GridKnownFunction and renaming it to "In".
2. Implementing a template column (of type GridBoundColumn) and overriding the following methods:
    a. EvaluateFilterExpression
    b. SetupFilterControls
    c. SetCurrentFilterValueToControl
    d. GetCurrent FilterValueFromControl

[Randall]

Hello Arif,

I am trying to implement similar functionality with a custom filter in my application.  I have a custom filter using a RadComboBox with an ItemTemplate of CheckBoxes, Images, and Descriptions.  You can select one or more CheckBoxes and click an Apply or Cancel button.  I would like to have this custom class also work as your solution describes, using the GridKnownFunction and overriding the base methods you have listed.

Could you post the code to your solution?

Thanks,

-Randall Price

[Arif]

Hi Randall,

Essentially, I have based my code on info from the following links:

http://www.telerik.com/help/aspnet/grid/grdimplementingfilteringfortemplatecolumns.html
http://www.screencast.com/users/mira_/folders/Jing/media/85f2e1fe-1029-4d3f-a881-7afe68c5e23f

I must add that I'm not doing the following:

1. I'm not handling the ItemClick event for the filter menu item (because it doesn't consistently fire for some reason)
2. I'm also not handling the ItemCommand event of the RadGrid because it seems to me that when this event is fired (when using template columns), only the original filter selection (prior to the postback) is available and not the new selection.
3. Another note - I've found that handling events like the OnTextChanged for a textbox in the filtering item cell tends to mess things up a little (in terms of when the selected filter function becomes available). If things don't seem to be working, it may be an idea to disable these event handlers and see if it makes a difference.

In the EvaluateFIlterExpression method, you simply create your custom filter expression and return it. Sample code below:

public

 

override string EvaluateFilterExpression(GridFilteringItem filteringItem)

 

{

 

 

    string filter = string.Empty;

 

 

 

    TableCell cell = filteringItem[this.UniqueName];

 

 

 

    TextBox tbPattern = cell.Controls[0] as TextBox;

 

 

 

    if (tbPattern.Text != string.Empty)

 

    {

 

 

 

 

        if (this.CurrentFilterFunction == GridKnownFunction.Custom)

 

        {

            filter =

 

//Create your filter expression for the Custom filter

 

        }

 

 

 

 

        else

 

 

        {

            filter = 

//Create your filter expression for the other known filters
        }

 

    }

 

 

    else

 

 

    {

        filter =

string.Empty;

 

    }

 

 

    return filter;
}

 

 

Hope that helps.


Arif

[Pavlina]

Hello Arif,

Thank you for sharing your solution with the community. I am sure it would be helpful for anyone searching such functionality.

Best wishes,
Pavlina
the Telerik team

[Anand]

Dear Mira,

I am trying to develop a custom filter on one of the column (Radgridlist) (datatype: bigint). In radgrid_itemcommand, I am able to capture the user click event on custom filter and also the value he/she is typing in the filter. Now what I need to do it is to write either sql query or force filter to execute filter command "EqualTo". (but in equal to I may would like to match with either the entered value or with appended 0 with prefix "21" (e.g. if user has entered 1234, I will match with 1234 and also with 2100001234). Total length 10. Would it be possible to demonstrate how it can be done. Its very urgent and I am using vb.net.

 

Protected Sub radGridList_ItemCommand(ByVal sender As Object, ByVal e As Telerik.Web.UI.GridCommandEventArgs) Handles radGridList.ItemCommand

Dim filterPair As Pair = CType(e.CommandArgument, Pair)

        If e.CommandName = RadGrid.FilterCommandName Then
            If filterPair.First.ToString() = "EqualTo" Then
                Dim colName As String = filterPair.Second.ToString()
                Dim tbPattern As TextBox = CType((CType(e.Item, GridFilteringItem))(colName).Controls(0), TextBox)
                ' Dim values As String() = tbPattern.Text.Split(" "c)
                e.Canceled = True
                Dim txtPayeenum As String = tbPattern.Text.ToString