Autocomplete feature for a cell when new record is added to grid or while editing the existing row in the grid

[Nick Thompson]

Hi,

I'm using SyncfusionASPNETMVC5 grid control. Is it possible to implement autocomplete feature for a cell when we add a new item to the grid  and also while editing a cell in the edit mode of the grid (as shown in the following image)? I'm trying to implement for AccomStatusDesc Column whose value comes from SQL database.

(Html.EJ().Grid<object>("Editing")
 
 
        .EditSettings(edit =>
        {
            edit.AllowAdding().AllowDeleting().AllowEditing()
 
              .EditMode(Syncfusion.JavaScript.EditMode.Normal);
        })
         .PageSettings(new Syncfusion.JavaScript.Models.PageSettings { PageSize = 49 })
 
        .ToolbarSettings(toolbar =>
            {
 
                toolbar.ShowToolbar().ToolbarItems(items =>
                {
                    items.AddTool(ToolBarItems.Add);
                    items.AddTool(ToolBarItems.Edit);
                    items.AddTool(ToolBarItems.Delete);
                    items.AddTool(ToolBarItems.Update);
                    items.AddTool(ToolBarItems.Cancel);
                });
            })
 
        .AllowPaging(true)
        .AllowReordering(true)
        .AllowResizeToFit(true)
        .EnableAltRow(true)
        .Columns(col =>
        {
            col.Field("AccomStatusDesc").HeaderText("Accom Status Desc").Add();
            col.Field("AccomStatusGroup").HeaderText("Accom Status Group").Add(); 
            col.Field("OrderIndex").HeaderText("Order Index").EditType(EditingType.Numeric).Add();
           
            }).IsUnbound(true).Add();
        })
 
        )

Thank you in advance.

[Nick Thompson]

Hi,
Anyone can please help me?

[Mohammed Farook J]

Hi NIck,

Thank you for helping us define this feature. We have added it to our feature request list and it can be tracked through our Features Management System:

http://www.syncfusion.com/support/directtrac/features/JS-4662

We are closing this incident now. You can communicate with us regarding the open features at any time using the “Contact” option.

Please let us know if you have any questions about this.

Thanks and Regards,
J.Mohammed Farook 

[Nick Thompson]

Thank you very much for your quick response. Is there any other ways to achieve this by hardcoding?

[Mohammed Farook J]

Hi Nick,

 

        We are glad to let you know that we have achieved your requirement by using the Client Side Event in  “ActionComplete” . Please find the code snippet.

 

@(Html.EJ().Grid<SampleAutoComplete_f117217.OrdersView>("FlatGrid")

       

         .ClientSideEvents(c => c.ActionComplete("complete"))

        .Columns(col =>

        {

            col.Field("CustomerID").HeaderText("Customer ID").Width(80).Add();

        }))

 

<script type="text/javascript">

    function complete(args)

    {

 

          var db = $("#FlatGrid").ejGrid("option", "dataSource");

           

        $("#FlatGridCustomerID").ejAutocomplete({ dataSource: db, fields: { text: "CustomerID" } });

}

</script>

 

The  above sample, In  the grid dataSource  to bind the  AutoComplete at that same time the fields is used to which you want to Suggestions key from the dataSource.

 

For your convenience we have created a sample and the same can be downloaded from below link.

 

Sample Location:

 

http://www.syncfusion.com/downloads/support/directtrac/129453/SampleAutoComplete_f1172172140320770.zip

 

Please Let us know if you have any queries,

 

Regards,

 

J.Mohammed Farook,

[Prasanth]

Hi there,

Any update on this?  or the recommendation still is to use the client side script as per the suggesion on this thread.

Regards

Prasanthan

[Balaji Marimuthu]

Hi Prasanthan,

In Grid, we have
default support to render the custom editor(ex: AutoComplete control) in columns while Adding/Editing the records by enabled the EditTemplate Features.  Please refer to the sample and code example as follows,

Sample: EditTemplate

@(Html.EJ().Grid<MVCSample.OrdersView>("Edittemplate")

        .Datasource((IEnumerable<object>)ViewBag.datasource)

        .AllowPaging()

             .EditSettings(edit => { edit.AllowAdding().AllowDeleting().AllowEditing(); })

            

           .AllowPaging()

            .Columns(col =>

            {

                col.Field("OrderID").HeaderText("Order ID").IsPrimaryKey(true).TextAlign(TextAlign.Right).Width(90).Add();

                col.Field("CustomerID").HeaderText("Customer ID").EditTemplate(a => { a.Create("create").Read("read").Write("write"); }).TextAlign(TextAlign.Right).Width(90).Add();

                

             . . . 

           })
)

The Create method is used to create the control in initial state. The Write method will assign the values to ejAutoComplete Control and Read method will get also display the values.

function
create() {

        return $("<input>");

    }

    //render AutoComplete Control

    function
write(args) {

        obj = $('#Edittemplate').ejGrid('instance');

        var data = ej.DataManager(obj.model.dataSource).executeLocal(new ej.Query().select("CustomerID")); //bind data to control

        args.element.ejAutocomplete({ width: "100%", dataSource: data, enableDistinct: true, value: args.rowdata !== undefined ? args.rowdata["CustomerID"] : "" });

    }

    //read the value

    function
read(args) {

        args.ejAutocomplete('suggestionList').css('display', 'none');

        return args.ejAutocomplete("getValue");

    }

So, you can display the custom editors (like MaskEdit, TimePicker) to the columns when edit/add the records by using the EditTemplate features. Please refer to the documents & demo in the following link,

Documentation link: http://helpjs.syncfusion.com/js/grid/editing#edit-template

Demo:
http://help.syncfusion.com/aspnetmvc/grid/editing#edit-template

Regards,
Balaji Marimuthu

Hi Prasanthan,

In Grid, we have default support to render the custom editor(ex: AutoComplete control) in columns while Adding/Editing the records by enabled the EditTemplate Features.  Please refer to the sample and code example as follows,
Sample: EditTemplate

@(Html.EJ().Grid<MVCSample.OrdersView>("Edittemplate")

        .Datasource((IEnumerable<object>)ViewBag.datasource)

        .AllowPaging()

             .EditSettings(edit => { edit.AllowAdding().AllowDeleting().AllowEditing(); })

           

            .AllowPaging()

            .Columns(col =>

            {

                col.Field("OrderID").HeaderText("Order ID").IsPrimaryKey(true).TextAlign(TextAlign.Right).Width(90).Add();

                col.Field("CustomerID").HeaderText("Customer ID").EditTemplate(a => { a.Create("create").Read("read").Write("write"); }).TextAlign(TextAlign.Right).Width(90).Add();

               

             . . .

            })
)

The Create method is used to create the control in initial state. The Write method will assign the values to ejAutoComplete Control and Read method will get also display the values.

function create() {

        return $("<input>");

    }

    //render AutoComplete Control

    function write(args) {

        obj = $('#Edittemplate').ejGrid('instance');

        var data = ej.DataManager(obj.model.dataSource).executeLocal(new ej.Query().select("CustomerID")); //bind data to control

        args.element.ejAutocomplete({ width: "100%", dataSource: data, enableDistinct: true, value: args.rowdata !== undefined ? args.rowdata["CustomerID"] : "" });

    }

    //read the value

    function read(args) {

        args.ejAutocomplete('suggestionList').css('display', 'none');

        return args.ejAutocomplete("getValue");

    }

So, you can display the custom editors (like MaskEdit, TimePicker) to the columns when edit/add the records by using the EditTemplate features. Please refer to the documents & demo in the following link,

Documentation link: http://helpjs.syncfusion.com/js/grid/editing#edit-template

Demo:
http://help.syncfusion.com/aspnetmvc/grid/editing#edit-template

Regards,
Balaji Marimuthu

[Prasanth]

Hi Balaji,

Thanks for the reply, I am able to get it going. 

However I have slight requirement change, in the grid I am trying to use Batch update / add.  Also in the sample you have the CustomerID which is being used search and insert (foreign key field).  I need to search by Name (e.g. Customer name) but able to insert Customer ID (for example in the sample you have given assuming ALFKI is a name of the customer and we have assigned 1 for Customer ID - I want to be able to search for ALFKI but once I find the customer vis suggestion list I should be able to select the record and insert/update 1 in the foreign key)

Sorry I have tried to play around but so far failed.  If you could assist me on this that would be appreciated.

Regards

Prasanth

[Balaji Marimuthu]

Hi Prasanth,

Thanks for the update.

We have analyzed your requirement but before proceeding further could you please share us following information?

1.     Are you using the ForeignKeyField column in Grid?

2.     Are you want to save the foreignkeyfield(ex: 1) to database and display/search the foreignkeyvalue (ex: “ALFKI”)?

3.     Share the Essential studio version details.

4.     Are you want the Autocomplete control to Foreignkeyfield column?

We have a default support for binding the foreignkey column in Grid. So the corresponding foreign key field is insert/update into database and display the foreignkey value in Grid. Please refer to the following link.
Demo: http://mvc.syncfusion.com/demos/web/grid/foreignkeycolumn
UG document: http://help.syncfusion.com/aspnetmvc/grid/columns#foreign-key-columns

@(Html.EJ().Grid<MVCSample.OrdersView>("Edittemplate")

        .Datasource((IEnumerable<object>)ViewBag.datasource)

       

            .Columns(col =>

            {

               col.Field("OrderID").HeaderText("Order ID").IsPrimaryKey(true).TextAlign(TextAlign.Right).Width(90).Add();

                col.Field("EmployeeID").HeaderText("Employee Name").ForeignKeyField("EmployeeID").ForeignKeyValue("FirstName").DataSource((IEnumerable<object>)ViewBag.dataSource2)

.TextAlign(TextAlign.Left).Width(90).Add();

               . . .

            }                    
)

If we misunderstood your requirement please share more information about your requirement that will be helpful to provide better solution at the earliest.

Regards,
Balaji Marimuthu

---

PR Prasanth November 27, 2015 02:21 AM UTC

Hi Balaji,

Thanks for the reply.  Sorry if I haven't explained the requirement properly.  I am using visual studio 15 and Syncfusion 13.3.0.7

Let's say we have two tables orders and customers.  Customer table has ID and Name field and there is relationship between Orders and Customers (N:1) hence order table has a foreign key (customer ID) 

Customer Table

ID = 1, Name "ALFKI"

Order Table

ID = xx, CustomerID = 1

In the orders grid:

I want to save the foreignkeyfield (ex: 1) to database while display/search the foreignkeyvalue (ex: “ALFKI”) via autocomplete control.

Also is it possible to achieve this with batch insert/update methods.

Regards

Prasanth

---

BM Balaji Marimuthu Syncfusion Team November 27, 2015 01:07 PM UTC

Hi Prasanth,

Your requirement is achieved by using the ForeignKeyField and ForeignKeyValue property in Grid columns. Refer to the sample and code example as below.
Sample: MVCSample

@(Html.EJ().Grid<MVCSample.OrdersView>("Edittemplate")

            .Datasource(ds => ds.Json((IEnumerable<object>)ViewBag.datasource).BatchURL("/Grid/BatchUpdate").Adaptor(AdaptorType.RemoteSaveAdaptor))

        .AllowPaging()

             .EditSettings(edit => { edit.AllowAdding().AllowDeleting().AllowEditing().EditMode(EditMode.Batch); })

            .ToolbarSettings(toolbar =>

            {

                toolbar.ShowToolbar().ToolbarItems(items =>

                {

                    items.AddTool(ToolBarItems.Add);

                    items.AddTool(ToolBarItems.Edit);

                    items.AddTool(ToolBarItems.Delete);

                    items.AddTool(ToolBarItems.Update);

                    items.AddTool(ToolBarItems.Cancel);

                });

            })

            .AllowPaging()

            .Columns(col =>

            {

                col.Field("OrderID").HeaderText("Order ID").IsPrimaryKey(true).TextAlign(TextAlign.Right).Width(90).Add();

                col.Field("EmployeeID").ForeignKeyField("EmployeeID")

                            .ForeignKeyValue("FirstName").DataSource((IEnumerable<object>)ViewBag.dataSource2).TextAlign(TextAlign.Left).Width(90).Add();

                col.Field("Freight").HeaderText("Freight").TextAlign(TextAlign.Right).Width(80).EditType(EditingType.Numeric).Format("{0:C}").Add();

                col.Field("ShipName").HeaderText("ShipName").Width(90).Add();

                col.Field("ShipCountry").HeaderText("ShipCountry").Width(90).EditType(EditingType.Dropdown).Add();

            })

           
)

[Controller]

public ActionResult BatchUpdate(List<EditableOrder> changed, List<EditableOrder> added, List<EditableOrder> deleted)

        {

            if (changed != null)

                OrderRepository.Update(changed);

            if (deleted != null)

                OrderRepository.Delete(deleted);

            if (added != null)

                OrderRepository.Add(added);

            var data = OrderRepository.GetAllRecords();

            return Json(data, JsonRequestBehavior.AllowGet);
        }

In above code example, we bound the Orders table to Grid and Employees table which contains “EmployeeID” and “FirstName” property. The EmployeeID is the foreignkeyfield of both Orders and Employees table. Refer to the helpdocument:
Demo: http://mvc.syncfusion.com/demos/web/grid/foreignkeycolumn
UG document: http://help.syncfusion.com/aspnetmvc/grid/columns#foreign-key-columns

So, while updating/inserting the record the corresponding ForeignKeyField(“EmployeeID”) which pass to the server side and save into the database. Refer to the post request as below.



By default, the dropdown edit will set into foreign key column while using foreign key field. It’s an default behavior of Grid. So we suggest you to use the above code example for insert/update the foreign key field to database.

Regards,
Balaji Marimuthu

---

PR Prasanth November 27, 2015 01:32 PM UTC

Hi Balaji,

Thanks for the quick reply.  Much appreciated.

I guess I could use drop down list instead of auto complete field here.  I will definitely give this a go and thank you so much for the source code, saves me so much time.

One final thing is it possible to have client side validation to ensure that duplicate employee id is not entered or does it needs to be done at server side.

But other than that everything is good for me, thanks once again for helping me out.

Regards

Prasanth

---

BM Balaji Marimuthu Syncfusion Team November 30, 2015 06:49 AM UTC

Hi Prasanth,

Thanks for the update.

To perform client side validation you can use the ValidationRules property in Grid and your requirement is achieved by using the custom validation methods. Refer to the sample and code example as below.
Sample: Sample-validation

@(Html.EJ().Grid<MVCSample.OrdersView>("Edittemplate")

            .Datasource(ds => ds.Json((IEnumerable<object>)ViewBag.datasource).BatchURL("/Grid/BatchUpdate").Adaptor(AdaptorType.RemoteSaveAdaptor))

        .AllowPaging()

             .EditSettings(edit => { edit.AllowAdding().AllowDeleting().AllowEditing().EditMode(EditMode.Batch); })

            .ToolbarSettings(toolbar =>

            {

                toolbar.ShowToolbar().ToolbarItems(items =>

                {

                    items.AddTool(ToolBarItems.Add);

                    items.AddTool(ToolBarItems.Edit);

                    items.AddTool(ToolBarItems.Delete);

                    items.AddTool(ToolBarItems.Update);

                    items.AddTool(ToolBarItems.Cancel);

               });

            })

            .AllowPaging()

            .Columns(col =>

            {

                col.Field("OrderID").HeaderText("Order ID").IsPrimaryKey(true).TextAlign(TextAlign.Right).ValidationRules(v => v.AddRule("required", true).AddRule("number", true)).Width(90).Add();

                col.Field("EmployeeID").HeaderText("Freight").ValidationRules(v => v.AddRule("customCompare",5)).TextAlign(TextAlign.Right).Width(80).Add();

                col.Field("ShipName").HeaderText("ShipName").Width(90).Add();

                col.Field("ShipCountry").HeaderText("ShipCountry").Width(90).EditType(EditingType.Dropdown).Add();

            })

           
)

<script type="text/javascript">

      $(function () {

          $.validator.addMethod("customCompare", function (value, element, params) {

              var obj = $("#Edittemplate").ejGrid("instance");

              var arrdata = ej.DataManager(obj.model.dataSource.dataSource.json).executeLocal(new ej.Query().select("EmployeeID"));

              var batchadded = ej.DataManager(obj.batchChanges.added).executeLocal(new ej.Query().select("EmployeeID"));

              var batchchanged = ej.DataManager(obj.batchChanges.changed).executeLocal(new ej.Query().select("EmployeeID"));

              var data = $.merge(arrdata, batchadded);

              var data1 = $.merge(data, batchchanged);

              if ($.inArray(parseInt(element.value), ej.distinct(data1)) == -1)

                  return true;

              return false;

          }, "Please enter new value");

      });

</script>

Refer to the demo and document as below.

Demo: http://mvc.syncfusion.com/demos/web/grid/customvalidation

Document: http://help.syncfusion.com/aspnetmvc/grid/editing#validation


Also, you can perform the validation in server side.

Regards,
Balaji Marimuthu

SIGN IN To post a reply.

[Prasanth]

Hi Balaji,

Thanks for the reply.  Sorry if I haven't explained the requirement properly.  I am using visual studio 15 and Syncfusion 13.3.0.7

Let's say we have two tables orders and customers.  Customer table has ID and Name field and there is relationship between Orders and Customers (N:1) hence order table has a foreign key (customer ID) 

Customer Table

ID = 1, Name "ALFKI"

Order Table

ID = xx, CustomerID = 1

In the orders grid:

I want to save the foreignkeyfield (ex: 1) to database while display/search the foreignkeyvalue (ex: “ALFKI”) via autocomplete control.

Also is it possible to achieve this with batch insert/update methods.

Regards

Prasanth

[Balaji Marimuthu]

Hi Prasanth,

Your requirement is achieved by using the ForeignKeyField and ForeignKeyValue property in Grid columns. Refer to the sample and code example as below.
Sample: MVCSample

@(Html.EJ().Grid<MVCSample.OrdersView>("Edittemplate")

            .Datasource(ds => ds.Json((IEnumerable<object>)ViewBag.datasource).BatchURL("/Grid/BatchUpdate").Adaptor(AdaptorType.RemoteSaveAdaptor))

        .AllowPaging()

             .EditSettings(edit => { edit.AllowAdding().AllowDeleting().AllowEditing().EditMode(EditMode.Batch); })

            .ToolbarSettings(toolbar =>

            {

                toolbar.ShowToolbar().ToolbarItems(items =>

                {

                    items.AddTool(ToolBarItems.Add);

                    items.AddTool(ToolBarItems.Edit);

                    items.AddTool(ToolBarItems.Delete);

                    items.AddTool(ToolBarItems.Update);

                    items.AddTool(ToolBarItems.Cancel);

                });

            })

            .AllowPaging()

            .Columns(col =>

            {

                col.Field("OrderID").HeaderText("Order ID").IsPrimaryKey(true).TextAlign(TextAlign.Right).Width(90).Add();

                col.Field("EmployeeID").ForeignKeyField("EmployeeID")

                            .ForeignKeyValue("FirstName").DataSource((IEnumerable<object>)ViewBag.dataSource2).TextAlign(TextAlign.Left).Width(90).Add();

                col.Field("Freight").HeaderText("Freight").TextAlign(TextAlign.Right).Width(80).EditType(EditingType.Numeric).Format("{0:C}").Add();

                col.Field("ShipName").HeaderText("ShipName").Width(90).Add();

                col.Field("ShipCountry").HeaderText("ShipCountry").Width(90).EditType(EditingType.Dropdown).Add();

            })

           
)

[Controller]

public ActionResult BatchUpdate(List<EditableOrder> changed, List<EditableOrder> added, List<EditableOrder> deleted)

        {

            if (changed != null)

                OrderRepository.Update(changed);

            if (deleted != null)

                OrderRepository.Delete(deleted);

            if (added != null)

                OrderRepository.Add(added);

            var data = OrderRepository.GetAllRecords();

            return Json(data, JsonRequestBehavior.AllowGet);
        }

In above code example, we bound the Orders table to Grid and Employees table which contains “EmployeeID” and “FirstName” property. The EmployeeID is the foreignkeyfield of both Orders and Employees table. Refer to the helpdocument:
Demo: http://mvc.syncfusion.com/demos/web/grid/foreignkeycolumn
UG document: http://help.syncfusion.com/aspnetmvc/grid/columns#foreign-key-columns

So, while updating/inserting the record the corresponding ForeignKeyField(“EmployeeID”) which pass to the server side and save into the database. Refer to the post request as below.



By default, the dropdown edit will set into foreign key column while using foreign key field. It’s an default behavior of Grid. So we suggest you to use the above code example for insert/update the foreign key field to database.

Regards,
Balaji Marimuthu

[Prasanth]

Hi Balaji,

Thanks for the quick reply.  Much appreciated.

I guess I could use drop down list instead of auto complete field here.  I will definitely give this a go and thank you so much for the source code, saves me so much time.

One final thing is it possible to have client side validation to ensure that duplicate employee id is not entered or does it needs to be done at server side.

But other than that everything is good for me, thanks once again for helping me out.

Regards

Prasanth

[Balaji Marimuthu]

Hi Prasanth,

Thanks for the update.

To perform client side validation you can use the ValidationRules property in Grid and your requirement is achieved by using the custom validation methods. Refer to the sample and code example as below.
Sample: Sample-validation

@(Html.EJ().Grid<MVCSample.OrdersView>("Edittemplate")

            .Datasource(ds => ds.Json((IEnumerable<object>)ViewBag.datasource).BatchURL("/Grid/BatchUpdate").Adaptor(AdaptorType.RemoteSaveAdaptor))

        .AllowPaging()

             .EditSettings(edit => { edit.AllowAdding().AllowDeleting().AllowEditing().EditMode(EditMode.Batch); })

            .ToolbarSettings(toolbar =>

            {

                toolbar.ShowToolbar().ToolbarItems(items =>

                {

                    items.AddTool(ToolBarItems.Add);

                    items.AddTool(ToolBarItems.Edit);

                    items.AddTool(ToolBarItems.Delete);

                    items.AddTool(ToolBarItems.Update);

                    items.AddTool(ToolBarItems.Cancel);

               });

            })

            .AllowPaging()

            .Columns(col =>

            {

                col.Field("OrderID").HeaderText("Order ID").IsPrimaryKey(true).TextAlign(TextAlign.Right).ValidationRules(v => v.AddRule("required", true).AddRule("number", true)).Width(90).Add();

                col.Field("EmployeeID").HeaderText("Freight").ValidationRules(v => v.AddRule("customCompare",5)).TextAlign(TextAlign.Right).Width(80).Add();

                col.Field("ShipName").HeaderText("ShipName").Width(90).Add();

                col.Field("ShipCountry").HeaderText("ShipCountry").Width(90).EditType(EditingType.Dropdown).Add();

            })

           
)

<script type="text/javascript">

      $(function () {

          $.validator.addMethod("customCompare", function (value, element, params) {

              var obj = $("#Edittemplate").ejGrid("instance");

              var arrdata = ej.DataManager(obj.model.dataSource.dataSource.json).executeLocal(new ej.Query().select("EmployeeID"));

              var batchadded = ej.DataManager(obj.batchChanges.added).executeLocal(new ej.Query().select("EmployeeID"));

              var batchchanged = ej.DataManager(obj.batchChanges.changed).executeLocal(new ej.Query().select("EmployeeID"));

              var data = $.merge(arrdata, batchadded);

              var data1 = $.merge(data, batchchanged);

              if ($.inArray(parseInt(element.value), ej.distinct(data1)) == -1)

                  return true;

              return false;

          }, "Please enter new value");

      });

</script>

Refer to the demo and document as below.

Demo: http://mvc.syncfusion.com/demos/web/grid/customvalidation

Document: http://help.syncfusion.com/aspnetmvc/grid/editing#validation


Also, you can perform the validation in server side.

Regards,
Balaji Marimuthu