I suggest you ....

Enable support for lookup columns in other webs in the REST API

When trying to retreive items from a list which uses a Site Column that is a looukp column to a list in a different web, we get the error:

{error":{"code":"-1, Microsoft.SharePoint.SPException","message":{"lang":"en-US","value":"The field 'TrouserSize' is not supported in query. The lookup list is in another web."}}}

140 votes
Vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    sympmarcsympmarc shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    8 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • Steven WongSteven Wong commented  ·   ·  Flag as inappropriate

        Andre, you should be testing with a call similar to this:

        /_api/web/lists(guid'61fd8e18-6c28-4cc8-bbc2-baf6c3d14dc9')/items?$select= ExampleAL/Title&$expand=ExampleAL

        where the list is in a sub site and ExampleAL is a lookup field to a list in a parent site.

      • Steven WongSteven Wong commented  ·   ·  Flag as inappropriate

        This is still an issue. This impacts MS Flows, and _web calls with "lists" when you try to expand a lookup of a column that exists in a different site.

        SharePoint o365 (with the latest previews enabled including the SharePoint Framework)
        SharePoint 2016 On-Prem
        SharePoint 2013 On-Prem
        latest packs as of Nov 2016

      • Anonymous commented  ·   ·  Flag as inappropriate

        Also impacts Flows that involve lists in subsites that make use of parent site columns

      • André LageAndré Lage commented  ·   ·  Flag as inappropriate

        @Marc, "behavior is not as expected" i totally agree with you and also believe your side doesn't work, but i was trying "still trying" to reproduce the error then i can provide the votes for this topic... even understand if i could miss some step...
        I included the field "ExampleAL" to Content Type "Item" and change field to have multiple options and access to sub site "/Example" and create custom List "ExampleList" and have the following output with multiple values without issues.
        - <d:ExampleALId m:type="Collection(Edm.Int32)">
        <d:element>78</d:element>
        <d:element>115</d:element>
        <d:element>35</d:element>
        </d:ExampleALId>.
        What version of SharePoint this issue happen? Online, 2013, 2016? I had issues with SP on-premise REST call's and Online worked fine because of the version.
        Did you use any Imported List for the connection or after some migration of content that could broke the content type id from top to bottom, just saying....

      • Marc D AndersonMarc D Anderson commented  ·   ·  Flag as inappropriate

        @André, I've found the behavior you saw in some circumstances. Maybe it's libraries versus lists? My error above is with a list, which for me is at least as common a situation as libraries.

        Either way, the behavior is not as expected, at least for me. I want to be able to get both the ID and Lookup value, just as I can with SOAP.

      • André LageAndré Lage commented  ·   ·  Flag as inappropriate

        Strange, i was not able to reproduce this error using Office 365.
        I create site Lookup column call "ExampleAL" with "Title" output to List in Root site, add to content type "Document" and "Update all content types inheriting from this type"
        Go to sub site /Example and access to Document Library with new Field, update the field "ExampleAL" with data from other List "root web".
        Make the REST Query "/Example/_api/web/Lists/GetByTitle('Documents')/items" and have the output <d:ExampleALId m:type="Edm.Int32">48</d:ExampleALId>
        The only thing i dont like from this the fact i cannot expand the Field "ExampleAL" with other output field, because there isn't any reference to the List or the output expected in this case "Title" only ID... if the lookup object "ExampleAL" could be expanded that would be great.

      • Marc D AndersonMarc D Anderson commented  ·   ·  Flag as inappropriate

        Bitten by this again today. Argh!!!

        Whenever I run into this, I fall back to SOAP calls, which Just Plain Work.

      • testtest commented  ·   ·  Flag as inappropriate

        yes. Please enable this feature. It is lame that there are so many issues in doing anything in SharePoint.

      Feedback and Knowledge Base