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.
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.
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:
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).
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.
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
Learn to digitize and optimize business processes and connect all your applications to share data in real time.
Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!