How to filter datetime column with date range picker in grid with odata?

[Alp Tosun]

Hello,

We're using Syncfusion.Blazor 19.4.0.48

I was trying to use DateRangePicker to filter DateTime columns in our grids which uses OData to populate data. I've added DateRangePicker to the FilterTemplate of the columns, however, I seem to be having issue with the default filter menu. I can filter the column by selecting dates on the date range picker and clicking apply but then the filter menu remains open and needs the Filter button to be clicked which doesn't actually filter by the selected dates. Is there a way to only use the daterangepicker for filtering a column?

<SfGrid @ref="Grid" TValue="Order" AllowSorting="true" Width="100%"
        AllowFiltering="true" AllowPaging="true" AllowReordering="true" EnablePersistence="true"
        AllowSelection="true" AllowResizing="true">
    <SfDataManager Url="https://services.odata.org/V4/Northwind/Northwind.svc/Orders/" Adaptor="Adaptors.ODataV4Adaptor"></SfDataManager>
    <GridPageSettings PageSize="20" PageSizes="@(new int[] { 20, 50, 100, 200 })"></GridPageSettings>
    <GridFilterSettings Type="Syncfusion.Blazor.Grids.FilterType.Menu"></GridFilterSettings>
    <GridColumns>
        <GridColumn Field=@nameof(Order.OrderID) HeaderText="Order ID" IsPrimaryKey="true" TextAlign="TextAlign.Right" Width="120"></GridColumn>
        <GridColumn Field=@nameof(Order.CustomerID) HeaderText="Customer Name" Width="150"></GridColumn>
        <GridColumn Field=@nameof(Order.OrderDate) HeaderText=" Order Date" Format="MM/dd/yyyy hh:mm tt"
                    TextAlign="TextAlign.Right" Width="250">
            <FilterTemplate>
                @{
                    <SfDateRangePicker Placeholder="Choose a Range" Width="500"
                                       TValue="DateTime">
                        <DateRangePickerEvents TValue="DateTime" ValueChange="ValueChangeHandler"></DateRangePickerEvents>
                    </SfDateRangePicker>
                }
            </FilterTemplate>
        </GridColumn>
        <GridColumn Field=@nameof(Order.Freight) HeaderText="Freight" Format="C2" TextAlign="TextAlign.Right" Width="120"></GridColumn>
    </GridColumns>
</SfGrid>

@code{
    private SfGrid<Order> Grid;
    public DateTime MinDate { get; set; } = new DateTime(1996, 07, 04);
    public DateTime MaxDate { get; set; } = new DateTime(1998, 05, 05);
    public class Order
    {
        public int? OrderID { get; set; }
        public string CustomerID { get; set; }
        public DateTime? OrderDate { get; set; }
        public double? Freight { get; set; }
    }
    public async Task ValueChangeHandler(RangePickerEventArgs<DateTime> args)
    {
        if (Grid.FilterSettings.Columns == null)
        {
            Grid.FilterSettings.Columns = new List<GridFilterColumn>();
        }

        if (Grid.FilterSettings.Columns.Count > 0)
        {
            Grid.FilterSettings.Columns.RemoveAll(c => c.Field == "OrderDate");
        }
        //Get all the Grid columns
        var columns = await Grid.GetColumns();
        //Fetch the Uid of OrderDate column
        string fUid = columns[1].Uid;
        Grid.FilterSettings.Columns.Add(new GridFilterColumn
        {
            Field = "OrderDate",
            Operator = Syncfusion.Blazor.Operator.GreaterThanOrEqual,
            Predicate = "and",
            Value = args.StartDate,
            Uid = fUid
        });
        Grid.FilterSettings.Columns.Add(new GridFilterColumn
        {
            Field = "OrderDate",
            Operator = Syncfusion.Blazor.Operator.LessThanOrEqual,
            Predicate = "and",
            Value = args.EndDate.AddDays(1).AddSeconds(-1),
            Uid = fUid
        });

        Grid.Refresh();
    }
}

Thanks in advance,

Alp

[Renjith Singh Rajendran]

Hi Alp,

Greetings from Syncfusion support.

Query : clicking apply but then the filter menu remains open and needs the Filter button to be clicked which doesn't actually filter by the selected dates.

To perform filter action when pressing the Filter button, we suggest you to bind OnActionBegin event to grid and prevent default filtering action using args.Cancel and now perform your custom filtering codes which you have done inside ValueChange inside OnActionBegin handler. Please refer and modify the codes as like below,

 

<GridColumn Field=@nameof(Order.OrderDate) HeaderText=" Order Date" Format="MM/dd/yyyy hh:mm tt"

                    TextAlign="TextAlign.Right" Width="250">

            <FilterTemplate>

                @{

                    <SfDateRangePicker Placeholder="Choose a Range" Width="500" @bind-StartDate="StartDate" @bind-EndDate="EndDate"

                                       TValue="DateTime">

                        <DateRangePickerEvents TValue="DateTime" ValueChange="ValueChangeHandler"></DateRangePickerEvents>

                    </SfDateRangePicker>

                }

            </FilterTemplate>

        </GridColumn>

 

 

    public DateTime StartDate{ get; set; }

    public DateTime EndDate{ get; set; }

    public async Task OnActionBegin(ActionEventArgs<Order> args)

    {

        if (args.RequestType.Equals(Action.Filtering) && args.CurrentFilteringColumn.Equals("OrderDate")))

        {

            args.Cancel = true; //cancel default filter action

            if (Grid.FilterSettings.Columns == null)

            {

                Grid.FilterSettings.Columns = new List<GridFilterColumn>();

            }

 

            if (Grid.FilterSettings.Columns.Count > 0)

            {

                Grid.FilterSettings.Columns.RemoveAll(c => c.Field == "OrderDate");

            }

            //Get all the Grid columns

            var columns = await Grid.GetColumns();

            //Fetch the Uid of OrderDate column

            string fUid = columns[2].Uid;

            Grid.FilterSettings.Columns.Add(new GridFilterColumn

            {

                Field = "OrderDate",

                Operator = Syncfusion.Blazor.Operator.GreaterThanOrEqual,

                Predicate = "and",

                Value = StartDate,

                Uid = fUid

            });

            Grid.FilterSettings.Columns.Add(new GridFilterColumn

            {

                Field = "OrderDate",

                Operator = Syncfusion.Blazor.Operator.LessThanOrEqual,

                Predicate = "and",

                Value = EndDate.AddDays(1).AddSeconds(-1),

                Uid = fUid

            });

 

            Grid.Refresh();

        }

    }

   public async Task ValueChangeHandler(RangePickerEventArgs<DateTime> args)

    {

        StartDate = args.StartDate;

        EndDate = args.EndDate;

    }

 

Also we found that you have wrong Uid value assigned for fUid. You have specified as 1 instead of 2(which is the column index of OrderDate). So please ensure to provide proper Uid value also.

Regards,

Renjith R

[Oliver Bowen]

Hi I'm running into the exact same use case and have tried to impliment this solution. However, I am finding that with persistence turned on, filtering then reloading the page and attempting to open the filter again causes a system conversion error.

blazor.server.js:1 [2022-06-14T10:49:15.921Z] Error: System.ArgumentException: Object of type 'System.Text.Json.JsonElement' cannot be converted to type 'System.Nullable`1[System.DateTime]'.

   at System.RuntimeType.TryChangeType(Object value, Binder binder, CultureInfo culture, Boolean needsSpecialCast)

   at System.Reflection.MethodBase.CheckArguments(StackAllocedArguments& stackArgs, ReadOnlySpan`1 parameters, Binder binder, BindingFlags invokeAttr, CultureInfo culture, Signature sig)

   at System.Reflection.RuntimeMethodInfo.InvokeOneParameter(Object obj, BindingFlags invokeAttr, Binder binder, Object parameter, CultureInfo culture)

   at System.Reflection.RuntimePropertyInfo.SetValue(Object obj, Object value, BindingFlags invokeAttr, Binder binder, Object[] index, CultureInfo culture)

   at System.Reflection.PropertyInfo.SetValue(Object obj, Object value)

   at Syncfusion.Blazor.Grids.Internal.Filter`1.UpdateFilterModel(Object modelInstance, GridFilterColumn Col)

   at Syncfusion.Blazor.Grids.Internal.FilterMenuRenderer`1.<BuildRenderTree>b__8_4(RenderTreeBuilder __builder3)

   at Syncfusion.Blazor.Popups.SfDialog.<BuildRenderTree>b__331_1(RenderTreeBuilder __builder2)

   at Microsoft.AspNetCore.Components.CascadingValue`1.Render(RenderTreeBuilder builder)

   at Microsoft.AspNetCore.Components.Rendering.ComponentState.RenderIntoBatch(RenderBatchBuilder batchBuilder, RenderFragment renderFragment, Exception& renderFragmentException)

Any suggestions for getting around this?

Thanks,

Olly

[Alp Tosun]

Thank you. That let me filter using the dates selected by the date range picker. I needed to add a null check on    args.CurrentFilteringColumn

Appreciate it

[Renjith Singh Rajendran]

Hi Alp,

Thanks for your update. Please get back to us if you need further assistance.

@Oliver Brown – Could you please check based on suggestion by @Alp. If you are still facing difficulties, then kindly get back to us with a issue reproducing sample along with a video showing the replication of the problem you are experiencing to proceed further.

Regards,

Renjith R

[Oliver Bowen]

Hi Renjith,

Yes I had to make that fix too but it has nothing to do with the use of local storage. I've attached a clip of the issue where you can see the error.

Thanks,

oll

Attachment: persistenceError_3784cee0.zip

[Oliver Bowen]

Hi Renjith,

Please see attached gif for error. The fix suggested by Alp I had to do as well but that is not related to this issue.

Attachment: persistenceError_9bf519ad.zip

[Oliver Bowen]

Also attached the project I have been playing

Attachment: Persistance_c78913b1.zip