This has been the pain in my side for a little while now. Let me explain the setup we’re working with and aiming towards. (If you dont really want to read all of this and just want to see the process of using workflow to populate a lookup field scroll down to where it says “Populate Lookup field using workflow!”)
We have a document library named “Drop Box” where someone can upload a document of any type, then specify its type in the metadata as well as filling in the field “Job Name”.
We have three more document librarys names “Specifications”, “Enquiries”, and “Quotes”.
We have a list called “Jobs Directory” – designed to be an overview of every job without needing to go into each individual library.
Workflow Task 1
When a document is uploaded, create item in “Jobs Directory” using the “Job Name” field specified in the current item.
Very simple. Taking the single line of text which is “Job Name” and putting into another single line of text field. Had no problems with this part.
The only issue I briefly encountered was how best to prevent it adding the Job Name with every document. We only wanted it to add the list item if the Job Name wasn’t already there. Still haven’t solved this with a particularly elegant solution, but my temporary workaround was to add a column to the form saying “First instance of this job?” with a checkbox. Like I said, not ideal. But if they’re unaware what else has happened in that job they should be collaborating more! Right?
Workflow task 2
Depending on the document type, selected when uploading to the dropbox, the document will be forwarded to one of the three libraries “Specification”, “Enquiries” or “Quotes”. For example if the document type is “Specification Document” it will be forwarded into the “Specification” Library, then deleted from the drop box.
This is where my issue came in. In order to maintain a consistent Job Name throughout the process (which is going to be continuing long after the stages I am currently sharing), the requirement was that the “Job Name” field in each destination library was a lookup to the “Job Name” field in the jobs Directory list.
When they asked if this could be done I said “Sure no problem, good idea guys”.
Turns out it is possible. Turns out it is remarkably easy. Turns out I didn’t have the foggiest…
I did however eventually find some great people who helped me out, and now I am able to share with you
How To Populate a Lookup Field Using SharePoint Designer Workflow.
The first thing you need to know is that SharePoint reads things differently to us. To us, the lookup field reads as a text value, its just that the text values we see are limited to those text values in another list.
Stop thinking like this.
In a lookup field SharePoint is looking up the ID of the list item. So when setting it up you’ve told it to look in the “Cars” list, and look in the “Manufacturer” Column. The two columns compare what we see, and what sharepoint sees:
And so on…
So the first step in your workflow to populate this lookup field is to get SharePoint to perform the lookup and store the result in a variable, so that we can call on it when we need it.
Click “Local Variables” in the “Variables” section of the ribbon.
In the box that pops up click “Add”.
Give your variable a meaningful name that you will recognise later. For Example “Car Manufacturer Lookup Value” – a bit long winded admittedly but I’m never in any doubt what it is.
From the “Type” dropdown list select “Integer”. Remember what I said about SharePoint looking up numbers rather than words? Thats what we’ve just told it its looking for.
Click OK on that window, then on the next window. We have just created the container for our lookup value to be stored in.
Next, either create a step or within an existing step, select “Set Workflow Variable” from the “Core Actions” section of the “Actions” list. Click “Workflow variable” and select the variable we just created. Then select the word value and choose where your value is coming from. This just uses the “fx” function the same way everything else in SharePoint Designer 2010 does. You need to tell it which list you’d like to retrieve from first. Then when it asks for “Field From Source” don’t be tempted to put in the actual column which houses the value we want. This is where SharePoint is interested only in the Item ID. Therefore, from the “Field from source” box, we select “ID”.
Tell Sharepoint how to find the correct item, for example, in my case it was “Job Name” equals “Current item:Job Name” or something similar.
Now, once the workflow has completed that stage the value we want inserted into our lookup field is stored in our variable.
When you reach the stage of wanting to utilise it, all you need do is apply the variable value as you would anything else. This works whether you simply want to update the item, or create a new item.
On the dialogue that pops up when you are inputting the value select the data source as “Workflow Variables and Parameters”, then from the “Field from source” list select the variable we created earlier.
Hey presto. Your workflow should be populating the lookup field.
Feel free to get in touch if you’d like further help on this, where I can help I will.