Display CategoryName and sort by CategoryId

kingkahanu

I have this table:

And I want to sort the Category column, but the Category Name is appended to the ViewModel for this table and can't be used as the sorting data. But the CategoryId is included with the table data and needs to be used as the sorting data.

Here is an example of a column data JSON object:

{
    "Id":2,
    "Name":"Equal 800 ct",
    "ItemNumber":"317047",
    "CategoryId":1,
    "CategoryName":"Food \u0026 Refreshments",
    "Cost":9.8800
}

This is my datatables code to render the table:

        var table = $('#taskTable').DataTable({
            "bProcessing": true,
            "bServerSide": true,
            "sAjaxSource": "@Url.Action("GetSupplyItems")",
            "iDisplayLength": 100,
            "lengthMenu": [[50, 100, 200, -1], [50, 100, 200, "All"]],
            "columns": [
                { "data": "Id", "visible": false, "searchable": false },
                { "data": "Name", "visible": true, "searchable": true },
                { "data": "Cost", "visible": true, "searchable": true },
                { "data": "ItemNumber", "visible": true, "searchable": false },
                { "data": "CategoryName", "visible": true, "searchable": true },
                {
                    "className": 'edit-category-control',
                    "data": null,
                    "orderable": false,
                    "searchable": false,
                    "mRender": function (data, type, full) { return "<a href=\"#\" onclick=\"Common_Javascript.UI.MainContentArea().hide();Common_Javascript.UI.MainFormArea().load('@Url.Action("_Partial_Edit_SupplyItem")?Id=" + full.Id + "');Common_Javascript.UI.MainFormArea().show();return false;\"><i class=\"fa fa-pencil\"></i></a>"; }
                },
                {
                    "className": 'delete-item-control',
                    "data": null,
                    "orderable": false,
                    "searchable": false,
                    "mRender": function (data, type, full) { return '<i class=\"fa fa-trash\"></i>' }
                }

            ],
            "order": [[0, "asc"]],
            dom: '<"col-sm-7"l><"col-sm-5 custombuttonarea"Bf>rtip',
            buttons: []
        });

What do need to change to make this work?

Thanks.

kthorngren

You can use columns.orderData to tell Datatables to order the Category column by the Id column.

Kevin

kingkahanu

@kthorngren - that doesn't seem like it would work, but I don't have a hidden column for CategoryId. It's just another property in the JSON object for that row.

Isn't there a way in the Column object in the Columns array to say something like "displayName": [and the category name], "data": "CategoryId"?

kingkahanu

@kthorngren - Ok, I see what you mean. I tried it and it worked. Here's the fix:

var table = $('#taskTable').DataTable({
            "bProcessing": true,
            "bServerSide": true,
            "sAjaxSource": "@Url.Action("GetSupplyItems")",
            "iDisplayLength": 100,
            "lengthMenu": [[50, 100, 200, -1], [50, 100, 200, "All"]],
            "columns": [
                { "data": "Id", "visible": false, "searchable": false },
                { "data": "Name", "visible": true, "searchable": true },
                { "data": "Cost", "visible": true, "searchable": true },
                { "data": "ItemNumber", "visible": true, "searchable": false },
                { "data": "CategoryName", "visible": true, "searchable": true, "orderData": 7 },
                {
                    "className": 'edit-category-control',
                    "data": null,
                    "orderable": false,
                    "searchable": false,
                    "mRender": function (data, type, full) { return "<a href=\"#\" onclick=\"Common_Javascript.UI.MainContentArea().hide();Common_Javascript.UI.MainFormArea().load('@Url.Action("_Partial_Edit_SupplyItem")?Id=" + full.Id + "');Common_Javascript.UI.MainFormArea().show();return false;\"><i class=\"fa fa-pencil\"></i></a>"; }
                },
                {
                    "className": 'delete-item-control',
                    "data": null,
                    "orderable": false,
                    "searchable": false,
                    "mRender": function (data, type, full) { return '<i class=\"fa fa-trash\"></i>' }
                },
                {
                    "data": "CategoryId", "visible": false
                }
            ],
            "order": [[0, "asc"]],
            dom: '<"col-sm-7"l><"col-sm-5 custombuttonarea"Bf>rtip',
            buttons: []
        });

Thanks for the help.