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.