For one of my projects I’ve been using a SharePoint lookup field called MapRef. This is a lookup field that was created within the top of the the site collection so that I could add this lookup to a content type. The lookup column uses data from a list that was created at the root of the site collection. This should all be quite basic stuff.
Then I tried to create a flow that triggers on the creation of a new item and the following error message is returned:
The field ‘MapRef’ is not supported in query. The lookup list is in another web.
I found a couple of similar issues related to SharePoint Designer workflows from quite a while back. It looks like this is quite a long outstanding issue without a good answer other than create the list local to the lookup column in sub sites.
This also means that the lookup column cannot be part of the site collection content types and lookup columns need to be created on the list or site where your lookup is used. This of course becomes a pain if a lookup is used in multiple places.
Another alternative is to use a Managed Metadata column, however that doesn’t work as well if you want multiple columns from your lookup list to be displayed in views in your SharePoint list.
So what happens in the When and item is created trigger? When you reopen a flow you will get the clue:
All fields are collected form the list item and this includes the lookup field that apparently the APIs used can’t handle. This really needs a fix to the APIs used.
[…] Microsoft Flow: The field ‘FieldName’ is not supported in query. The lookup list is in another w… by Pieter Veenstra […]
Can’t find a useful solution for getting subsite lookup values based on parent lists / site columns through Flow, and they haven’t come up with a way to share list driven lookups across their nee modern sites. They do seem to be aware of the need, but incapable of implementing a solution. Even worse, any potential solutions do not appear on any planned roadmaps. Looks like they skimmed the easy APIs to add to Flow, and are just going to ignore anything ‘complex’. Very disappointing.
I am working with the Flow team in Redmond. In general the quickest way to get something done is to add it to the community site under ideas. Then create some noise around it.