Solution: Converting lookup values to text and working around SharePoint error “lookup list is in another web”


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.

First and foremost, note that much of my trial and error was with SharePoint Designer 2013 workflows and Microsoft Flow. You must use a SharePoint Designer 2010 workflow for this to work. And remember, you can start a 2010 workflow from within a 2013 workflow, so it shouldn’t interfere too much with your larger processes.

You will need:

  • A new single line of text column for each lookup column you want to “convert” to text (i.e. WFTextDivision, WFTextUnit, etc.)
  • SharePoint Designer
  1. Create a SPD 2010 Workflow on the list which you’d like text values instead of lookup values. (List workflow –> select list name)
  2. Name and describe your workflow, then select SharePoint 2010 Workflow as platform type. Click OK.
  3. Type “update” and hit enter.
  4. Click “this list”
  5. Click “Add” and find the new TEXT field that matches the lookup column field you’re setting. Then click the “function” button next to “to this value”.
  6. Find the lookup column for the second field, and set “return field as” to “Lookup Value (as Text)”
  7. Click OK twice, and repeat steps 5-7 for each field you need to have converted. Then click OK.
  8. Go to the workflow settings and, under “Start Options”, set this to run manually, when items are created and when items are modified. This just ensures your text fields are always being updated to the lookup values.startoptions

That’s it! Publish your workflow, and your lookup columns’ values will always be converted to text. You can then use those WFTextABC columns in Power BI reports, workflows, if/then statements, calculated columns and more where we couldn’t before with lookup values.


8 Replies to “Solution: Converting lookup values to text and working around SharePoint error “lookup list is in another web””

  1. I don’t quite understand. I’m trying to run an approver workflow. I’d like to have the emails come from a lookup column. This pulling of strings would work. However, I’m not sure how to apply this rule in Designer. In Microsoft 365.

  2. Hi thanks a lot for the article.

    This process works pretty well for transforming to text only the lookup column but what about the fields that you bring from that lookup. Is any way/workaround to have these fields saved as text in the destination list ?

    Thank you very much in advance.

  3. The problem happens because the WF makes a Rest Query that include &expand=Division but you could make an individual rest query using the action “call web service” and only include the “DivisionId” and this value is useful to set another values, without expands

  4. May have missed a step, but I get a workflow cancelled saying, “Coercion Failed: Unable to transform the input lookup data into the requested type.”

    SharePoint 2010 Enterprise

  5. Hi Nate. Well written article! Have you tried to acheive the same thing with SharePoint Online (O365)? I’ve been trying for days to find a way to filter a powerapps gallery by comparing User().FullName with a column in the list (either People or Lookup column). It worked with a people column but only if ‘allow multiple selections’ is off. Any ideas?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.