Working with row reordering

Working with row reordering

dynasoftdynasoft Posts: 422Questions: 67Answers: 3

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();
    }
    

Answers

  • dynasoftdynasoft Posts: 422Questions: 67Answers: 3

    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().

  • allanallan Posts: 61,431Questions: 1Answers: 10,048 Site admin

    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

  • dynasoftdynasoft Posts: 422Questions: 67Answers: 3

    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)'
                }
            });
    
  • allanallan Posts: 61,431Questions: 1Answers: 10,048 Site admin

    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

  • dynasoftdynasoft Posts: 422Questions: 67Answers: 3

    Many thanks Allan. That worked.

  • FrankCFrankC Posts: 6Questions: 0Answers: 0

    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

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    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

  • FrankCFrankC Posts: 6Questions: 0Answers: 0
    edited 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.

  • FrankCFrankC Posts: 6Questions: 0Answers: 0

    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;
    }
    
  • FrankCFrankC Posts: 6Questions: 0Answers: 0
    edited 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.

  • FrankCFrankC Posts: 6Questions: 0Answers: 0

    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.

  • allanallan Posts: 61,431Questions: 1Answers: 10,048 Site admin

    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

  • FrankCFrankC Posts: 6Questions: 0Answers: 0

    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.

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    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

  • allanallan Posts: 61,431Questions: 1Answers: 10,048 Site admin

    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

This discussion has been closed.