scan column and set values

marwi

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

kthorngren

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

colin

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

marwi

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 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

kthorngren

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

marwi

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?

marwi

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

```php
<?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();

<?php > ``` ?>

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

regards,
marwi

kthorngren

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

allan

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

marwi

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

allan

Perfect - thanks for posting back with your solution.

Allan