Grid - Excel Style Filters - Is it Possible to "Or" filter on two columns?

[Chris]

Hi,

I have an grid with Excel style filters enabled.  Is it possible to have an "or" filter on two different columns?

For example:

I have a Foreign Key column called "Status".  It can have the following values "In Progress", "Completed", "Lost", "Withdrawn" and "Disqualified".

I have another column called "ChangedDate".  It is a datetime field and it gets updated with the current date whenever a row is updated.

Is there a way to set the filters such that the grid will show all rows where the status is "In Progress" or the ChangedDate is within the last month?

If you were writing a SQL statement it would look like this:

SELECT *

FROM mytable

WHERE UPPER(mytable.status) = 'IN PROGRESS' OR (ChangedDate >= '2018-05-04 00:00:00' AND ChangedDate <= '2018-06-04 23:59:59')

Thanks,

Chris

[Farveen Sulthana Thameeztheen Basha]

Hi Chris, 

Query #:- I have an grid with Excel style filters enabled.  Is it possible to have an "or" filter on two different columns? 

Thanks for contacting Syncfusion Support. 

We have checked your query and we have achieved filtering by using external button click. In that we have filter the columns using model.filterSettings.filterColumns and applied the combinations of filtering using and, or predicate and finally used refreshContent method to refresh the Grid. By default Filtering for Foreign key would takes place by Field value. To overcome that, we have used ForeignKeyAdaptor to filter the column based on text value. 

Please refer to the code example:- 

<button type="button" onclick="refreshData()">FIlterColumn</button> 

 

<ej-grid id="Grid" datasource="ViewBag.datasource" load="load" allow-paging="true"> 

   <e-edit-settings allow-adding="true" allow-editing="true" allow-deleting="true"></e-edit-settings> 

        

    <e-columns> 

         .   .   . 

         <e-column field="EmployeeID" header-text="Employee ID" text-align="Right"></e-column> 

    </e-columns> 

</ej-grid> 

<script type="text/javascript"> 

    var foreignData = @Html.Raw(Json.Encode(ViewBag.foreignCol)); 

 

    var foreignObj = [ 

        { 

            dataSource: foreignData, 

            foreignKeyField: "EmployeeID", //Property in the Grid's main dataSource 

            field: "EmployeeID", //Property in foreignkey dataSource 

            foreignKeyValue: "FirstName" //Property in foreignkey dataSource 

        } 

    ]; 

 

 

    function load(args){ 

        this.model.dataSource.adaptor = new ej.ForeignKeyAdaptor(foreignObj, "remoteSaveAdaptor"); 

    } 

    function refreshData(args) { 

        var gridObj = $("#FlatGrid").ejGrid('instance'); 

        var startOfFilter = new Date('2017,11,13 00:00:00'); 

        var endOfFilter = new Date('2017,11,14 00:00:00'); 

          

        gridObj.model.filterSettings.filteredColumns = [ 

           { field: "OrderDate", operator: "greaterthanorequal", predicate: "and", matchcase: true , value: startOfFilter },  

           { field: "OrderDate", operator: "lessthan", predicate: "and", matchcase: true , value: endOfFilter }, 

           { field: "EmployeeID_FirstName", operator: "equal", predicate: "or", matchcase: true, value: "Nancy" }, 

       ]; 

        gridObj.refreshContent(); 

    } 

</script> 

 

Please refer to the API link:- 

https://help.syncfusion.com/api/js/ejgrid#members:filtersettings-filteredcolumns 

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

https://help.syncfusion.com/aspnetmvc/grid/data-adaptors#foreign-key-adaptor 

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

Regards, 

Farveen sulthana T 

[Chris]

Thanks for response.  Sorry it took me so long to try it out.

I'm finding that each individual column is being filtered correctly but it is doing an "AND" filter between Column A and Column B.  I need it to be an "OR" filter between Column A and Column B.

Here is my code:

        function filterCurrent() {
            var gridObj = $("#TelecomGrid").ejGrid('instance');

            var curDate = new Date();

            var startOn = new Date(curDate.getFullYear(), curDate.getMonth(), curDate.getDate(), 0, 0, 0);
            startOn.setMonth(startOn.getMonth() - 1);           

            var endOn = new Date(curDate.getFullYear(), curDate.getMonth(), curDate.getDate(), 23, 59, 59);
           
            gridObj.model.filterSettings.filteredColumns = [
                { field: "StageChangedDate", operator: "greaterthanorequal", predicate: "and", matchcase: true, value: startOn },
                { field: "StageChangedDate", operator: "lessthanorequal", predicate: "and", matchcase: true, value: endOn },
                { field: "TelecomStatusId", operator: "equal", predicate: "or", matchcase: true, value: "2" },
            ];

            gridObj.refreshContent();

        }

I need to be able to do something like this:

            gridObj.model.filterSettings.filteredColumns = [
              ***  { field: "StageChangedDate", operator: "greaterthanorequal", predicate: "and", matchcase: true, value: startOn },
                { field: "StageChangedDate", operator: "lessthanorequal", predicate: "and", matchcase: true, value: endOn },  ***

--------------- OR --------------------------

              ***  { field: "TelecomStatusId", operator: "equal", predicate: "or", matchcase: true, value: "2" }, ***
            ];

The "StageChangedDate" column is an between filter and the "TelecomStatusId" column is an equal filter.  However I want the overall filter effect to be "StageChangedDate conditions OR TelecomStatusId condition".  Is that possible?

Thanks,

Chirs

[Farveen Sulthana Thameeztheen Basha]

Hi Chris, 

While using Excel Filter we can only perform AND function for two different Fields and we cannot perform OR function for two different Fields. Please get back to us if you need any further assistance. 

Regards, 

Farveen sulthana T 

[Chris]

Thank you.

I have submitted a feature request to have this functionality included in a future release.

Chris

[Farveen Sulthana Thameeztheen Basha]

Hi Chris, 

 

Query #:-  When using Excel Filters I would like to be able to perform an "OR" filter between two different fields/columns. I have submitted a feature request to have this functionality included in a future release. 

 

We have achieved this requirement through workaround by filtering the two different columns using or predicate. If you want to filter the columns externally, we have filtered the column using model.filterSettings.filterColumns and used or predicate for Filtering for date column. 

Please refer to the code example:- 

 

<ej-grid id="Grid" datasource="ViewBag.datasource"  allow-paging="true">  

   <e-edit-settings allow-adding="true" allow-editing="true" allow-deleting="true"></e-edit-settings>  

         

    <e-columns>  

         .   .   .  

         <e-column field="EmployeeID" header-text="Employee ID" text-align="Right"></e-column>  

    </e-columns>  

</ej-grid>  

 

    function refreshData(args) {  

        var gridObj = $("#FlatGrid").ejGrid('instance');  

        var startOfFilter = new Date('2017,11,13 00:00:00');  

        var endOfFilter = new Date('2017,11,14 00:00:00');  

           

        gridObj.model.filterSettings.filteredColumns = [  

           { field: "OrderDate", operator: "greaterthanorequal", predicate: "or", matchcase: true , value: startOfFilter },   

           { field: "OrderDate", operator: "lessthan", predicate: "or", matchcase: true , value: endOfFilter },  

           { field: "EmployeeID ", operator: "equal", predicate: "or", matchcase: true, value: "4" },  

       ];  

        gridObj.refreshContent();  

    }  

</script>  

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

Regards, 

Farveen sulthana T