Populating one dropdown based on selection in another

agarn

var oEditor = new $.fn.dataTable.Editor({
                ajax: {
                    create: {
                        type: 'POST',
                        url: '@Url.Action("AddData")',
                        contentType: 'application/json',
                        dataType: 'json',
                        data: customizePostedData
                    },
                    edit: {
                        type: 'POST',
                        url: '@Url.Action("UpdateData")',
                        contentType: 'application/json',
                        dataType: 'json',
                        data: customizePostedData
                    },
                    remove: {
                        type: 'POST',
                        url: '@Url.Action("DeleteData")',
                        contentType: 'application/json',
                        dataType: 'json',
                        data: customizePostedData
                    },
                    upload: {
                        type: 'POST',
                        contentType: 'application/json',
                        dataType: 'json',
                        url: '@Url.Action("UploadFile")',
                        complete: function (jqXHR, textStatus) {
                            console.log("Image upload complete. Status: " + textStatus);
                            lastImageUploadTimeStamp = Math.round(Date.now() / 10000);
                        },
                        error: function (jqXHR, textStatus) {
                            console.log("Image upload error. Status: " + textStatus);
                        },
                        cache: false
                    }

                },
                table: "#myDataTable",
                display: 'bootstrap',
                idSrc: 'id',
                fields: [{
                    label: "Id:",
                    name: "id",
                    type: "hidden"
                }, {
                    label: "Category Name:",
                    name: "categoryName",
                    type: "select",
                    theme: "bootstrap",
                    options: [
                       @foreach(var myCategory in (List<string>)ViewData["CategoryList"])
                       {
                           @: { label: "@myCategory", value: "@myCategory" },
                       }
                    ],
                    opts: {
                        placeholder: "Select a category",
                        allowClear: false,
                        multiple: false,
                    }
                }, {
                    label: "Subcategory Name:",
                    name: "subCategoryName",
                    id: "subCategoryName",
                    type: "select",
                    theme: "bootstrap",
                    options: [
                       @foreach(var mySubCategory in (List<string>)ViewData["SubCategoryList"])
                       {
                           @: { label: "@mySubCategory", value: "@mySubCategory" },
                       }
                    ],
                    opts: {
                        placeholder: "Select a category",
                        allowClear: false,
                        multiple: false,
                    }
                }, {
                    label: "Security Product:",
                    name: "securityProduct",
                    id: "securityProduct",
                    type: "select",
                    theme: "bootstrap",
                    options: [
                       @foreach (KeyValuePair<string, List<SelectListItem>> subCatgeories in (Dictionary<string, List<SelectListItem>>)ViewData["SecurityProduct_SC"])
                       {
                           foreach(SelectListItem securityProduct in subCatgeories.Value)
                           {
                                @: { label: "@securityProduct.Text", value: "@securityProduct.Text" },
                            }
                        }
                    ],
                    opts: {
                        placeholder: "Select a security product",
                        allowClear: false,
                        multiple: false,
                    }
                }
                    ]
                }
                ]
            });

var oTable = $('#myDataTable').DataTable({
                "sPaginationType": "full_numbers",
                "bProcessing": true,
                "bServerSide": true,
                "bJQueryUI": false,
                "aLengthMenu": [[10, 25, 50, 100, -1], [10, 25, 50, 100, "All"]],
                "iDisplayLength": 50,
                "autoWidth": false,
                "sDom": 'lBfrt<"tableFooter"ip>',
                "ajax": {
                       "type": "POST",
                    "url": '@Url.Action("GetDataTables")',
                },
                "oLanguage": {
                    "sSearch": "Search all columns:"
                },
               "columns": [
                   { "name": "SelectBox", "data": "selectBox", "id": "SelectBox", "defaultContent": '', "class": 'select-checkbox', "orderable": false, "searchable": false, "width": "2%" },
                   { "name": "Id", "data": "id", "title": "Id", "orderable": false, "searchable": false, "visible": false, "editField": "id"},
                   { "name": "CategoryName", "data": "categoryName", "title": "Category", "orderable": true, "searchable": true, "visible": true, "width": "6%", "editField": "categoryName" },
                   { "name": "SubCategoryName", "data": "subCategoryName", "title": "Subcategory", "orderable": true, "searchable": true, "visible": true, "width": "6%", "editField": "subCategoryName" },
                   { "name": "SecurityProduct", "data": "securityProduct", "title": "Security Product", "orderable": true, "searchable": true, "visible": true, "width": "6%", "editField": "securityProduct" },
               ],

               buttons: [
                   {
                       extend: "create", editor: oEditor, formTitle: "Create new ancillary item"
                       }
                   },
                   { extend: "edit", editor: oEditor },
                   {
                       extend: "remove",
                       editor: oEditor,
                        formMessage: function (e, dt) {
                            var rows = dt.rows(e.modifier()).data().pluck('id');
                            var ids = rows.join(',');
                            var rowCount = rows.length;
                            var queryMsg;
                        }
                   }
                ],
               select: {
                   style: 'os',
                   selector: 'td:first-child'
               },
               "order": [1, "asc"],
               initComplete: function () {
                   // Category Name
                   var categoryNameColumn = this.api().column("CategoryName:name");
                   var categoryNameSelect = $('<select class="searcSelectPicker" data-live-search="true"><option value="">All</option></select>')
                       .appendTo($(categoryNameColumn.footer()).empty())
                       .on('change', function () {
                           var val = $.fn.dataTable.util.escapeRegex($(this).val());
                           categoryNameColumn.search(val ?  val  : '', true, false).draw();
                       });

                   @foreach (var gc in (List<string>)ViewData["CategoryList"])
                   {
                       <text>categoryNameSelect.append('<option value="@gc">@gc</option>');</text>
                   }

                   //Sub category Name
                   var subCategoryNameColumn = this.api().column("SubCategoryName:name");
                   var subCategoryNameSelect = $('<select class="searcSelectPicker" data-live-search="true"><option value="">All</option></select>')
                       .appendTo($(subCategoryNameColumn.footer()).empty())
                       .on('change', function () {
                           var val = $.fn.dataTable.util.escapeRegex($(this).val());
                           subCategoryNameColumn.search(val ? val : '', false, false).draw();
                       });
                   @foreach (var sc in (List<string>)ViewData["SubCategoryList"])
                   {
                       <text>subCategoryNameSelect.append('<option value="@sc">@sc</option>');</text>
                   }

                   //Security Product category
                   var securityProductColumn = this.api().column("SecurityProduct:name");
                   var securityProductSelect = $('<select class="searcSelectPicker" data-live-search="true"><option value="">All</option></select>')
                       .appendTo($(securityProductColumn.footer()).empty())
                       .on('change', function () {
                           var val = $.fn.dataTable.util.escapeRegex($(this).val());
                           securityProductColumn.search(val ? val : '', false, false).draw();
                       });
                   @foreach (var sp in (List<string>)ViewData["SecurityProductList"])
                   {
                       <text>securityProductSelect.append('<option value="@sp">@sp</option>');</text>
                   }

                   $('.searcSelectPicker').selectpicker();
                }
            });

So I have to populate product categories based on sub categories. Not sure how to get subcategory name/id in fields. Please help!

colin

Hi @agarn ,

I don't quite follow, but this example may help. Here, the dropdowns are being adjusted to reflect the current available options based on the other choices. Is this what you mean? If not, would you be able to give more details, please.

Cheers,

Colin

agarn

Thanks colin for trying!

Actually I found the below article useful and perfect to what I desire to do:
https://datatables.net/blog/2017-09-01

But it seems like that this uses MVC API and I am using Asp.NET MVC4 so I am not able to understand how to convert methods he has described.

allan

The code you show above is effectively static (it gets rendered by the server-side, into static Javascript the browser will execute). So it won't work quote that way for a dependent list.

What you need to do is have the client-side make an Ajax call to the server when the top level list value is updated (can be done with dependent()) and then have the server return the corresponding data for the child list. That's different from what you have above where all of the options are populated up front.

The key thing is to be able to make an Ajax call to the server and get JSON back. The Editor manual has some basic information about that - use that kind of controller (assuming you are using MVC in that way) and use return Json(...) with the options needed.

Allan

agarn

Thanks Allan. Let me try converting my code.

agarn

Hi allan,

Do you have nay clue as to how can I return Json in the following format at server side:

{
    "options": {
        "country" [
            ...
        ]
    }
}

The code that I am using for binding dropdwon is as follows

public ActionResult ProductOptions()
        {
            var request = System.Web.HttpContext.Current.Request;
            string subcategoryName = request.Params["values[subCategoryName]"];

            using (EApplication db = new EApplication ())
            {
                var sc = db.SubCategories.FirstOrDefault(c =>c.SubCategory == subcategoryName);
                int subCatgeoryId = sc.Id;
                var query = db.ProductCategories.Where(c => c.SubCategoryId == subCatgeoryId).Select(c => c.Product).ToList();

                return Json(query);
            }
        }

I can see that query gets populated successfully with correct product options to be shown based on categories but unfortunately I don;t see output on screen. I believe its because the data returned is not in format mentioned above.

Thanks

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

have a look here to see how I've done it before.

There are loads of other ways to do it I believe, like creating a class which has that required structure and then passing it through Newtonsoft JSON.NET, but I found that to be on of the easiest options! I should note that I'm a JS dev far more than a .NET one!

Allan

agarn

Oh, I got it. Thanks Allan. Let me try using other means somehow.

agarn

Hi,

I am doing the following to fill my dynamic select product based on subcategory:

oEditor.dependent('subCategoryName', function (val) {
            $.ajax({
                type: 'GET',
                url: '//SPOptions?subcategoryName=' + val,
                dataType: 'json',
                data: { securityProduct: $("#securityProduct").val() },
                error: function ( textStatus, errorThrown) {
                    alert("Status: " + textStatus + "\n\nError: " + errorThrown);
                },
                success: function (data) {
                    $("#securityProduct").html("");
                    $("#securityProduct").append('<option value="-Select one (optional--">-- Select one (optional) --</option>');
                        $.each(data, function (i, itemData) {
                            $("#securityProduct").append('<option value="' + itemData + '">' + itemData + '</option>');
                        });
                }
            });

Select is filled and options are changing dynamically BUT the issue is that on Add/Edit button of datatable, product is returned as null always.

JSON returned on click of Add/Edit button is as follows:
{"action":"create","data":{"0":{"id":"","subCatgeoryName":"Audio","securityProduct":null}}}

Can anybody help as to why data is returned as null?

allan

Is #securityProduct an Editor select input? If so, you need to use its field().update() method to update it, not direct HTML modification.

Allan

agarn

Thanks a lot Allan for all the help.
What worked for me is:

1)
In cshtml page when we declare datatable I used following for the column to be dynamically populated:

{
                label: "Security Product:",
                name: "securityProduct",//used in field as a parameter
                id: "securityProduct",
                type: "select",
                theme: "bootstrap",
                options: [],
                opts: {
                    placeholder: "Select a security product",
                    allowClear: false,
                    multiple: false,
                }
}

2)
After completion of editor declarations and definitions, i.e,
oEditor = new $.fn.dataTable.Editor ({ /all code here/ });
use editor.dependent as follows:

Note: subCategoryName is the field based on whose Id, name you want the other to get populated. I want security product to be populated based on sub category

oEditor.dependent('subCategoryName', function (val) {
            var optionsSP = [];
            $.ajax({
                type: 'GET',
                url: '@Url.Action("SecurityProductOptions", "Item")?subcategoryName=' + val,
                dataType: 'json',
                data: { subcategoryName: $("#subcategoryName").val() },
                error: function (textStatus, errorThrown) {
                    alert("Status: " + textStatus + "\n\nError: " + errorThrown);
                },
                success: function (data) {
                    var option = {};
                    option.label = "-- Select one (optional) --";
                    option.value = "-- Select one (optional) --";
                    optionsSP.push(option);
                    option = {};
                    $.each(data, function (i, itemData) {
                        option.label = itemData;
                        option.value = itemData;
                        optionsSP.push(option);
                        option = {};
                    });
                }
            }).done(function () {
                oEditor.field('securityProduct').update(optionsSP);
            });
        });

3) At server side

public ActionResult SecurityProductOptions(string subcategoryName)
        {
                var securityProducts = db.tablename.Where(/* condition here */).Select(/* column here */).ToList(); //new { label = c.SecurityProduct, value = c.SecurityProduct });
                return Json(securityProducts, JsonRequestBehavior.AllowGet);
            }
        }

Everything worked - new, edit, update, delete, etc. Infact in bubble editing too the selected value appeared.

allan

Nice one - thanks for posting back!

Allan