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

[Solved] Handling Foreign Key Fields

8 Answers 306 Views

Grid

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

This question is locked. New answers and comments are not allowed.

Brian Roth asked on 08 Jun 2010, 05:37 PM

I'm using the grid in an ajax binding scenario and trying to figure out the best way to handle columns for fields that are tied to another table as a foreign key. In the database for our application, most of our tables use an autogenerated integer field for an id and then have a name field used for display purposes. So for example, in a grid for my Employee object, I would want a column that references the Employee Type table. My View Model is set up to have an EmployeeTypeId field and an EmployeeTypeName field that point to the corresponding columns in the Employee Type table. For data editing purposes, I want the EmployeeTypeId field to be the one that is updated, but for display purposes I want the EmployeeTypeName field to be shown to the end user. When editing, I want the edit control to be a select list of all the possible values from the Employee Type table.

I first went down the path of binding the column to the EmployeeTypeId field and using a ClientTemplate to display the EmployeeTypeName field. But this broke down for sorting and filtering operations because the grid was using the id field instead of the name field. So sorting would be based on the id rather than the name and filtering attempted to filter on a numeric value (the id). So to get those to work, I bound the column to the EmployeeTypeName field. I created an Editor Template based on some samples found in the forums here to create a drop down control and set it's value to the EmployeeTypeId value. But now the field being updated in the Update operation was EmployeeTypeName. Since the name is treated as a display only field in my View Model it is not set to be updated. So I added some javascript logic to create a hidden input field tied to the EmployeeTypeId and in the change event of the drop down I populate the hidden field with the newly selected value. This way the update operation correctly sets the value of the EmployeeTypeId property.

So I guess after all the long-winded explanation, my question is - does this seem like the best way to do this? Or does anyone have any other ideas of a simpler way to accomplish the same thing?

In working through this, I had a few ideas for possible enhancements to the grid control:
1) A way to define a field as a display field for a column. This field would be used for display, sorting, and filtering purposes while the bound field would be used for editing operations. I could see this being defined in the column definition or maybe with a custom metadata attribute in the view model. Another possibility would be binding to a NameValuePair object and have the Name used for display and the Value used for edit binding.
2) Some kind of built-in support for foreign key columns, where the fields would behave as described in #1 and in edit mode would show a drop down control (or maybe the new Telerik combo box). There would have to be some way to define where the data for the drop down would come from, maybe either defining the View data that holds the information or defining a web service or server-side method to get the data.
3) And if I can get really greedy with my requests... if #2 is possible, it would be great to have the filter for the column use a drop down as well bound to the same data.

Thanks in advance for your ideas.

Regards,
Brian

8 Answers, 1 is accepted

answered on 09 Jun 2010, 07:51 AM

Hi Brian Roth,

Thanks for the feedback. Up to your questions:

The display and edit templates example shows one way to handle foreign key columns. You can check it out to see if it meets your requirements.

All other suggestions sound like nice features to have and we will consider them in a future release. I have logged them all as a public item so users can vote.

Regards,
Atanas Korchev
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

answered on 24 Sep 2010, 09:06 PM

Hi Chris,

Are you using inline editing, in-form editing, or popup form editing? For either of the two form methods, you can create a read-only property on your model that will output the text you want to see in the grid and then have your id property with a custom editor to display in the edit form. Your model might look something like this:

public class RaceResult

{

[ScaffoldColumn(false)]

public Runner Runner{get; set;}

[ScaffoldColumn(false)]

public string RunnerName {get { return Runner == null ? string.Empty : Runner.Name;}}

[Required]

[UIHint("DropDownList")]

public int RunnerId {get; set;}

[...Your other properties here...]

}

And then in your grid you would bind a column to "RunnerName", but since it is a ScaffoldColumn it will not appear in the edit form. The RunnerId property will appear in the edit form and use the UIHint to display a dropdownlist or whatever control template you want to define as the editor.

Hope that helps!

Regards,
Brian