Autocomplete - Display of Text, not ID

airmaster · September 2019

I am testing the simple (popup) based input form, and I have a field with autocomplete. How can I get it to display the associated text, rather than the ID of the field that is stored in the database?

            var editor; // use a global for the submit and return data rendering in the examples

            $(document).ready(function () {


                editor = new $.fn.dataTable.Editor({
                    ajax: "/winloss/join",
                    table: "#example",
                    fields: [{
                        label: "ID",
                        name: "tblWinLoss.ID",
                        type: "readonly"
 
                        }, {
                            label: "Customer", 
                            name: "tblWinLoss.CustomerID",
                            type: "autoComplete"

                                }
                        
                    
                    ]
                });

                $('#example').DataTable({
                    dom: "Bfrtip",
                    "pageLength": 25,
                    ajax: {
                        url: "/winloss/join",
                        type: 'POST'
                    },
                    columns: [
                        { data: "tblWinLoss.ID" },

                        { data: "Customers.CompanyName", editField: "tblWinLoss.CustomerID" },                        
                      
                    ],
                    order: [1, 'asc'],
                    select: true,
                    buttons: [
                        { extend: "create", editor: editor },
                        { extend: "edit", editor: editor },
                        { extend: "remove", editor: editor }
                    ]
                });
            });

colin · September 2019

Hi @airmaster ,

This thread might help. If not, could you link to a test case demonstrating the issue, please.

Cheers,

Colin

airmaster · September 2019

Here is my code for the controller

            using (var db = new DataTables.Database("sqlserver", connectionString))
            {
                var Response = new Editor(db, "tblWinLoss", "ID")
                    .Model<JoinWinLoss>("tblWinLoss")
                    .Model<JoinCompany>("Customers")
                    .Field(new Field("tblWinLoss.CustomerID")
                        .Options(new Options()
                            .Table("tblCompany")
                            .Value("ID")
                            .Label("CompanyName")
                        )
                    )
                    .LeftJoin("tblCompany as Customers", "Customers.ID", "=", "tblWinLoss.CustomerID")
                    .Process(request)
                    .Data();

                return Json(Response, JsonRequestBehavior.AllowGet);
            }

and view. So, I have a datatable which displays correctly, and when I use the popup to edit, the autocomplete displays the ID of the company, but not the name, which is what I want to do. The ID is stored in the database as a relation to the table of companies, of course.

        <script type="text/javascript" language="javascript" class="init">
            var editor; // use a global for the submit and return data rendering in the examples

            $(document).ready(function () {

                var output;
                editor = new $.fn.dataTable.Editor({
                    ajax: "/winloss/join",
                    table: "#example",
                    fields: [{
                        label: "ID",
                        name: "tblWinLoss.ID",
                        type: "readonly"
                        }, {
                            label: "Customer" ,
                            name: "tblWinLoss.CustomerID",
                            type: "autoComplete",
                        }                                          
                    ]
                });

                $('#example').DataTable({
                    dom: "Bfrtip",
                    "pageLength": 25,
                    ajax: {
                        url: "/winloss/join",
                        type: 'POST'
                    },
                    columns: [
                        { data: "tblWinLoss.ID" },
                        { data: "Customers.CompanyName", editField: "tblWinLoss.CustomerID" }
                    ],
                    order: [1, 'asc'],
                    select: true,
                    buttons: [
                        { extend: "create", editor: editor },
                        { extend: "edit", editor: editor },
                        { extend: "remove", editor: editor }
                    ]
                });
            });
        </script>
    }
</head>
<body>
    <table id="example" class="display" cellspacing="0" width="100%">
        <thead>
            <tr>
                <th>ID</th>
                <th>Customer</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <th>ID</th>
                <th>Customer</th>
            </tr>
        </tfoot>
    </table>
</body>

airmaster · September 2019

I did try what was suggested in your link, it didn't work. I don't know if I did anything wrong.

allan · September 2019

I find this to be a really odd decision by the jQuery UI team to show the value. I get why technically it is that way, but I've never encountered a case where you actually want to show the value rather than the label in the input element. If you aren't wedded to jQuery UI AutoComplete then it might be worth using another library such as Select2.

That said it is possible to do what you are looking for with jQuery UI has discussed in this SO thread.

In the case of Editor integration you can pass jQuery UI AutoComplete options to the field using its opts object - e.g.:

}, {
  label: "Customer" ,
  name: "tblWinLoss.CustomerID",
  type: "autoComplete",
  opts: {
    focus: function(event, ui) {
        event.preventDefault();
        $(this).val(ui.item.label);
    }
  }
}

Allan

airmaster · September 2019

Alan,

I can get it to now enter the name of the company with something similar

                    }, {
                        label: "Customer",
                         name: "tblWinLoss.CustomerID",
                        type: "autoComplete",
                            opts: {
                                select: function (event, ui) {
                                    event.preventDefault();
                                    $(this).val(ui.item.label);
                                },
                            }                            
                        }

But when I pull up a existing record, it displays the name, not the record number. Also, I can save the record, since it says that it can't update the identity column 'ID'. I need to find out how to make sure that datatables isn't trying to change the primary key. Then I also need to get it to display the name when when the popup box first shows.

allan · September 2019

Here is a demo using Select2: http://live.datatables.net/layonado/67/edit

I ended up using Select2 as it simply works. I honestly couldn't see how to get jQuery UI's AutoComplete to display the label at all times rather than the value. Which seems completely redundant as a select replacement to me, but there we go... Are you okay with the switch to Select2 - are you using jQuery UI's AutoComplete elsewhere in your code base?

In the demo I've got both the label and the value showing in the table - that's just to demonstrate that the value is correctly there. I've also got it logged the data to console so you can see it there as well.

Also, I can save the record, since it says that it can't update the identity column 'ID'.

I wouldn't recommending writing to the primary key at all. If that is happening then the field name is probably pointing at the primary key column rather than the column that references the pkey.

Could you show me the code for your two models and also the SQL schema for the two tables please?

Thanks,
Allan

airmaster · September 2019

Which field in your example is autocomplete? I see a dropdown, a date, currency, and two text fields. I use jQuery autocomplete quite a lot in the code, but I can use the Select2, maybe just for these views.

I can truncate down the SQL, but the tables are quite long, with multiple keys.

I don't know why its trying to write the primary key.

Models in my code

    public class JoinWinLoss
    {
        public int ID { get; set; }
        public string Title { get; set; }        
    }
    public class JoinCompany
    {
        public int ID { get; set; }
        public string CompanyName { get; set; }
    }

The SQL tblWinLoss


CREATE TABLE [dbo].[tblWinLoss](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](255) NULL,
    [CustomerID] [int] NULL,
    [WinningCompanyID] [int] NULL,
 CONSTRAINT [PK_tblWinLoss_1] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


ALTER TABLE [dbo].[tblWinLoss] CHECK CONSTRAINT [FK_tblWinLoss_tblCompany]
GO

ALTER TABLE [dbo].[tblWinLoss]  WITH CHECK ADD  CONSTRAINT [FK_tblWinLoss_tblCompany1] FOREIGN KEY([CustomerID])
REFERENCES [dbo].[tblCompany] ([ID])
GO

ALTER TABLE [dbo].[tblWinLoss] CHECK CONSTRAINT [FK_tblWinLoss_tblCompany1]
GO

tblCompany

CREATE TABLE [dbo].[tblCompany](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CompanyName] [nvarchar](255) NULL,
 CONSTRAINT [PK_tblCompany] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

My code for the view

<!DOCTYPE html>




<html>
<head>
    <meta charset="utf-8">
    <link rel="shortcut icon" type="image/ico" href="http://www.datatables.net/favicon.ico">
    <meta name="viewport" content="width=device-width, initial-scale=1, minimum-scale=1.0, user-scalable=no">
    <title>Editor example - Joined tables</title>
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css">
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.5.6/css/buttons.dataTables.min.css">
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/select/1.3.0/css/select.dataTables.min.css">
    <link href="~/Content/DatatablesEditor/editor.dataTables.min.css" rel="stylesheet" />

    <style type="text/css" class="init"></style>

    <link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/select2/4.0.2/css/select2.min.css" />


    <script type="text/javascript" language="javascript" src="https://cdnjs.cloudflare.com/ajax/libs/select2/4.0.2/js/select2.min.js"></script>
    <script type="text/javascript" language="javascript" src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
    <script type="text/javascript" language="javascript" src="https://cdn.datatables.net/buttons/1.5.6/js/dataTables.buttons.min.js"></script>
    <script type="text/javascript" language="javascript" src="https://cdn.datatables.net/select/1.3.0/js/dataTables.select.min.js"></script>
    <script src="~/Scripts/DatatablesEditor/dataTables.editor.min.js"></script>



    @section Scripts
{


        <script type="text/javascript" language="javascript" class="init">



            var editor; // use a global for the submit and return data rendering in the examples

            $(document).ready(function () {
                editor = new $.fn.dataTable.Editor({
                    ajax: "/winloss/join",
                    table: "#example",
                    fields: [{
                        label: "ID",
                        name: "tblWinLoss.ID",
                        type: "readonly"
                    }, {
                        label: "Title",
                        name: "tblWinLoss.Title",
                        type: "text"
                    }, {
                        label: "Winner",
                        name: "tblWinLoss.WinningCompanyID",
                        type: "select"
                    }, {
                        label: "Customer",
                        name: "tblWinLoss.CustomerID",
                        type: "autoComplete"
                        }
                    ]
                });
                $('#example').DataTable({
                    dom: "Bfrtip",
                    "pageLength": 25,
                    ajax: {
                        url: "/winloss/join",
                        type: 'POST'
                    },
                    columns: [
                        { data: "tblWinLoss.ID" },
                        { data: "tblWinLoss.Title", editField: "tblWinLoss.Title" },
                        { data: "Customers.CompanyName", editField: "tblWinLoss.CustomerID" },
                        { data: "Winners.CompanyName", editField: "tblWinLoss.WinningCompanyID" },
                    ],
                    order: [1, 'asc'],
                    select: true,
                    buttons: [
                        { extend: "create", editor: editor },
                        { extend: "edit", editor: editor,  },
                        { extend: "remove", editor: editor }
                    ]
                });
            });



        </script>
    }
</head>
<body>
    <table id="example" class="display" cellspacing="0" width="100%">
        <thead>
            <tr>
                <th>ID</th>
                <th>Title</th>
                <th>Customer</th>
                <th>Winner</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <th>ID</th>
                <th>Title</th>
                <th>Customer</th>
                <th>Winner</th>
            </tr>
        </tfoot>
    </table>

</body>

</html>

<script>
    (function ($, DataTable) {


        if (!DataTable.ext.editorFields) {
            DataTable.ext.editorFields = {};
        }

        var _fieldTypes = DataTable.Editor ?
            DataTable.Editor.fieldTypes :
            DataTable.ext.editorFields;

        _fieldTypes.autoComplete = {
            create: function (conf) {
                conf._input = $('<input type="text" id="' + conf.id + '">')
                    .autocomplete(conf.opts || {});

                return conf._input[0];
            },

            get: function (conf) {
                return conf._input.val();
            },

            set: function (conf, val) {
                conf._input.val(val);
            },

            enable: function (conf) {
                conf._input.autocomplete('enable');
            },

            disable: function (conf) {
                conf._input.autocomplete('disable');
            },

            canReturnSubmit: function (conf, node) {
                if ($('ul.ui-autocomplete').is(':visible')) {
                    return false;
                }
                return true;
            },

            owns: function (conf, node) {
                if ($(node).closest('ul.ui.autocomplete').length) {
                    return true;
                }
                return false;
            },

            // Non-standard Editor method - custom to this plug-in
            node: function (conf) {
                return conf._input;
            },

            update: function (conf, options) {
                conf._input.autocomplete('option', 'source', options);
            }
        };


    })(jQuery, jQuery.fn.dataTable);
</script>

Controller code

        public ActionResult Join()
        {
           connectionString = "xxx"
            using (var db = new DataTables.Database("sqlserver", connectionString))
            {
                var Response = new Editor(db, "tblWinLoss", "ID")
                    .Model<JoinWinLoss>("tblWinLoss")
                    .Model<JoinCompany>("Winners")
                    .Model<JoinCompany>("Customers")
                    .Field(new Field("tblWinLoss.WinningCompanyID")
                        .Options(new Options()
                            .Table("tblCompany")
                            .Value("ID")
                            .Label("CompanyName")
                        )
                    )
                    .Field(new Field("tblWinLoss.CustomerID")
                        .Options(new Options()
                            .Table("tblCompany")
                            .Value("ID")
                            .Label("CompanyName")
                        )
                    )
                    .LeftJoin("tblCompany as Winners", "Winners.ID", "=", "tblWinLoss.WinningCompanyID")
                    .LeftJoin("tblCompany as Customers", "Customers.ID", "=", "tblWinLoss.CustomerID")

                    .Debug(true)
                    .Process(request)
                    .Data();



                return Json(Response, JsonRequestBehavior.AllowGet);
            }
        }

allan · September 2019

Which field in your example is autocomplete? I

Its the "Office field" - I suppose its less auto complete in that example and more a filtering list for a select dropdown. I'll give jQuery UI auto complete another bash and get back to you - if you are using it elsewhere it makes sense to keep using it.

And thanks for the code! I think it might get getting upset about the ID readonly field. Its readonly in Javascript, but it still gets submitted to the server and thus attempted to be written.

Add this to your C# Editor code:

.Field( new Field("tblWinLoss.CustomerID").set(false) )

which should do it.

Regards,
Allan

allan · September 2019

This is the closest I've been able to get in the last hour of tinkering and googling with jQuery AutoComplete: http://live.datatables.net/layonado/20/edit .

It does actually work as needed when you start typing, but I haven't found a way to have it programmatically show the label when you set the value, so it looks rubbish when you first click edit! There are a ton of threads about this on the web, but I've not found a definitive answer about it, nor is it clear from the jQuery UI docs how this could be done.

You noted that you use jQuery UI AutoComplete elsewhere in your code base, how do you handle this there?

Thanks,
Allan

airmaster · September 2019

In most of my code, the ID is stored in a hidden field, and (in MVC form submit) is bound to the field. So, the ID and search are separate.


@Html.TextBox('searchCompany')

@Html.HiddenFor(model => model.WinLoss.CustomerID, new { id = "hiddenCustomerID" })

$("#searchCompany").autocomplete({
    source: '@Url.Action("GetCompanyAutoComplete", "WinLoss")',
    minLength: 3,
    select: function (event, ui) {
       $("#hiddenCustomerID").val(ui.item.id);
    }
    })

The problem with the Datatables editor is that the source of the autocomplete doesn't seem to be able to be separate from the ID. What I thought I could do was make tblWinLoss.CustomerID hidden, and then run the autocomplete in a separate field, but that didn't work, since it couldn't bind the autocomplete.

airmaster · September 2019

Well, I tried to be cute, and used the autocomplete API and that worked, but then I tried to set the returned value to the value of the element, but that didn't pass through to the controller:

            $(document).ready(function () {
                editor = new $.fn.dataTable.Editor({
                    ajax: "/winloss/join",
                    table: "#example",
                    fields: [{
                        label: "ID",
                        name: "tblWinLoss.ID",
                        type: "readonly"
                    }, {
                        label: "Title",
                        name: "tblWinLoss.Title",
                        type: "text"
                    }, {
                        label: "Winner",
                        name: "tblWinLoss.WinningCompanyID",
                        type: "select"
                    }, {
                        label: "tblWinLoss.CustomerID",
                        name: "tblWinLoss.CustomerID",
                        type: "hidden"
                    }, {
                        label: "Customer",
                        name: "Customers.CustomerName",
                        type: "autoComplete"
                        }
                    ]
                });
                $('#example').DataTable({
                    dom: "Bfrtip",
                    "pageLength": 25,
                    ajax: {
                        url: "/winloss/join",
                        type: 'POST'
                    },
                    columns: [
                        { data: "tblWinLoss.ID" },
                        { data: "tblWinLoss.Title", editField: "tblWinLoss.Title" },
                        { data: "Customers.CompanyName", editField: "Customers.CustomerName" },
                        { data: "Winners.CompanyName", editField: "tblWinLoss.WinningCompanyID" },
                    ],
                    order: [1, 'asc'],
                    select: true,
                    buttons: [
                        { extend: "create", editor: editor },
                        { extend: "edit", editor: editor,  },
                        { extend: "remove", editor: editor }
                    ]
                });
            });

Snippet of the autocomplete function

            create: function (conf) {
                conf._input = $('<input type="text" id="' + conf.id+ '">')
                    .autocomplete($.extend({}, conf.opts, {
                        source: '@Url.Action("GetCompanyAutoComplete", "WinLoss")',
                        select: function (event, ui) {
                            $('#DTE_Field_tblWinLoss.CustomerID').val(ui.item.id);
                        },
                    }));
                return conf._input[0];
            },

So, this fills in the customer name field properly with the result of the API call. It won't save, though, because the value of tblWinLoss.CustomerID is null (verified that through debug). I guess setting the value of ''DTE_Field_tblWinLoss.CustomerID" doesn't do much.

Also, when I try to open an existing record, CustomerName is empty.

The autocomplete returns a JSON with "id" and "label".

I wonder if somehow I could:
1. When the dialog box is opening, inject the name of the current customer into the customer field.
2. When the autocomplete is selected, inject the id of the current customer into tblWinLoss.CustomerID.

airmaster · September 2019

I may have figured it out, need to test more, but its getting late. I still haven't gotten it to have the name listed in the popup form when that loads.

                conf._input = $('<input type="text" id="' + conf.id+ '">')
                    .autocomplete($.extend({}, conf.opts, {
                        source: '@Url.Action("GetCompanyAutoComplete", "WinLoss")',
                        select: function (event, ui) {
                            editor.val('tblWinLoss.CustomerID', ui.item.id);
                        },
                    }));
                return conf._input[0];
            },

allan · September 2019

I still haven't gotten it to have the name listed in the popup form when that loads.

That's the bit that I just cannot get - going from the value to the label programmatically. Given that you are using Ajax for the source, I suspect that it will require an Ajax call to the server in the set function for the plug-in that will get the label for the selected value.

Allan

airmaster · September 2019

Why doesn't

columns: [
....
     { data: "Customers.CompanyName"},

...                    ],

Populate the box?

allan · September 2019

By "box" do you mean the auto-complete input?

In the above it looks like it should fill in the value of Customers.CustomerName into the the field of the same name. If that isn't happening try removing the type: 'autoComplete' to see if the issue is coming from there.

Note that if your user submitting that form, they could edit up editing the linked table's customer name column. Is that want you want? Rather than just selecting a different customer? If so, then you need to make sure that you also submit the primary key value of the Customers table in a hidden field.

Allan

airmaster · September 2019

Nope... changed the type to text, and then monitored the val, Title and tblWinLoss.CustomerID had a val, but Customers.CustomerName was null in the sete function (the log is so I can set a breakpoint). There must be something simple I am missing. Note that in the displayed table, there is a name for the Customer.

        _fieldTypes.text = {
            create: function (conf) {

            },

            set: function (conf, val) {
                console.log("hello");
            },
        }

airmaster · September 2019

Heh....had the name a bit off under the editor constructor. I will keep on trying, but I think we are close. One thing that bothers me is that putting the ajax request in the autocomplete is sort hardwiring the solution. Obviously I can put in some if-then statements for multiple autocompletes to trigger the right ajax call, but it may work ...we will see.

airmaster · September 2019

Okay, its working. Key lessons:

Autocomplete text field needs to be separate from the ID field. You need list the ID in the editor constructor, or it doesn't exist, preferably set it to type 'hidden'. The display field should be of the autocomplete type. Notice how I have the type as being autoCompleteCustomer? The reason why is I have a second autocomplete for the city, and its just easier make a separate function for each one.

 {
   label: "Customer",
   name: "Customers.CompanyName",
   type: "autoCompleteCustomer"                        
 }, {       
   label: "tblWinLoss.CustomerID",
   name: "tblWinLoss.CustomerID",
   type: "hidden"
}

In the DataTable constructor, you just need to put in the entry for the autocomplete string.

{ data: "Customers.CompanyName"},

Inside the create portion of the autoComplete, which is custom for each of of these, since you need a separate AJAX call, you need to set the hidden ID.

        _fieldTypes.autoCompleteCustomer = {
            create: function (conf) {
                conf._input = $('<input type="text" id="' + conf.id+ '">')
                    .autocomplete($.extend({}, conf.opts, {
                        source: '@Url.Action("GetCompanyAutoComplete", "WinLoss")',
                        select: function (event, ui) {
                            editor.val('tblWinLoss.CustomerID', ui.item.id);
                        }
                        }));
                return conf._input[0];
            },

            get: function (conf) {
                return conf._input.val();
            },

            set: function (conf, val) {
                conf._input.val(val);
            },

            enable: function (conf) {
                conf._input.autocomplete('enable');
            },

            disable: function (conf) {
                conf._input.autocomplete('disable');
            },

            canReturnSubmit: function (conf, node) {
                if ($('ul.ui-autocomplete').is(':visible')) {
                    return false;
                }
                return true;
            },

            owns: function (conf, node) {
                if ($(node).closest('ul.ui.autocomplete').length) {
                    return true;
                }
                return false;
            },

            // Non-standard Editor method - custom to this plug-in
            node: function (conf) {
                return conf._input;
            },

            update: function (conf, options) {
                conf._input.autocomplete('option', 'source', options);
            }
        };

colin · September 2019

Hi @airmaster ,

Thanks for reporting back, that will be useful,

Cheers,

Colin

INR · April 2020

Hi,

I have scenario to get 2 values from the datatable and pass to the MVC controller to get the customer details.

How we can get values from other values and pass to the Action ? also How do we make the action as POST Method ?

create: function (conf) {
conf._input = $('<input type="text" id="' + conf.id+ '">')
.autocomplete($.extend({}, conf.opts, {
source: '@Url.Action("GetCompanyAutoComplete", "WinLoss")',
select: function (event, ui) {
editor.val('tblWinLoss.CustomerID', ui.item.id);
}
}));
return conf._input[0];
},

INR · April 2020

managed to get the POST Method working, but I am unable to reference the editor.

i have nested data tables. the editor is enabled on inner datatable when the outer is expanded.

any idea how i can refer the innerTable in the autocomplete ?

INR · April 2020

thanks found other posts and managed to do it

allan · April 2020

Thanks for the update - good to hear you've got it working. I wonder if you would be willing to post your solution so others can benefit from it?

Thanks,
Allan

sharkowolf · January 2021

INR, please! POST correct way)

Autocomplete - Display of Text, not ID

Autocomplete - Display of Text, not ID

Answers

Howdy, Stranger!

Categories

DataTables

Autocomplete - Display of Text, not ID

Autocomplete - Display of Text, not ID

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables