Excel filter search value formating

[Unknown]

Hello,

it there a possibility to some things with Excel filter:

1. If i have column type as number - allow user to enter only number values in search box? (including decimal places by format)

2. If I have column type as date or datetime - allow to enter only date (or datetime) values by format (some pattern on sth like that) by current locale? For example to show user template like ____-__-__ and allow to enter only numbers to know that we always get valid date format. (as I see filter is trying to get data only if valid format is entered in search box so I need to know when this is happening because I need to do some additional actions on this action also)

I am using remote data in my grid.

[Hariharan J V]

Hi AC, 

Thanks for contacting Syncfusion support. 

We have analyzed your query. Before proceeding your query, Could you please confirm that in the below screenshots which input box do you want to customize the value, 

1)Search box input 

 

2) Excel filter Dialog inputs 

 

It will help us to provide a better solution as soon as possible. 

Regards, 

Hariharan 

[Unknown]

Hello,

I have column type as number and want to allow insert only number values:

And other query I have type as date (or dateTime) and want to allow insert (know when it is inserted) date:

[Unknown]

Hello,

it there any solution for this request?

[Hariharan J V]

Hi AC, 

Thanks for your patience. 

We are sorry for the delay. we can achieve this requirement by using actionComplete event of the Grid by using requestType as ‘filterafteropen’. Using the event we can render the custom components based on columnType instead of textbox. In our sample we have rendered custom components for number and date column. Please find the below code snippet and sample for your reference. 

App.component.html 

   <ejs-grid #grid [dataSource]='data' allowPaging='true' allowFiltering='true' (actionComplete)="complete($event)" [pageSettings]='pageSettings' [filterSettings]='filterSettings'> 

        <e-columns> 

. . .  

    </ejs-grid> 

 

App.component.ts 

export class AppComponent { 

     . . . 

    @ViewChild('grid', {static: true}) 

    public grid: GridComponent; 

    ngOnInit(): void { 

        this.data = orderDataSource; 

        this.dateMask = new MaskedTextBox({    //initializing the maskedinputbox 

        width: 200, 

        cssClass:'e-maskalign', 

        mask: '[0-3][0-9]/[0-1][0-9]/[0-3][0-9][0-9][0-9]', 

        }); 

        this.numericBox = new NumericTextBox( {    //initializing the numericbox 

          min: 0, 

          max: 20000, 

          format: '#', 

          cssClass:'e-numericalign', 

          width:210, 

        }); 

        this.pageSettings = { pageCount: 5 }; 

        this.filterSettings = { type: 'Excel' }; 

    } 

     

complete (args) { 

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

if (args.columnType === 'number' && args.filterModel && !args.filterModel.dlg.querySelector('.e-numericalign')) { 

this.filterModel = args.filterModel;   //rendering masked textbox for number column 

args.filterModel.sInput.parentElement.remove(); 

var numericInputElement = document.createElement('INPUT'); 

args.filterModel.dlg.querySelector('.e-contextmenu-wrapper').appendChild(numericInputElement); 

this.numericBox.appendTo(numericInputElement); 

this.numericBox.element.classList.add('e-searchinput'); 

this.numericBox.element.onkeyup = () => this.filterModel.refreshCheckboxes();  //refreshing the checkboxes for each ketstroke 

args.filterModel.searchBox = this.numericBox.element.parentElement; 

args.filterModel.sInput = this.numericBox.element; 

} 

if (args.columnType == 'date' && args.filterModel && !args.filterModel.dlg.querySelector('.e-maskedtextbox') ) { 

  this.filterModel = args.filterModel;          //rendering masked textbox for datecolumn 

args.filterModel.sInput.parentElement.remove(); 

var dateInputEle = document.createElement('INPUT'); 

args.filterModel.dlg.querySelector('.e-contextmenu-wrapper').appendChild(dateInputEle); 

this.dateMask.appendTo(dateInputEle); 

this.dateMask.element.classList.add('e-searchinput'); 

args.filterModel.searchBox = this.dateMask.element.parentElement; 

args.filterModel.sInput = this.dateMask.element; 

this.dateMask.change = () => this.filterModel.refreshCheckboxes();     

//refreshing the checkboxes for each ketstroke 

} 

} 

    } 

} 

Sample: https://stackblitz.com/edit/angular-f145174-custom-sample-z2xskb?file=app.component.ts 

Please get back to us, if you need further assistance. 

Regards, 

Hariharan 

[Unknown]

Hello,

in this case I can see numeric text field and maskedit field where I need but I also need to add additional parameters when I am typing in these boxes but now if I am using binding onkeyup event - the query are not sent to webAPI (if I am doing it without template for search box - the data is sent correctly to WebAPI on typing in searchbox). Also is there a possiblity to work with there templates like with normal search box? For example when I am open filter I do not want to see previous inserted value and I need clear button for there templates also to allow user to empty search box.

 I am adding my example here with trying to add additional parameters in request on search: https://stackblitz.com/edit/angular-f145174-custom-sample-h6f7c8?file=app.component.ts

[Hariharan J V]

Hi AC, 

Thanks for your update. 

we have validated your requirements. While validating your sample we found that you have overridden the onkeyup event for numeric textbox. This is the cause of the issue. To avoid overriding of the onKeyup event and send addParams we have modified the code with oninput event for numerictextBox and onkeyup event for maskedTextBox. 

Also you can show the clear button by enabling ShowClearButton property for both components also you can clear the previous inserted value as the below way. 

Please find the below code snippet and sample for your reference. 

export class AppComponent { 

   . . . 

  ngOnInit(): void { 

    this.data = new DataManager({ url: SERVICE_URI + 'api/Orders', adaptor: new WebApiAdaptor }); 

    this.pageSettings = { pageCount: 3 }; 

    this.dateMask = new MaskedTextBox({ 

      width: 200, 

      created: ()=>{this.dateMask.value = null}, 

      showClearButton: true, 

      cssClass: 'e-maskalign', 

      mask: '[0-3][0-9]/[0-1][0-9]/[0-3][0-9][0-9][0-9]', 

    }); 

    this.numericBox = new NumericTextBox({ 

      min: 0, 

      max: 20000, 

      showClearButton: true, 

      created: ()=>{this.numericBox.value = null}, 

      format: '#', 

      cssClass: 'e-numericalign', 

      width: 210, 

    }); 

    this.pageSettings = { pageCount: 5 }; 

    this.filterSettings = { type: 'Excel' }; 

  } 

  complete(args) { 

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

      if (args.columnType === 'number' && args.filterModel && !args.filterModel.dlg.querySelector('.e-numericalign')) { 

        this.filterModel = args.filterModel; 

        args.filterModel.sInput.parentElement.remove(); 

        var numericInputElement = document.createElement('INPUT'); 

        args.filterModel.dlg.querySelector('.e-contextmenu-wrapper').appendChild(numericInputElement); 

        this.numericBox.appendTo(numericInputElement); 

        this.numericBox.element.classList.add('e-searchinput'); 

        this.numericBox.element.onkeyup = () => this.filterModel.refreshCheckboxes(); 

        this.numericBox.element.oninput = this.addFilterParamsOnKeyUp.bind(this, args);   // binding oninput event for numericBox 

        args.filterModel.searchBox = this.numericBox.element.parentElement; 

        args.filterModel.sInput = this.numericBox.element; 

      } 

      if (args.columnType == 'date' && args.filterModel && !args.filterModel.dlg.querySelector('.e-maskedtextbox')) { 

        this.filterModel = args.filterModel; 

        args.filterModel.sInput.parentElement.remove(); 

        var dateInputEle = document.createElement('INPUT'); 

        args.filterModel.dlg.querySelector('.e-contextmenu-wrapper').appendChild(dateInputEle); 

        this.dateMask.appendTo(dateInputEle); 

        this.dateMask.element.classList.add('e-searchinput'); 

        args.filterModel.searchBox = this.dateMask.element.parentElement; 

        args.filterModel.sInput = this.dateMask.element; 

        this.dateMask.change = (args) => {this.filterModel.refreshCheckboxes()}; 

        this.dateMask.element.onkeyup = this.addFilterParamsOnKeyUp.bind(this, args);   // binding onkeyup event for maskedBox 

      } 

    } 

  } 

  public addFilterParamsOnKeyUp(args, args2) { 

      this.grid.query.addParams("where" + args2.currentTarget.value, args2.currentTarget.value);  //here you can add params as per your requirement 

   console.log(this.grid.query) 

  } 

} 

Documentation: https://ej2.syncfusion.com/angular/documentation/api/numerictextbox#showclearbutton 

                             https://ej2.syncfusion.com/angular/documentation/api/maskedtextbox#showclearbutton 

Sample: https://stackblitz.com/edit/angular-f145174-custom-sample-addparams?file=app.component.ts 

Please get back to us, if you need further assistance. 

Regards, 

Hariharan 

[Unknown]

Hello,

now I have one more problem - I can see clear button but after clicking it only text clears but I am not getting additional request to WebAPI to know that I need to show all data.

[Thavasianand Sankaranarayanan]

Hi AC, 

Thanks for your update. 

We could see that while using  clear button there is no network request to server side. Since we have customized the textbox with custom components we suggest you to use the below way to achieve your requirement. We have used change event of the numericTextBox and maskedInput box to achieve your requirement. 

Please find the below code snippet and sample for more information. 

ngOnInit(): void { 

    this.data = new DataManager({ url: SERVICE_URI + 'api/Orders', adaptor: new WebApiAdaptor }); 

    this.pageSettings = { pageCount: 3 }; 

    this.dateMask = new MaskedTextBox({ 

      width: 200, 

      change : (args) => {    

          if (args.value === '' && args.isInteracted) { 

            this.dateMask.element.value = ''; 

            this.isRefreshMask = true;   //refreshing the checkbox based on while clearing 

          } 

        this.filterModel.refreshCheckboxes(); 

          if (this.isRefreshMask) { 

               this.dateMask.refresh(); 

               this.isRefreshMask = false; 

             } 

      }, 

      created: ()=>{this.dateMask.value = null}, 

      showClearButton: true, 

      cssClass: 'e-maskalign', 

      mask: '[0-3][0-9]/[0-1][0-9]/[0-3][0-9][0-9][0-9]', 

    }); 

    this.numericBox = new NumericTextBox({ 

      min: 0, 

      max: 20000, 

      showClearButton: true, 

      change : (args) => { 

          if (args.value === null && args.isInteracted) { 

             this.filterModel.refreshCheckboxes();//refreshing the checkbox based on while clearing 

          } 

      }, 

      created: ()=>{this.numericBox.value = null}, 

      format: '#', 

      cssClass: 'e-numericalign', 

      width: 210, 

    }); 

Sample: https://stackblitz.com/edit/angular-f145174-custom-addparam?file=app.component.ts 

Regards, 

Thavasianand S.