We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date
close icon

Excel filter search value formating

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.

8 Replies

HJ Hariharan J V Syncfusion Team June 13, 2019 08:52 AM UTC

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 



UN Unknown Syncfusion Team June 13, 2019 10:30 AM UTC

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:







UN Unknown Syncfusion Team June 18, 2019 10:26 AM UTC

Hello,

it there any solution for this request?


HJ Hariharan J V Syncfusion Team June 24, 2019 10:47 AM UTC

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 
    } 



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

Regards, 
Hariharan 



UN Unknown Syncfusion Team June 26, 2019 11:40 AM UTC

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


HJ Hariharan J V Syncfusion Team July 2, 2019 11:06 AM UTC

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) 
  } 



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


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


Regards, 
Hariharan 



UN Unknown Syncfusion Team July 22, 2019 08:54 PM UTC

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.


TS Thavasianand Sankaranarayanan Syncfusion Team July 25, 2019 08:44 AM UTC

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, 
    }); 



Regards, 
Thavasianand S. 


Loader.
Live Chat Icon For mobile
Up arrow icon