This is a migrated thread and some comments may be shown as answers.

Problems filtering by foriegn key column

2 Answers 183 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Chris
Top achievements
Rank 1
Chris asked on 27 Aug 2012, 06:18 PM
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:
{"fclID":3,
 "fclName":"TestFacility",
 "fclActive":true,
 "fclCity":"Mobile",
 "stateID":3,
 "state":{"country":null,
    "stateID":3,
    "stateName":"Alaska",
    "stateAbbr":"AK",
    "ctyID":1}
}

Here's a simplified version of my datasource:
 
var fclDataSource = new kendo.data.DataSource({        //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:
$('.adminboxlarge').append($('#admingrid').kendoGrid({
            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 + '"/>')
            .appendTo(container)
            .kendoDropDownList({
                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 Answers, 1 is accepted

Sort by
0
Petur Subev
Telerik team
answered on 30 Aug 2012, 02:30 PM
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:
http://demos.kendoui.com/web/grid/foreignkeycolumn.html 


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!
0
Todd
Top achievements
Rank 1
answered on 31 Oct 2012, 04:22 AM
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.
Tags
Grid
Asked by
Chris
Top achievements
Rank 1
Answers by
Petur Subev
Telerik team
Todd
Top achievements
Rank 1
Share this question
or