Data source - Child row Table

fhaff

Hello,

I have an API from a server which returns this code format:

{
   "status":"success",
   "time(sql)":0,
   "count":2,
   "list":[
      {
         "number":"ITF00198325",
         "type":"II",
         "requestor":"fhaff",
         "reqdate":"2019-06-18 09:59:04",
         "efr":"fhaff",
         "status":"End",
         "order":"1100070645",
         "country":"FR",
         "city":"Sophia Antipolis",
         "zip":"06560",
         "address":"2740 route des Cretes",
         "comments":"",
         "item":[
            {
               "sku":"FSA-3000D",
               "desc":"Advanced Threat Protection System - 4 x GE RJ45, 2 x GE SFP slots, 2 x 10GbE SFP+ Slots, redundant PSU, 28 VMs with Win7 and (3) MS Office License included",
               "qty":"1",
               "serial":"FSA3KD3R16000260"
            }
         ]
      },
      {
         "number":"ITF00214345",
         "type":"II",
         "requestor":"fhaff",
         "reqdate":"2019-09-30 01:50:45",
         "efr":"fhaff",
         "status":"End",
         "order":"1100082924",
         "country":"FR",
         "city":"Valbonne",
         "zip":"06560",
         "address":"2740 route des cretes",
         "comments":"",
         "item":[
            {
               "sku":"FWF-60E-DSL",
               "desc":"9 x GE RJ45 ports (including 7 x Internal Ports, 1 x WAN Ports, 1 x DMZ Port), Internal ADSL2/2+ and VDSL2 (Annex A/M) modem, Wireless (802.11ac)",
               "qty":"1",
               "serial":"FW60EVTK19000051"
            }
         ]
      }
   ]
}

I would like to build a DataTable with childrow:

Table: (for Each element from list)
list.number | list.type | list.status | list.requestor | list.order | list.reqdate | list.address | list.city | list.zip | list.country

Child row (for each element from list.item)

List.item.sku | List.item.qty | List.item.serial

Really important: I have to update this table from a form and a button (already created)

Can somebody help me with the data management ?

Thnaks !

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

colin

This example will show you how to use ajax objects. You'll need to specify ajax.dataSrc to be list, as that object contains your data.

Then, for child rows, you can get the full data for the row with row().data() which you can then display as a child row.

Colin

fhaff

Thanks for your help !
For an example, considering my html table as:

<table id="example" class="display" style="width:100%">
        <thead>
            <tr>
                <th>ITF number</th>
                <th>ITF requestor</th>
                <th>ITF status</th>
            </tr>
        </thead>
</table>

My variable "data" as:

{"status":"success","time(sql)":0,"count":2,"list":[{"number":"ITF00198325","type":"II","requestor":"fhaff","reqdate":"2019-06-18 09:59:04","efr":"fhaff","status":"End","order":"1100070645","country":"FR","city":"Sophia Antipolis","zip":"06560","address":"2740 route des Cretes","comments":"","item":[{"sku":"FSA-3000D","desc":"Advanced Threat Protection System - 4 x GE RJ45, 2 x GE SFP slots, 2 x 10GbE SFP+ Slots, redundant PSU, 28 VMs with Win7 and (3) MS Office License included","qty":"1","serial":"FSA3KD3R16000260"}]},{"number":"ITF00214345","type":"II","requestor":"fhaff","reqdate":"2019-09-30 01:50:45","efr":"fhaff","status":"End","order":"1100082924","country":"FR","city":"Valbonne","zip":"06560","address":"2740 route des cretes","comments":"","item":[{"sku":"FWF-60E-DSL","desc":"9 x GE RJ45 ports (including 7 x Internal Ports, 1 x WAN Ports, 1 x DMZ Port), Internal ADSL2/2+ and VDSL2 (Annex A/M) modem, Wireless (802.11ac)","qty":"1","serial":"FW60EVTK19000051"}]}]}

So, I have to create my table like this ?

$(document).ready(function() {
    $('#example').DataTable( {
        "ajax": data,
        "columns": [
            { "list": "number" },
            { "list": "type" },
            { "list": "requestor" }
        ]
    } );
} );

kthorngren

As Colin mentioned you need to use ajax.dataSrc. Then use columns.data. Something like this:

$(document).ready(function() {
    $('#example').DataTable( {
        "ajax": {
            "url": "myApiUrl",
            "dataSrc": "list"
        },
        "columns": [
            { "data": "number" },
            { "data": "type" },
            { "data": "requestor" }
        ]
    } );
} );

Kevin

fhaff

Ok kthorngren thanks I understood for the structure.
However I'm really not used to work with Ajax.

Here is my query to my API:

var  params = { "token":tokenVal,
                                "oid" : oid,
                                "eid" : requestor,
                                "type" : type,
                                "status" : status,
                                "region" : 'EMEA',
                                "startDt" : ITF_startDate,
                                "endDt" : ITF_endDate
                               };

                                $.post(
                                    url,
                                    params,
                                    function(data) 
                                    {
                                                 var display = JSON.stringify(data);
                                                 showMsg(display);
                                     }
                                  );

How can I integrate it in the DataTables construction ?
Thanks

kthorngren

You can initialize your Datatables within the $.post callback. Like this:

                                    function(data)
                                    {
                                                 var display = JSON.stringify(data);
                                                 showMsg(display);
                                                 $('#example').DataTable( {
                                                     "data": data.list,
                                                     "columns": [
                                                         { "data": "number" },
                                                         { "data": "type" },
                                                         { "data": "requestor" }
                                                     ]
                                                 } );
                                     }

Instead of using ajax use the data option to load the table data.

Kevin

fhaff

Thanks Kevin, it works !

I did it in order to do a new search from my form and "update" the table:

$.post(
                                    url,
                                    params,
                                    function(data) 
                                    {
                                                 var display = JSON.stringify(data);
                                                 showMsg(display);
                                                 
                                        if ( $.fn.DataTable.isDataTable('#example') ) {
                                          $('#example').DataTable().destroy();
                                        }

                                        $('#example tbody').empty();

                                        $('#example').DataTable( {
                                             "data": data.list,
                                             "columns": [
                                                 { "data": "number" },
                                                 { "data": "requestor" },
                                                 { "data": "status" }
                                             ]
                                         } );
                   

                                                 
         
                                    },
                                    'json'
                                );

I will investigate now for drawing the child rows ^^
Thanks again

kthorngren

Looks good!

Kevin

fhaff

Hi Kevin.

Yesterday all was working perfectly.
However for security reasons, the server changer JSON to JSONP.

How can I use it to have the same result ?

Thanks in advance

fhaff

I found it by myself, thanks for your help.
If it can help somebody, here is my DataTables creation:

table = $('#example').DataTable( {
                                             "data": data.list,
                                             "initComplete": function( settings, json ) {
                                                document.getElementById("table-div").style.display = "block";
                                                document.getElementById("loader").style.display = "none";
                                              },
                                             "pageLength": 50,
                                             fixedColumns:   {
                                                    leftColumns: 1,
                                                    rightColumns: 1
                                             },
                                             "columns": [

                                                 {
                                                    "className":      'details-control',
                                                    "orderable":      false,
                                                    "data":           null,
                                                    "defaultContent": ''

                                                 },

                                                 { "data": "number" },
                                                 { "data": "requestor" },
                                                 { "data": "status" },
                                                 { "data": "reqdate" },
                                                 { "data": "country" }
                                             ]
                                         } );

And the JSONP structure:

{"status":"success","time(sql)":0,"count":1,"list":[{"number":"ITF00000055","type":"II","requestor":"rhardy","reqdate":"2009-01-19 00:00:00","efr":"rlenz","status":"End","order":"-1","country":"France ","city":"Biot","zip":"06410","address":"120 Rue Albert Caquot","comments":"","item":[{"sku":"FG-620B-US","desc":"FortiGate-620B","qty":"1","serial":"FG600B3908600699"},{"sku":"FE-400B","desc":"FortiMail 400B","qty":"1","serial":"FE400B3M09000021"},{"sku":"FG-50B-HD-US","desc":"FortiGate-50B-HD","qty":"1","serial":"FG50BH3G08600153"},{"sku":"ASM-CX4","desc":"ASM-CX4","qty":"2","serial":"ASMCX43G08000083,ASMCX43G08000094"},{"sku":"FG-310B-US","desc":"FortiGate-310B","qty":"1","serial":"FG300B3908605761"},{"sku":"FL-400B","desc":"FortiAnalyzer 400B","qty":"1","serial":"FL400B3M08600072"},{"sku":"ASM-CE4","desc":"ASM-CE4","qty":"1","serial":"ASMCE43P08000047"}]}]}