cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

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

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.

 

 

 

 

 

5 REPLIES 5
Highlighted
Anonymous
Not applicable

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

{
"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...
Highlighted
Advocate II
Advocate II

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

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

Highlighted
Community Support
Community Support

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

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

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Anonymous
Not applicable

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

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.

Highlighted
Anonymous
Not applicable

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Upcoming Events

Experience what’s next for Power Automate

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Users online (6,927)