Problems filtering by foriegn key column

3 posts, 0 answers
  1. Chris
    Chris avatar
    2 posts
    Member since:
    May 2012

    Posted 27 Aug 2012 Link to this post

    Hi.  I have a grid with a foriegn key reference to "states".  I was finally able to get the states column to display correctly and to behave when adding a new row or editing a new row.  The problem that has now been pointed out is that the "filter" interface is trying to filter by the (integer) ID value rather than the displayed string value. 

    This is the Json "facility" object with the dependent State:

    Here's a simplified version of my datasource:
    var fclDataSource = new{        //datasource for grid
                batch: false,
                autosync: true,
                transport: {
                    create: { url: "/DataService/InsertFacility",
                        dataType: "json",
                        cache: false
                    read: {
                        url: "/DataService/GetFacilities",
                        dataType: "json",
                        cache: false
                    update: {
                        url: "/DataService/UpdateFacility",
                        dataType: "json",
                        cache: false
                schema: {
                    model: {
                        id: "fclID",
                        fields: {
                            fclName: { type: "string",
                                validation: { required: true }
                            fclCity: { type: "string" },
                            fclActive: { type: "boolean",
                                defaultValue: false
                            stateID: { type: "number",
                                validation: { required: true }
                pageSize: 10

    and Grid:
                dataSource: fclDataSource,
                height: 600,
                width: 890,
                filterable: true,
                sortable: true,
                pageable: true,
                scrollable: false,
                toolbar: [
                    { name: "create", text: "Add Facility"}        //button to add new row
                columns: [
                        field: "fclName",
                        title: "Name",
                        editable: true,
                        filterable: true
                        //width: 250
                    }, {
                        field: "fclCity",
                        title: "City",
                        editable: true,
                        filterable: true
                        //width: 75
                    }, {
                        //field: "state.stateAbbr",
                        field: "stateID",
                        template: "#=state.stateAbbr#",
                        title: "State",
                        editor: facilityStateEditor,
                        editable: true,
                        filterable: true
                        //width: 75
                    }, {
                        command: [
                            { name: "edit", text: "" }
                        //width: 50
                editable: {
                    mode: "inline",
                    update: true, // puts the row in edit mode when it is clicked
                    destroy: false, // does not remove the row when it is deleted, but marks it for deletion
                edit: function () {
                    curr_container = this._editContainer; //selects the current edit container
                    //deletes the text from the "Update" and "Cancel" buttons
                    $(curr_container).find("a.k-grid-update").text("").append('<span class="k-icon k-update"/>');
                    $(curr_container).find("a.k-grid-cancel").text("").append('<span class="k-icon k-cancel"/>');

    And combo box editor.
    function facilityStateEditor(container, options) {      //defines facility combobox
                $('<input data-text-field="stateName" data-value-field="stateID" data-bind="value:' + options.field + '"/>')
                    dataSource: fclstateDataSource,
                    dataTextField: "stateName",
                    dataValueField: "stateID"

    This all works when I reference the parameter for the dependent object as the column field (state.stateAbbr).  However, when I try to filter by this column, I get the filter choices for a number column, not a string column.  If I change the column value to "state" instead of "stateID", the filtering won't work because it tries to convert the state object to lowercase.  If I specify stateAbbr, the display property of the state object, I get errors when attempting to edit or add a new item.

    I tried to use paramter mapping in the datasource to add a stateAbbr property to the JSon object, but the documentation is pretty sparse and I wasn't able to get any of my attempts to work.

    If it's possible to display other attributes of a foriegn key column, it seems it should be possible to set what attribute is used for filtering.  That's all I'm really looking for: a way to define the filter parameter to be the same as the display template.

    Over the last week, as I've tried to google my way through configuring the database and grid for foriegn keys, I've run across all sorts of easter eggs and undocumented functionality in Kendo.  I'm hoping this is another one of those instances where there's a column attribute I just wasn't finding.
  2. Petur Subev
    Petur Subev avatar
    1882 posts

    Posted 30 Aug 2012 Link to this post

    Hello Chris,

    Did you check the ForeignKey functionality which the Grid provides? If you did why it does not suit your case?
    Here is an example to see it in action: 

    Kind regards,
    Petur Subev
    the Telerik team
    Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
  3. Todd
    Todd avatar
    26 posts
    Member since:
    Sep 2012

    Posted 30 Oct 2012 Link to this post

    While your pointing people to the ForeignKey demo, why don't you update the demo to have the "defaultValue" property set on the field in the model.  Without this, when you add a new row, you can not select the first item in the drop-down.  Embarassing for a demo, and also causing many customers to waste time figuring out why they can't select the first item in their drop-down.
Back to Top