scan column and set values

scan column and set values

marwimarwi Posts: 33Questions: 9Answers: 0
edited June 2018 in Free community support

Hi

I would like to add a custom button on top of the table. When clicked, the script should scan the column "sequence" and re-number its values to "increment by 10", remaining the same order sequence as before.

Example
Before clicked:

description     sequence
------------------------
Anna                1
Bob                35
Cecily              2
Dora               13

After clicked:

description     sequence
------------------------
Anna               10
Bob                40
Cecily             20
Dora               30

Please note, if you sorted the table by column "sequence", both tables (before and after) would show the names in the same sequence (1. Anna, 2. Cecily, 3. Dora, 4. Bob).

Do you have any idea how to do this in Javascript?

buttons: [{ 
  text: 'Re-Number Sequence',
  action: function ( e, dt, node, config ) {
      seq_idx = $('#mytable').dataTable().api().columns().dataSrc().indexOf('sequence'); };
      pk_idx = $('#mytable').dataTable().api().columns().dataSrc().indexOf('sid'); };

      // Possible script logic:

      // Create JS object with row id and seq.
      // Order object items by seq
      // Set seq with new values, starting with 10, increment by 10
      // update data table with new seq values

  }
}]

Kind regards,
marwi

This question has an accepted answers - jump to answer

Answers

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,735

    Maybe the orderFixed would work for you to keep the table ordered by the description column. Or you could disable ordering for the sequence column using columns.orderable.

    You might be able to use rows().every() to iterate each row then row.data() to update the sequence column.

    Maybe you can give it a try first and if you need help provide a test case with your code:
    https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

    Kevin

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

    Another option is to use the ranking that Kevin did a while back - see the thread here. Once you've ranked that column, you can then apply the multiplication,

    Cheers,

    Colin

  • marwimarwi Posts: 33Questions: 9Answers: 0

    I have tried to use rows().every() but it seems as if the new set values are not saved to the table. Do you see how to fix?

    This is my script for the button:

    {
      text: 'Re-Number Sequence',
      action: function ( e, dt, node, config ) {
    
            // Create Array with seq and indexes, ordered by seq
            var arr_indexes = [];
            console.log('Initial Seq values:');
            this.rows().every(function(idx){ 
                console.log(idx + ' '+this.row(idx).data().dt_columns.db_column + ' '+this.row(idx).data().dt_columns.column_seq); 
                arr_indexes.push([this.row(idx).data().dt_columns.column_seq, idx]);
            });
            arr_indexes.sort(function(a, b){ return a[0]-b[0]; });
    
            // Set new seq, starting at 10 with increment 10
            console.log('\nChanges made to Seq values:');
            var new_seq = 10;
            for (i in arr_indexes) {
                console.log('row index '+arr_indexes[i][1]+' with old seq '+arr_indexes[i][0]+' should be updated to new seq '+(new_seq)+', db_column '+this.row(arr_indexes[i][1]).data().dt_columns.db_column);
                
                dt_columns_editor
                    .edit( arr_indexes[i][1], false )
                    .set( 'dt_columns.column_seq', new_seq )
                    .submit();
                
                new_seq += 10;
            }
    
            console.log('\nCheck result after changes:');
            for (i in arr_indexes) {
                console.log('row index '+arr_indexes[i][1]+', having new seq '+this.row(arr_indexes[i][1]).data().dt_columns.column_seq+', db_column '+this.row(arr_indexes[i][1]).data().dt_columns.db_column);
            }
      }
    }           
    

    After clicking the button, it seems not be working. The values of column "column_seq" are still the same as initially :neutral: This is the console output:

    Initial Seq values:
    0 dpk 10
    1 dhk 30
    2 details 31
    
    Changes made to Seq values:
    row index 0 with old seq 10 should be updated to new seq 10, db_column dpk
    row index 1 with old seq 30 should be updated to new seq 20, db_column dhk
    row index 2 with old seq 31 should be updated to new seq 30, db_column details
    
    Check result after changes:
    row index 0, having new seq 10, db_column dpk
    row index 1, having new seq 30, db_column dhk
    row index 2, having new seq 31, db_column details
    

    regards,
    marwi

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,735

    Looks like you are using the Editor to save the updated values to the database. Are the new values saved in the DB?

    If yes, then what information is returned from the server script? You can see the in the ajax response using the browser's developer tools.

                dt_columns_editor
                    .edit( arr_indexes[i][1], false )
                    .set( 'dt_columns.column_seq', new_seq )
                    .submit();
    

    Do you have an Editor field called dt_columns.column_seq for the set API?

    Kevin

  • marwimarwi Posts: 33Questions: 9Answers: 0

    Hi Kevin, yes I'd like to store the new values for field "column_seq" (there is a a field called dt_columns.column_seq) to the DB.
    According to XHR (DevTools Firefox) it only transmitts the first change (seq 10) but not all the three. The servers respond is normal, meaning the server scripts responds with the one record (seq 10) only. Maybe there is some logic issue in the javascript?

  • marwimarwi Posts: 33Questions: 9Answers: 0

    Further to my previous post, this is the corresponding server script, using DataTables default PHP library:

    <?php
    // DataTables PHP library and database connection
    include( "php/DataTables.php" );
    
    // Alias Editor classes so they are easy to use
    use
        DataTables\Editor,
        DataTables\Editor\Field,
        DataTables\Editor\Format,
        DataTables\Editor\Mjoin,
        DataTables\Editor\Options,
        DataTables\Editor\Upload,
        DataTables\Editor\Validate;
    
    // Build our Editor instance and process the data coming from _POST
    Editor::inst( $db, 'dt_columns', 'id' )
        ->fields(
            Field::inst( 'dt_columns.id' )->set(false),
            Field::inst( 'dt_columns.dt_table_id' )->options(Options::inst()->table('dt_tables')->value('id')->label('db_table'))->validator(Validate::dbValues(null, null, null, null, [ 0 ])),
            Field::inst( 'dt_columns.db_column' ),
            Field::inst( 'dt_columns.column_seq' )
        )->leftJoin('dt_tables','dt_tables.id','=','dt_columns.dt_table_id')
        ->process( $_POST )
        ->json();
    ?>
    

    Above script is just an extract, there are more fields, but I think this is not relevant at the moment.

    regards,
    marwi

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,735

    Interesting. Wonder if the problem is that the previous edit().submit() is still outstanding the next iteration through the loop. HAven't tried something like this but I could see where that would be a problem and the next edit().submit() wouldn't work.

    Maybe you need to structure the loop to pause until the submitComplete event fires. Here is a list of edit events in order:
    https://editor.datatables.net/manual/events#Edit

    Kevin

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin
    Answer ✓

    Spot on as usual Kevin. The issue is here:

            for (i in arr_indexes) {
                console.log('row index '+arr_indexes[i][1]+' with old seq '+arr_indexes[i][0]+' should be updated to new seq '+(new_seq)+', db_column '+this.row(arr_indexes[i][1]).data().dt_columns.db_column);
                 
                dt_columns_editor
                    .edit( arr_indexes[i][1], false )
                    .set( 'dt_columns.column_seq', new_seq )
                    .submit();
                 
                new_seq += 10;
            }
    

    Since the Ajax call is async, the loop will continue to run, triggering edit commands. Editor currently can only handle one edit request at a time, thus the issue.

    You could split it up and use submitComplete to trigger the next item in the loop, but I think you'd be better using multi-row editing so you can make all of the changes via a single Ajax request.

    Allan

  • marwimarwi Posts: 33Questions: 9Answers: 0

    Hi Alan, thanks for the hint with "multi-row editing", so I could implement it this way and it is working fine:

    {
      text: 'Re-Number Sequence', 
      action: function ( e, dt, node, config ) {
    
            // Create Array with seq and indexes, ordered by seq
    
            dt_columns_editor.edit( dt_columns_table.rows().indexes(), false );
            var vals = dt_columns_editor.field('dt_columns.column_seq').multiGet();
            var arr = new Array(); for (a in vals) { arr.push([a, vals[a]]); }
            arr.sort(function(a, b){ return a[1]-b[1]; });
            for (var i in arr) { dt_columns_editor.field('dt_columns.column_seq').multiSet(arr[i][0], ((1*i+1)*10)+""); }
            dt_columns_editor.submit();
    
      }
    }           
    

    Regards,
    Marwi

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    Perfect - thanks for posting back with your solution.

    Allan

This discussion has been closed.