How best to select a cell from database row column value

How best to select a cell from database row column value

DatagaardDatagaard Posts: 68Questions: 20Answers: 3

I want to default a selected cell within a data table based on looking through the underlying data to locate a (row, column) value.
Are there any examples of selecting a specific cell based on data (row, column) value?

This question has accepted answers - jump to:

Answers

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

    To be clear, is this setting a cell value on initialisation based on another value in the same row? Or in a different row? Or some other criteria?

    Colin

  • DatagaardDatagaard Posts: 68Questions: 20Answers: 3

    Hi Colin.

    Neither, my aim is to select the cell in question after it shows, so it is selected by default based on data found in the data set.

    By iterate through the underlying data, when the (row, column) data matches a set value, then select that cell.

    Hope that makes sense.

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

    I think that make sense :) Could you look at this example, please - I'm selecting all cells that contain 'London' when the table is initialised. Could you look at that, please, and see if it helps. If it's still not working for you, please can you update my example, or link to your page, so that we can see the problem.

    Cheers,

    Colin

  • DatagaardDatagaard Posts: 68Questions: 20Answers: 3

    Hi Colin,

    Nearly... not quite.

    Yes that will select every cell that contains 'London', but what if we just want to select the cell which contains 'London' and where row name = Angelica Ramos (which in your eaxmple is the first row)

    Regards

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

    Ha, we're getting there. You can do something like this - this is just expanding the conditional:

          api.cells().every(function() {
            var idx = this.index();
            if (this.data() == 'London' && api.cell(idx.row, 0).data() == 'Angelica Ramos') {
              this.select();
            }
          })
    

    Colin

  • DatagaardDatagaard Posts: 68Questions: 20Answers: 3

    Hi Colin,

    We get this error now, where we didn't before:

    Uncaught ReferenceError: api is not defined.

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    Answer ✓

    Add the statement var api = this.api(); like in Colin's example.

    Kevin

  • DatagaardDatagaard Posts: 68Questions: 20Answers: 3

    Thanks, now all I have to do is get the cell to look like it has been selected by the user, which has the highlighted boarder

    Image 1 is the cell selected, image 2 is what I would like

    Regards

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

    Please update my test case to demonstrate the issue, with instructions on how to reproduce the issue,

    Colin

  • DatagaardDatagaard Posts: 68Questions: 20Answers: 3
    edited December 2021

    HTML page

    <html>
        <head>
            <meta http-equiv="content-type" content="text/html; charset=utf-8" />
            <title>DataTables Editor - terms_codes</title>
            <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/bs4-4.1.1/jq-3.3.1/moment-2.18.1/dt-1.10.21/b-1.6.2/b-html5-1.6.2/r-2.2.5/sp-1.1.1/sl-1.3.1/datatables.min.css">
            <link rel="stylesheet" type="text/css" href="css/generator-base.css">
            <link rel="stylesheet" type="text/css" href="css/editor.bootstrap4.min.css">
            <link href="https://nightly.datatables.net/select/css/select.dataTables.css" rel="stylesheet" type="text/css" />
    
            <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.11.0/umd/popper.min.js"></script>
            <script type="text/javascript" charset="utf-8" src="https://cdn.datatables.net/v/bs4-4.1.1/jq-3.3.1/moment-2.18.1/dt-1.10.21/b-1.6.2/b-html5-1.6.2/r-2.2.5/sp-1.1.1/sl-1.3.1/datatables.min.js"></script>
            <script type="text/javascript" charset="utf-8" src="js/dataTables.editor.min.js"></script>
            <script type="text/javascript" charset="utf-8" src="js/editor.bootstrap4.min.js"></script>
            <script src="https://nightly.datatables.net/select/js/dataTables.select.js"></script> 
            <script type="text/javascript" charset="utf-8" src="js/table.terms_codes.js"></script>
        </head>
        <body class="bootstrap4">
            <div class="container">
    
                <h1>
                    DataTables Editor <span>terms_codes</span>
                </h1>
                
                <table cellpadding="0" cellspacing="0" border="0" class="table table-striped table-bordered" id="terms_codes" width="100%">
                    <thead>
                        <tr>
                            <th>termscode</th>
                            <th>tdescription</th>
                            <th>duedatecutoff</th>
                            <th>duefixeddom</th>
                            <th>dueinsomanydays</th>
                            <th>duemethodcode</th>
                            <th>termscutoffdom</th>
                            <th>termsday</th>
                            <th>termsfixeddom</th>
                            <th>termspercent</th>
                            <th>tmethodcode</th>
                        </tr>
                    </thead>
    <tbody>
    <tr id="row_1" role="row" class="odd"><td class="sorting_1 dtr-control">1</td><td>7 DAYS</td><td>31</td><td>14</td><td>0</td><td>3</td><td>0</td><td style="display: none;">0</td><td style="display: none;">0</td><td style="display: none;">5.00</td><td style="display: none;">1</td></tr><tr id="row_10" role="row" class="even"><td class="sorting_1 dtr-control">10</td><td>1.5% STRATCO</td><td>31</td><td>31</td><td>0</td><td>3</td><td>31</td><td style="display: none;">0</td><td style="display: none;">31</td><td style="display: none;">1.50</td><td style="display: none;">2</td></tr><tr id="row_14" role="row" class="odd"><td class="sorting_1 dtr-control">14</td><td>5% 14DYS/30NETT</td><td>31</td><td>30</td><td>0</td><td class="selected">3</td><td>0</td><td style="display: none;">14</td><td style="display: none;">0</td><td style="display: none;">5.00</td><td style="display: none;">1</td></tr><tr id="row_2" role="row" class="even"><td class="sorting_1 dtr-control">2</td><td>21/2 30DAYS</td><td>31</td><td>30</td><td>0</td><td>3</td><td>31</td><td style="display: none;">0</td><td style="display: none;">30</td><td style="display: none;">2.50</td><td style="display: none;">2</td></tr><tr id="row_3" role="row" class="odd"><td class="sorting_1 dtr-control">3</td><td>3%</td><td>31</td><td>30</td><td>0</td><td>3</td><td>31</td><td style="display: none;">0</td><td style="display: none;">30</td><td style="display: none;">3.00</td><td style="display: none;">2</td></tr><tr id="row_30" role="row" class="even"><td class="sorting_1 dtr-control">30</td><td>30 DAYS NETT</td><td>31</td><td>30</td><td>0</td><td>3</td><td>0</td><td style="display: none;">0</td><td style="display: none;">0</td><td style="display: none;">0.00</td><td style="display: none;">1</td></tr><tr id="row_5" role="row" class="odd"><td class="sorting_1 dtr-control">5</td><td>21/2 30 DAYS</td><td>31</td><td>31</td><td>0</td><td>3</td><td>31</td><td style="display: none;">0</td><td style="display: none;">30</td><td style="display: none;">2.50</td><td style="display: none;">2</td></tr><tr id="row_7" role="row" class="even"><td class="sorting_1 dtr-control">7</td><td>STRAIGHT 7 DAYS</td><td>0</td><td>0</td><td>7</td><td>1</td><td>0</td><td style="display: none;">0</td><td style="display: none;">0</td><td style="display: none;">0.00</td><td style="display: none;">1</td></tr><tr id="row_CO" role="row" class="odd"><td class="sorting_1 dtr-control">CO</td><td>2% 30 DAYS</td><td>31</td><td>14</td><td>0</td><td>3</td><td>31</td><td style="display: none;">0</td><td style="display: none;">14</td><td style="display: none;">2.00</td><td style="display: none;">2</td></tr>
    </tbody>
                </table>
    
            </div>
        </body>
    </html>
    

    JS File

    /*
     * Editor client script for DB table terms_codes
     * Created by http://editor.datatables.net/generator
     */
    
    (function($){
    
    $(document).ready(function() {
        var editor = new $.fn.dataTable.Editor( {
            ajax: 'php/table.terms_codes.php',
            table: '#terms_codes',
            fields: [
                {
                    "label": "termscode:",
                    "name": "termscode"
                },
                {
                    "label": "tdescription:",
                    "name": "tdescription"
                },
                {
                    "label": "duedatecutoff:",
                    "name": "duedatecutoff"
                },
                {
                    "label": "duefixeddom:",
                    "name": "duefixeddom"
                },
                {
                    "label": "dueinsomanydays:",
                    "name": "dueinsomanydays"
                },
                {
                    "label": "duemethodcode:",
                    "name": "duemethodcode"
                },
                {
                    "label": "termscutoffdom:",
                    "name": "termscutoffdom"
                },
                {
                    "label": "termsday:",
                    "name": "termsday"
                },
                {
                    "label": "termsfixeddom:",
                    "name": "termsfixeddom"
                },
                {
                    "label": "termspercent:",
                    "name": "termspercent"
                },
                {
                    "label": "tmethodcode:",
                    "name": "tmethodcode"
                }
            ]
        } );
    
        var table = $('#terms_codes').DataTable({
            ajax: 'php/table.terms_codes.php',
            columns: [
                {
                    "data": "termscode"
                },
                {
                    "data": "tdescription"
                },
                {
                    "data": "duedatecutoff"
                },
                {
                    "data": "duefixeddom"
                },
                {
                    "data": "dueinsomanydays"
                },
                {
                    "data": "duemethodcode"
                },
                {
                    "data": "termscutoffdom"
                },
                {
                    "data": "termsday"
                },
                {
                    "data": "termsfixeddom"
                },
                {
                    "data": "termspercent"
                },
                {
                    "data": "tmethodcode"
                }
            ],
            select: {
                style: 'single',
                selector: 'td:not(:last-child)' /* no row selection on last column */
                //items: 'cells'
            },
            initComplete: function() {
                var api = this.api();
                this.api().cells().every(function() {
                    var idx = this.index();
                    if (this.data() == '3' && api.cell(idx.row, 0).data() == '14') {
                        this.select();
                    }
                    
                    //var cellObj = this;
                    //var cellRowNo = cellObj[0][0].row;
                    //var cellColNo = cellObj[0][0].column;
                    //var rowDataObj = table.rows().data()[cellRowNo]; // same as row(0).data()
                    //if (rowDataObj.termscode == 'CO' && cellColNo == '4') {
                    //  if (this.data() == '0') {
                    //      this.select();
                    //  }
                    //}
                });
            },
            keys: true,
            lengthChange: true,
            autoWidth: true,
            responsive: true,
        });
    
        //window.setTimeout(function ()
        //{
        //  table.cell( ':eq(0)' ).focus(); // As javascript is synchronus this ensures focus is the last thing done.
        //}, 0);    
        
        
        new $.fn.dataTable.Buttons( table, [
            { extend: "create", editor: editor },
            { extend: "edit",   editor: editor },
            { extend: "remove", editor: editor }
        ] );
    
        table.buttons().container()
            .appendTo( $('.col-md-6:eq(0)', table.table().container() ) );
    } );
    
    }(jQuery));
    

    Hope thats all you require Colin, tried formatting correctly but not so.

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

  • DatagaardDatagaard Posts: 68Questions: 20Answers: 3

    When code runs. the cell is selected, but if you select another cell in another row you get the cell highlighted with a boarder, that is how we are trying to get the cell to appear in the first place.

  • DatagaardDatagaard Posts: 68Questions: 20Answers: 3

    Oh the php page for terms_codes is:

    '''
    <?php /* * Editor server script for DB table terms_codes * Created by http://editor.datatables.net/generator */ // DataTables PHP library and database connection include( "lib/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, DataTables\Editor\ValidateOptions; // The following statement can be removed after the first run (i.e. the database // table has been created). It is a good idea to do this to help improve // performance. $db->sql( "CREATE TABLE IF NOT EXISTS `terms_codes` ( `tcid` int(10) NOT NULL auto_increment, `termscode` varchar(255), `tdescription` varchar(255), `duedatecutoff` numeric(9,2), `duefixeddom` numeric(9,2), `dueinsomanydays` numeric(9,2), `duemethodcode` varchar(255), `termscutoffdom` numeric(9,2), `termsday` numeric(9,2), `termsfixeddom` numeric(9,2), `termspercent` numeric(9,2), `tmethodcode` varchar(255), PRIMARY KEY( `tcid` ) );" ); // Build our Editor instance and process the data coming from _POST Editor::inst( $db, 'terms_codes', 'termscode' ) ->fields( Field::inst( 'termscode' ) ->validator( Validate::notEmpty() ) ->validator( Validate::maxLen( 3, ValidateOptions::inst() ->message( 'Terms Code length must be between 1 & 3 Characters in length' ) ) ), Field::inst( 'tdescription' ) ->validator( Validate::notEmpty() ) ->validator( Validate::maxLen( 15 ) ), Field::inst( 'duedatecutoff' ) ->validator( Validate::minMaxNum( 0, 99 , "", ValidateOptions::inst() ->message( 'The due date cut off must be 2 digits or less' ) ) ), Field::inst( 'duefixeddom' ) ->validator( Validate::minMaxNum( 0, 99 ) ), Field::inst( 'dueinsomanydays' ) ->validator( Validate::minMaxNum( 0, 9999 ) ), Field::inst( 'duemethodcode' ) ->validator( Validate::notEmpty() ) ->validator( Validate::maxLen( 1 ) ), Field::inst( 'termscutoffdom' ) ->validator( Validate::minMaxNum( 0, 99 ) ), Field::inst( 'termsday' ) ->validator( Validate::minMaxNum( 0, 9999 ) ), Field::inst( 'termsfixeddom' ) ->validator( Validate::minMaxNum( 0, 99 ) ), Field::inst( 'termspercent' ) ->validator( Validate::minMaxNum( 0, 9999.99 ) ), Field::inst( 'tmethodcode' ) ->validator( Validate::notEmpty() ) ->validator( Validate::maxLen( 1 ) ) ) ->process( $_POST ) ->json(); ?>
    '''

  • DatagaardDatagaard Posts: 68Questions: 20Answers: 3
    edited December 2021

    Here's the ajax data:

    {"data":[{"DT_RowId":"row_1","termscode":"1","tdescription":"7 DAYS","duedatecutoff":"31","duefixeddom":"14","dueinsomanydays":"0","duemethodcode":"3","termscutoffdom":"0","termsday":"0","termsfixeddom":"0","termspercent":"5.00","tmethodcode":"1"},{"DT_RowId":"row_10","termscode":"10","tdescription":"1.5% STRATCO","duedatecutoff":"31","duefixeddom":"31","dueinsomanydays":"0","duemethodcode":"3","termscutoffdom":"31","termsday":"0","termsfixeddom":"31","termspercent":"1.50","tmethodcode":"2"},{"DT_RowId":"row_14","termscode":"14","tdescription":"5% 14DYS\/30NETT","duedatecutoff":"31","duefixeddom":"30","dueinsomanydays":"0","duemethodcode":"3","termscutoffdom":"0","termsday":"14","termsfixeddom":"0","termspercent":"5.00","tmethodcode":"1"},{"DT_RowId":"row_2","termscode":"2","tdescription":"21\/2 30DAYS","duedatecutoff":"31","duefixeddom":"30","dueinsomanydays":"0","duemethodcode":"3","termscutoffdom":"31","termsday":"0","termsfixeddom":"30","termspercent":"2.50","tmethodcode":"2"},{"DT_RowId":"row_3","termscode":"3","tdescription":"3%","duedatecutoff":"31","duefixeddom":"30","dueinsomanydays":"0","duemethodcode":"3","termscutoffdom":"31","termsday":"0","termsfixeddom":"30","termspercent":"3.00","tmethodcode":"2"},{"DT_RowId":"row_30","termscode":"30","tdescription":"30 DAYS NETT","duedatecutoff":"31","duefixeddom":"30","dueinsomanydays":"0","duemethodcode":"3","termscutoffdom":"0","termsday":"0","termsfixeddom":"0","termspercent":"0.00","tmethodcode":"1"},{"DT_RowId":"row_5","termscode":"5","tdescription":"21\/2 30 DAYS","duedatecutoff":"31","duefixeddom":"31","dueinsomanydays":"0","duemethodcode":"3","termscutoffdom":"31","termsday":"0","termsfixeddom":"30","termspercent":"2.50","tmethodcode":"2"},{"DT_RowId":"row_7","termscode":"7","tdescription":"STRAIGHT 7 DAYS","duedatecutoff":"0","duefixeddom":"0","dueinsomanydays":"7","duemethodcode":"1","termscutoffdom":"0","termsday":"0","termsfixeddom":"0","termspercent":"0.00","tmethodcode":"1"},{"DT_RowId":"row_CO","termscode":"CO","tdescription":"2% 30 DAYS","duedatecutoff":"31","duefixeddom":"14","dueinsomanydays":"0","duemethodcode":"3","termscutoffdom":"31","termsday":"0","termsfixeddom":"14","termspercent":"2.00","tmethodcode":"2"}],"options":[],"files":[],"searchPanes":{"options":[]}}

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

    No, we'd need to see the code running, it's easier to debug that way. Please update my test case, or create your own, so we can see the issue you want help with,

    Colin

  • DatagaardDatagaard Posts: 68Questions: 20Answers: 3

    Hi Colin,

    Merry Christmas, hope you had a wonderful time.

    I have updated the test case, but can you correct the links and script src in the html to the appropriate location on the nightly side for my editions. Hoping that will show case the example. It works as is, but I can't get the down arrow, and right arrow keys to select the next row, column... probably due to the incorrect references to the nightly build locations.

    Regards

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

    Merry Christmas to you too. You'll need to post the link, as the version strings update on the edits.

    Colin

  • DatagaardDatagaard Posts: 68Questions: 20Answers: 3

    Hi Colin,

    My bad, not quiet used to all this, here is the link example

    Regards

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    Answer ✓

    You are using the KeyTable extension which is highlighting the cell with just the border. Sounds like you want to use the KeyTalbe cell().focus() API instead of the select cell().select() API. Like this:
    http://live.datatables.net/xamigesi/5/edit

    BTW, I added the KeyTable library to the test case.

    Kevin

  • DatagaardDatagaard Posts: 68Questions: 20Answers: 3

    Thanks Kevin,

    Spot on.

Sign In or Register to comment.