SharePoint trigger fails when lookup field points to list in another web

[Anonymous]

Hello, in SharePoint, when creating a Lookup Field, it is possible (via API - they don't really allow this via the default UI) to point the source of a Lookup Field to a different Web.  

https://msdn.microsoft.com/en-us/library/ff798514.aspx 

This is a fairly common design in applications that spam a few subwebs.  The definition lists are at the root, and the varioius lookup fields are in subwebs.  This is also how the User Information List works behind the scenes.

Now in the SharePoint Flow trigger on item created/modified - if the source list has a lookup that points to a different web, we are seeing this error:

The field 'StoreListLocation' is not supported in query. The lookup list is in another web.

Now I understand that Flow looks at a Web at a time, so it's having a hard time figuring out the lookup from a different web.  So I'm not really expecting the Lookup values to be resolved.  Can we just have it skipped?  Or perhaps just pass the Lookup ID value of the field.  And if we want to do some actual lookup - we can make a separate Get List Item connection to the root web to lookup that.

I'd like to use Flow to trigger on lists that have such lookup fields, rather than nothing.

[Anonymous]

{
"status": 400,
"message": "The field 'StoreListLocation' is not supported in query. The lookup list is in another web.\r\nclientRequestId: d8172dc6-1c27-46d0-8a1a-a7c438f9c055",
"source": "https://xxxx.sharepoint.com/intranet/hr/_api/SP.APIHubConnector.GetListItem(listName='0475bf8e-98be-...')",
"errors": [
"-1",
"Microsoft.SharePoint.SPException"
]
}

This implies the Exception was thrown from SP.APIHubConnector - could be something that the SharePoint team need to look at.

Microsoft.SharePoint.SPException implies that they are using the Server Side Object Model which is disappointing, thought the promise was that MS wasn't going to build backdoors...

[ivan_wilson]

You can create a lookup to a separate web without coding by defining a lookup site column in one web and then using it in a subweb

[v-yamao-msft]

Hi spg-john-liu,

It seems that the lookup column performs a lookup on a list on a different site on your side.

I am afraid that a lookup column that looks up to a different site is not supported currently in Flow.

But it seems that there is a workaround for this issue, please check this blog for a reference:
https://sharepointlibrarian.com/2017/05/08/solution-microsoft-flow-error-the-field-is-not-supported-...

Best regards,
Mabel Mao

[Anonymous]

Hi Mabel,

The workaround is very unacceptable.  Imagine if Flow can not lookup a record in a SQL database table, and the only workaround is to create another SQL database table, create a T-SQL trigger that copies every row to a new table, and then perform Flow lookup from that.  It is a very very poor choice.

I suggest the correct workaround should be this:

When the Flow connector sees a lookup field that is looking up a different site - the connector should just return the value (typically, just a ID number field).  

https://msdn.microsoft.com/en-us/library/office/microsoft.sharepoint.client.fieldlookupvalue.lookupi...

It doesn't need to resolve the actual FieldLookupValue.LookupValue - since to check that we need a second connection to a different site.  We can do that as a second step in the Flow. 

We don't need the connector to try to do the JOIN and then fail.

This needs a change in the Trigger and Action of connecting to any SharePoint List.

[Anonymous]

Adding an additional related link.

https://sharepoint.uservoice.com/forums/329220-sharepoint-dev-platform/suggestions/9065329-enable-su...