Remove duplicate entries from excel filter of a date type column

[Yasas Petangoda]

Hello There!,

Kindly refer the demo example given https://ej2.syncfusion.com/angular/demos/#/material/grid/filter-menu 

and the following image of it.



We can see that shipped date column type is date and value is formatted("MM/dd/yyyy") only to show date part . When we open the excel filter we can see that duplicate date choices are listed. Appreciate if you could provide us a way to get rid of duplicate date choices from the filter popup. I attempted using setHours(0,0,0) on each value of the json and it works. But the problem is, we cannot sort considering time when we do so. Is there a way to distinct on displayed value. If you have a better approach for this kindly let me know.

Thanks a lot!

Regards,

Yasas

[Sujith Kumar Rajkumar]

Hi Yasas, 

Greetings from Syncfusion support. 

The checkbox data in the grid’s excel filter displays the distinct data filtered from the underlying data source by default. As for your query in online sample, since the Shipped Date column is set as date format and distinct time value is present for this column the values are repeated in the filter data. If you do not require the time value you can remove it in the underlying json data to display the distinct date values alone. However if you need the time value also then you need to set the column format to ‘datetime’ to display distinct datetime values as demonstrated in the below code snippet, 

<ejs-grid #grid [dataSource]='data' allowPaging='true' allowFiltering='true' [pageSettings]='pageSettings' [filterSettings]='filterSettings'> 

    <e-columns> 

                 . 

                  . 

        <e-column field='ShippedDate' headerText='Shipped Date' width='130' type='date' [format]="formatoptions" textAlign='Right'></e-column> 

    </e-columns> 

</ejs-grid> 

ngOnInit(): void { 

               . 

               . 

        this.formatoptions = { type: 'dateTime', format: 'M/d/y hh:mm a' } 

} 

Let us know if you have any concerns. 

Regards, 

Sujith R 

[Yasas Petangoda]

Hi Sujith,

Thanks for the prompt reply. Even though I need to consider date + time internally(including sorting), time does not need to be showed to the user on both filter popup choices and column data(client requirement). With the provided solution, date + time is shown on both filter popup and the column itself. Is there a way to override how distinct is applied to the filter pop choices so that I can strip out repeated items.

Thanks & Regards,

Yasas

[Sujith Kumar Rajkumar]

Hi Yasas, 

The filtering operation is performed based on the data present in the underlying data source as explained in our previous update. If we modify this checkbox filter data with custom data source then it will be displayed in the filter check box but the filtering operation will not be performed properly as it will not return the proper result when comparing this custom data with the json data present. That is why we suggested you to either remove time value from the json data or set format to display both date and time. 

Let us know if you have any concerns. 

  

Regards, 

Sujith R 

[Yasas Petangoda]

Hi Sujith,

I achieved the required behavior using the following approach. It did not affect any other operations as I am temporally cloning original data. It may not be the best. But does the trick :)

  actionBegin(args) { 

    if(args.requestType == 'filterbeforeopen'){

      

      args.filterModel.options.height = 325

      //Below code removes repeated entries from date type filter popup choices

      if(args.filterModel.options.field == "createdDatetime"){

        let users = JSON.parse(JSON.stringify(this.userListData));

        users.forEach(x => {

          if(!isNullOrUndefined(x.createdDatetime)){

            let createdDatetime = new Date(x.createdDatetime)

            createdDatetime.setHours(0,0,0)

            x.createdDatetime = createdDatetime

          }

        });   

        args.filterModel.options.dataSource = users;

      }

    }

    if (args.requestType == "filtersearchbegin") { 

      args.operator = 'contains';

    }

    if(args.requestType == "filterchoicerequest"){

      args.filterChoiceCount = 1000000

    } 

  }

 Thanks for the support!

Regards,

Yasas

[Sujith Kumar Rajkumar]

Hi Yasas, 

We are glad to hear that you have achieved your required behavior. This is the approach we mentioned in our previous update by dynamically changing the filter data source. So please ensure if it is working properly for your cases. 

Please get back to us if you require further assistance. 

  

Regards, 

Sujith R