0

I am trying to fetch items using lookup but seems like it I cannot expand the child people picker column like there is a restriction in which I can't use person field by expanding the child list. Here's my below code:

$.ajax({
      url: .../_api/Web/Lists/GetByTitle('APP_NAME')/items$select=*, BookingID/ID,BookingID/Author&$expand=BookingID`,
      type: "GET",
      dataType: "json",
      headers: {
        accept: "application/json;odata=verbose",
      },
      success: (resultData) => {
        var listdata = {
          accounting: [],
        };
        console.log(resultData.d.results);
        $.each(resultData.d.results, (index, value) => {
            console.log(value.HasPPEKit)
            listdata.accounting.push({
              Author: value.BookingID.Author,
              ID: value.BookingID.ID
            });        
        });

When I look in console it is giving an error but if I remove the BookingID/Author from filter and Author value and try to fetch only ID value using $select=*, BookingID/ID&$expand=BookingID the ID value is fetching.

Is there a mistake in the API I am doing or is there any other to fetch items from 2 lists?

[and also there are some items which will not match other list. It's like I am using inner join in the list, don't focus on this line much, though].

4
  • Are you saying that BookingID is a lookup column and you want to fetch the Created By from 2nd list from where BookingID is referenced? – Ganesh Sanap Nov 23 '20 at 13:44
  • Right, BookingID is a lookup column looking in 2nd list and I want to fetch items from Created By column from 2nd list. Is there any way to extract the items? – Dhanjay Bhardwaj Nov 24 '20 at 5:19
  • It is not possible in one REST call. you need to use nested REST calls. Fetch items from 1st list and then fetch items from 2nd list based on BookingID you get from 1st call. see my answer below. – Ganesh Sanap Nov 24 '20 at 5:38
  • 1
    Yeah that's ok If I need to use nested REST call. So you mean fetch items from both lists and then filter out. Got it. Thanks! – Dhanjay Bhardwaj Nov 24 '20 at 6:14
1

This is a known behavior of SharePoint.

Extending Person or Group field using lookup columns is not supported in SharePoint. So, you will not be able fetch or add any filter on Person or Group field from lookup list.

To get the data from 2nd list, you need to use nested REST call. You can filter the list items based on the item ID you will get from BookingID column.

Supported Column Types:

  • Single line of text
  • Number
  • Date and Time

Unsupported Column Types:

  • Multiple lines of text
  • Currency
  • Person or Group
  • Calculated
  • Hyperlink or Picture
  • Custom Columns
  • Yes/No
  • Choice
  • Lookup

Microsoft official documentation: Create list relationships by using unique and lookup columns

3
  • Can you please send me code or link with proper working example of using nested call and filter. I am new to React Sharepoint. – Dhanjay Bhardwaj Nov 24 '20 at 6:54
  • 1
    I am not able to find any code link which matches exactly to your requirement. here is one similar explanation – Ganesh Sanap Nov 24 '20 at 7:23
  • 1
    First call: /_api/Web/Lists/GetByTitle('APP_NAME')/items?$select=*, BookingID/ID&$expand=BookingID. Then in success function of first call, you can store list item id from booking ID to one variable (say bookingItemId). then you can filter 2nd list like: /_api/Web/Lists/GetByTitle('<Second list name>')/items?$select=Title,Author/ID&$expand=Author&$filter=ID eq <bookingItemId> – Ganesh Sanap Nov 24 '20 at 7:26

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.