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."}}}

288 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…)
    sympmarc shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    25 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...
      • JM commented  ·   ·  Flag as inappropriate

        Still not fixed...
        Old SharePoint svc support this. Why doesn't Rest calls?

        Had a lot of trouble to get this to work inside the workflows.

        Had to make a SVC call just to get a value I already had and put it an email because Workflows will crash because of this error...

      • Alex Cope-Norris commented  ·   ·  Flag as inappropriate

        Please, why hasnt this been fixed yet. It greats a massive amount of headache to achieve such simple tasks!! Been waiting for a year now and was told by support it was being addressed.

      • Derek commented  ·   ·  Flag as inappropriate

        This feature would make my life a million times easier. Please move this along. Is this an issue in 0365?

      • WebSolProv commented  ·   ·  Flag as inappropriate

        Yes please do enable this, especially for sharepoint sites that sit as subsites to the location of the list you want to pull data from; just like site lookup columns this should be possible

      • Anonymous commented  ·   ·  Flag as inappropriate

        PLEASE FIX! Having to make separate API calls for each one to get the expanded data causes inoperable load times!

      • Paul F. commented  ·   ·  Flag as inappropriate

        If one field causes an error, at least let us use the other fields from the list. But because I have one lookup field that is causing this problem I cannot work with the list at all with a Flow.

      • John Liu commented  ·   ·  Flag as inappropriate

        The Flow Connector to SharePoint fails when there's a LookupField with a list in another web.

        "message": "The field 'StoreListLocation' is not supported in query. The lookup list is in another web.
        "source": "https://xxxx.sharepoint.com/intranet/hr/_api/SP.APIHubConnector.GetListItem(listName='0475bf8e-98be-...')"
        "Microsoft.SharePoint.SPException"

        Even though this is related to Flow, because the connector that's erroring is _api/SP.APIHubConnector, I think this is something that SharePoint needs to fix.

        If cross site lookups can't happen, just the LookupId will be fine. In Flow, we can make a second request to perform a manual lookup resolution if needed.

      • Peter commented  ·   ·  Flag as inappropriate

        I fully agree that this needs to be fixed.

        However I would just like to log here a workaround for WF.

        In a WF, you can get the Id of the site lookup column by appending "Id" onto the name of the column when doing a REST query to the current item. e.g. "siteColumnName" becomes "siteColumnNameId" in your $select filter to the current item. Once you have this Id, you can do another query to the actual list where the lookup data is stored and fetch it that way.

        Obviously adds a bit more bulk to your workflow but it gets the job done for now.

        Here's hoping they actually fix this properly though.

      • Wim commented  ·   ·  Flag as inappropriate

        We are moving from Sharepoint on prem to Sharepoint online. I never had this issue on prem. We absolutely need this to work again online. We use site column lookups a lot.

      • Steven Wong commented  ·   ·  Flag as inappropriate

        any update on this? For our JavaScript work, we had to change most of our calls to use "FieldValuesAsHtml" which is much heavier that directly requesting only the fields you need with "$select". Please fix this...it will also fix MS Flow and PowerApps as its the same blocking issue.

        Flow: https://powerusers.microsoft.com/t5/Flow-Ideas/Support-SharePoint-Lists-with-Lookup-Fields-that-point-to/idi-p/27577

      • sympmarc commented  ·   ·  Flag as inappropriate

        This bit me again today in a SharePoint Designer workflow - I wasn't even making a REST call myself!

      • Ofer Gal commented  ·   ·  Flag as inappropriate

        I have the same issue with SP 2016 in a discussion board that has 2 site columns that are multi select lookup to lists that are on the root.

        Wonder if I will be able to do it with JsoM

      • Stefanie Sloper commented  ·   ·  Flag as inappropriate

        Just came across this as well in Flow, it did not like my list with lookup columns pointing to lists in the root site. Hope it can be fixed!

      • Steven 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 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

      ← Previous 1

      Feedback and Knowledge Base