Grid Autocomplete Cell

[Prasanth]

Hi there,

I think this query was raised earlier with this thread http://www.syncfusion.com/forums/117217/autocomplete-feature-for-a-cell-when-new-record-is-added-to-grid-or-while-editing-the-existing-row-in

in this thread last time it was suggested to use drop down using ForeignKeyField and ForeignValue property.  However this time around the related Entity type has 1000s of records and it would be hard for end user to scroll through the drop down to select the appropriate entry.

I was wondering whether with new version released Grid supports AutoComplete functionality.

To reiterate the desired functionality, I have grid with batch update functionality for records from an Entity (Car) which has relationship with another entity (Model).  So for each car entry row in the grid in one of the cells we have corresponding car model detail (foreign key but want to display text).  When user wants to change / add car model detail, if user can type in the car model text to narrow down the search and select the model (assigning car model primary key value) that would be great.

@(Html.EJ().Grid<AssessmentCBDViewModel>("cbdGrid")

        .AllowFiltering().FilterSettings(filter => { filter.FilterType(FilterType.Excel); })

        .AllowResizeToFit().AllowResizing().AllowSorting().AllowPaging()

        .Datasource(ds =>

        {

            ds.URL("/Home/Data");

            ds.BatchURL("/Home/Update");

            ds.Adaptor(AdaptorType.UrlAdaptor);

        })

If this is hard may be is it possible to have this functionality implemented via 

1. DialogEditing or 
   
2. Inline Form Editing or
3. External Form Editing

Regards

Prasanth

[Gowthami V]

Hi Prasanthan,

Thanks for using Syncfusion products.

We have provided support for “EditTemplate” which is used to create custom editor (like AutoComplete). Using “EditTemplate” we can render the AutoComplete control as follows,

@(Html.EJ().Grid<object>("FlatGrid")
            .Datasource(ds => ds.URL("BatchDataSource").BatchURL("BatchUpdate").Adaptor(AdaptorType.UrlAdaptor))
. . . .
. . . .

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

  }))

<script type="text/javascript">

    function create() {

        return $("<input>");

    }

    function write(args) {

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

        var data = [];

        $.ajax({

//get the data for the autoComplete control

            type: "GET",

            url: "/Grid/BatchDataSource",

          

            success: function (data, status, xhr) {

                 var data1 = ej.DataManager(data.result).executeLocal(new ej.Query().select("CustomerID"));

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

            }

        });

    }

    function read(args) {

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

        return args.ejAutocomplete("getValue");

    }

    $("#FlatGrid").keyup(function (e) {

        if (e.keyCode == 40 && $(e.target).hasClass("e-autocomplete")) {

            var autocomp = $("#EdittemplateEmployeeID").ejAutocomplete("instance")

            if (autocomp._getActiveText() != "No suggestions")

                $(e.target).val(autocomp._getActiveText());

        }

    });

</script>

Refer to the below link for more clarification about EditTemplate,

https://help.syncfusion.com/aspnetmvc/grid/editing#cell-edit-template

https://mvc.syncfusion.com/demos/web/grid/edittemplate


Regards,

Gowthami V.

[Prasanth]

Hi Gowthami,

Thanks for the reply.

I believe I have tried this solution before but the issue here is 

1. The CustomerID is stored in the Order table and used to display as well, however I am hoping to store CustomerID (e.g. Int or GUID) in the Order table but display CustomerName on the grid (and also use the name to search for the customer).  
2. Customer table will store the CustomerName field
3. I believe the sample solution retrieves data and just provides the autocomplete functionality for entire data in tabel.  Issue for me is that BatchDataSource call will retrieve subset of primary data using primary ID.  But we want to be able to connect any individual record within the subset data on the grid to secondary (related) data using autocomplete.

I hope the data structure described below explains the functionality I am after.  Basically lets say we have grid with Course Registration data specific for a Student (i.e BatchDataSource is filtered primary data for a particular student not all the students).  Now if the end users could Type in the CourseName in the grid cell displaying CourseName (not the CourseID) and able to have filtered / autocomplete functionality based on the user input that would be great.  The Course can be any course that student is not previously associated with.  I believe the sample provided on the page (http://mvc.syncfusion.com/demos/web/grid/edittemplate) doesn't cover this scenario.

Primary Data

CourseRegistration

StudentID

CourseID

 

Secondary Data (Related Data)

Course

CourseID

CourseName

 

Regards

Prasanth

[Seeni Sakthi Kumar Seeni Raj]

Hi Prasanthan,

Query : The CustomerID is stored in the Order table and used to display as well, however I am hoping to store CustomerID (e.g. Int or GUID) in the Order table but display CustomerName on the grid (and also use the name to search for the customer).  Customer table will store the CustomerName field

Your requirement can be achieved using ForeignKeyField and ForeignKeyValue of the columns. ForiegnKey column requires a dataSource (related dataSource), can be bound by the ForeignKeyField and ForeignKeyValue values. It will display the ForeignKeyValue of dataSource(bound to the column) in the Grid.

Also you would like to render the autoComplete instead of dropdown. We suggest you to use the filterSearch along with dropdownlist which is similar to the autocomplete. Refer to the following code example.

@(Html.EJ().Grid<object>("Editing")

        .Datasource(ds => ds.URL("/Home/BatchDataSource").BatchURL("/Home/BatchUpdate").Adaptor(AdaptorType.UrlAdaptor))

        .AllowPaging()

        .ClientSideEvents(eve => eve.CellEdit("cellEdit").CellSave("cellSave"))

            .Columns(col =>

            {

                col.Field("OrderID").IsPrimaryKey(true).Add();

                col.Field("EmployeeID").ForeignKeyField("EmployeeID").ForeignKeyValue("FirstName").DataSource(ViewBag.datasource).Add();

                    . .  . . .

            })

)

<script>

    function cellEdit(args) {

        if (args.columnName == "EmployeeID") {

            args.columnObject.dataSource = undefined;

            args.columnObject.editParams = { enableFilterSearch: true };

        }

    }

    function cellSave(args){

        this.model.columns[1].dataSource = @Html.Raw(Json.Encode(ViewBag.datasource));//EmployeeID column

    }
</script>

We have prepared a sample that can be downloaded from the following location.

https://www.syncfusion.com/downloads/support/forum/121775/ze/BatchEdit_dropdownAutoComplete276765115

Regards,
Seeni Sakthi Kumar S.

[Prasanth]

Hi Seeni

Thanks for the solution.  I have ran the sample solution seems to be working fine and it meets my requirement.

I will port your solution into my project and will let you know if there are any issues

Thanks agian

Regards

Prasanth

[Seeni Sakthi Kumar Seeni Raj]

Hi Prasanthan,

We are happy to hear that the solution meets your requirement. Please let us know if you need any further assistance.

Regards,
Seeni Sakthi Kumar S.

[Megatron]

Hi, I am also using auto complete on a few columns, can you show me how to load/setup/handle the FK setting for the column via the adaptor, and cache it.

thanks

[Seeni Sakthi Kumar Seeni Raj]

Hi Prasanthan,

From your query, we understood that you would like to render the ejAutoComplete for the foreignKey column using the ej.ForeignKeyAdaptor. It is possible but the foreignKeyAdaptor will work only with the local datasource. Refer to the below sample,

Sample: https://www.syncfusion.com/downloads/support/forum/121775/ze/ForeignKey_AutoComplete1788849815

@(Html.EJ().Grid<object>("Editing")

           .Datasource(ds => ds.Json((IEnumerable<object>)ViewBag.datasource).InsertURL("/Home/Insert").UpdateURL("/Home/Update").RemoveURL("/Home/Delete").Adaptor(AdaptorType.RemoteSaveAdaptor))

            .AllowPaging()

             . .. . . . .

                .Columns(col =>

                {

                    col.Field("OrderID").HeaderText("Order ID").Add();

                     . . . . .

                    col.Field("FirstName").EditTemplate(temp => { temp.Create("create").Read("read").Write("write"); }).Add();//Virtual column

                })

                    .ClientSideEvents(evt => evt.Load("onLoad"))

)

<script>

    var data = @Html.Raw(Json.Encode(ViewBag.datasource1));

    var arr =[{ dataSource: data, foreignKeyField: "EmployeeID", foreignKeyValue: "FirstName" }];

    function onLoad(args) {

        this.model.dataSource.adaptor = new ej.ForeignKeyAdaptor(arr,"remoteSaveAdaptor");

    }

    function create() {

        return "<input>";

    }

    function read(args) {

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

        return args.ejAutocomplete("getValue");

    }

    function write(args) {

        var data1 = ej.DataManager(data).executeLocal(new ej.Query().select("FirstName"));

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

    }

    $("#Editing").keyup(function (e) {

        if (e.keyCode == 40 && $(e.target).hasClass("e-autocomplete")) {

            var autocomp = $("#EditingFirstName").ejAutocomplete("instance")

            if (autocomp._getActiveText() != "No suggestions")

                $(e.target).val(autocomp._getActiveText());

        }

    });
</script>

The above code example illustrates how to render the foreignkey column using the ej.ForeignKeyAdaptor. ForeignKeyAdaptor accepts two parameters. First parameter accepts the array of objects, which has dataSource, foreignKeyField and foreignKeyValue for the virtual column (“FirstName”) in Grid. Whereas the second parameter holds the type of secondary adaptor (either JsonAdaptor or remoteSaveAdaptor).

ejAutoComplete rendered using the editTemplate of ejGrid.

Please make a note that ForeignKey column of the Grid (at the column level) will not accept editType other than dropDown.

                .Columns(col =>

                {

                    col.Field("EmployeeID").HeaderText("FirstName").ForeignKeyField("EmployeeID").ForeignKeyValue("FirstName").Add();

                  .  . .. .

                })

So we did it using the ej.ForeignKeyAdaptor and at the same time foreignKeyAdaptor handles only with local datasource.

In your query, you have quoted the “adaptor” and doesn’t mention any adaptor name. So we have done this using foreignKeyAdaptor. If we misunderstood your query, please explain your requirement specifically.

Regards,
Seeni Sakthi Kumar S.

[Prasanth]

Hi Senthil, 

I was able to use the solution you provided on 27th January (just using foreign key/value field column and using celledit / cellsave javascript functions, enableFilterSearch)

I have ran into minor issues but didn't have time to look into them till now, tried to fix them myself but having trouble.  I have attached word document with screenshots to explain things better.  But the list is below.

Issue one (rendering)

When adding/editing on the autocomplete cell White space doesn’t extend to the drop down arrow

Issue Two (autocomplete)

Once filtered on the filtered list individual item needs double click to be selected just simply clicking an item doesn’t select it

If I click on it individual item then the cell is just empty

Issue Three (check box interaction)

For some reason check box is not working correctly when editing if I try to click on check box while hovering over the check box then I get to see the stop icon

In this case if I click anywhere else within the cell then Check box is updated, but the issue is unpredictable as it doesn’t happen 100% of the time.

Issue Four

Based on check box value on the grid column is it possible to disable the row so that the row is locked and users aren’t allowed to delete and individual columns aren’t editable as well.  I have server side validation but client side validation would be nice (also showing dialog to inform that the records can’t be deleted)

If you could provide some assistance that would be greatly appreciated.

Regards

Prasanth

Attachment: GridIssues_326c3d05.zip

[Seeni Sakthi Kumar Seeni Raj]

Hi Prasanth 

Query #1: When adding/editing on the autocomplete cell White space doesn’t extend to the drop down arrow.  Query #2: Once filtered on the filtered list individual item needs double click to be selected just simply clicking an item doesn’t select it. Query #3: Issue Three (check box interaction) 

We are unable to reproduce the issue at our end with the solution provided on January 27, 2016 update. We have checked the reported issue from the 13.4.0.53 release and the latest releases.  After enabling the filter search for the dropdown corresponding column appears to be working fine. Thereafter, selecting the items of dropdown also saving the value in the Grid.  

We have also prepared a sample that can be downloaded from the following location. 

Sample: https://www.syncfusion.com/downloads/support/forum/121775/ze/Sample-2043137073.zip 

Please provide the following information to analyze the issue and provide you solution as early as possible. 

1)      Code example of Grid 

2)      Version of Essential Studio (scripts and dlls) referred in project? 

3)      If possible, modify the attached sample and replicate the issue. 

Query #4: Issue Four 

You would like to disable and prevent the record from editing based on the Boolean column in the Grid. In the cellEdit Event of Grid, check the value of the current editing record and based on that prevent the record from performing further action(args.cancel=true). Refer to the following code example. 

@(Html.EJ().Grid<object>("Editing") 

         .Datasource(ds => ds.URL("/Home/BatchDataSource").BatchURL("/Home/BatchUpdate").Adaptor(AdaptorType.UrlAdaptor)) 

            .AllowPaging() 

                    .ClientSideEvents(eve => eve.CellEdit("cellEdit")) 

                   . . . 

                .Columns(col => 

                { 

                        . . . 

                    col.Field("Verified").HeaderText("Verified").EditType(EditingType.Boolean).Width(80).Add(); 

                }) 

) 

<script> 

    function cellEdit(args) { 

        if(!args.rowData.Verified) 

            args.cancel = true 

            . . ..  

    } 

</script> 

We have also provided this solution in the attached sample. 

Regards, 

Seeni Sakthi Kumar S. 

[Prasanth]

Hi Seeni,

Thanks for the reply,  I have gone through the sample you have sent. 

I have tried my best on my end but I am still having issues within my project.  Tried to follow the sample provided, and I couldn't figure out why the issue is persistent.

I have copied my project and created a sample (click on the Test hyperlink in the header - next to home) to reproduce the issues

I have two entities fruits and orders.  Orders basically contain fruits.  The grid on the Test page displays order where Fruits can be searched for edit/add.  

The issues as per previous message

• Once searched - filter, need to double click on the filtered list entry to select (couldn't achieve this with single click on or enter key)
• White space is still there
• Checkbox issue I haven't tested but assume it's there as I think the issues might be due to script files not being added correctly or has incorrect content

The syncfusion I am using is 13.3.0.18, planning to update to latest version at some stage in near future

Regards

Prasanth

Attachment: ProjectXEDWeb_ee063357.zip

[Seeni Sakthi Kumar Seeni Raj]

Hi Prasanth, 

Query #1: Once searched - filter, need to double click on the filtered list entry to select (couldn't achieve this with single click on or enter key), #2: White space is still there 

We are able to reproduce the issues with the provided project. We have fixed these issues in our latest release. So we suggest to upgrade to our latest Essential Studio version 14.1.0.46. 

Please follow the below steps to upgrade to 14.1.0.46. 

1)            Download and install the Essential Studio v14.1.0.146 from the below link.   

https://www.syncfusion.com/forums/123963/essential-studio-2016-volume-1-service-pack-release-v14-1-0-46-available-for-download 

2)            Replace the Syncfusion dlls, scripts, and CSS in your project from the following location. 

            Scripts and CSS: C:\Program Files (x86)\Syncfusion\Essential Studio\XX.X.X.XX\JavaScript\assets 

            Dlls: C:\Program Files (x86)\Syncfusion\Essential Studio\ XX.X.X.XX \Assemblies 

Here XX.X.X.XX denotes the product version(14.1.0.46). 

After upgrading please ensure to clear the bin folder and browser cache to avoid accidental reference of the old assemblies and Scripts/CSS.  

Query #3: Checkbox issue I haven't tested but assume it's there as I think the issues might be due to script files not being added correctly or has incorrect content 

On hovering the checkbox, a restrict-cursor will prevent the checkbox from editing and on clicking the column will render the edit form and change the state of the checkbox which is the default behavior of the Grid. If you are reporting any other issue, please provide the exact replication procedure. 

Regards, 

Seeni Sakthi Kumar S. 

[Prasanth]

Hi Seeni, 

Thanks for the reply and solution.  Regarding query 3 is it possible to change the default behavior of check box (where clicking over the check box doesn't work) to enable clicking over check box in a cell to change check box status.

Regards

Prasanth

[Seeni Sakthi Kumar Seeni Raj]

Hi Prasanth, 

On hovering the checkbox in columns, Grid will prevent the checkbox from editing and on clicking the column/corresponding cell will render the edit form and change the state of the checkbox which is the behavior of the Grid. For an batch editing mode, we have logged an usability feature “Support to render the checkbox column in enabled mode for batch editing” to change the current behavior and we have created a new support incident under your account to track the task. Please log on to our support website to check for further updates. 

https://www.syncfusion.com/account/login?ReturnUrl=/support/directtrac/incidents 

Regards, 

Seeni Sakthi Kumar S. 

[Bruno Figueiredo]

Hi Prasanthan,

Thanks for using Syncfusion products.

We have provided support for “EditTemplate” which is used to create custom editor (like AutoComplete). Using “EditTemplate” we can render the AutoComplete control as follows,

@(Html.EJ().Grid<object>("FlatGrid")
            .Datasource(ds => ds.URL("BatchDataSource").BatchURL("BatchUpdate").Adaptor(AdaptorType.UrlAdaptor))
. . . .
. . . .

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

  }))

<script type="text/javascript">

    function create() {

        return $("");

    }

    function write(args) {

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

        var data = [];

        $.ajax({

//get the data for the autoComplete control

            type: "GET",

            url: "/Grid/BatchDataSource",

          

            success: function (data, status, xhr) {

                 var data1 = ej.DataManager(data.result).executeLocal(new ej.Query().select("CustomerID"));

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

            }

        });

    }

    function read(args) {

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

        return args.ejAutocomplete("getValue");

    }

    $("#FlatGrid").keyup(function (e) {

        if (e.keyCode == 40 && $(e.target).hasClass("e-autocomplete")) {

            var autocomp = $("#EdittemplateEmployeeID").ejAutocomplete("instance")

            if (autocomp._getActiveText() != "No suggestions")

                $(e.target).val(autocomp._getActiveText());

        }

    });

script>

Refer to the below link for more clarification about EditTemplate,

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

http://mvc.syncfusion.com/demos/web/grid/edittemplate


Regards,

Gowthami V.

Hi,

Is there any way to change the popup width of the autocomplete in javascript?

In this case I have the autocomplete in a grid cell.

Thanks

EDIT:

Found it, 

Thanks anyway!

$("#autocomplete").ejAutocomplete({
		popupWidth: '152px' 
	});

Attachment: autocomplete_grid_a1d9f959.7z

[Sathyanarayanamoorthy Eswararao]

Hi Bruno, 

  

We are happy to hear that your issue has been solved. 

Please let us know if you need any further assistance. 

  

Regards, 

Sathyanarayanamoorthy