By: Kevin Lin
At some point or another, as a SharePoint developer, you will have to deal with lookup columns. Unfortunately, lookup columns are not as easy to handle as "Single line of text" columns. Fortunately, there is a plethora of information to aid you in this ordeal. The main thing I wanted to discuss with this blog post is how to programmatically deal with blank lookup columns to ensure that your code will function properly even though the lookup column is not populated, and also how the programmatic nature of a lookup column will vary when a lookup list expands beyond a certain point.
The first thing to note is that lookup lists come in two types. In the image below, the Game lookup contains a small amount of items and the Gamer Score lookup contains a large number of items. The Game lookup would be the first type, which is a normal drop down lookup column whereas the Gamer Score lookup would be the second type which not only changes the way the arrow looks but allows for the user to enter text searches to help filter through the list. Once a lookup list grows large enough, SharePoint will convert it from the first type to the second time.
Now that we know that there are two different types of lookup lists, let's try to make an simple workflow. This workflow will take the Game and Gamer Score lookups and put them in a separate comment field. Below is the code:
private void SetComment_ExecuteCode(object sender, EventArgs e)
{
SPListItem item = workflowProperties.Item;
SPFieldLookupValue gameLook = new SPFieldLookupValue(item["Game"].ToString());
string game = gameLook.LookupValue;
SPFieldLookupValue gamerScoreLook = new SPFieldLookupValue(item["Gamer Score"].ToString());
string gamerScore = gamerScoreLook.LookupValue;
string comment = GetComment(game, gamerScore);
item["Comment"] = comment;
item.Update();
}
private string GetComment(string game, string score)
{
return "Game: "+game+ Environment.NewLine+"Score: "+ score;
}
Since lookup column data converted to string format looks like "id#value" SPFieldLookupValue is used to parse the value part.
Below is an image of the results when the workflow is run. We see that when run, the workflow shows Error Occurred only when the Game lookup column is blank. But interestingly enough, there is no error when the Gamer Score lookup column is blank. What could be happening?
When coders don't know what's going on there is only one thing left to do, use the debugger. Upon debugging, we find the gamerScoreLook variable appears to be throwing an NullReferenceException. Let's remedy that with code.
Let's try checking for nulls with the code below, in addition I will add "Blank" if the column is blank. Below is an updated version of the code, the highlighted parts are the changes:
private void SetComment_ExecuteCode(object sender, EventArgs e)
{
SPListItem item = workflowProperties.Item;
string game = "Blank";
string gamerScore = "Blank";
if (item["Game"] != null)
{
SPFieldLookupValue gameLook = new SPFieldLookupValue(item["Game"].ToString());
game = gameLook.LookupValue;
}
if (item["Gamer Score"] != null)
{
SPFieldLookupValue gamerScoreLook = new SPFieldLookupValue(item["Gamer Score"].ToString());
gamerScore = gamerScoreLook.LookupValue;
}
string comment = GetComment(game, gamerScore);
item["Comment"] = comment;
item.Update();
}
private string GetComment(string game, string score)
{
return "Game: "+game+ Environment.NewLine+"Score: "+ score;
}
Let's try running our modified code. Below are the results when the workflow is run again on all items, what's interesting is that Score is still empty, it is not recording a "Blank" what could be happening?
Let's go back to the debugger and see what is going on, we will focus on seeing what value is stored in the gamerScore string. It appears that gamerScore is getting flipped from "Blank" to null. How is this possible? We already have an if statement detecting nulls.
In order to find the source of the problem we will have to add the items to quick watch. The values of item["Game"] and item["Gamer Score"] are listed below and we see that they have different values despite both being lookup columns. This is where my first point I mentioned comes into play. Remember that there are two types of lookup columns, the moment the lookup becomes the second type with a query box the programmatic nature changes. The item lookup field is never null even when blank, however when blank the LookupValue is null.
Keeping that in mind, let's rewrite the code again to also check for a blank or null lookup value.
private void SetComment_ExecuteCode(object sender, EventArgs e)
{
SPListItem item = workflowProperties.Item;
string game = "Blank";
string gamerScore = "Blank";
if (item["Game"] != null)
{
SPFieldLookupValue gameLook = new SPFieldLookupValue(item["Game"].ToString());
if (!string.IsNullOrEmpty(gameLook.LookupValue))
{
game = gameLook.LookupValue;
}
}
if (item["Gamer Score"] != null)
{
SPFieldLookupValue gamerScoreLook = new SPFieldLookupValue(item["Gamer Score"].ToString());
if (!string.IsNullOrEmpty(gamerScoreLook.LookupValue))
{
gamerScore = gamerScoreLook.LookupValue;
}
}
string comment = GetComment(game, gamerScore);
item["Comment"] = comment;
item.Update();
}
private string GetComment(string game, string score)
{
return "Game: "+game+ Environment.NewLine+"Score: "+ score;
}
Below are the results, the code finally works correctly.
On a final note, it is also possible to use (string)item["Game"] instead of item["Game"].ToString() and the correct code for that is listed below:
private void SetComment_ExecuteCode(object sender, EventArgs e)
{
SPListItem item = workflowProperties.Item;
string game = "Blank";
string gamerScore = "Blank";
SPFieldLookupValue gameLook = new SPFieldLookupValue((string)item["Game"]);
if (!string.IsNullOrEmpty(gameLook.LookupValue))
{
game = gameLook.LookupValue;
}
SPFieldLookupValue gamerScoreLook = new SPFieldLookupValue((string)item["Gamer Score"]);
if (!string.IsNullOrEmpty(gamerScoreLook.LookupValue))
{
gamerScore = gamerScoreLook.LookupValue;
}
string comment = GetComment(game, gamerScore);
item["Comment"] = comment;
item.Update();
}
private string GetComment(string game, string score)
{
return "Game: "+game+ Environment.NewLine+"Score: "+ score;
}
I hope you found this guide useful. If anything is unclear, or there are any issues feel free to post a comment or shoot me an email at klin@gig-werks.com
-- Kevin W. Lin