Join data from two lists using SharePoint REST API

Question

I have two lists which will be used to keep a log of changes in some data:

ChangeLog:

• Title
• Event
• Created By
• Other stuff

ChangeLogDetails:

• Id of related record in the ChangeLog list
• What was changed
• Old Value
• New Value

For each record in ChangeLog, the ChangeLogDetails list will contain one or more records, recording each column changed by user in some data.

I need to present a report that will combine the data from both lists. Each row must look like this:

ChangeLog.Title | ChangeLog.Event | [array of ChangeLogDetails.WhatWasChanged WHERE ChangeLogDetails.RelatedID = ChangeLog.ID]

It seems that this is impossible to do with SharePoint REST API, because there is no syntax to merge data from two lists. Am I correct?

Would it be possible to do this with CSOM? (I know nothing about it)

Answer 1

Yes, it is possible. You can use SharePointPlus.js javascript library for this. It allows joining two or more lists with client object model.

Answer 2

It's not possible with REST or CSOM with single query to get details from both lists.

First you should get items from list1, then loop these items and get the items from List2 with lookup field value filter.

If you have 'N' items in you List1 then you should do 'N+1' REST calls.

If three is only one item in Look up list then you can use $expand in REST API to get required details of the item.

Hope this helps to you. :)

Answer 3

Make a GET request to each list, store the results in a global variable.

For example

let listOne
let listTwo

using jQuerys ,merge() https://api.jquery.com/jquery.merge/

Merge the two arrays together