0

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.

0

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>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.