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

1 Answer 19 Views
Grid
Marianne
Top achievements
Rank 1
Marianne asked on 07 May 2022, 06:39 PM | edited on 07 May 2022, 06:41 PM

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!

1 Answer, 1 is accepted

Sort by
0
Aleksandar
Telerik team
answered on 11 May 2022, 11:47 AM

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

Love the Telerik and Kendo UI products and believe more people should try them? Invite a fellow developer to become a Progress customer and each of you can get a $50 Amazon gift voucher.

Marianne
Top achievements
Rank 1
commented on 11 May 2022, 01:29 PM

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
Top achievements
Rank 1
commented on 11 May 2022, 02:41 PM

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
Telerik team
commented on 16 May 2022, 05:40 AM

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>

 

Tags
Grid
Asked by
Marianne
Top achievements
Rank 1
Answers by
Aleksandar
Telerik team
Share this question
or