Add a row to an empty table using datatables and use editor to edit the new row

pcpartner

We can add a row as shown in this example https://datatables.net/examples/api/add_row.html but we would like to inline edit the newly created row afterwards. I can't find an example or information for how to do this.

allan

Assuming that the row has been added using the same data structure as the rest of the DataTable, it should just work automatically.

The key thing here is that the row must have a primary key value.

I'm guessing, correct me if I am wrong, that you are using row.add() to add a new row and then inline editing to make it appear that a new row can be created via inline editing? I'm afraid that is not the case since it doesn't have a primary key value (which would require the row to be added on the database).

What you could do is use create() to add a new row and that would submit it to the server and then allow regular inline editing.

Allan

pcpartner

Allan, thx for the prompt response.

We are now able to add the row to the database through editor.create but I can't seem to find a way to redraw the table with the new created row.

Our code:

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

    ajax: "views/ajax/invoices.php",
    table: "#invoice",
    fixedHeader: true,
    idSrc: 'CF_InvoiceElements.id',
    fields: [{
            name: "CF_InvoiceElements.InvoiceCode"
        }, {
            name: "CF_InvoiceElements.Debtor"
        }, {
            name: "CF_InvoiceElements.Ordering"
        }, {
            name: "CF_InvoiceElements.Print"
        }],
    select: {
        style: 'single'
    }
});

$('#invoice').on('click', 'tbody td:not(:first-child)', function (e) {
    editor.inline(this, {
        onBlur: 'submit'
    });
});

var table = $('#invoice').DataTable({
            dom: "Bfrtip",
            fixedHeader: true,
            "oLanguage": {"sUrl": "3rdparty/DataTables/i18n/dataTables.dutch.lang.json"},
            bPaginate: false,
            ajax: "views/ajax/invoices.php",
            order: [[0, 'asc']],
            columns: [
                {data: "CF_InvoiceElements.InvoiceCode"},
                {data: "CF_InvoiceElements.Debtor"},
                {data: "CF_InvoiceElements.Ordering"},
                {data: "CF_InvoiceElements.Print"}
            ],
            columnDefs: [{"visible": false, "targets": [0, 1]}
            ],
            select: true,
            buttons: [
                {
                    extend: "create",
                    text: "Nieuw",
                    //editor: editor,
                    className: 'btn btn-success waves-effect waves-light m-r-5',
                    action: function (e, dt, node, config) {

                        var InvoiceCode = $("#invoicecode").val();
                        var DebtorId = $('#SearchDebtorInvoice').data('debtorId');

                        editor
                                .create(false)
                                .set('CF_InvoiceElements.InvoiceCode', InvoiceCode)
                                .set('CF_InvoiceElements.Debtor', DebtorId)
                                .set('CF_InvoiceElements.Ordering', '10')
                                .set('CF_InvoiceElements.Print', '1')
                                .submit();

                    }
                },
                {
                    extend: "remove",
                    text: "Verwijder",
                    className: 'btn btn-danger waves-effect waves-light',
                    editor: editor
                }
            ]
        });

        <?php

// DataTables PHP library
include( "../../3rdparty/DataTables/Editor-PHP-1.6.2/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 = Editor::inst( $db, 'CF_InvoiceElements' )
    ->fields(
        Field::inst( 'CF_InvoiceElements.id' ),
        Field::inst( 'CF_InvoiceElements.Ordering' ),
        Field::inst( 'CF_InvoiceElements.Print' ),
        Field::inst( 'CF_InvoiceElements.InvoiceCode' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'CF_InvoiceElements.Debtor' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'CF_InvoiceElements.Date' ),
        Field::inst( 'CF_InvoiceElements.Number' ),
        Field::inst( 'CF_InvoiceElements.ProductCode' ),
        Field::inst( 'CF_InvoiceElements.Description' ),
        Field::inst( 'CF_InvoiceElements.PriceExcl' ),
        Field::inst( 'CF_InvoiceElements.TaxPercentage' ),
        Field::inst( 'CF_InvoiceElements.Ventil' ),
        Field::inst( 'CF_InvoiceElements.Rekening' ),
        Field::inst( 'CF_InvoiceElements.DiscountPercentage' ),
        Field::inst( 'CF_InvoiceElements.Periods' ),
        Field::inst( 'CF_InvoiceElements.Periodic' ),
        Field::inst( 'CF_InvoiceElements.PeriodicID' ),
        Field::inst( 'CF_InvoiceElements.StartPeriod' ),
        Field::inst( 'CF_InvoiceElements.EndPeriod' ),
        Field::inst( 'CF_InvoiceElements.Free1' ),
        Field::inst( 'CF_InvoiceElements.Free2' ),
        Field::inst( 'CF_InvoiceElements.Free3' ),
        Field::inst( 'CF_InvoiceElements.Free4' ),
        Field::inst( 'CF_InvoiceElements.Free5' )
    )
    ->where( 'InvoiceCode', 'CF_InvoiceElements.InvoiceCode', '=' )
    ->process( $_POST )
    ->json();

allan

Hi,

When the create Ajax request is sent to the server, what is the response from the server. My guess is that it is an object with an empty data array, possibly caused by the where condition that is being used above.

The where condition is basically doing:

WHERE InvoiceCode = 'CF_InvoiceElements.InvoiceCode'

Note the quotes - it is using the second parameter as a string because it is being escaped automatically by the library for security.

Before we discuss how that could be addressed, could you say why that WHERE condition is there? I don't think it is actually doing anything (if it were to work) since it is just doing InvoiceCode = InvoiceCode on the same table.

Allan

pcpartner

Hi Allan,

It is indeed an empty data object

In the header of the invoice I create a new invoice number, once the invoice headers is created i would like to add row(s) to the invoice related to this new invoice number.

Therefore I would like to use the posted InvoiceCode value in the WHERE clause

pcpartner

I've figured it out

$invoicecode = isset($_POST['data'][0]['CF_InvoiceElements']['InvoiceCode']) ? $_POST['data'][0]['CF_InvoiceElements']['InvoiceCode'] : "dummy";

allan

Is your id column an auto incrementing sequence? Editor should really do that for you and there should be no need to specify a where condition just to limit the response to the newly created row.

Allan