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."}}}
sympmarc
shared this idea
Thanks for your feedback! We’re happy to report that your suggestion is now part of the RenderListDataAsStream method in the list REST APIs. You can find updated documentation on the RenderListDataAsStream operation from SharePoint Dev documentation at https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/working-with-lists-and-list-items-with-rest#working-with-list-items-by-using-rest.
This is the same method which is used by 1st party functionalities to resolve complex fields such as lookups and managed metadata fields.
30 comments
-
WebSolProv
commented
Does anyone have any comments / help on how you would implement a call to this new RenderListDataAsStream end point from a SP2013 workflow?
-
Anonymous
commented
Not available in Microsoft Flow. I have a site column that is a lookup - single line of text - multiple values. I can use the site column on my subsite custom list but I can't grab the values in Microsoft Flow. I don't see the field listed.
-
sympmarc
commented
I wrote a blog post with my take on the suggested solution. Unfortunately, it doesn't seem to me to fit the bill, but I'm happy to be proven wrong.
Blog Post: Enable support for lookup columns in other webs in the REST API - Boom! It's Done? http://ow.ly/vMdW30j66fe
-
Marc Nemegeer
commented
Is there somewhere a sample on how to use this ?
-
Daniel Laskewitz
commented
Great news! :) Thanks for implementing this!
-
Anonymous
commented
Any news about it, yet?
-
JM
commented
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...
-
Anonymous
commented
Please fix this, im having to use SharePoint Designer 2010 workflows.......
-
Peanut Deedee
commented
PLEASE FIX THIS ALREADY WTF
-
Alex Cope-Norris
commented
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
This feature would make my life a million times easier. Please move this along. Is this an issue in 0365?
-
WebSolProv
commented
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
PLEASE FIX! Having to make separate API calls for each one to get the expanded data causes inoperable load times!
-
Paul F.
commented
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
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
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
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
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.
-
sympmarc
commented
This bit me again today in a SharePoint Designer workflow - I wasn't even making a REST call myself!
-
Ofer Gal
commented
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