Autocomplete Sort

[Chris]

I'm very new to Syncfusion (this is my first project) and I apologize if I missed something obvious.

I've created an autocomplete control that displays our clients.  Some of our clients have the entire first word of their name capitalized.  I have it set to sort ascending but it is doing a case sensitive sort.

For example, if I type in the letter "a" I get something like the following:
ASTRO Company
AZTEC Company
Abc Company
Access Company
Anvil Company
Atlas Company

Is is possible to make it do a case insensitive sort?  I would like to have it sorted like this:
Abc Company
Access Company
Anvil Company
ASTRO Company
Atlas Company
AZTEC Company

Thank You!

[Arun Palaniyandi]

 

Hi Chris Falk,   

   

Thanks for contacting Syncfusion Support.   

   

   

Based on your displayed result in the Autocomplete, we suspect that you are using Adaptors to bind the datasource for your control. Hence if we use the Adaptors to give the datasource for the controls, we need to do sorting, filtering, paging in the server side using LINQ or using DataManager API operations. Please find the below code snippet for more reference.  

 

 

 

CSHTML: 

 

<ej-autocomplete id="AutoComplete" allow-sorting="true" sort-order="Ascending" > 

    <e-datamanager id="DataAdaptor" adaptor="UrlAdaptor" url="Index/InlineSearch" /> 

    <e-autocomplete-fields key="id" text="title" /> 

</ej-autocomplete> 

 

 

Controller: 

 

      public JsonResult InlineSearch([FromBody]DataManager dm) 

        { 

 

            Syncfusion.JavaScript.DataSources.DataOperations operation = new Syncfusion.JavaScript.DataSources.DataOperations(); 

 

            lstResults.Add(new SearchResult() { Title = "ASTRO Company", ID = 1 }); 

            lstResults.Add(new SearchResult() { Title = "AZTEC Company", ID = 2 }); 

            lstResults.Add(new SearchResult() { Title = "Abc Company", ID = 3 }); 

            lstResults.Add(new SearchResult() { Title = "Access Company", ID = 4 }); 

            lstResults.Add(new SearchResult() { Title = "Anvil Company", ID = 5 }); 

            lstResults.Add(new SearchResult() { Title = "Atlas Company", ID = 6 }); 

            IEnumerable search = null; 

            IEnumerable Data = null; 

            if (dm.Where != null && dm.Where.Count > 0) //Filtering 

            { 

 

              search = from n in lstResults where n.Title.StartsWith(dm.Where[0].value.ToString().ToUpper()) select n; // Filtering  

 

            } 

 

            if (dm.Sorted != null && dm.Sorted.Count > 0) //Sorting 

            { 

                Data = operation.PerformSorting(search, dm.Sorted); // do sorting with the filtered result 

            } 

            return Json(Data); // return the sorted result 

        } 

 

    }    

 

 

 

Sorting data in server side in datamanager 

 

 

 

 

 

 

Filtering data in server side in datamanager 

 

 

 

 

 

Output: 

 

 

 

 

 

 

We have also prepared a sample below for your reference. 

 

https://www.syncfusion.com/downloads/support/forum/135708/ze/AutocompleteSearchInputs294344223 

 

 

Please find the below KB Link to find more information about the Server-side API for DataManager operations. 

 

https://www.syncfusion.com/kb/4300/server-side-api-for-datamanager-operations 

 

 

Please check the shared sample and details and let us know if you need any further assistance.   

   

Regards,
Arun P. 

[Chris]

Hi,

Sorry, it took so long to get back to you.  I really like how your example only queries the database as you type.

My autocomplete is actually inside a batch edit grid.  How would I have to change the sample to get it to work inside the grid?  Would it help if I posted some of the code I have so far?  (If so, how do you add a code block inside a post?)

Thanks,
Chris

[Arun Palaniyandi]

 

Hi Chris Falk,    

    

Thanks for your update. 

 

Based on your requirement of using the Autocomplete sorting inside Grid in Batch Editing, we have prepared a sample for your reference. To make the column as a Autocomplete use the Edit template property in the Grid write function to convert. Then bind the datamanager as datasource property to use the same URL and give it to the Autocomplete and also bind the fields property for it. Now it will work as you expected and the serverside filtering and sorting will work as shared before and use the condition to differentiate the return the JSON data for Autocomplete and for Grid initial loading. Please find the below code snippet for more reference.   

View: 

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

         .Datasource(ds => ds.URL("/Grid/UrlDataSource").UpdateURL("/Grid/UrlUpdate").InsertURL("/Grid/UrlInsert").RemoveURL("/Grid/UrlDelete") 

                     .Adaptor(AdaptorType.UrlAdaptor)) 

. 

. 

. 

. 

. 

 

        .Columns(col => 

        { 

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

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

. 

. 

. 

. 

. 

 

        })) 

</div> 

 

<script type="text/javascript"> 

    function create() { 

        return $("<input>"); 

    } 

 

    function write(args) { 

        var dataManager = ej.DataManager({ url: "/Grid/UrlDataSource", adaptor: "UrlAdaptor" }); 

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

    } 

. 

. 

. 

. 

. 

. 

</script> 

Controller 

public ActionResult UrlDataSource(DataManager dm) 

        { 

            IEnumerable DataSource = OrderRepository.GetAllRecords(); 

            DataOperations ds = new DataOperations(); 

            List<string> str = new List<string>(); 

            if (dm.Search != null && dm.Search.Count > 0) 

            { 

                DataSource = ds.PerformSearching(DataSource, dm.Search); 

            } 

 

            if (dm.Where != null && dm.Where.Count > 0) //Filtering 

            { 

                DataSource = ds.PerformWhereFilter(DataSource, dm.Where, dm.Where[0].Operator); 

                 

            } 

 

            if (dm.Sorted != null && dm.Sorted.Count > 0) //Sorting 

            { 

                DataSource = ds.PerformSorting(DataSource, dm.Sorted); 

                if(dm.Where[0].Operator == "startswith")  // return the sorted value of Autocomplete from here 

                return Json(DataSource); 

            } 

 

            var count = DataSource.Cast<EditableOrder>().Count(); 

            DataSource = ds.PerformSkip(DataSource, dm.Skip); 

            DataSource = ds.PerformTake(DataSource, dm.Take); 

 

            return Json(new { result = DataSource, count = count }); 

 

        } 

We have also prepared a sample below for your reference.  

https://www.syncfusion.com/downloads/support/forum/135708/ze/MvcApplication2-1638258181 

 

 

Please check the shared sample and details and if you still face any issue, share us your code or sample as told. This would be very helpful for us to provide you a prompt solution 

 

Regards, 

Arun P. 

[Chris]

Thank you so much for your help so far.  I think I am almost there.  Hopefully, this is the last time I have to ask for help on this. 

The autocomplete column is actually a foreign key value to another table.  I tried setting up two datasources.  One for the grid and one for the autocomplete column.  I got it partly working.  While I am typing in a cell in the autocomplete column it is querying the database and displaying the expected results.  (It's so cool that queries the db while you type)

The table the grid is based on is called "Project" and the table the autocomplete foreign key is based on is called "Contact".  Project has a field called "ClientId" which links to the "Id" field in the "Contact" table. There is already data in the two tables.

Here are the problems that I am facing:
1) When you first load the page the the autocomplete columns comes up with the foreign key values.  (not the client names)
2) If you set a cell in the autocomplete column to a client and then move out of the control it losses its value.

I suspect that I have done something wrong in the "e-columns" and in the javascript.

Here is my view:

ej-grid id="FlatGrid" allow-paging="true" enable-alt-row="true" action-failure="failure">
    <e-datamanager url="ProjectDataSource" batch-url="ProjectBatchUpdate" adaptor="UrlAdaptor" />
    <e-edit-settings allow-editing="true" allow-adding="true" edit-mode="@(EditMode.Batch)" show-confirm-dialog="false" />
    <e-toolbar-settings show-toolbar="true" toolbar-items="@(new List<string>() {"add","edit","update","cancel" })"></e-toolbar-settings>
    <e-columns>
        <e-column field="Id" is-primary-key="true" visible="false"></e-column>
        <e-column field="ProjectName" header-text="Project" validation-rules='new Dictionary<string, object>() { {"required",true} }'></e-column>
        <e-column field="ClientId" header-text="Client" foreign-key-field="Id" foreign-key-value="ContactName" validation-rules="@(new Dictionary<string, object>() { { "required", true } })">
            <e-edit-template create="create" read="read" write="write">
            </e-edit-template>
        </e-column>
    </e-columns>
</ej-grid>

<script type="text/javascript">
    function failure(args) {
        window.alert("Unable to update Projects");
    }
    function create() {
        return $("<input>");
    }

    function write(args) {       
        obj = $('#Edittemplate').ejGrid('instance');
        var dataManager = ej.DataManager({ url: "ClientDataSource", adaptor: "UrlAdaptor" });
       
        args.element.ejAutocomplete({
            width: "100%",
            dataSource: dataManager,
            enableAutoFill: true,
            watermarkText: "Select a client",
            fields: { text: "ContactName", Key: "Id" },
            value: args.rowdata !== undefined ? args.rowdata["ClientId"] : ""
        });
       
    }

    function read(args) {       
        args.ejAutocomplete('suggestionList').css('display', 'none');
        return args.ejAutocomplete("getValue");
    }
    $("#Edittemplate").keyup(function (e) {    
        if (e.keyCode == 40 && $(e.target).hasClass("e-autocomplete")) {
            var autocomp = $("#EdittemplateClientID").ejAutocomplete("instance")
            if (autocomp.getValue() != "" && autocomp.getActiveText() != "No suggestions")
                $(e.target).val(autocomp.getActiveText());
        }
    });
</script>

Here are the datasources from the controller:

       // datasource for the grid
        public IActionResult ProjectDataSource([FromBody]DataManager dm)
        {
            IEnumerable data = _projData;
            DataOperations operation = new DataOperations();
            if (dm.Sorted != null && dm.Sorted.Count > 0) //Sorting
            {
                data = operation.PerformSorting(data, dm.Sorted);
            }
            if (dm.Where != null && dm.Where.Count > 0) //Filtering
            {
                data = operation.PerformWhereFilter(data, dm.Where, dm.Where[0].Operator);
            }
            int count = data.Cast<Project>().Count();
            if (dm.Skip != 0)
            {
                data = operation.PerformSkip(data, dm.Skip);
            }
            if (dm.Take != 0)
            {
                data = operation.PerformTake(data, dm.Take);
            }

            var retVal = Json(new { result = data, count = count });
          
            return retVal;
        }

       // datasource for the autocomplete
        public JsonResult ClientDataSource([FromBody]DataManager dm)
        {
            Syncfusion.JavaScript.DataSources.DataOperations operation = new Syncfusion.JavaScript.DataSources.DataOperations();

            IEnumerable search = null;
            IEnumerable Data = null;

            if (dm.Where != null && dm.Where.Count > 0) //Filtering
            {
                search = from n in _clients where n.ContactName.ToUpper().StartsWith(dm.Where[0].value.ToString().ToUpper()) select new { n.Id, n.ContactName };
            }

            if (dm.Sorted != null && dm.Sorted.Count > 0 && search != null) //Sorting
            {

                Data = operation.PerformSorting(search, dm.Sorted);

            }

            return Json(Data);
        }

[Arun Palaniyandi]

 

Hi Chris,  

Thanks for your update. 

 

We have analyzed your query and we could see you would not bind the datasource for the column, So please bind the datasource to the column. 

 

We already created  KB to add the Autocomplete to the foreginKey column, So please refer the following link:  

 

https://www.syncfusion.com/kb/7137/how-to-use-autocomplete-control-for-foreignkey-column  

  

Regards,  

Arun P 

 

[Chris]

Hi,

Thank you for your response.  I got it working using the latest example you provided.  The only problem is that it loads all the possible values in the autocomplete field at the beginning.  I was hoping to have the autocomplete field query as you type and have it as a foreign key field in the grid. 

It works in this case as we have between 6000 and 7000 clients so it loads up fairly quickly.  I was thinking about a future grid that I'm going to have to make which will have over 200000 possible values in the foreign key autocomplete field.

Chris

[Arun Palaniyandi]

Hi Chris,  

 

Thanks for your update. 

 

According to your request you do not need to load all possible values in the AutoComplete at the beginning. In knowledgeBase documentation we have bound local data to the AutoComplete, so it load all the values at the beginning.   

 

If you need to fetch the data from server-side when you type on AutoComplete, we suggest you to bind remote data to the AutoComplete.   

 

Find the code example:   

 

 

<ej-grid id="FlatGrid" allow-paging="true">  

    <e-datamanager url="/Home/DataSource" adaptor="UrlAdaptor" />  

    <e-toolbar-settings show-toolbar="true" toolbar-items='@new List<string> {"add","edit","update","cancel","delete"}' />  

    <e-edit-settings allow-adding="true" allow-editing="true" allow-deleting="true" edit-mode="@(EditMode.Batch)"></e-edit-settings>  

    <e-columns>  

          

        <e-column field="EmployeeID" header-text="FirstName" width="80" foreign-key-field="EmployeeID" foreign-key-value="FirstName" datasource="@ViewBag.datasource">  

            <e-edit-template create="create" read="read" write="write"></e-edit-template>  

</e-column>  

  

    </e-columns>  

</ej-grid>  

  

<script id="template" type="text/javascript">  

    function create()  

        {  

          return $("<input>");  

        }  

    function write(args)  

    {  

        var dataManger = ej.DataManager({ url: "/Home/Data", adaptor: new ej.UrlAdaptor() });  

        // Query creation  

        var Query = ej.Query().select("EmplyeeID", "FirstName");  

        var val = ej.isNullOrUndefined(args.rowdata["EmployeeID"]) ? "" : args.rowdata["EmployeeID"];  

        args.element.ejAutocomplete({  

           dataSource: dataManger,  

            query: Query,  

            showPopupButton: true,  

            enableAutoFill: true,  

            fields: { text: "FirstName", key: "EmployeeID" },  

            width: "100%"  

        });  

       }  

    function read(args)  

       {  

        var data = args.ejAutocomplete("getSelectedItems")[0];  

  

        if (!data)  

            return {};  

        return data["EmployeeID"];//normal and other edit modes  

  

    }  

  

</script>  

  

Please let us know if you need any further assistance.

 

Regards,  

Arun P.