odata-v4 filter foreign key column

[Greg]

When trying to filter the ProcessType column in the example below, I receive a 400 error.  If I change the field to ProcessTypeID from ProcessType the filter works but than the add/edit options do not work correctly.  I tried injecting a custom filter and received the same result.  I followed the api documentation to the GitHub Odata 4 with WebAPI controller example, downloaded and ran locally and get the same result.  Can someone point in the right direction on this?

 

<div id="grid"></div>

<script>

    $(function() {

        $("#grid").kendoGrid({
            dataSource: {
                type: "odata-v4",
                transport: {
                    read: {
                        url: "/odata/Processes",
                        data: {
                            $expand: "ProcessType"
                        }
                    },
                    update: {
                        url: function (data) {
                            return "/odata/Processes(" + data.ProcessID + ")";
                        }
                    },
                    create: {
                        url: "/odata/Processes?$expand=ProcessType"
                    }
                },
                schema: {
                    model: {
                        id: "ProcessID",
                        fields: {
                            ProcessID: { type: "number" },
                            Name: { type: "string" },
                            Description: { type: "string" },
                            ProcessTypeID: { type: "number" },
                            Enabled: { type: "boolean", defaultValue: true },
                            DDLFile: { type: "string" },
                            Type: { type: "string" },
                            Method: { type: "string" },
                            Parameter: { type: "string" },
                            ProcessType: { defaultValue: { ProcessTypeID: 1, Name: "Import" } }
                        }
                    }
                },
                requestEnd: function(e) {
                    if (e.type == "create") {
                        // Make a read request to expand Category. By default the OData controller doesn't expand on create.
                        this.read();
                    }
                },
                change: function(e) {
                    console.log(e.sender);
                },
                pageSize: 10,
                serverFiltering: true,
                serverPaging: true,
                serverSorting: true
            },
            height: "485px",
            toolbar: ["create"],
            sortable: {
                mode: "multiple",
                allowUnsort: true
            },
            filterable: {
                mode: "row"
            },
            resizable: true,
            scrollable: true,
            pageable: {
                pageSizes: [10, 25, 50],
                buttonCount: 5
            },
            columns: [
                { command: "edit", width: 200 },
                { field: "Name", width: 400 },
                { field: "Description", width: 400 },
                { field: "ProcessType", width: 300, title: "Process Type", template: "#: ProcessType ? ProcessType.Name : '' #", editor: processTypeEditor },
                { field: "Enabled", width: 170 },
                { field: "DLLFile", width: 400 },
                { field: "Type", width: 400 },
                { field: "Method", width: 400 },
                { field: "Parameter", width: 400 }
            ],
            editable: "inline",
            edit: function (e) {
                if (!e.model.isNew()) {
                    var processType = $(e.container.find('input[data-role="dropdownlist"]')).data("kendoDropDownList");
                    processType.enable(false);

                    e.container.find('input[name=Name]').attr('disabled', 'disabled').addClass('k-state-disabled');
                }
            },
            save: function (e) {
                e.model.set("ProcessTypeID", e.model.ProcessType.ProcessTypeID);
            }
        });

    });

    function processTypeEditor(container, options) {
        console.log(options);
        $('<input required data-text-field="Name" data-value-field="ProcessTypeID" data-bind="value:' + options.field + '"/>')
            .appendTo(container)
            .kendoDropDownList({
                autoBind: false,
                dataSource: {
                    type: "odata-v4",
                    transport: {
                        read: {
                            url: "/odata/ProcessTypes"
                        }
                    }
                }
            });
    }

</script>

[Greg]

Should add:

 <package id="Telerik.UI.for.AspNet.Mvc5" version="2016.1.112" targetFramework="net45" />  

 <package id="Microsoft.AspNet.OData" version="5.8.0" targetFramework="net45" />

 <package id="Microsoft.AspNet.Mvc" version="5.2.3" targetFramework="net45" />

<package id="jQuery" version="2.2.0" targetFramework="net45" />

[Greg]

bump

[Rosen]

Hello Greg,

Most probably the cause for the server error is that the field on which filtering is made is an object. Instead it should be a plain type, such as number, string, bool etc. In order to correct this you may try binding the column to the 
ProcessType.Name and modify the template to use ProcessType instead of the field name passed in via the options (which in this configuration will be the ProcessType.Name).

Something similar to the following:

{ field: "ProcessType.Name", width: 300, title: "Process Type", template: "#: ProcessType ? ProcessType.Name : '' #", editor: processTypeEditor }

$('<input required data-text-field="Name" data-value-field="ProcessTypeID" data-bind="value:ProcessType"/>')

Please give it a try and see if it helps.

Regards,
Rosen
Telerik

 

 

[Greg]

Hi Rosen,

 

The creation or updating of records is not the issue.  That works as expected.  The problem is with the filtering of data.  Using 

filterable: { mode: "row" }

causes to filter with an autocomplete.  That throws this error as I type: GET http://localhost:30139/odata/Processes?%24format=json&%24expand=ProcessType&%24top=10&%24filter=ProcessType+eq+%27Ex%27&%24count=true 400 (Bad Request) 

To get the dropdownlist effect, I changed the mode to menu and added 

function processTypeFilter(element) {
    element.kendoDropDownList({
        dataTextField: "Name",
        dataValueField: "ProcessTypeID",
        dataSource: {
            type: "odata-v4",
            transport: {
                read: {
                    url: "/odata/ProcessTypes"
                }
            }
        },
        optionLabel: "Select Type..."
    });
}

This returns this error: GET http://localhost:30139/odata/Processes?%24format=json&%24expand=ProcessType&%24top=10&%24filter=ProcessType+eq+2&%24count=true 400 (Bad Request)

So for create/update to work, I need this: 

{ field: "ProcessType", width: 300, title: "Process Type", template: "#: ProcessType ? ProcessType.Name : '' #", editor: processTypeEditor, filterable: { ui: processTypeFilter } },

 To get the filter to work properly but create and update to not work, this would be the code: 

{ field: "ProcessTypeID", width: 300, title: "Process Type", template: "#: ProcessType ? ProcessType.Name : '' #", editor: processTypeEditor, filterable: { ui: processTypeFilter } },

 

[Rosen]

Hello Greg,

As I have mentioned in my previous reply the error during filtering is caused by the fact that set this way it will try to filter on an object instead of its field which will not work. Therefore, did you try any of the suggestions I have made and what was the outcome?

Regards,
Rosen
Telerik

 

 

[Greg]

I did try it before, I receive this error for menu mode: GET http://localhost:30139/odata/Processes?%24format=json&%24expand=ProcessType&%24top=10&%24filter=ProcessType+eq+%27Export%27&%24count=true 400 (Bad Request)

and these errors on row mode: GET http://localhost:30139/odata/Processes?%24format=json&%24expand=ProcessType&%24filter=startswith(tolower(ProcessType)%2C%27ex%27)&%24count=true 400 (Bad Request) as I type and GET http://localhost:30139/odata/Processes?%24format=json&%24expand=ProcessType&%24top=10&%24filter=ProcessType+eq+%27Ex%27&%24count=true 400 (Bad Request) when I hit the enter key.

This does it for my code as well as the example project on github that telerik provides.

[Rosen]

Hello Greg,

 

Please find updated version of the sample project with the suggestions I have made in my initial reply. Could you please take a look, maybe I'm missing something obvious.

 

Regards,
Rosen
Telerik

 

 

[Greg]

It was the field: "ProcessType.Name" I was missing or maybe I had a typo when trying it the first time.  The sample project link didn't work for me, was producing the same errors I was receiving but the zip you added at the bottom of the last reply did work.

 

Thank you for your help!