is there a way to update the table from the result of an api call

leighJane

I am populating my table from mysql data. Does anyone know how I could update the data programatically with the result of an ajax call to an external api? I am able to retrieve the data from the call in JSON format and I would like to have it update the pertinent cells in the table without having to do it manually.

colin

You can just add the rows with rows.add() or row.add(),

Colin

leighJane

Hi Colin

Thanks for the reply. This is for an admin area not public. I would be updating some existing cells in rows not adding rows or changing an entire row. Below is an example of how a single order data is returned to me from each call to the API. I would have to make a call for each row of data in my table that requires updating based on criteria.

I was thinking that I could create a new button next to the existing NEW /EDIT /DELETE buttons name it UPDATE. Admin would select a row with checkbox and click update which calls the api with ajax and returns the JSON then I pick out the data programatically and insert it in each cell that needs updating.

{"status":"found-order","order_info":{"id":"5339892","name":"MARK ZIMMER","status":"Shipped","percent":100,"ship_from":"TX","ship_date":"March 11, 2020","tracking":{"carrier":"UPS","number":"1ZW3761R234567142","url":"https:\/\/www.ups.com\/mobile\/track?loc=en_US&t=t&trackingNumber=1ZW3761R234567142UPS"}},"message":{"status_message":"Shipped","message_class":"in-process","long_message":"Your order has been shipped."},"display":[{"title":"Details","detail":"Your order has been shipped."},{"title":"Name","detail":"MARK ZIMMER"},{"title":"Ships from","detail":"TX"},{"title":"Ship date","detail":"March 11, 2020"},{"title":"Carrier","detail":"UPS"}]}

kthorngren

You can use row().data() or cell().data() to update the selected row. This example shows how to get the selected row. You can use use a jQuery ajax() or jQuery getJSON() to fetch the data then one of the data() APIs to update the row. Use draw() to update the table sorting, etc.

Kevin

leighJane

Hi Kevin

That is very helpful . I am new to this table but slowly getting the hang of it. One last question, do you have an example of creating the new button in the editor for the update ? I tried adding to this and it bombed on me. I also am not sure how the parameters would be named if for instance I was using the button to call an api rather than a built in routine.

buttons: [
            { extend: "create", editor: editor },
            { extend: "edit",   editor: editor },
            { extend: "remove", editor: editor },
        ]

kthorngren

The example I linked to has a button to fetch the selected cell. You can use something like that. If you still have troubles you can use this basic editor example and update it to show what you are trying to do.

Kevin

leighJane

Kevin
Perfect !! Thank you so much

leighJane

Hi Kevin

Almost there but having trouble updating table cell. cell().data() doesn't seem to work at all even when I use the sample call shown in your link. I have tried several different ways using the cell().data() method and none have worked. I also tried using plain javascipt to update the cell and it doesn't work either and the table never redraws even when I call table.draw(). I am using the editor version of datatables with the serverside : true variable. Tried tuning that off and still nothing. Running out of ideas Can you help please ?

        buttons: [
            { extend: "create", editor: editor },
            { extend: "edit",   editor: editor },
            { extend: "remove", editor: editor },
            {extend: 'selectedSingle', text: 'Update', action: function ( e, dt, button, config ) {
                var rowData=( dt.row( { selected: true } ).data() );
                var table = $('#example').DataTable();
                alert( rowData.GeminiOrderNo );  // gets correct cell
                $.ajax({
                  url: "https://tracking.xyz.com/api/?key=b39f008e318efd2bb988d724a161b61c6909677f&order="+rowData.GeminiOrderNo,  
                  type: 'POST',
                 success: function(result) {
                      console.log(result);  //  gets good JSON result
                      
                    // try to update row cell   
                      rowData.shipFrom = 'Test';
                     table.draw();
                    
                    // var cell = table.cell( this ); //does nothing
                    //cell.data( cell.data() + 1 ).draw();   //does nothing
                    }

                });
                }
            }
        ]

colin

Do you want the cell you're updating to be a permanent change? As you're using Editor, are you expecting to get that updated value sent back to the server? If so, you would want to use edit() - cell().data() would only update the value locally in the table, not in the server.

Colin

leighJane

hi Colin

When I say I am using editor I mean that the editor is being used in my table not that I want to use editor to change values by showing the manual edit window.

I need to change the value of cells from javascript and yes it will be a permanent change updated on the server. My data source is mysql.

I am able to select the target row and column by clicking the row selector checkbox on the table and assigning the dt.row data to the variable rowData

var rowData  =  ( dt.row( { selected: true } ).data() );

Then by referencing the the data field name 'shipFrom' I get the value of the target cell.

alert( rowData.shipFrom );  // gets correct cell data

Now all I need to do to wrap all this up is to change the value of that cell and redraw the table but I am having no luck changing the value of that cell even though I can read it. I tried

rowData.shipFrom = 'Test';     table.draw();

and it has no effect on the table, does not change the cell or redraw the table. It is ignored and does not even produce an error in the js console.

I have looked at edit api and cell selector api and frankly it is all way over my head. If you or someone could give me a piece of code that will update my cell and table I would be done with this and I can move on.

kthorngren

Try this:

var row = dt.row( { selected: true } );
var rowData  =  ( dt.row( { selected: true } ).data() );
rowData.shipFrom = 'Test';

row.data( rowData ).draw();  // Update the datatables data cache and refresh the table.

You need to use row().data() to update the Datatables data cache.

Kevin

leighJane

Hi Kevin
Thanks for the code but again nothing happened, no cell change and no redraw. I inspected the console network response tab and it is firing with this response which is the entire data for all rows but the field I want to change "shipFrom" is still empty or null

{"data":[{"DT_RowId":"row_6895","customer_full_name":"Pearson, Lynne","customer_id":"3346","OrderNumber":"1518896","OrderStatus":"In Production","GeminiOrderNo":"5343765","shippedFrom":"","shippedDate":null,"ShippedVia":"UPS Ground","TrackingNumber":null},{"DT_RowId":"row_6894","customer_full_name":"Metzdorf, Bryan","customer_id":"3345","OrderNumber":"1518895","OrderStatus":"In Production","GeminiOrderNo":"5342898","shippedFrom":null,"shippedDate":null,"ShippedVia":"UPS Ground","TrackingNumber":null},{"DT_RowId":"row_6893","customer_full_name":"Wrzesinski, Jason (order14)","customer_id":"2149","OrderNumber":"1518894","OrderStatus":"In Production","GeminiOrderNo":"5341993","shippedFrom":null,"shippedDate":null,"ShippedVia":"UPS Ground","TrackingNumber":null},{"DT_RowId":"row_6892","customer_full_name":"Sherman, Jason","customer_id":"3344","OrderNumber":"1518893","OrderStatus":"In Production","GeminiOrderNo":"5340519","shippedFrom":null,"shippedDate":null,"ShippedVia":"","TrackingNumber":null},{"DT_RowId":"row_6891","customer_full_name":"Rassi, Denver","customer_id":"3343","OrderNumber":"1518892","OrderStatus":"In Production","GeminiOrderNo":"5339929","shippedFrom":null,"shippedDate":null,"ShippedVia":"","TrackingNumber":null},{"DT_RowId":"row_6887","customer_full_name":"Wrzesinski, Jason (order13)","customer_id":"2149","OrderNumber":"1518888","OrderStatus":"In Production","GeminiOrderNo":"5338561","shippedFrom":null,"shippedDate":null,"ShippedVia":"UPS Ground","TrackingNumber":null},{"DT_RowId":"row_6886","customer_full_name":"Wieber, William (order91)","customer_id":"60","OrderNumber":"1518887","OrderStatus":"In Production","GeminiOrderNo":"5338608","shippedFrom":null,"shippedDate":null,"ShippedVia":"","TrackingNumber":null},{"DT_RowId":"row_6885","customer_full_name":"Packard, Charles (order2)","customer_id":"3342","OrderNumber":"1518886","OrderStatus":"in Production","GeminiOrderNo":"5336957","shippedFrom":null,"shippedDate":null,"ShippedVia":"UPS Ground","TrackingNumber":null},{"DT_RowId":"row_6883","customer_full_name":"Davis, Stacy (order5)","customer_id":"1441","OrderNumber":"1518884","OrderStatus":"In Production","GeminiOrderNo":"5336247","shippedFrom":null,"shippedDate":null,"ShippedVia":"","TrackingNumber":null},{"DT_RowId":"row_6882","customer_full_name":"Cabaniss, Bob","customer_id":"3340","OrderNumber":"1518883","OrderStatus":"In Production","GeminiOrderNo":"5335656","shippedFrom":null,"shippedDate":null,"ShippedVia":"UPS Ground","TrackingNumber":null}],"options":[],"files":[],"draw":2,"recordsTotal":"10","recordsFiltered":"10"}

Here is the JS code for the call

    var editor; // use a global for the submit and return data rendering in the examples
    editor = new $.fn.dataTable.Editor( {
        ajax: "staff.php",
        table: "#example",
        fields: [ {
                label: "Last, First Name:",
                name: "customer_full_name"
            }, {
                label: "ID:",
                name: "customer_id"
            }, {
                label: "Our Order #:",
                name: "OrderNumber"
            }, {
                label: "Status:",
                name: "OrderStatus"
            }, {
                label: "Gemini Order #:",
                name: "GeminiOrderNo"
            }, {
                label: "Ship From:",
                name: "shippedFrom"
            }, {
                label: "Ship Date:",
                name: "shippedDate",
                type: "datetime"
            }, {
                label: "Carrier:",
                name: "ShippedVia"
            }, {
                label: "Tracking #:",
                name: "TrackingNumber"
            }
        ]
    } );
 
    // Activate an inline edit on click of a table cell
    $('#example').on( 'click', 'tbody td:not(:first-child)', function (e) {
        editor.inline( table.cell( this ).index(), {
            onBlur: 'submit'
        } );
    } );
    var table = $('#example').DataTable( {
        dom: 'Bfrtip',
        ajax: {
            url: 'staff.php',
            type: 'POST'
        },
        
        serverSide: true,
        order: [[ 3, 'dec' ]],
        columns: [
            {
                data: null,
                defaultContent: '',
                className: 'select-checkbox',
                orderable: false,
                searchable: false
            },
            { data: "customer_full_name", editField: "customer_full_name" },
            { data: "customer_id", editField: "customer_id" },
            { data: "OrderNumber", editField: "OrderNumber" },
            { data: "OrderStatus", editField: "OrderStatus" },
            { data: "GeminiOrderNo", editField: "GeminiOrderNo" },
            { data: "shippedFrom", editField: "shippedFrom"},
            { data: "shippedDate", editField: "shippedDate"},
            { data: "ShippedVia", editField: "ShippedVia"},
            { data: "TrackingNumber", editField: "TrackingNumber"}
        ],
        select: {
            style:    'os',
            selector: 'td:first-child'
        },
        buttons: [
            { extend: "create", editor: editor },
            { extend: "edit",   editor: editor },
            { extend: "remove", editor: editor },
            {extend: 'selectedSingle', text: 'Update', action: function ( e, dt, button, config ) {
                var row = dt.row( { selected: true } );
                var rowData  =  ( dt.row( { selected: true } ).data() );
                rowData.shipFrom = 'Test';
                row.data( rowData ).draw();  // Update the datatables data cache and refresh the table.
                }
            }
        ]
    });
});

kthorngren

You have serverSide: true, which will use Ajax to fetch the current page's data from the server which will overwrite the update from row.data( rowData ). You can try the update without using draw() but then the table's sorting and searching would be updated based on the table change.

Are you wanting to also update your database with this value? If so then the edit() is the better option. Maybe something like this:

var row = dt.row( { selected: true } );

editor
    .edit( row, false )
    .set( 'shipFrom', 'Test' )
    .submit();

For example:
http://live.datatables.net/guwafemu/48/edit

Kevin

leighJane

YAY!!!! Hey Kevin you nailed it !! When I first ran it I got an error on the console, field: shipFrom is not defined then I realized it is supposed to be shippedFrom and when I changed it everything worked ...

Thank you so much .. I can now move on