What type of filter conditions can be used in a custom filter

[Marianne]

Hello,

I am working on an application that the client has just added additional filter criteria beyond what grid filter provides.

I believe I might be able to do this using the example here Create Filter Widgets with Multiple Filter Criteria | Kendo UI Grid for jQuery | Kendo UI for jQuery (telerik.com) - maybe. I'm using ASP.Net Core 5 Razor pages so sometimes examples don't always work.

All of the fields (except for a couple that are date fields) are char or text type columns in the database.  The client wants to be able to use functions like "greater than", "in",  and "Is one of".  (I know greater than is not going to give them what they think) but the In condition or the Is One of condition they say are needed.

The filter now for 2 items is not enough.

For example, they need to filter for several POs at once - as you might do with a sql query SELECT * FROM tableaname WHERE fieldname in ('po1', 'po1', 'po3', 'po4') as an example.  none of the data is sequential so the need for "IN" or "IS ONE OF" filtering conditions.

Is this possible?

How would it be coded and handled on the backend?

Are the available filters documented somewhere here on the site?

Can we use wild cards in the filter value?  As when the search condition may vary inside the string and not with the start or end with characters?

thanks!

[Marianne]

Hi Mihaela,

Yes that was my question in the other thread and your suggestion is what I needed.

For anyone interested, this is the finished razor page.  It has functions to get the filter operators and data to pass to a backend function (we need to manipulate the filtered data to generate a report that is sent to our customers), custom filtering to use an "IN" type SQL server condition on one column and a custom menu for same column.

For anyone that is interested, based on Mihaela's suggestion, this is how we are adding a custom filter menu to grid using a GridColumnSettings.  This application also gets and passes the filters and data to a backend function that creates and downloads a custom report for our external customers.  

@page
@model TestData.Pages.CreateReportModel
@{
    ViewData["Title"] = "Create Test Data Report";
}
@using TestData.Models
@using Kendo.Mvc.UI
@inject Microsoft.AspNetCore.Antiforgery.IAntiforgery Xsrf
@Html.AntiForgeryToken()

<h1>HPK Data - Create Report</h1>
<p><span style="font-weight: bold;color: red;">@Model.message</span></p>
<p>Use filtering and sorting to locate information - click button when done.</p>
<p>
<form method="post">
    <input type="hidden" id="export-data" name="data" />
    <input type="hidden" id="export-model" name="model" />
    <input type="hidden" id="export-filter" name="filter" />
    <input type="submit" asp-page-handler="ExportServer" class="k-button k-button-increase k-button-solid-primary k-button-solid k-button-md k-rounded-md download" data-format="csv" data-title="CSVTestDataReport" value="&nbsp;&nbsp;&nbsp;&nbsp;Export to CSV&nbsp;&nbsp;&nbsp;&nbsp;" />
 </form>
</p>
<div id="griddiv">
    @(Html.Kendo().Grid<HPK_DATA>(Model.testdata)    
    .Name("grid")
    .Columns(columns => {
       columns.LoadSettings(Model.columns);
    })
    .Events(ev=>{
        ev.Filter("onFilter");
        //ev.FilterMenuInit("filterMenuInit");
    })
    .Pageable()
    .Sortable()
    .Scrollable()
    .Resizable()
    .Filterable()
    .Resizable(r => r.Columns(true))
    .DataSource(dataSource => dataSource
        .Ajax()
        .PageSize(20)
        .Read(read => read.Url(Url.Action()+"?handler=Read").Data("forgeryToken"))
     )
)
</div>


<script>
     var escapeQuoteRegExp = /'/ig;

        $(document).ready(function () {
        var grid = $("#grid").data("kendoGrid"); // Get an instance of the Grid
        var gridColumns = grid.columns; //get the current grid columns options
    gridColumns[1].filterable = { //set the "filterable" options of the 3rd column "SERIAL_NO"
            extra: false,
            operators: {

//specify the operators that should be displayed in the column filter menu. Here you can review the available operators based on the field data type (date, number, string, enum): https://docs.telerik.com/kendo-ui/api/javascript/ui/grid/configuration/filterable.operators
                string: {
                    contains: "Contains",
                    eq: "Equal to",
                    startswith: "Starts With",
                    endswith: "Ends With",
                    neq: "Not Equal to",
                    doesnotcontain: "Doesn't contain",
                    doesnotstartwith: "Does not start with",
                    doesnotendwith: "Does not end with",

                }
            }
        };
        grid.setOptions({ //update the grid column settings
            columns: gridColumns
        });
    });

 function encodeFilterValue(value, encode) {
        if (typeof value === "string") {
            if (value.indexOf('Date(') > -1) {
                value = new Date(parseInt(value.replace(/^\/Date\((.*?)\)\/$/, '$1'), 10));
            } else {
                value = value.replace(escapeQuoteRegExp, "''");

                if (encode) {
                    value = encodeURIComponent(value);
                }

                return "'" + value + "'";
            }
        }

        if (value && value.getTime) {
            return "datetime'" + kendo.format("{0:yyyy-MM-ddTHH-mm-ss}", value) + "'";
        }
        return value;
    }

    function serializeFilter(filter, encode) {
    if (filter.filters) {
            return $.map(filter.filters, function (f) {
                var hasChildren = f.filters && f.filters.length > 1,
                    result = serializeFilter(f, encode);

                if (result && hasChildren) {
                    result = "(" + result + ")";
                }

                return result;
            }).join("~" + filter.logic + "~");
        }

        if (filter.field) {
            return filter.field + "~" + filter.operator + "~" + encodeFilterValue(filter.value, encode);
        } else {
            return undefined;
        }
    }

   function onFilter(e){
  if(e.filter === null) {
     e.sender.dataSource.filter();
     //this will clear all
      //e.preventDefault()
      //e.sender.dataSource.filter({})
       } else {      
       //come here is not null and then if field is serial no
        if(e.field == "SERIAL_NO"){
     var operator = e.filter.filters.filter(x=>x.field == "SERIAL_NO")[0].operator;
     var searchcondition = (e.filter.filters.filter(x=>x.field == "SERIAL_NO")[0].value);
            var isComma = searchcondition.includes(",");
             e.preventDefault()
             e.filter.logic = "or";
            if(isComma) {
                const conditions = searchcondition.split(",");
                for(i = 0; i<conditions.length; i++) {
                    var c = conditions[i].trim();                    
           e.filter.filters.push({field:"SERIAL_NO",operator:""+operator+"",value: c})
                }

            } else {
                 e.filter.filters.push({field:"SERIAL_NO",operator:""+operator+"",value: e.filter.filters.filter(x=>x.field == "SERIAL_NO")[0].value})
            }

            e.sender.dataSource.filter(e.filter);
        }
               }  //else e.filter is not null
    }

    $(".download").click(function () {
        var grid = $("#grid").data("kendoGrid");
        var options = {
            format: $(this).data("format"),
            title: "TestData"
        }

        $("#export-data").val(encodeURIComponent(JSON.stringify(options)));
        $("#export-model").val(encodeURIComponent(JSON.stringify(grid.columns)));
        $("#export-filter").val(encodeURIComponent(serializeFilter(grid.dataSource.filter())));
    });


    function forgeryToken() {
        return kendo.antiForgeryTokens();
    }
</script>

                                     

[Mihaela]

Thank you for sharing the final solution, Marianne. It is greatly appreciated! :)

[Aleksandar]

Hello Marianne,

The available filter operators can be reviewed in the dataSource API section. The filter configuration also supports setting the logic.

From the description provided I can suggest reviewing the Filter component and in particular this Demo of the DataSource and ListView integration (you can use a Grid instead of a ListView, for example).  The example demonstrates how you can configure multiple conditions. To my knowledge the SQL IN operator is a shorthand for multiple OR conditions, though I am not sure what the "IS ONE OF" translates to.

An alternative approach can be handling the filter event and modifying the filter object based on which the dataSource will be filtered. Using wildcards for filtering is not supported. We have a Feature Request on the item and I will urge you to vote on it, if you'd like to see such functionality available in the future.

Regards,
Aleksandar
Progress Telerik

[Marianne]

Thanks Aleksandar.  I'll study those references you provided.  And I agree with you - I'm not sure what "IS ONE OF" would do either.  It sort of sound like "IN" to me so my client is going to have to explain herself more.

[Marianne]

Hello Aleksandar,

I reviewed the page references you provided & not sure how to integrate that into my Razor page.  Do I need to rewrite the entire page as Jquery?  We have some code already embedded that catches the filter conditions and passes it back to .cs when the user is requesting to build a report document to send to our customers. 

Currently, this is the page that is in progress:

CreateReport.cshtml

<h1>HPK Data - Create Report</h1>
<p><span style="font-weight: bold;color: red;">@Model.message</span></p>
<p>Use filtering and sorting to locate information - click button when done.</p>
<p>
<form method="post">
    <input type="hidden" id="export-data" name="data" />
    <input type="hidden" id="export-model" name="model" />
    <input type="hidden" id="export-filter" name="filter" />
    <input type="submit" asp-page-handler="ExportServer" class="k-button k-button-increase k-button-solid-primary k-button-solid k-button-md k-rounded-md download" data-format="csv" data-title="CSVTestDataReport" value="&nbsp;&nbsp;&nbsp;&nbsp;Export to CSV&nbsp;&nbsp;&nbsp;&nbsp;" />
 </form>
</p>
<div id="griddiv" >
    @(Html.Kendo().Grid<HPK_DATA>
    (Model.testdata).Name("grid")
    .Sortable()
    .Scrollable()
    .Filterable()
    .Resizable(r => r.Columns(true))
     .Columns(columns => columns.LoadSettings(Model.columns)
    )
    .DataSource(ds => ds.Ajax()
    .Events(events => events.Error("error_handler"))
    .ServerOperation(false)
    .Read(r => r.Url(Url.Action()+"?handler=Read").Data("forgeryToken"))
    .Model(model =>
    {
        model.Id(p => p.ROWID);
        model.Field(p => p.ITEM_NO).Editable(false).;
        model.Field(p => p.PONAME).Editable(false);
        model.Field(p => p.CUSTOMER_NO).Editable(false);
        model.Field(p => p.TITLE_CODE).Editable(false);
        model.Field(p => p.E_DATE).Editable(false);
        model.Field(p => p.SERIAL_NO).Editable(false);
        model.Field(p => p.LINE_NO).Editable(false);
        model.Field(p => p.SENT_FLAG).Editable(false);
        model.Field(p => p.HPK_FILE_DATE).Editable(false);
        model.Field(p => p.FILE_TYPE).Editable(false);
        model.Field(p => p.CREATED_BY).Editable(false);
        model.Field(p => p.CREATED_DATE).Editable(false);
        model.Field(p => p.DATA1).Editable(false);
        model.Field(p => p.DATA2).Editable(false);
        model.Field(p => p.DATA3).Editable(false);
        model.Field(p => p.DATA4).Editable(false);
        model.Field(p => p.DATA5).Editable(false);
        model.Field(p => p.DATA6).Editable(false);
        model.Field(p => p.DATA7).Editable(false);
        model.Field(p => p.DATA8).Editable(false);
        model.Field(p => p.DATA9).Editable(false);
        model.Field(p => p.DATA10).Editable(false);
        model.Field(p => p.DATA11).Editable(false);
        model.Field(p => p.DATA12).Editable(false);
        model.Field(p => p.DATA13).Editable(false);
        model.Field(p => p.DATA14).Editable(false);
        model.Field(p => p.DATA15).Editable(false);
    })
    .PageSize(100)
    )
    .Pageable()
    )
</div>


<script>
     var escapeQuoteRegExp = /'/ig;

 function encodeFilterValue(value, encode) {
        if (typeof value === "string") {
            if (value.indexOf('Date(') > -1) {
                value = new Date(parseInt(value.replace(/^\/Date\((.*?)\)\/$/, '$1'), 10));
            } else {
                value = value.replace(escapeQuoteRegExp, "''");

                if (encode) {
                    value = encodeURIComponent(value);
                }

                return "'" + value + "'";
            }
        }

        if (value && value.getTime) {
            return "datetime'" + kendo.format("{0:yyyy-MM-ddTHH-mm-ss}", value) + "'";
        }
        return value;
    }

    function serializeFilter(filter, encode) {
       // alert("in serializefilter");
        if (filter.filters) {
            return $.map(filter.filters, function (f) {
                var hasChildren = f.filters && f.filters.length > 1,
                    result = serializeFilter(f, encode);

                if (result && hasChildren) {
                    result = "(" + result + ")";
                }

                return result;
            }).join("~" + filter.logic + "~");
        }

        if (filter.field) {
         //   alert(filter.field + "~" + filter.operator + "~" + encodeFilterValue(filter.value, encode));
            return filter.field + "~" + filter.operator + "~" + encodeFilterValue(filter.value, encode);
        } else {
            return undefined;
        }
    }

    $(".download").click(function () {
        var grid = $("#grid").data("kendoGrid");
        var options = {
            format: $(this).data("format"),
            title: "TestData"
        }

        $("#export-data").val(encodeURIComponent(JSON.stringify(options)));
        $("#export-model").val(encodeURIComponent(JSON.stringify(grid.columns)));
        $("#export-filter").val(encodeURIComponent(serializeFilter(grid.dataSource.filter())));
    });

    function forgeryToken() {
        return kendo.antiForgeryTokens();
    }
</script>

 

On the backend we do this is the CreateReport.cshtml.cs For brevity, I am showing only the OnPost function as there are several functions that have to create and configure the report to download.

   public ActionResult OnPostExportServer(string model, string data, string filter)
        {
            // message = "In ExportServer method";
           
            if (String.IsNullOrEmpty(filter))
            {
                message = "No filters selected";
                return Page();
            }
            else
            {
                message = "";
                var filters = FilterDescriptorFactory.Create(filter);
                tmpfilteredData = testdata.ToDataSourceResult(new DataSourceRequest() { Filters = filters, Page = 1 }).Data;
                int i = 0;
                string titlecode = string.Empty;
                string itemnumber = string.Empty;
                string serialnumber = string.Empty;
                DateTime shipdate = new DateTime();
                string customernumber = string.Empty;
                string po = string.Empty;

                foreach (HPK_DATA fd in tmpfilteredData)
                {
                    if (i == 0)
                    {
                        titlecode = fd.TITLE_CODE.ToString();
                        itemnumber = fd.ITEM_NO.ToString();
                        serialnumber = fd.SERIAL_NO.ToString();
                        po = fd.PONAME.ToString();  
                        shipdate = (DateTime)fd.HPK_FILE_DATE;
                        customernumber = fd.CUSTOMER_NO.ToString();
                        break;
                    }
                }
                DateTime rightnow = DateTime.Now;
                string reportdate = rightnow.ToLongDateString();
                  reportdate = reportdate.Replace(",", "");
                reportdate = reportdate.Replace(" ", "_");

                this.filename = "TestData_" + customernumber + "_" + itemnumber + "_" + reportdate;
                List<TITLE_PATTERN_1> tp1 = GetTitlePattern(titlecode);
                List<string> tpColumnHeaders = getTPColumnHeaders(tp1);
                string[] atpcolumnheaders = tpColumnHeaders.ToArray();
                List<string> tpColumnFooters = getTPFooters(tp1);
                string[] atpcolumnfooters = tpColumnFooters.ToArray();
                dynamic options = JsonConvert.DeserializeObject(HttpUtility.UrlDecode(data));
                string sformatprovider = options.format.ToString();
                if (sformatprovider == "csv")
                {
                    this.sformattype = "text/csv";
                    this.formatprovider = new Telerik.Windows.Documents.Spreadsheet.FormatProviders.TextBased.Csv.CsvFormatProvider();
                    this.filename = this.filename + ".csv";
                }
                else
                {
                    this.sformattype = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    this.formatprovider = new Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx.XlsxFormatProvider();
                    this.filename = this.filename + ".xlsx";
                }
                this.CreateWorkbook();
                this.SetColumnHeaders(atpcolumnheaders);
                this.SetData();
                SetFooters(atpcolumnfooters);
                this.ExportDirectory = @"./Files/ReportFiles/";
             

                this.PrepareDirectory(this.ExportDirectory, this.filename);
       
                byte[] renderedBytes = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    this.formatprovider.Export(workbook, ms);
                    renderedBytes = ms.ToArray();
                }
                return File(renderedBytes, this.sformattype, filename);
            }  
        }

 

[Aleksandar]

Hi Marianne,

No, you do not have to rewrite the page in jQuery. The references provided demonstrate how you can handle filtering on the client-side, which I see you already do in the serializeFilter function. From the snippet provided it is not clear when this function is called, but my suggestion is, for example:

• User filters PONAME column, but the requirement is to filter for that value also the CUSTOMER_NO and DATA1, columns, for example
• Use the Filter event to check if the filtered field is PONAME
• If so set the filtering logic and extend the filters

Depending on the logic required you might need to handle clearing of the applied filters. 

In this REPL I have created a sample to demonstrate the above suggestion - filtering on the ShipName column applies a filter to the ShipCity column as well.

@(Html.Kendo().Grid<Kendo.Mvc.Examples.Models.OrderViewModel>()    
    .Name("grid")
    .Events(ev=>ev.Filter("onFilter"))
...
)

<script>
    function onFilter(e){
        if(e.field == "ShipName"){
            e.preventDefault()
            e.filter.logic = "or";
            e.filter.filters.push({field:"ShipCity",operator:"contains",value: e.filter.filters.filter(x=>x.field == "ShipName")[0].value})
            e.sender.dataSource.filter(e.filter);
        }
    }
</script>

 

[Marianne]

Thank you -- this works brilliantly - We are going to tell users to enter multiple values as comma separated.  I modified the JS to check the value for ",".  then add each one to the filtering conditions.  As you know, this also preserves the filtering for backend flterfactory to create the RPL report to send to customer.  (I includes JS below.)

Except I'm having trouble coming up with a custom clear function.  I looked at a number of examples but everything I do keeps throwing error filters undefined.  I assume that we have to replace the filters and reload the datasource and that's when I get the error.  I'm trying something simple like this (note I've tried various combinations of e.filter.filters.filter which all error.)  I think I have to capture this on each filterable field, not just the custom one because once I add more than one filtered column, the clear function on the filter no longer works.

  function filterMenuInit(e) {
    e.container.on("click", "[type='reset']", function () {
       var fieldname = e.field;
       alert(fieldname);
      var newresult =  e.filters(({field}) => !field.includes(fieldname));
       //var newresult = e.filter.filters(({field}) => !field.includes(fieldname));
        e.sender.dataSource.filter();

    });

 

This is the code that creates an "IN" condition based on the sample you provided:

 function onFilter(e){
       
        if(e.field == "SERIAL_NO"){
           var searchcondition = (e.filter.filters.filter(x=>x.field == "SERIAL_NO")[0].value);
            var isComma = searchcondition.includes(",");
             e.preventDefault()
             e.filter.logic = "or";
            if(isComma) {
                const conditions = searchcondition.split(",");
                for(i = 0; i<conditions.length; i++) {
                    var c = conditions[i].trim();
                    //alert("push or " + c);
           e.filter.filters.push({field:"SERIAL_NO",operator:"contains",value: c})
                }

            } else {
                 e.filter.filters.push({field:"SERIAL_NO",operator:"contains",value: e.filter.filters.filter(x=>x.field == "SERIAL_NO")[0].value})
            }
                     e.sender.dataSource.filter(e.filter);
        }
                
    }              

[Aleksandar]

Marianne, from your next post I understand you have already resolved the issue when the filter is cleared. In case the issue is still not resolved - the Grid filter event API documents such a scenario - if the e.filter parameter is null this means the filter has been cleared, for example by click on the clear button. Note also that if you desire to clear all filters applied to the dataSource you can pass an empty object to the dataSource filter method:

<script>
    function onFilter(e){
        if(e.filter == null){
            kendo.alert("Clearing all filters")
            e.preventDefault()
            e.sender.dataSource.filter({})
        } else {
            if(e.field == "ShipName"){
                e.preventDefault()
                e.filter.logic = "or";
                e.filter.filters.push({field:"ShipCity",operator:"contains",value: e.filter.filters.filter(x=>x.field == "ShipName")[0].value})
                e.sender.dataSource.filter(e.filter);
            }
        }
    }
</script>

Here is an updated REPL.

[Marianne]

Thanks for following up!

At the moment, this seems to be working to clear one filter at a time.  The clients have not (yet) asked to clear all columns at once but I copied your code & added it to a commented section just in case that turns out to be the case. 

 if(e.filter === null) {
     e.sender.dataSource.filter();
     //this will clear all
      //e.preventDefault()
      //e.sender.dataSource.filter({})
       } else {      
           if(e.field == "SERIAL_NO"){
             var operator = e.filter.filters.filter(x=>x.field == "SERIAL_NO")[0].operator;
              var searchcondition = (e.filter.filters.filter(x=>x.field == "SERIAL_NO")[0].value);
            var isComma = searchcondition.includes(",");
             e.preventDefault()
             e.filter.logic = "or";
            if(isComma) {
                const conditions = searchcondition.split(",");
                for(i = 0; i<conditions.length; i++) {
                    var c = conditions[i].trim();                    
           e.filter.filters.push({field:"SERIAL_NO",operator:""+operator+"",value: c})
                }

            } else {
                 e.filter.filters.push({field:"SERIAL_NO",operator:""+operator+"",value: e.filter.filters.filter(x=>x.field == "SERIAL_NO")[0].value})
            }
                 e.sender.dataSource.filter(e.filter);
        }
               }  //else e.filter is not null
    }

[Marianne]

In case anyone is interested, with the help of the example provided by Aleksandar, this is the function that I got to enable an "IN" condition in a grid column for product part serial numbers that are non-sequential, text data.  Users will enter multiple search items as comma-separated.  To improve user experience, I will need to add custom filter menu to that column as the code as written will only handled multiple OR conditions where the search value is contained in target. For us this will work perfectly; just need to remove other filter options from that particular column.

(note: we are creating custom reports for our external customers from the filtered data.  The custom filter serialized to the backend razor code perfectly.)

<div id="griddiv">
    @(Html.Kendo().Grid<HPK_DATA>(Model.testdata)    
    .Name("grid")
    .Columns(columns => {
       columns.LoadSettings(Model.columns);

    })
    .Events(ev=>{
        ev.Filter("onFilter");
        //ev.FilterMenuInit("filterMenuInit");
    })
    .Pageable()
    .Sortable()
    .Scrollable()
    .Filterable()
    .DataSource(dataSource => dataSource
        .Ajax()
        .PageSize(20)
        .Read(read => read.Url(Url.Action()+"?handler=Read").Data("forgeryToken"))
     )
)
</div>

function onFilter(e){
  if(e.filter === null) {
     e.sender.dataSource.filter();
       } else {      
       //come here is not null and then if field is serial no
        if(e.field == "SERIAL_NO"){
          //  var fieldname = e.field;          
           var searchcondition = (e.filter.filters.filter(x=>x.field == "SERIAL_NO")[0].value);
            var isComma = searchcondition.includes(",");
             e.preventDefault()
             e.filter.logic = "or";
            if(isComma) {
                const conditions = searchcondition.split(",");
                for(i = 0; i<conditions.length; i++) {
                    var c = conditions[i].trim();                    
           e.filter.filters.push({field:"SERIAL_NO",operator:"contains",value: c})
                }

            } else {
                 e.filter.filters.push({field:"SERIAL_NO",operator:"contains",value: e.filter.filters.filter(x=>x.field == "SERIAL_NO")[0].value})
            }

            //e.filter.logic = "or";
            //e.filter.filters.push({field:"SERIAL_NO",operator:"contains",value: e.filter.filters.filter(x=>x.field == "SERIAL_NO")[0].value})
            e.sender.dataSource.filter(e.filter);
        }
               }  //else e.filter is not null
    }

              

[Mihaela]

Hey Marianne,

You could customize the column filter options as is suggested in the following thread:

https://www.telerik.com/forums/how-to-use-custom-filter-menu-on-one-column-when-using-gridcolumnsettings

[Marianne]

Please see my comment from 5/24 12:41PM for final solution/answer.  thanks!