Use Custom Filter for ForeignKey Column in Grid

Environment

Product

Progress® Kendo UI® Grid for jQuery

Description

How can I use a custom filter over a foreign key column in the Grid?

Solution

By default, the Grid internally matches the values collection in the column with the corresponding value field from the dataItem and creates a collection with the unique values for the filtering of the foreignKey column.

Apply custom filtering and manually handle the matching and the creation of the unique values collection.

       <script src="https://demos.telerik.com/kendo-ui/content/shared/js/products.js" type="text/javascript"></script>
        <div id="example">
            <div id="grid"></div>

            <script>
                var categories = [{
                    "value": 1,
                    "text": "Beverages"
                },{
                    "value": 2,
                    "text": "Condiments"
                },{
                    "value": 3,
                    "text": "Confections"
                },{
                    "value": 4,
                    "text": "Dairy Products"
                },{
                    "value": 5,
                    "text": "Grains/Cereals"
                },{
                    "value": 6,
                    "text": "Meat/Poultry"
                },{
                    "value": 7,
                    "text": "Produce"
                },{
                    "value": 8,
                    "text": "Seafood"
                }];

var filterDataSource = new kendo.data.DataSource({});

    function onFilterMenuInit(e) {
        var grid = e.sender;
        e.container.data("kendoPopup").bind("open", function () {
            filterDataSource.sort({ field: e.field, dir: "asc" });
            var uniqueDsResult = removeDuplicates(grid.dataSource.view(), e.field);
            filterDataSource.data(uniqueDsResult);
          if(e.field == "CategoryID"){
            var popup = e.container.data("kendoPopup").element;
            var checkboxes = popup.find("input[type='checkbox']");

            checkboxes.each(function(){
              var checkbox = $(this);              
              if(!checkbox.hasClass("k-check-all")){
                var categoryID = checkbox.val();
                categories.forEach(function(el){
                  if(el.value == categoryID){
                        checkbox.parent().contents().last()[0].textContent = el.text;   
                  }
                })                 
             }
            })
          }
        })

        function removeDuplicates(items, field) {
            var getter = function (item) { return item[field] },
                result = [],
                index = 0,
                seen = {};

            while (index < items.length) {
                var item = items[index++],
                    text = getter(item);

                if (text !== undefined && text !== null && !seen.hasOwnProperty(text)) {
                    result.push(item);
                    seen[text] = true;
                }
            }
            return result;
        }
    }

        function onChange(e) {
        filterDataSource.data(e.items);
    }

                $(document).ready(function () {
                    var dataSource = new kendo.data.DataSource({
                        pageSize: 20,
                        data: products,
                        autoSync: true,
                      change: onChange,
                        schema: {
                            model: {
                                id: "ProductID",
                                fields: {
                                    ProductID: { editable: false, nullable: true },
                                    ProductName: { validation: { required: true} },
                                    CategoryID: { field: "CategoryID", type: "number", defaultValue: 1 },
                                    UnitPrice: { type: "number", validation: { required: true, min: 1} }
                                }
                            }
                        }
                    });


                    $("#grid").kendoGrid({
                        dataSource: dataSource,
                        filterable: true,
                      filterMenuInit: onFilterMenuInit,
                        groupable: true,
                        pageable: true,
                        height: 540,
                        toolbar: ["create"],
                        columns: [
                            { field: "ProductName", title: "Product Name", filterable: { multi: true, dataSource: filterDataSource } },
                            { field: "CategoryID", width: "200px", values: categories, title: "Category", filterable: { multi: true, dataSource: filterDataSource } },
                            { field: "UnitPrice", title: "Unit Price", format: "{0:c}", width: "200px", filterable: { multi: true, dataSource: filterDataSource } },
                            { command: "destroy", title: " ", width: "150px", filterable: { multi: true, dataSource: filterDataSource }}],
                        editable: true
                    });



                });


            </script>
        </div>