Category / Section
How to use AutoComplete control for foreignKey column?
1 min read
This Knowledge Base explains the way to place the AutoComplete control for the Foreign Key column using the Edit Template feature of the Grid.
HTML
<div id="Grid"></div>
JS
Render the Grid with the foreign key column and define the editTemplate with read, write and create events.
<script type="text/javascript">
$(function () {
$("#Grid").ejGrid({
dataSource: window.gridData,
allowPaging: true,
editSettings: { allowEditing: true, allowAdding: true, allowDeleting: true },
toolbarSettings: { showToolbar: true, toolbarItems: ["add", "edit", "delete", "update", "cancel"] },
columns: [
{ field: "OrderID", isPrimaryKey: true },
{ field: "CustomerID" },
{
field: "EmployeeID",
foreignKeyField: "EmployeeID",
foreignKeyValue: "FirstName",
editTemplate: { create: "create", read: "read", write: "write" },
dataSource: window.employeeView,
},
{ field: "Freight", format: "{0:C2}" },
{ field: "OrderDate", format: "{0:MM/dd/yyyy}" },
]
});
});
</script>
Razor
@(Html.EJ().Grid<object>("Grid")
.Datasource((IEnumerable<object>)ViewBag.order)
.AllowPaging()
.EditSettings(edit => edit.AllowAdding().AllowDeleting().AllowEditing())
.ToolbarSettings(tools => tools
.ShowToolbar()
.ToolbarItems(items =>
{
items.AddTool(ToolBarItems.Add);
items.AddTool(ToolBarItems.Edit);
items.AddTool(ToolBarItems.Update);
items.AddTool(ToolBarItems.Delete);
items.AddTool(ToolBarItems.Cancel);
}
))
.Columns(col =>
{
col.Field("OrderID").IsPrimaryKey(true).Add();
col.Field("CustomerID").Add();
col.Field("EmployeeID")
.EditTemplate(
edit =>
{
edit.Create("create")
.Read("read")
.Write("write");
})
.ForeignKeyField("EmployeeID").ForeignKeyValue("FirstName")
.DataSource(ViewBag.employee).Add();
col.Field("Freight").Format("{0:MM/dd/yyyy}").Add();
col.Field("OrderDate").Format("{0:C2}").Add();
})
)
Controller
namespace Sample.Controllers
{
public class HomeController : Controller
{
public ActionResult Index()
{
ViewBag.order = new NorthwindDataContext().Orders.ToList();
ViewBag.employee = new NorthwindDataContext().Employees.ToList();
return View();
}
}
}
Aspx
<ej:Grid ID="Grid" runat="server" AllowPaging="True">
<Columns>
<ej:Column Field="OrderID" />
<ej:Column Field="CustomerID" />
<ej:Column Field="EmployeeID" ForeignKeyField="EmployeeID" ForeignKeyValue="FirstName">
<EditTemplate Create="create" Read="read" Write="write" />
</ej:Column>
<ej:Column Field="Freight" Format="{0:C2}" />
<ej:Column Field="OrderDate" Format="{0:MM/dd/yyyy}" />
</Columns>
</ej:Grid>
namespace Sample
{
public partial class _Default : Page
{
protected void Page_Load(object sender, EventArgs e)
{
this.Grid.DataSource = new NorthwindDataContext().Orders.ToList();
this.Grid.Columns[2].DataSource = new NorthwindDataContext().Employees.ToList();
this.Grid.DataBind();
}
}
}
In the write event of the editTemplate, render the AutoComplete control with the necessary properties and bind the datasource from the respective foreign key column.
Please make a note that the read event’s return is different for batch edit mode when comparing to the other edit modes such as normal, dialog, external forms and inline forms (applicable to respective template mode also).
<script type="text/javascript">
function create() {
return "<input>";
}
function read(args) {
var data = args.ejAutocomplete("getSelectedItems")[0];
if (!data)
return {};
return data["EmployeeID"];//normal and other edit modes
//return { text: data["FirstName"], value: data["EmployeeID"] }; //batch edit mode alone
}
function write(args) {
var val = ej.isNullOrUndefined(args.rowdata["EmployeeID"]) ? "" : args.rowdata["EmployeeID"];
args.element.ejAutocomplete({
dataSource: args.column[2].dataSource,
showPopupButton: true,
enableAutoFill: true,
fields: { text: "FirstName", key: "EmployeeID" },
width: "100%"
}).ejAutocomplete("selectValueByKey", val);
}
</script>
Figure: Grid with AutoComplete for Foreign Key column
