Complex filter for grid

[Bernd Schuhmacher]

Hi

Is it possible to define a more complex filter in Grid.
For example I would like to show all rows containing a column that starts with "BB" or "DD" but does not contain "CC"?

Example:

Column 1	Column 2	Column 3
123	AAbbbCCC	1
456	BBCCC	2
789	BBAADD	3
012	DDBB	4
345	DDCC	5
678	BBDDCC	6
901	BBAADD	7

Now I want wo set a filter for column 2 like descrbed above. I would like to get a grid lie this:

Column 1	Column 2	Column 3
789	BBAADD	3
012	DDBB	4
901	BBAADD	7

Is there a possibility to achive this?

[Venkatesh Ayothi Raman]

Hi Bernd, 

Thanks for contacting Syncfusion support. 

We don’t have support for filter operator like notcontains for string column. In Grid has filter operators for string and numbers columns like as follows, 

1)      Equal 

2)      notEqual 

3)      contains 

4)      startswith 

5)      endsWith 

6)      greaterthan 

7)      lesserthan 

8)      greaterthanOrEqual 

9)      lesserthanOrEqual 

So, we have achieved your requirement by customizing the filter operators externally and perform the filtering in “notcontains” filter operators by using fitlercolumn method. But we need to initially define the filteroperator in create event of grid like as follows, 

Code example: 

@Grid 

<input type="button" value="filter" ej-button id="button"  (click)="buttonClick($event)" /> 

<ej-grid #grid [dataSource]="gridData" [allowPaging]="true" (create)="create($event)" [allowFiltering]="true" [filterSettings]="filter" > 

     

</ej-grid> 

 

@Create event 

 

create(e: any) { 

        //create event in Grid 

        var isNull = function (val) { 

            return val === undefined || val === null; 

        }; 

        var toLowerCase = function (val) { 

            return val ? val.toLowerCase ? val.toLowerCase() : val.toString().toLowerCase() : (val === 0 || val === false) ? val.toString() : ""; 

        }; 

        var b = { 

            //set the customized filter operator 

            notcontains: function (actual, expected, ignoreCase) { 

                if (ignoreCase) 

                    return !(!isNull(actual) && !isNull(expected) && toLowerCase(actual).indexOf(toLowerCase(expected)) != -1); 

 

                return !(!isNull(actual) && !isNull(expected) && actual.toString().indexOf(expected) != -1); 

            } 

        } 

 

var a = $.extend(true, ej.data.fnOperators, b);// add the customized filter operator to default filters 

    } 

Screenshot 1: “Select the  operators in custom filter dialog on Excel filter” 

 

Screenshot 2:” output” 

 

Note: we have enabled the excel filter in grid. Please refer to the following help documentation for excel filter. 

https://help.syncfusion.com/angular-2/grid/filter#excel-like-filter 

While we click the button then we have filtered the specific columns by notcontains operator in Grid using filterColumn method. Please refer to the following code example and sample, 

Code example: 

@button click event 

buttonClick(e: any) { 

 

        this.Grid.widget.filterColumn("Column2", "notcontains", "CC"); //filter the column based on customized operator 

    } 

Output: 

 

We have created a sample based on your requirement which can be download from following link, 

Sample:http://www.syncfusion.com/downloads/support/directtrac/general/ze/ejGrid_exporting297893600 

 Regards, 

Venkatesh Ayothiraman. 

[Bernd Schuhmacher]

Hi

Thanks for looking into my request.

Unfortunately it seems the problem is not solved. As soon as I click the button "filter" the other filters are reset and so only the "notcontains" filter works as it seems. As soon as I add another { Column1: 129, Column2: "AAbbbBBB", Column3: 1 } this row is shown to. SO the startsWith "BB" or "DD" filter seems not to work in that moment.

Can I add more than one filter for the same column with the filterColumn Method. It seems the EXCEL style filter is able to do this ...

Regards

Bernd

[Venkatesh Ayothi Raman]

Hi Bernd, 

Thanks for the update. 

Yes, filtered column method removes the previous filter objects while filtering the same column using filterColumn API. So we suggest you to push the new filter objects in filteredColumn arrays and then refresh the grid content by calling the refreshContent api. Please refer to the following code example and Help documentation for more information, 

Code example: 

@Grid 

<input type="button" value="filter" ej-button id="button"  (click)="buttonClick($event)" /> 

<ej-grid #grid [dataSource]="gridData" [allowPaging]="true" (create)="create($event)" [allowFiltering]="true" [filterSettings]="filter" > 

     

</ej-grid> 

@button click event 

buttonClick(e: any) { 

 

         this.Grid.widget.model.filterSettings.filteredColumns.push({ field: "Column2", operator: "notcontains", value: "CC", predicate: "and" }) //filter the column based on customized operator 

        this.Grid.widget.refreshContent(); //refresh the grid 

    } 

 

 

Note: In above code example, we can add more than one filter for same column. 

Help documentation: 

1)      https://help.syncfusion.com/api/js/ejgrid#methods:filtercolumn 

2)      https://help.syncfusion.com/api/js/ejgrid#methods:refreshcontent 

We have also prepared a sample for your convenience which can be download from following link, 

Sample: http://www.syncfusion.com/downloads/support/directtrac/general/ze/ejGrid_exporting_(2)-1574985294 

Regards, 

Venkatesh Ayothiraman. 

[Bernd Schuhmacher]

Hi

Great. This looks like it is exactly what I was looking for.

Thanks.

[Venkatesh Ayothi Raman]

Hi Bernd, 

Thanks for feedback. 

We are glad to know your requirement is achieved. 

Thanks, 

Venkatesh Ayothiraman.