1

How do I return a datatable in SharePoint 2019 from list with a lookup column from another list?

enter image description here

Script used:

<table id="Employee" class="display" cellspacing="0" width="100%">
<thead>
<tr>
<th>Title</th>
<th>ID_Number</th>
<th>ID_Number:First_Names</th>
<th>ID_Number:Last_Name</th>
</tr>
</thead>
</table>
</div>

<script>

$(document).ready(function() {
GetItems();
});

function GetItems() {
var siteUrl = _spPageContextInfo.webAbsoluteUrl;
var oDataUrl = siteUrl + "/_api/web/lists/getbytitle('VettingList')/items?$select=Title,ID_Number,ID_Number:First_Names,ID_Number:Last_Name";
$.ajax({
url: oDataUrl,
type: "GET",
dataType: "json",
headers: {
"accept": "application/json;odata=verbose"
},
success: OnSuccess,
error: OnFailure
});
}

function OnSuccess(data) {
try {
$('#Employee').DataTable({
"aaData": data.d.results,
"aoColumns": [
{
"mData": "Title"
},
{
"mData": "ID_Number"
},
{
"mData": "ID_Number:First_Names"
},
{
"mData": "ID_Number:Last_Name"
}
]
});
} catch (e) {
alert(e.message);
}
}

function OnFailure(data, errMessage) {
alert("Error: " + errMessage);
}

</script>

1 Answer 1

1

You can fetch item details from Parent list selected in lookup column settings by using $expand to expand the lookup column & $select to select the individual properties from lookup column like:

var oDataUrl = siteUrl + "/_api/web/lists/getbytitle('VettingList')/items?$select=Title,ID_Number/ID,ID_Number/First_Names,ID_Number/Last_Name&$expand=ID_Number";

Make sure you are using correct internal names of your ID_Number, ID_Number:First_Names and ID_Number:Last_Name column in above REST API call.

You can get the internal name of your column by following this article: How to find the Internal name of columns in SharePoint Online?

For more information, check:

  1. How to use expand in rest api to get child list records
  2. REST API $expand issues
6
  • The datatable is generated but returns the error: [object Object] for ID_Number
    – Bra Tabs
    Commented Aug 25, 2023 at 6:02
  • What is the error message? Are you using the correct names of the columns? Commented Aug 25, 2023 at 6:03
  • It doesn't pull any data. I used the following column names: "mData": "Title" "mData": "ID_Number" "mData": "ID_Number/First_Names" "mData": "ID_Number/Last_Name"
    – Bra Tabs
    Commented Aug 25, 2023 at 6:22
  • You have to use the internal names of column everywhere. Try adding console.log(data.d.results) inside the success function (function OnSuccess(data) {}) and see if it returns the array result in browser console. Commented Aug 25, 2023 at 6:24
  • I got it. Column name should be: "mData": "Title" }, { "mData": "ID_Number.ID_Number" }, { "mData": "ID_Number.First_Names" }, { "mData": "ID_Number.Last_Name" }
    – Bra Tabs
    Commented Aug 26, 2023 at 3:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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