The Problem
I came across this issue due to the fact that you cannot use a Choice Column in a Lookup Field. Basically I wanted to use a Lookup Column in a Calculated field.
No dice.
Look here -> http://www.myfriedmind.com/techBlog/2010/12/01/Sharepoint2010LookupFieldsWillNotAllowChoiceColumn.aspx for more info.
Setup for Solution
So what I ended up doing was using some of the nifty new features in Visual Studio 2010 and its integration with Sharepoint 2010, particularly its exposure of List Events. Leveraging this I was able to create a project that generates a new Column based upon other Columns. Because it of the way the system functions I can snag the data from the Lookup Column and use it. In effect I can code my on Calculated Column.
There are a number of good blogs on this, but why not have one more? <g> Plus I will talk about some extra stuff (like the best way to reference those Columns)...
Let us start with two lists - DataLookup and EventListenerTest.
DataLookup is very simply - it merely consists of the Title Column. Its sole purpose is to provide a Lookup Column to EventListenerTest. Let's populate it with four entries with the data "First DataEntry Lookup", "Second DataEntry Lookup", "Third DataEntry Lookup", and (of course) "Fourth DataEntry Lookup".

EventListenerTest consists of four Columns besides Title - a Single Line of Text Column named "SingleLineOfText", a Choice Column named "ChoiceColumn", a DateTime Column called (you guessed it) DateAndTime with a default of the current date and time, and a Lookup Column referencing the Title from DataLookup that is named "DataEntryLookup". Not very creative, but it gets the job done.

Our goal is to populate the Title Column with the text generated by combining the entries contained in the SingleLineofText, the ChoiceColumn and the DataEntryLookup with spaces in between. Basically [SingleLineOfText] & " " & [ChoiceColumn] & " " & [DataEntryLookup]. Were we to attempt to do this in a Calculated Column it would not work because the DataEntryLookup would not be available due to its being of the Lookup Column type. Please note there are 'workarounds' that involve tricking Sharepoint 2010 into allowing a Calculated Column to reference a Lookup Column. That is not what I am aiming towards. Incidentally, I chose the Title column to help show multiple ways to reference columns.
Creating the Project
Creating the Project in Visual Studio 2010 is cake.
- Create a new project and from within the Installed Templates. Select Sharepoint; 2010; then EventReceiver. Give it an appropriate name and click OK.

- On the next screen you have to decide whether to go sandboxed or farm.
This is a bigger issue than at first glance. Normally I would recommend sandboxed for two reason. First, if you do go to the 'public cloud' you will need to have it sandboxed unless you are paying for your own box. Second, this should in theory minimize the amount of times that this program runs. Because EventReceivers are not assigned to specific lists just to ListTypes, they fire on Every matching ListType Every Time. It is much nicer to have it fire only on those Lists in the appropriate Site rather than on every List in your Web.

- Now you get to select the type of event(s) that you want to interact with. In this case you will want "List Item Events".
We are going to select both "An item is being added" (ItemAdding) and "An item is being updated" (ItemUpdating). For demo purposes I am also going to scroll down and select "An item was added" (ItemAdded) and "An item was updated" (ItemUpdated).
You also get to choose what type of list that you want these Events to be captured on. You most likely want to change your event source to be "Custom List". If we selected "Announcements" (the default) for example, our code would only run when Lists of the type Announcements Added or Updated to. See http://www.myfriedmind.com/techBlog/2011/08/17/Sharepoint2010ListEventCodeNotBeingTriggered.aspx.
Event "properties" - SPItemEventProperties
The project now displays with four overridden Methods - ItemAdding; ItemUpdating; ItemAdded; and ItemUpdated because you are inheriting from SPItemEventReceiver. Just as in any project, you can simply add more overridden events if you so choose at any time. HOWEVER, if you do this after the project has been created (and not through the wizard) then you MUST modify the Elements.XML file or these events will never be captured. There is a simple way to do this by modifying the properties of the folder that contains the Elements.xml file. See http://www.myfriedmind.com/techBlog/2011/08/17/Sharepoint2010ListEventCodeNotBeingTriggered.aspx for more info.

It is important to note that they all passed a single object - an SPItemEventProperties object referred to as "properties". Since we all refactor, we are going then simply pass that along to a common Method which we will call SetCalculatedTitle. There are two things to note here which we will utilize later. First, not all the data is populated equally depending on the SPReceiverEventType (eg ItemAdded vs ItemAdding). Second, we can determine what the SPReceiverEventType is from the SPItemEventProperties.
To demonstrate what each of these four events exposes we are going to add some code to this Method. The first thing we have to do is verify that we are in the correct List. This is achieved by checking the ListTitle property of the SPItemEventProperties object that we passed in. If it is the correct List, then we will look at particular aspects to see what each of the events are passed in.
Important Warning: changing the Title of the List will prevent this code from running. Unlike Columns, which can be referenced via their internal names (see http://www.myfriedmind.com/techBlog/2011/07/25/Sharepoint2010HowBestToReferenceAColumnFieldFromAnSPListItem.aspx for more on this), there is no Internal Name property of a List. There is a clunky workaround I will blog about later, but for now just cross your fingers and hope that your users do not rename the Title of the List.
The SPItemEventProperties object contains a number of properties, including
- The name of the List Title
- The Event Type (or SPEventReceiverType) such as "ItemAdding"
- The SPListItem itself that is triggering the Event
- An SPItemEventDataCollection containing the properties of the ListItem before the event completes (called BeforeProperties)
- An SPItemEventDataCollection containing the properties of the ListItem after the event completes (called AfterProperties)
What are the properties for each event?
In order to answer that question I wrote the following bit of code:
private void SetCalculatedTitle(SPItemEventProperties properties)
{
if (properties.ListTitle == "EventListenerTest")
{
SPListItem _listItem = properties.ListItem;
SPItemEventDataCollection _beforeProperties = properties.BeforeProperties;
SPItemEventDataCollection _afterProperties = properties.AfterProperties;
bool _hasBeforeProperties = HasItems(_beforeProperties);
bool _hasAfterProperties = HasItems(_afterProperties);
string _singleLineOfTextFromItem;
string _singleLineOfTextFromBeforeProperties;
string _singleLineOfTextFromAfterProperties;
if (properties.EventType != SPEventReceiverType.ItemAdding)
{
_singleLineOfTextFromItem = _listItem["SingleLineOfText"].ToString();
}
if (_hasBeforeProperties)
{
_singleLineOfTextFromBeforeProperties = _beforeProperties["SingleLineOfText"].ToString();
}
if (_hasAfterProperties)
{
_singleLineOfTextFromAfterProperties = _afterProperties["SingleLineOfText"].ToString();
}
int _numberOfBeforePropertiesChanged = properties.BeforeProperties.ChangedProperties.Count;
int _numberOfAfterPropertiesChanged = properties.AfterProperties.ChangedProperties.Count;
}
}
If you are curious, the HasItems was a simple static method I wrote to return a boolean as such:
private static bool HasItems(SPItemEventDataCollection collection)
{
bool _hasItem = false;
foreach (object o in collection)
{
_hasItem = true;
}
return _hasItem;
}
Each of the four Methods calls SetCalculatedTitle and passes the properties object, like so:
public override void ItemAdding(SPItemEventProperties properties)
{
SetCalculatedTitle(properties);
}
public override void ItemUpdating(SPItemEventProperties properties)
{
SetCalculatedTitle(properties);
}
public override void ItemAdded(SPItemEventProperties properties)
{
SetCalculatedTitle(properties);
}
public override void ItemUpdated(SPItemEventProperties properties)
{
SetCalculatedTitle(properties);
}
So, what are the results? Let us say we create a new item, putting "Hi Mom" in the SingleLineOfText entry. We then update that item to change the SingleLineOfText to "Hi Dad".


ItemAdding

As you can see, the ListItem does not exist (it has not been added), there are no BeforeProperties, but there are AfterProperties. In fact, this is the only place where you can find out what is going on.
As you can see, the AfterProperties for the SingleLineOfText column is "Hi Mom". There is one major showstopper here, which I will also mention later, and that is that Lookup Columns (which contain a Key called LookupId and a Value called LookupValue) do NOT have their LookupValue set yet. They do have their LookupId, but it is only when the ListItem is instantiated that the LookupValue is then assigned. This will play a crucial role later as we shall see.
ItemAdded

By this time the ListItem exists. This is, of course, because the Item has been Added. There are still no BeforeProperties, but there are AfterProperties - in fact the same as in ItemAdding. Both the ListItem properties and the AfterProperties contain the same column information - they both show that SingleLineOfText is "Hi Mom". Also, unlike ItemAdding, the LookupValue of a Lookup Column has been set in the ListItem.
ItemUpdating

Here is where it gets interesting. The ListItem exists (of course). There are STILL no BeforeProperties, but there are AfterProperties. However the value of SingleLineOfText is different in the ListItem (reflecting the original value) and the AfterProperties (reflecting the new value). ListItem shows "Hi Mom", AfterProperties shows "Hi Dad". Again, like ItemAdding, the LookupValue of a Lookup Column is not set in the AfterProperties although its LookupId is. Note that the LookupValue of a Lookup Column exists in the ListItem, but it is for the original (not updated) value.
ItemUpdated

Unlike ItemUpdating, by this time it has already been Updated, and so the AfterProperties and the ListItem column information for SingleLineOfText both show "Hi Dad". Similar to ItemAdding the LookupValue of the Lookup Column has been set.
So, where do you set the value of Title?
Is it ItemAdding or ItemAdded? Do you want ItemUpdating or
ItemUpdated?
The answer seems obvious. It is not.
If you were paying attention above you will note that the common thread is AfterProperties. ItemAdding does not have the ListItem yet, and nobody has a BeforeProperties, but all have the AfterProperties set to the new values. Except, of course, if you have a Lookup Column.
Since we are going to be using a Lookup Column in our code, we need to create an SPFieldLookupValue object from the string that is stored in that Column. You then can access the LookupValue property to get the string you want to use in your code. You would like to do it this way:
SPItemEventDataCollection _afterProperties = properties.AfterProperties;
SPFieldLookupValue _dataEntryLookup =
new SPFieldLookupValue(_afterProperties["DataEntryLookup"].ToString());
But you can't.
The issue is that the AfterProperties contains the LookupId, but NOT the LookupValue. That LookupValue is not assigned until the Item is actually Added or Updated. It is only then that you can get the LookupValue from an SPFieldLookupValue object. So you have to do it this way:
SPListItem _listItem = properties.ListItem;
SPFieldLookupValue _dataEntryLookup =
new SPFieldLookupValue(_listItem["DataEntryLookup"].ToString());
This will not work in ItemAdding/ItemUpdating. This is because ItemAdding does not have a ListItem yet, and because the ListItem in ItemUpdating contains the old values. If the Lookup Column's value has changed then referring to the ListItem will only give you the old value, not the new.
This means that if you need to use a Lookup Column as part of your code, you can not use ItemAdding or the ItemUpdating without jumping through some ugly hoops. You can still use it for validation checking (using LookupID for example), but it gets messy real fast. Ultimately this means that if you have a Lookup Column that you are needing the LookupValue for you have to use ItemAdded and ItemUpdated. You could do some validation in the other two Events like I said, just do not expect it to be smooth.
So, you will need to use ItemAdded/ItemUpdated. As a result, the generating the new text would look something like this:
SPListItem _listItem = properties.ListItem;
SPFieldLookupValue _dataEntryLookup =
new SPFieldLookupValue(_listItem["DataEntryLookup"].ToString());
string _newTextForTitle =
String.Format("{0} {1} {2}", _listItem["SingleLineOfText"],
_listItem["ChoiceColumn"], _dataEntryLookup.LookupValue);
Assigning it would be equally as simple you would think. What you want to do is assign it to the AfterProperties.
Remember those? The AfterProperties are what are assigned to the Added/Updated ListItem. For example, to assign a new Title, you can change it directly like so:
SPItemEventDataCollection _afterProperties = properties.AfterProperties;
_afterProperties["Title"] = _newTextForTitle;
Thus when the Item is Added or Updated it is assigned the new Title. The problem is that AfterProperties only apply BEFORE the item is Added or Updated. As a result while you can modify the AfterProperties in ItemAdding and ItemUpdating, You cannot in ItemAdded and ItemUpdated. Once the ListItem has been Added or Updated, the AfterProperties cannot be changed.
What about directly changing the ListItem itself in ItemAdded and ItemUpdated? Like so:
SPListItem _listItem = properties.ListItem;
SPFieldLookupValue _dataEntryLookup =
new SPFieldLookupValue(_listItem["DataEntryLookup"].ToString());
string _newTextForTitle =
String.Format("{0} {1} {2}", _listItem["SingleLineOfText"],
_listItem["ChoiceColumn"], _dataEntryLookup.LookupValue);
_listItem[new Guid("fa564e0f-0c70-4ab9-b863-0177e6ddd247")] = _newTextForTitle; // we use the Title Column's GUID for demo
We are definitely getting warmer. The only issue is that running this code, while it does not throw an error, also does not change the value of the Title Column. Why? Because the Events that we are running this in happen AFTER the ListItem has been changed. In order to get this change applied we need to call the SystemUpdate event on the ListItem, like so:
_listItem.SystemUpdate();
Except that this throws errors. Why? Because we are triggering the code AGAIN. The SystemUpdate method of a SPListItem updates the database, which would trigger an Event, correct? What we need to do is turn off Event notifications for this section of code, made the change, and turn then back on again
this.EventFiringEnabled = false;
_listItem.SystemUpdateUpdate();
this.EventFiringEnabled = true;
Now our code is using the ItemAdded/ItemUpdated Methods to get the values and update the Title Column.
Conclusion
In an ideal world there would be one process, one Event to rule them all. In this case you have options, which is not always a bad thing. The pivotal decision is whether you are using a Lookup Column in our code. If you are then you should use ItemAdded and ItemUpdated because the LookupValue of the Lookup Column is not set until after the changes have been made. If you are not using a Lookup Column, then you are free to use ItemAdding and ItemUpdating, just remember to use the AfterProperties and not the ListItem to get the Columns' values.
- If using a Lookup Column in your code, using ItemAdded and ItemUpdated. Use the ListItem to get and set the Columns' values
- If not using a Lookup Column, you can use ItemAdding and ItemUpdating. Use AfterProperties to get and set the Columns' values
Below is the basic code in which we are using ItemAdded and ItemUpdated:
public class EventReceiver1 : SPItemEventReceiver
{
public override void ItemAdded(SPItemEventProperties properties)
{
SetCalculatedTitle(properties);
}
public override void ItemUpdated(SPItemEventProperties properties)
{
SetCalculatedTitle(properties);
}
private void SetCalculatedTitle(SPItemEventProperties properties)
{
if (properties.ListTitle == "EventListenerTest")
{
if (properties.EventType == SPEventReceiverType.ItemAdded ||
properties.EventType == SPEventReceiverType.ItemUpdated)
{
SPListItem _listItem = properties.ListItem;
SPFieldLookupValue _dataEntryLookup =
new SPFieldLookupValue(_listItem["DataEntryLookup"].ToString());
string _newTextForTitle =
String.Format("{0} {1} {2}", _listItem["SingleLineOfText"],
_listItem["ChoiceColumn"], _dataEntryLookup.LookupValue);
// note that you could also reference the Title column as _listItem["Title"]
_listItem[new Guid("fa564e0f-0c70-4ab9-b863-0177e6ddd247")] = _newTextForTitle;
this.EventFiringEnabled = false;
_listItem.SystemUpdate();
this.EventFiringEnabled = true;
}
}
}
}