Lookup columns aren’t friendly to a lot of things. Power BI reports, calculated columns, creating new items via workflow when both lists have lookup columns, if/then statements, etc. Especially when your lookup column is looking up to a list from another site, not the same subsite in which you’re working.
A previous scenario required that I create a new item in a different site’s list when conditions were met in the origin site’s list item. Both lists used the same lookup column, and I received the “lookup is in another web” error when trying to do a direct copy via workflow, from lookup column to lookup column. The solution ended up being creating a new item in a temporary, lookup-free list that received the lookup values just as text. Then SharePoint Designer copied those over to the final list, which received the text and happily converted it back to the appropriate lookup values. See the full solution here.
This post will focus on the same error message, but this time is triggered by a SharePoint Designer workflow in a different scenario where we just want to convert our lookup values to text so we can use them for various purposes.
To save you time, I also tried (and failed) at these potential solutions before finding success:
- Setting workflow variables to the lookup values and trying to set the variables to text values, or use the variables in my if/then statements to create new text values (this defeats the purpose of using lookup columns, of course)
- Using a number of combinations of Microsoft Flow and SharePoint Designer to get the data from the lookup column extracted then “pasted” back in as text
So let’s get to the solution. Feel free to comment with your scenario specifics – I’ve had a lot of experience with this error, and would be happy to help.