Multiple Multiselect Filters for Grid

[Aaron]

I'm trying to filter my grid using multiple multiselect controls in a toolbar template. They each seem to work separately, sort of, but not completely together as I would like. I know it's just a matter of getting the filtering logic written correctly, but I've been at it for days now and can't seem to get it working 100%. The grid is running in Row filtering mode.

I want to filter multiple columns based on multiple selections.

For simplicity sake, I'll break it down like this:

I have a Species column that might contain: Kokanee, Rainbow Trout, Salmon, Steelhead, Walleye

I have an Area Type column that might contain: Freshwater Lake, River, Marine, Secret

I can get it to filter by a single species, or by a single area type, as the records in the grid can only be of one type, and have one species.

What I want to filter by looks like this: (Kokanee OR Salmon) AND (Freshwater Lake OR River)

I would want to be able to show both of those species, in either of those area types.

My Multiselects look like this:
                    @(Html.Kendo().MultiSelect()
                        .Name("areaTypeFilter")
                        .Placeholder("All Area Types")
                        .DataTextField("AreaTypeName")
                        .DataValueField("AreaTypeID")
                        .AutoBind(true)
                        .AutoClose(false)
                        .AutoWidth(true)
                        .Events(e => e.Change("filterChange"))
                        .DataSource(ds =>
                        {
                            ds.Read("ToolbarTemplate_AreaType", "Reports");

                        })
                    )

My filtering function:

        function filterChange() {
            var filters = [];
            var grid = $("#Grid").data("kendoGrid");

            if ($("#areaTypeFilter").val()) {
                $.each($("#areaTypeFilter").val(), function (key, id) {
                    filters.push({ field: "AreaTypeID", operator: "eq", value: id });
                });
            }
            if ($("#stateFilter").val()) {
                //filter = [];
                $.each($("#stateFilter").val(), function (key, id) {
                    filters.push({ field: "StateID", operator: "eq", value: id });
                });
            }
            if ($("#countyFilter").val()) {
                $.each($("#countyFilter").val(), function (key, id) {
                    filters.push({ field: "CountyID", operator: "eq", value: id });
                });
            }
            if ($("#speciesFilter").val()) {
                //filter = [];
                $.each($("#speciesFilter").val(), function (key, id) {
                    filters.push({ field: "SpeciesID", operator: "eq", value: id });
                });
            }
            grid.dataSource.filter({ logic: "and", filters: filters });
        }

 

I've tried changing the logic to OR from AND and that's definitely not the issue. Any help would be appreciated.

[Aaron]

I stopped thinking about this for a little bit and that's usually when then solution presents itself. I manually constructed what I think is a filter solution that works the way I want. The results seem to look correct.. can someone else take a look and maybe verify this is what I was describing above? Perhaps lend a hand constructing the loops to create this in code?

 

        function filterChange() {
            var filters = [];
            var grid = $("#Grid").data("kendoGrid");

            filters = [{
                logic: "and",
                filters: [
                    {
                        logic: "or",
                        filters: [
                            { field: "AreaTypeID", operator: "eq", value: 1 },
                            { field: "AreaTypeID", operator: "eq", value: 3 }
                        ]
                    },
                    {
                        logic: "or",
                        filters: [
                            { field: "SpeciesID", operator: "eq", value: 58 }, // Walleye
                            { field: "SpeciesID", operator: "eq", value: 53 }  // Steelhead
                        ]
                    }
                ]
            }];

            grid.dataSource.filter({ logic: "and", filters: filters });
        }

[Aaron]

I got it all worked out myself. I'll post my filter function in case anyone else finds it useful.

As a reminder, the logic I was going for was:

(A=1 or A=3 or A=6) and (F=7 or F=2 or F=9) and (X=12 or X=23 or X=19)

function filterChange() {
    var filters = [];
    var filter = [];
    var grid = $("#Grid").data("kendoGrid");

    if ($("#areaTypeFilter").val()) {
        $.each($("#areaTypeFilter").val(), function (key, id) {
            filter.push({ field: "AreaTypeID", operator: "eq", value: id });
        });
        filters.push({ logic: "or", filters: filter });
    }

    if ($("#stateFilter").val()) {
        filter = [];
        $.each($("#stateFilter").val(), function (key, id) {
            filter.push({ field: "StateID", operator: "eq", value: id });
        });
        filters.push({ logic: "or", filters: filter });
    }

    if ($("#countyFilter").val()) {
        filter = [];
        $.each($("#countyFilter").val(), function (key, id) {
            filter.push({ field: "CountyID", operator: "eq", value: id });
        });
        filters.push({ logic: "or", filters: filter });
    }

    grid.dataSource.filter({ logic: "and", filters: filters });
}

[Patrick | Technical Support Engineer, Senior]

Hi Aaron,

Thank you very much for sharing your approach to the community, and I'm glad you were able to resolve your issue.  

Regards,
Patrick
Progress Telerik