Scenario: you have a Nintex workflow and you want to create an item in a list, where one of its column is a lookup column to another list.
Background: this took *too* long to figure out. I had an initial version of a workflow; it was actually a list workflow, running on an item which already had the lookup value I needed. Using the ‘Create Item’ action to create an item in a different list (which had the same lookup column) was straight-forward; you simply reference the current item in a list lookup:
However, I came to replicate the same thing in a site workflow, and it was not so straight forward. The many variant attempts I had usually resulted in one of two things:
- No value created in the field
- Workflow failed with “Coercion Failed: Unable to transform the input lookup data into the requested type”
It’s the second issue that starts to hint at what’s going. What you need to know about lookup fields is that they consist of two components; the numeric ID of the item in the ‘source’ list and a string representation of the value of the item. This is separated by the literal ;#
To see this, setup a simple case – i.e., two custom lists. One ‘source’ list which has the lookup value you want to use. The ‘target’ or ‘consumer’ list that ultimately looks up into your source list. Create some entries in both and then grab powershell. My lists use a ‘shared’ column called ‘client ref’ for demo purposes. By looking at the XML properties of the SPListItem, we can this value. So in powershell…
$web = Get-SPWeb http://mysharepoinrt $list = $web.Lists["My List"] $list.GetItems() | select XML
In the ows_Client_x0020_Ref= bit; the 2 is the ID of the item in the source list and the XYV789 is the string representation of the value. These are separated by ;#
It is this entire value needs to be set on the ‘target’ list that has the lookup column. I am working in SP2016 and could not find a Nintex action that did this automatically, despite many, many attempts with ‘Create Item’ and ‘Set Field Value’ and so on.
You are supposed to be able to achieve this using the List Lookup section of a ‘Create Item’ action.
For me, no dice. I was finally able to get it to work in what feels like a hack. It shouldn’t be like this; it’s why we use Nintex in the first place. But by crafting the ID value directly using a Set Variable, it works:
And then poking that into a Create Item…
Hurrah. In in order to get those values, you can use a Query List action and a Build String, but that bit is easy.