Solution: Microsoft Flow error “The field ‘…’ is not supported in query. The lookup list is in another web.”

Perhaps you, like me, built an exciting Microsoft Flow workflow and let it go into the wild without much additional thought. But at some point, you drop a lookup column into the mix and your Flow stops working. It tells you the field is not supported in query, even if that specific field isn’t being utilized in the Flow. I believe this has something to do with REST, but we won’t dwell on the cause – let’s get to the workaround.

The scenario I’ll be using is my cross-site publishing alternative using Microsoft Flow where I’m basically copying data from list items in one site collection to create new list items in a different site collection. This is helpful when someone does some sort of data entry once, and other people are then entering much of the exact same data. This copies all of the overlapping data to a new list item for the second site collection to reduce duplication of work.

It sounds simple but with a lookup column in the destination list we get the error. For this I’ll be using SharePoint Designer and Microsoft Flow (of course) in combination, though you could certainly try it all in Microsoft Flow. I just find parts of the process simpler in SPD. And while your origin data may be different (MailChimp, Twitter, etc.), and your exact scenario may differ, this workaround should still have value in concept.

Concept: Create a hidden list to act as a lookup-free, temporary home near the final destination that can safely receive data from Flow and then move that data into the final destination with lookup columns.

1. In SharePoint: Create a “temporary” list which is a duplicate of your destination list WITHOUT the lookup columns. Call it Temp_ListName. This list does not need to appear in any navigation whatsoever. We’ll only visit it for troubleshooting in the future via Site Contents.
   Note: If you are wanting to copy data from Flow into the trouble-making lookup columns eventually, create single-line of text columns as placeholders for now. Note that the incoming data will need to match values in the eventual lookup columns to transfer correctly.
2. In Microsoft Flow: Change your Flow’s “destination” list so that it creates items in the temporary list instead of the final destination list. Since you basically copied the final list to create the temp list, this should be relatively simple. Flow tends to remember the field matching when switching destination lists as long as the field names match.
   Note: Don’t forget to map any origin lookup columns, if applicable, to the new single-line text fields instead of lookup columns (which should not exist in your temp list).
3. In SharePoint Designer: Create a SharePoint Designer workflow (2010 or 2013) also called Temp_ListName. Give it a snazzy description like “temporary space station for interception of cross-galaxy satellite transmissions” or something more practical like, “acts as lookup-friendly ‘middle” step between Microsoft Flow and destination list. Copies new items from Flow to final destination list, then deletes temporary item.”
   1. Now that I’ve spoiled the rest with an adequate description, set your new workflow to run whenever a new item is created and manually (in case you ever need to force an item through for some reason).
   2. For your first workflow step, do “Create item” in the final destination list. For me, this is MasterData.
   3. Set all of the fields in your new item to match the original fields. If your lists (origin and destination) have matching fields this will be easier. Name to name, address to address, etc.
   4. The second/last step of the workflow is to delete the current item. Flow copied data to our temp list, our SPD workflow copied the temporary data to its final destination, now we can delete the item on the temp list and save some valuable space.
   5. If you’re doing a 2013 workflow, be sure to set the “Go to” to End of Workflow.
   6. Publish.

That’s it! Test it from the beginning by triggering your Microsoft Flow. In my scenario:

1. List item copies from origin list to temp list via Microsoft Flow
2. SPD workflow copies list item from temp list to final list locally (not across site collections)
3. SPD workflow deletes the temporary list item