none
Rest Query use additional Columns from Lookup RRS feed

  • Question

  • I have a Lookup Column going to another list. I can $expand and get the Title of the item.  I have additional columns I'd like to pull in.  Can I do that with just $expand or do I need to do some sort of JOIN?

    David Jenkins

    Thursday, May 7, 2015 8:23 PM

Answers

  • Hi,

    As my test, expand twice is not supported, and SharePoint REST cannot recognize additional field which is another lookup, user(groups), or hyperlink.

    As a workaround, user can get the ID value of the first time expand, and then make another REST query to the looked up list with filter(ID -eq value) to get the second expand values.

    Best Regards,
    Lisa Chen


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Tuesday, May 12, 2015 2:09 AM
    Moderator

All replies

  • I can get the ID but can't seem to get a column I made.  Does it need to be referenced in the list?

    David Jenkins

    Thursday, May 7, 2015 8:49 PM
  • Hi,

    I have done test in my SharePoint 2013 and I can reproduce your issue.

    I created a custom column named tr1, I can reproduce your issue with the following:

    ?$select=lookup/tr1 &$expand=lookup/tr1

    But I can use the following to query  additional Columns from Lookup:

    ?$select=lookup/OData_x0074_r1 &$expand=lookup/OData_x0074_r1

    I suggest you use rest API to get the name of the custom column;

    You can also use SharePoint manager 2013 to get the EntityPropertyName of the custom column:

    Best Regards,

    Lisa Chen


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.


    Friday, May 8, 2015 8:15 AM
    Moderator
  • I think this is what I'm experiencing.  I'm going to verify.  Thanks.

    David Jenkins

    Friday, May 8, 2015 1:07 PM
  • Would the Entity Property name be any different than the name used when querying with REST?

    If I do a rest query on the list itself I can get data.  I think the problem is that I'm trying to expand twice.

    The lookup column I'm trying to get is a group itself.  So it's probably the path I need to take to get there.

    My Documents library has a "Job Codes" lookup.  This lookup contains an additional column called "Assigned to Job".  The Assigned to Job can have multiple users in it.

    My REST query looks like this:

    I verified my lookup column like this:

    http://sharepoint/_api/web/lists/getbytitle('Job%20Codes')/items?$select=Assigned_x0020_To_x0020_Job_x002/Title,Assigned_x0020_To_x0020_Job_x002/Name&$expand=Assigned_x0020_To_x0020_Job_x002/Title,Assigned_x0020_To_x0020_Job_x002/Name

    Starting my query I can get the Title of the "Job Code".

    http://doccenter.photomask.com/_api/web/lists/getbytitle('Documents')/items?$select=Job_x0020_Codes/Title&$expand=Job_x0020_Codes/Title

    I'd think the Assigned To would be something like:

    $select=Job_x0020_Codes/Assigned_x0020_To_x0020_Job_x002/Title
    $expand=Job_x0020_Codes/Assigned_x0020_To_x0020_Job_x002/Title

    This doesn't seem to work.


    David Jenkins


    Friday, May 8, 2015 1:59 PM
  • Can anyone please provide any sort of clue as to how I should proceed?  I want to lookup a expand a lookup column then expand a people column from the first expand.  So expand twice.  Once for the lookup column.  Then again for the people inside the additional column.


    David Jenkins

    Friday, May 8, 2015 4:04 PM
  • Hi,

    As my test, expand twice is not supported, and SharePoint REST cannot recognize additional field which is another lookup, user(groups), or hyperlink.

    As a workaround, user can get the ID value of the first time expand, and then make another REST query to the looked up list with filter(ID -eq value) to get the second expand values.

    Best Regards,
    Lisa Chen


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Tuesday, May 12, 2015 2:09 AM
    Moderator