REST API append data to datatables

Question

I have 2 lists, first list has the following columns:

• StaffName
• UnitName

The second list has the following columns:

• UnitName
• Department

Essentially, i want to pull all names from the first list from the same Department. To get a list of all units in the same Department, I'm using the following:

var orgDivUri = webAbsUrl + "/_api/web/Lists/getByTitle('Organization')/Items?$\
    filter=Dept eq '" + encodeURIComponent(orgDiv) + "'&$\
    select=Id, Title, Dept&$\
    orderby=Dept, Title";

If this results in 4 Units (Title), then I want the datatable to display all the Staff from these 4 Units.

Rest of the codes:

var ajaxSecByDiv = $.ajax({
url: orgDivUri,
type: "GET", 
dataType: "json", 
headers: { "accept": "application/json;odata=verbose" },
success: function (data) {
    if(data.d != undefined) {
        $.each(data.d.results, function (index, item) {
            SecByDivId = data.d.results[SecByDivCounter].Id;
            SecByDivName = data.d.results[SecByDivCounter].Title

                var requestSecUri = webAbsUrl + "/_api/web/Lists/getByTitle('Staff List')/Items?$\
                filter=SectionName eq '" + encodeURIComponent(SecByDivName) + "'&$\
                select=Id, Title, SectionName, StaffName/LastName, StaffName/FirstName, StaffName/EMail, Phone&$\
                expand=StaffName/Id&$\
                top=5000&$\
                orderby=StaffName/FirstName asc";

                var ajaxDivPrint = $.ajax({
                    url: requestSecUri,
                    type: "GET", 
                    dataType: "json", 
                    headers: { "accept": "application/json;odata=verbose" },
                    success: printAll, 
                    error: myErrorHandler
                });

            SecByDivCounter++
        });
    }
}, 
error: function (data) { alert("Failed to get sections by div info");   }
});

function printAll(data) {
try {
    var tblPrint = $('#tablePrint').DataTable();
        if (tblPrint != 'undefined') {
            tblPrint.destroy();
        }
    tblPrint = $('#tablePrint').DataTable({
        "pageLength": 25, 
        "searching": false,
        "order": [[0, "asc"],[1, "asc"]], 
        "paging": false, 
        "info": false, 
        srollY: 300,
        "aaData": data.d.results,
        "aoColumns": [
            { "mData": "StaffName.FirstName" },
            { "mData": "StaffName.LastName" },
            { "mData": "Title" },
            { "mData": "Phone" } 
        ],
    });
} catch (e) {
alert(e.message); }
}

I can't figure out how to append the data using row.add().draw even after reading the description. My output is always just a list of staff from the last Unit identified on the first REST call.

Answer 1

The following example code for your reference.

<script type="text/javascript" src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/datatables/1.10.19/js/jquery.dataTables.min.js"></script>  
<link href="https://cdnjs.cloudflare.com/ajax/libs/datatables/1.10.19/css/jquery.dataTables.min.css" rel="Stylesheet" type="text/css" />       
<link href="https://cdnjs.cloudflare.com/ajax/libs/datatables/1.10.19/css/dataTables.jqueryui.min.css" rel="Stylesheet" type="text/css">      

<table id="UsersInfo"  cellspacing="0" width="100%">    
    <thead>    
        <tr>    
            <th>FirstName</th>    
            <th>LastName</th>    
            <th>EMail</th>  
            <th>Phone</th>          
        </tr>
    </thead>
    <tbody></tbody>
    <tfoot>  
        <tr>    
            <th>FirstName</th>    
            <th>LastName</th>    
            <th>EMail</th>
            <th>Phone</th>          
        </tr>   
    </tfoot>    
</table> 
<script type="text/javascript">
var webAbsUrl = _spPageContextInfo.webAbsoluteUrl;
var orgDiv="Dep1";
$(function(){
    getAllUnit().done(function(data){
        $.each(data.d.results, function (index, item){
            getUserInfo(item.Title).done(function(users){
                $.each(users.d.results,function(i,user){
                    var trHTML="";
                    trHTML="<tr>";
                    trHTML+="<td>"+user.StaffName.FirstName+"</td>";
                    trHTML+="<td>"+user.StaffName.LastName+"</td>";     
                    trHTML+="<td>"+user.StaffName.EMail+"</td>";
                    trHTML+="<td>"+user.Phone+"</td>";                  
                    trHTML+="</tr>";
                    $("#UsersInfo>tbody").append(trHTML);
                });             
            });
        });
        $('#UsersInfo').DataTable({
            "pageLength": 25, 
            "searching": false,
            "order": [[0, "asc"],[1, "asc"]], 
            "paging": false, 
            "info": false
        });
    });
});
function getAllUnit(){
    var orgDivUri = webAbsUrl + "/_api/web/Lists/getByTitle('Organization')/Items?$filter=Dept eq '" + encodeURIComponent(orgDiv) + "'&$select=Id, Title, Dept&$orderby=Dept, Title";
    return $.ajax({
        url: orgDivUri,
        type: "GET",
        headers: {
            "accept": "application/json;odata=verbose",
        }
    });
}
function getUserInfo(SecByDivName){
    var requestSecUri = webAbsUrl + "/_api/web/Lists/getByTitle('Staff List')/Items?$filter=SectionName eq '" + encodeURIComponent(SecByDivName) + "'&$select=Id, Title, SectionName, StaffName/LastName, StaffName/FirstName, StaffName/EMail, Phone&$expand=StaffName/Id&$top=5000&$orderby=StaffName/FirstName asc";
    return $.ajax({
        url: requestSecUri,
        type: "GET",
        async:false,
        headers: {
            "accept": "application/json;odata=verbose",
        }
    });
}
</script>