Working with row reordering

dynasoft · June 2019

HI

I'm trying to make reordering work but having problems. Clicking and dragging works but dragging moves the items for a second or two then the rows go back to where they were. The number under ReadingOrder seems to be set to the correct values though, only the rows are not ordered down numerically. Also how do I set the value for ReadingOrder when adding a new row via create().submit() api and or remove a row via row().remove(). Many thanks.

Refs:

<link rel="stylesheet" type="text/css" href="~/assets/scripts/editor-datatables/css/jquery.dataTables.min.css" />
<link rel="stylesheet" type="text/css" href="~/assets/scripts/editor-datatables/css/rowReorder.dataTables.min.css" />
<link rel="stylesheet" type="text/css" href="~/assets/scripts/editor-datatables/css/shCore.css" />
<link rel="stylesheet" type="text/css" href="~/assets/scripts/editor-datatables/css/buttons.bootstrap.min.css" />
<link rel="stylesheet" type="text/css" href="~/assets/scripts/editor-datatables/css/select.bootstrap.min.css" />
<link rel="stylesheet" type="text/css" href="~/assets/scripts/editor-datatables/css/dataTables.bootstrap.min.css" />
<link rel="stylesheet" type="text/css" href="~/assets/scripts/editor-datatables/css/editor.bootstrap.min.css" />

<script type="text/javascript" language="javascript" src="~/assets/scripts/editor-datatables/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" language="javascript" src="~/assets/scripts/editor-datatables/js/dataTables.buttons.min.js"></script>
<script type="text/javascript" language="javascript" src="~/assets/scripts/editor-datatables/js/buttons.bootstrap.min.js"></script>
<script type="text/javascript" language="javascript" src="~/assets/scripts/editor-datatables/js/dataTables.select.min.js"></script>
<script type="text/javascript" language="javascript" src="~/assets/scripts/editor-datatables/js/dataTables.rowReorder.min.js"></script>
<script type="text/javascript" language="javascript" src="~/assets/scripts/editor-datatables/js/shCore.js"></script>
<script type="text/javascript" language="javascript" src="~/assets/scripts/editor-datatables/js/dataTables.editor.min.js"></script>
<script type="text/javascript" language="javascript" src="~/assets/scripts/editor-datatables/js/dataTables.bootstrap.min.js"></script>
<script type="text/javascript" language="javascript" src="~/assets/scripts/editor-datatables/js/editor.bootstrap.min.js"></script>

JS:

function CRUDAdvFileField() {

    editor3 = new $.fn.dataTable.Editor({

        ajax: '/BillingFiles/CRUDAdvFileField/',
        table: '#tblFileFieldTable',
        fields: [ {
                label: 'id',
                name: 'id'
            }, {
                label: 'Order',
                name: 'ReadingOrder',
                fieldInfo: '@(lblo.lblFieldEditedClickDrag)'
            }, {
                label: 'FieldID',
                name: 'FieldID'
            }, {
                label: 'FieldType',
                name: 'FieldType'
            }
        ],
        i18n: {
            error: {
                system: '@(lblo.lblError5)'
            }
        }
    });

    var dataTable = $('#tblFileFieldTable').DataTable( {

        scrollY: '300px',
        scrollCollapse: true,
        paging: false,
        ordering: false,
        info: false,
        bFilter: false,
        order: [[0, 'desc']],
        columnDefs: [
            { 'bVisible': false, 'targets': 0 },
            { 'bVisible': false, 'targets': 2 },
            { 'orderable': false, 'targets': [ 1,2,3 ] }
        ],
        dom: 'Bfrtip',
        ajax: {
            url: '/BillingFiles/CRUDAdvFileField/',
            type: 'GET',
            async: true,
            dataType: 'json',
            contentType: 'application/json; charset=utf-8',
        },
        columns: [                    
            { data: 'id' },
            { data: 'ReadingOrder', className: 'reorder' },
            { data: 'FieldID' ,
                render: function ( data, type, row ) {
                    return data;
                }
            },
            { data: 'FieldType' ,
                render: function ( data, type, row ) {
                    strTp = '';
                    if (data != null)
                    {
                        if (data == '0')
                        {
                            strTp = '@(lblo.lblDefault)';
                        }
                        else
                        {
                            strTp = '@(lblo.lblUserDefined)';
                        }
                    }
                    return strTp;
                }
            }
        ],
        rowReorder: {
            dataSrc: 'ReadingOrder',
            editor3:  editor3
        },
        select: true,
           buttons: [
        ],
        language: {
            zeroRecords: '@(lblo.lblNoDataFound)',
            loadingRecords: '@(lblo.lblLoading)',
            emptyTable: '@(lblo.lblNoDataFound)'
        }
    });

    editor3
        .on( 'postCreate postRemove', function () {
            // After create or edit, a number of other rows might have been effected -
            // so we need to reload the table, keeping the paging in the current position
            dataTable.ajax.reload( null, false );
        } )
        .on( 'initCreate', function () {
            // Enable order for create
            editor3.field( 'ReadingOrder' ).enable();
        } )
        .on( 'initEdit', function () {
            // Disable for edit (re-ordering is performed by click and drag)
            editor3.field( 'ReadingOrder' ).disable();
        } );
}

Server:

public static DtResponse CRUDAdvFileField()
{
    string strTp = string.Empty;
    Editor editor = null;

        HttpRequest formData = System.Web.HttpContext.Current.Request;

        using (Database db = new Database(SetGetDbType2, SetGetDbConnection))
        {
            editor = new Editor(db, "AdvBillingFileCol").Model<BillingFilesAdvFileFieldDBModel>();
            editor.Field(new Field("id")
                .Set(false)
            );
            editor.Field(new Field("FieldID")
                .SetFormatter(Format.IfEmpty(null))                        
                .GetFormatter((val, host) => CommonUtilities.IsNullOrEmpty(val) == true ? 0 : val)
            );
            editor.Field(new Field("ReadingOrder")
                .SetFormatter(Format.IfEmpty(null))
                .Validator(Validation.Numeric())
            );
            editor.Field(new Field("FieldType")
                .SetFormatter(Format.IfEmpty(null))
            );
            editor.PreCreate += (sender, e) => e.Editor.Db()
                .Query("update", "AdvBillingFileCol")
                .Set("ReadingOrder", "ReadingOrder+1", false)
                .Where("ReadingOrder", e.Values["ReadingOrder"], ">=")
                .Exec();
            editor.PreRemove += (sender, e) =>
            {   // On remove, the sequence needs to be updated to decrement all rows beyond the deleted row. 
                //Get the current reading order by id (don't use the submitted value in case of a multi-row delete).
                var order = e.Editor.Db()
                    .Select("AdvBillingFileCol", new[] {"ReadingOrder"}, query => query.Where("id", e.Id))
                    .Fetch();

                e.Editor.Db()
                    .Query("update", "AdvBillingFileCol")
                    .Set("ReadingOrder", "ReadingOrder-1", false)
                    .Where("ReadingOrder", order["ReadingOrder"], ">")
                    .Exec();
            };
            editor.TryCatch(false);
            editor.Debug(true);
            editor.Process(formData);
            editor.Data();
        }

    return editor.Data();
}

dynasoft · June 2019

Please ignore this part of my post: Also how do I set the value for ReadingOrder when adding a new row via create().submit() api and or remove a row via row().remove().

allan · June 2019

ordering: false,

Remove this. The order of the rows in the DataTable is entirely down to the ordering of the data applied to the table. You can use columns.orderable set to false if you don't want to allow end users to modify the sorting. But RowReorder effectively depends upon the table sorting to display the data in the order you'd expect.

Allan

dynasoft · June 2019

Hi. Thanks but I still get the same issue. Here's my current JS code:

        editor3 = new $.fn.dataTable.Editor({

            ajax: '/BillingFiles/CRUDAdvFileField/',
            table: '#tblFileFieldTable',
            fields: [ {
                    label: 'id',
                    name: 'id'
                }, {
                    label: 'Order',
                    name: 'ReadingOrder',
                    fieldInfo: '@(lblo.lblFieldEditedClickDrag)'
                }, {
                    label: 'FieldID',
                    name: 'FieldID'
                }, {
                    label: 'FieldType',
                    name: 'FieldType'
                }
            ],
            i18n: {
                error: {
                    system: '@(lblo.lblError5)'
                }
            }
        });

        var dataTable = $('#tblFileFieldTable').DataTable( {

            scrollY: '300px',
            scrollCollapse: true,
            paging: false,
            info: false,
            bFilter: false,
            order: [[0, 'asc']],
            columnDefs: [
                { 'bVisible': false, 'targets': 0 },
                { 'bVisible': false, 'targets': 2 },
                { 'bVisible': false, 'targets': 4 },
                { 'orderable': false, 'targets': [ 1,2,3,4,5,6 ] }
            ],
            dom: 'Bfrtip',
            //ajax: {
            //    url: '/BillingFiles/CRUDAdvFileField/',
            //    type: 'GET',
            //    async: true,
            //    dataType: 'json',
            //    contentType: 'application/json; charset=utf-8',
            //},
            ajax: '/BillingFiles/CRUDAdvFileField/',
            columns: [
                { data: 'id' },
                { data: 'ReadingOrder', className: 'reorder' },
                { data: 'FieldID' },
                { data: 'FieldType' }
            ],
            rowReorder: {
                dataSrc: 'ReadingOrder',
                editor:  editor3
            },
            select: true,
               buttons: [
            ],
            language: {
                zeroRecords: '@(lblo.lblNoDataFound)',
                loadingRecords: '@(lblo.lblLoading)',
                emptyTable: '@(lblo.lblNoDataFound)'
            }
        });

allan · June 2019

order: [[0, 'asc']],

Which is id. But dataSrc: 'ReadingOrder', for RowReorder (which is column index 1).

So its sorting by id, but data swapping on ReadingOrder. Try:

order: [[1, 'asc']]

Allan

dynasoft · June 2019

Many thanks Allan. That worked.

FrankC · June 2019

I am having a similar issue, but I can't seem to resolve it based on what I've read in the Docs and this thread. I added a select checkbox as my first column, so my "rank" column is now the second in the table. I updated the order attribute to order on column index 1, and I have rowReorder set to:
rowReorder: { dataSrc: 'rank', editor: editor, enable: true, selector: '.reorder', update: true, formOptions: { submit: 'allIfChanged' } },
I would like the rank column to automatically update so that the new rank can be updated in the database.

Referenced this in the docs: https://datatables.net/reference/option/rowReorder.update

colin · June 2019

Hi @FrankC ,

Have you followed the diagnostic paths in this thread - as it was resolved here for the OP? If so, and it's still an issue, could you post your client and server code, please, as the OP did.

Cheers,

Colin

FrankC · June 2019

Hi @colin, i just saw your response. I still have not had luck. I have found what was preventing datatables from updating the ranks, but when it saves to the database the ranks aren't updated, and some data is lost. I haven't explored into that issue yet as I want to figure out why two lines of code are preventing datatables from sorting. When the on('row-reorder') action fires, the edit event doesn't fire unless I comment out these two lines of code.

    editor.field('underwriterKey').update(uwNameOptions);
    editor.field('underwriterTypeKey').update(uwTypeOptions);

Code is too long to include in one post, so i will break out into multiple comments.

FrankC · June 2019

JS Code:
// Global Variables
var editor;
var uwTypeOptions = [];
var uwNameOptions = [];
let NUM_ROWS = 0;
let API_URL = '/api/underwriterrelationship';

$(document).ready(function () {
    // Editor Initialization 
    editor = new $.fn.dataTable.Editor({
        //ajax: API_URL,
        ajax: {
            create: {
                type: 'POST',
                url: '/api/underwriterrelationship',
                contentType: 'application/json',
                data: function (data) {
                    console.log('create row ajax is running');
                    let postData = prepareDataToPost(data);
                    let dataToPost = JSON.stringify(postData);
                    return dataToPost;
                }
            },
            edit: {
                type: 'PUT',
                url: '/api/underwriterrelationship',
                contentType: 'application/json',
                data: function (data) {
                    console.log('edit row ajax is running');
                    let postData = prepareDataToPost(data);
                    let dataToPost = JSON.stringify(postData);
                    return dataToPost;
                }
            },
            remove: {
                method: 'DELETE',
                url: 'api/underwriterrelationship',
                dataType: 'json',
                beforeSend: function (request) {
                    request.setRequestHeader('content-type', 'application/json');
                },
                data: function (data) {
                    console.log('delete row ajax is running...');
                    let postData = prepareDataToPost(data);
                    let dataToPost = JSON.stringify(postData);
                    return dataToPost;
                }
            }
        },
        table: '#tblUnderwriters',
        idSrc: 'ipoUnderwriterKey', // primary key
        fields: [
            {
                label: 'Rank',
                name: 'rank',
                fieldInfo: 'This field can only be edited via click and drag row reordering.'
            },
            {
                label: 'Underwriter Name',
                name: 'underwriterKey',
                type: 'select'
            },
            {
                label: 'Underwriter Type',
                name: 'underwriterTypeKey',
                type: 'select'
            },
            {
                label: 'IPO Underwriter Key',
                name: 'ipoUnderwriterKey',
                type: 'readonly'
            },
            {
                label: 'IPO Key',
                name: 'ipoKey',
                type: 'readonly'
            }
        ],
        formOptions: {
            inline: {
                submit: 'allIfChanged'
            }
        }
    });

    editor.on('open', function (e, json, data) {
        //console.log('form is opened.');
        // Select2 dropdown lists
        $('#DTE_Field_underwriterKey').select2({
            placeholder: 'Select an Underwriter',
            allowClear: true
        });
        $('.select2-container').removeAttr('style');
    });

    editor.on('postCreate', function (e, json, data, id) {
        console.log('postCreate function called.');
         // After create or edit, a number of other rows might have been effected -
         // so we need to reload the table, keeping the paging in the current position
        uwTable.ajax.reload(null, false);
        // increment the number of rows variable after the new row has been added to the table
        NUM_ROWS++;
    });
    editor.on('postRemove', function (e, json, ids) {
        console.log('postRemoe function is called.');
        // After create or edit, a number of other rows might have been effected -
        // so we need to reload the table, keeping the paging in the current position
        uwTable.ajax.reload(null, false);
        NUM_ROWS--;
    });

    editor.on('postSubmit', function (e, json, data, action, xhr) {
        console.log('postSubmit function called');
        if (json !== null) {
            let status = json.status;
            console.log('Submit status is: ' + status);
            if (status !== undefined && status !== 200) {
                alert(json.title);
            }
            console.log(json);
        }
    });
    editor.on('preEdit', function (e, json, data, id) {
        console.log('preEdit function called');
    });
    editor.on('initCreate', function (e) {
        console.log('initCreate function called.');
    });
    editor.on('create', function (e, json, data) {
        console.log('create function called.');
    });


    // DataTable Initialization
    var uwTable = $('#tblUnderwriters').DataTable({
        dom: 'Bfrtip',
        ajax: {
            url: '/api/underwriterrelationship/' + $('#InfoForPage_IPOKey').val(),
            dataSrc: function (json) {
                console.log('data is loading into the table...');
                uwTypes = json.underwriterDropdowns.underwriterTypeDD;
                uwNames = json.underwriterDropdowns.underwriterDD;
                var option = {};
                $.each(uwNames, function (i, e) {
                    option.label = e.text;
                    option.value = e.value;
                    uwNameOptions.push(option);
                    option = {};
                });
                $.each(uwTypes, function (i, e) {
                    option.label = e.text;
                    option.value = e.value;
                    uwTypeOptions.push(option);
                    option = {};
                });
                editor.field('underwriterKey').update(uwNameOptions);
                editor.field('underwriterTypeKey').update(uwTypeOptions);
                NUM_ROWS = json.ipoUnderwriterRelationships.length;
                return json.ipoUnderwriterRelationships;
            }
        },
        order: [[1, 'asc']],
        columns: [
            {
                data: null,
                defaultContent: '',
                className: 'select-checkbox',
                orderable: false
            },
            {
                data: 'rank',
                className: 'reorder rank'
            },
            {
                data: 'underwriterKey',
                render: function (data, type, row) {
                    if (type === 'display') {
                        return row.underwriterName;
                    }
                    return data;
                }
            },
            {
                data: 'underwriterTypeKey',
                render: function (data, type, row) {
                    if (type === 'display') {
                        return row.underwriterTypeName;
                    }
                    return data;
                }
            },
            {
                data: 'ipoUnderwriterKey',
                className: 'd-none'
            },
            {
                data: 'ipoKey',
                className: 'd-none'
            }
        ],
        columnDefs: [
            {
                orderable: false,
                //target: [1, 2, 3, 4, 5]
                target: [0, 2, 3, 4, 5]
            }
        ],
        rowReorder: {
            enable: true,
            dataSrc: 'rank',
            editor: editor,
            selector: '.reorder'
        },
        select: {
            style: 'os',
            selector: 'td:first-child'
        },
        buttons: [
            { extend: "create", editor: editor },
            { extend: "edit", editor: editor },
            { extend: "remove", editor: editor }
        ],
        bFilter: false,
        autoWidth: true,
        keys: {
            columns: ':not(:first-child)',
            keys: [9],
            editor: editor,
            editOnFocus: true
        },
        bPaginate: false
    });

    uwTable.on('row-reorder', function (e, details, edit) {
        console.log('row-reoder function called');
        editor
            .edit(edit.nodes, false, {
                submit: 'allIfChanged'
            })
            .multiSet(edit.dataSrc, edit.values)
            .submit();
    });
});

function prepareDataToPost(data) {
    let postData = []
    $.each(data, function (key, data_element) {
        if (key === 'data') {
            $.each(data_element, function (key, value) {
                //CURRENT_KEY = key;
                //console.log('key = ' + key);
                //console.log('value = ' + value);
                postData.push(value);
            });
        }
    });
    //console.log('postData is:');
    //console.log(postData);
    return postData;
}

FrankC · June 2019

C# WebAPI Controller Action Code for Updates

       [HttpPut]
            public async Task<ActionResult<DataTablesReturnObj>> Update(IPOUnderwriterRelationship[] data)
            {
                DataTablesReturnObj returnData = new DataTablesReturnObj();
                if (ModelState.IsValid)
                {
                    try
                    {
                        IPOUnderwriterRelationship[] dataArr = new IPOUnderwriterRelationship[data.Length];
                        for (int i = 0; i < data.Length; i++)
                        {
                            await _service.UpdateUnderwriterRelationship(data[i]);
                            var newUwr = await _service.GetUnderwriterRelationship(data[i].IPOUnderwriterKey);
                            // The returned data needs to be formated as an array
                            dataArr[i] = newUwr;
                        }
                        returnData.Data = dataArr;
                    }
                    catch (Exception e)
                    {
                        //return BadRequest(new { message = e.Message });
                        returnData.Error = e.Message;
                    }
                    return returnData;
                }
                returnData.Error = "ModelState is not valid.";
                return returnData;
            }

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

FrankC · June 2019

I am adding, on the 'row-reorder' function, the line for .multiSet(edit.dataSrc, edit.values), edit.dataSrc = "rank" and edit.values = undefined:2.

The columns in my data table are the datatables checkboxes in first column, rank column, and then 2 columns with dropdown lists using the select2 library to make the dropdowns into comboboxes.

allan · June 2019

These two lines:

editor.field('underwriterKey').update(uwNameOptions);
editor.field('underwriterTypeKey').update(uwTypeOptions);

in initComplete are causing Editor to not submit on a row reorder?

I fear this is something we really will need an example for. Editor will work on http://live.datatables.net - here is a basic editing example: http://live.datatables.net/nuvihosi/1/edit . Or even better, if there is a way to send me a link to your page, that would be great!

Thanks,
Allan

FrankC · June 2019

Yes @allan, when those two lines are included in the data table initialization (under the ajax dataSrc property), then after the row-reorder function runs, the editor ajax functions do not run.

colin · June 2019

Hi @FrankC ,

Would you be able to link to your page or modify that test case that Allan posted, without seeing the issue it'll be hard to progress,

Cheers,

Colin

allan · June 2019

Try dropping this code:

    uwTable.on('row-reorder', function (e, details, edit) {
        console.log('row-reoder function called');
        editor
            .edit(edit.nodes, false, {
                submit: 'allIfChanged'
            })
            .multiSet(edit.dataSrc, edit.values)
            .submit();
    });

You are using rowReorder.editor so there should be no need to trigger Editor's edit on row reordering yourself. This example shows that.

Allan

Working with row reordering

Working with row reordering

Answers

Howdy, Stranger!

Categories

DataTables

Working with row reordering

Working with row reordering

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables