Ticket T268885

Visible to All Users

ASPxGridView - How to use date constants in filter

Eugene Paradizov

created 10 years ago

[DevExpress Support Team: CLONED FROM T166826: ASPxFilterControl - Support predefined operations for date columns like in WinForms]
This functionality is sorely needed. Many of our users are asking for this. We allow our user to save grid layout. They can later pick it from a list and restore the layout as they had it. However, the need to be able to filter date fields based on relative dates. They need to be able to say [SomeDateField] >= TODAY. And, be able to save that as grid's layout for later retrieval. Please provide a work-around on how this can be done. Also, besides being able to use relative dates, they need to retain the ability to filter using absolute values.
If the grid control would allow to compare fields, such as [Field1] [Comparison] [Field2], then I could add a field called [Today] into the grid and let users compare to that field. But, this option is also missing. Please provide a usable work-around.

Answers approved by DevExpress Support

Mike (DevExpress)

created 10 years ago (modified 10 years ago)

While there are no such built-in options available, the closest way to mimic this behavior is to use the approach from the ASPxGridView - Date auto filter example.

UPDATED:

Having prepared a sample, I have found another solution based on the specialized FunctionOperator functions (instead of combining the FilterDateType functions that are mainly intended for desktop GridControl/FilterControl controls):

C#
new BinaryOperator(  
    new OperandProperty(e.Column.FieldName), //Left Operand  
    new FunctionOperator() { OperatorType = FunctionOperatorType.Today }, //Right Operand  
    BinaryOperatorType.GreaterOrEqual //Operator  
)

Please refer to the attached project.

T268885_2.zip

Show previous comments (7)

Eugene Paradizov 10 years ago

Mike,
I have scoured your support site prior to posting. The example you are referring me to - I saw it and it does not work for me. I would be surprised if it works for any good app. This example seems to limit filtering to just those choices in the drop-down. So, there is no way to filter for an absolute value.
Also, my users save their filters (grid layouts) for later reuse. Selecting "Today" from the drop-down will not save "Today" into the filter. The filter will actually contain the current date of the day the user makes this filter. The next day, this filter will continue to show previous days' records, not the records of the current day. So, this is really a non-working solution.
What I think could work is some kind of work-around that would use Custom Function approach. So, could you try to provide such an example? You have one example for a Numbers column. However, that works with a text column, not date/time column. Could you create an example of Custom Function usage for a date/time column that would be usable with ASPxGridView control? Maybe you will have to use a textbox control instead of date editor to accomplish this. Or, maybe an extended date editor that has another button added to it with a small popup menu to select relative dates (Today, Tomorrow, Yesterday, etc). How about it?
And, finally, what about field-to-field comparison in the grid's filter? Is this also too much to ask? If we could have this feature, we could just add a Today column into the grid and compare to it.

DevExpress Support Team 10 years ago

Hi,

We're working on this question and need some more time to provide you with a more precise solution. Thank you for your patience.

Best Regards,
Luke

Mike (DevExpress) 10 years ago

Hello Eugene,

Thank you for your detailed explanation. I agree that the functionality available in our desktop grids may be useful for ASPxGridView as well. We are working on it in the upcoming versions.
I have attached a sample project that illustrates how to achieve a similar functionality (without a calendar yet) with version 15.1 by using the existing "date intervals" filtering engine. I look forward to your feedback once you have had the opportunity to try this approach. Uncomment the SettingsCookies tag to check how the layout is saved with the applied filter.

T268885_1.zip

Eugene Paradizov 10 years ago

Hello Mike,
I checked out your sample project. I see what's going on. Of course, this is not enough by any means. Can this be combined with the Filter Row and Filter Builder? So, that you can use those features AND this feature? And be able to save layout?
I saw that FilterExpression reflects the function name Is…([C1]). However, this approach requires a function for each possible comparison operation the user may need. And even the simplest filter, like [DateField] > Today, is still not doable using the new FilterDateType. It does not seem to have a function IsGreaterThanOrEqualToToday.
So, I still need a sample that would work. This does not provide enough functionality, unfortunately.
And, the easiest solution would be to be able to compare filed-to-field. Can that be done in any way? Using a custom function approach, maybe? Say, you add a column called [Date_Today] and use it in the filter to compare some date field to this [Date_Today]. Then, any logical comparison can be used and it would still work fine. Of course, more such special [Date_] columns can be added for Yesterday, Tomorrow, LastWeek, and so on.

Mike (DevExpress) 10 years ago

Hello Eugene,

>>Can this be combined with the Filter Row and Filter Builder? So, that you can use those features AND this feature?

Currently, there is no way to use these filters simultaneously. The FilterBuilder (ASPxFilterControl) does not yet support rendering such a layout, and the AutoFilterRow displays an editor with a single value set according to the AutoFilterCondition.

>>It does not seem to have a function IsGreaterThanOrEqualToToday.

If there is no exact function in the FilterDateType enumeration, it is possible to extend any of the existing functions (such as "Today") using the Criteria Language Syntax (the uniform syntax for the DevExpress filtering).

I have attached the modified project that illustrates this idea in action.

T268885_2.zip

Mike (DevExpress) 10 years ago

Note that this solution will be based on the current Today Date's absolute value. In your case, it is necessary to combine the filters available in the FilterDateType enumeration to implement a value-independent filter.

Eugene Paradizov 10 years ago

So, could you provide an example of value-independent filter, please?

Mike (DevExpress) 10 years ago

Hello Eugene,

C#
new BinaryOperator(  
    new OperandProperty(e.Column.FieldName), //Left Operand  
    new FunctionOperator() { OperatorType = FunctionOperatorType.Today }, //Right Operand  
    BinaryOperatorType.GreaterOrEqual //Operator  
)

Please refer to the attached project.

T268885_2.zip

Eugene Paradizov 10 years ago

Mike,
This works. I also implemented other variants, such as IsToday:
           new GroupOperator(GroupOperatorType.And,    //Group Operator
               new BinaryOperator(
                   new OperandProperty(e.Column.FieldName), //Left Operand
                   new FunctionOperator(FunctionOperatorType.Today), //Right Operand
                   BinaryOperatorType.GreaterOrEqual //Operator
               ),
               new BinaryOperator(
                   new OperandProperty(e.Column.FieldName), //Left Operand
                   new FunctionOperator(FunctionOperatorType.AddDays, new FunctionOperator(FunctionOperatorType.Today), 1), //Right Operand
                   BinaryOperatorType.Less //Operator
               )
           )
IsYesterday:
           new GroupOperator(GroupOperatorType.And,    //Group Operator
               new BinaryOperator(
                   new OperandProperty(e.Column.FieldName), //Left Operand
                   new FunctionOperator(FunctionOperatorType.AddDays, new FunctionOperator(FunctionOperatorType.Today), -1), //Right Operand
                   BinaryOperatorType.GreaterOrEqual //Operator
               ),
               new BinaryOperator(
                   new OperandProperty(e.Column.FieldName), //Left Operand
                   new FunctionOperator(FunctionOperatorType.Today), //Right Operand
                   BinaryOperatorType.Less //Operator
               )
           )
IsTomorrow:
           new GroupOperator(GroupOperatorType.And,    //Group Operator
               new BinaryOperator(
                   new OperandProperty(e.Column.FieldName), //Left Operand
                   new FunctionOperator(FunctionOperatorType.AddDays, new FunctionOperator(FunctionOperatorType.Today), 1), //Right Operand
                   BinaryOperatorType.GreaterOrEqual //Operator
               ),
               new BinaryOperator(
                   new OperandProperty(e.Column.FieldName), //Left Operand
                   new FunctionOperator(FunctionOperatorType.AddDays, new FunctionOperator(FunctionOperatorType.Today), 2), //Right Operand
                   BinaryOperatorType.Less //Operator
               )
           )
All these are working well. The only issue now is how the can edit the overall filter (for non-date columns) and the newly added custom functions? If I enable Filter Row and Filter Builder (it's a must for all other columns and complex logic), then these custom functions get erased right after I edit the filter in the Filter Builder. Of course, they are not shown in the Builder, so they get erased. So, how do we make it all work nicely together?
Our grids have tens of columns of all kinds of data types. So, I must have the Filter Row and Filter Builder enabled. How could I merge them with custom functions and still have some workable solution?

Mike (DevExpress) 10 years ago

Hello Eugene,

Thank you for informing me of your progress.

>>How could I merge them with custom functions and still have some workable solution?

As I mentioned earlier, currently, there is no way to use these filters simultaneously. The FilterBuilder (ASPxFilterControl) does not yet support rendering such a layout, and the AutoFilterRow displays an editor with a single value set according to the AutoFilterCondition.
We are working on improving the "date range/intervals" filtering functionality and on providing some new features in the in the next major version (15.2).

Created: July 19, 2015 7:27 PM

Modified: July 28, 2015 2:57 PM

Platform: ASP.NET Web Forms

Product: ASPxGridView

Build: 15.1.5

Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

ASPxGridView - How to use date constants in filter

Answers approved by DevExpress Support

Recently viewed tickets