# Setting a Lookupfield for current Item in List

my use-case:

I'm trying to implement an event-management system to sharepoint using visual web part(s).

I have two custom lists, one is called "events". Let's say it has a column "Title" which shows the Event Details in a Displayform (the out-of-the-box title field it is, actually) and some other non-relevant details in other columns.

The second List is called "Attendees". There are two columns, first: "Attendee" (user field getting the loginname of the current user). Second: "Event" (at the moment: String of the Event title).

At my visual webpart (added via &ToolPaneView=2 above display form of the "Events"-List) I show buttons ("register" and "unregister") depending on the following code:

...
using System.Linq;
...
//Snippet of prerequisites for better understanding
SPWeb web = SPContext.Current.Web;
SPUser currentUser = web.CurrentUser;
SPListItem currentEventItem = (SPListItem)SPContext.Current.Item;
string userString = currentUser.ID.ToString() + ";#" + currentUser.LoginName.ToString();
...
private void GetButtonView()
{
try
{
bool foundUsers = (from SPListItem item in tn.Items
where item["Attendee"].ToString() == userString
&& item["Event"].ToString() == currentEventItem.Title
select item).Count() > 0;
if (foundUsers)
{
...
ButtonRegister.Visible = false;
}
else
{
...
ButtonUnregister.Visible = false;
}
}
catch (Exception ex)
{
...
}
}


Problem at this use-case: If the event title ("Title") is changed later on in the events-list, I don't get a correct mapping attendee => event because at the moment I check only for matching of title-strings.

Preferred Solution: Make the "Event" Column at the Atendees-List a lookup column which gets the actual Event (ID and the title, I think) and shows the title in the "Attendees" List "Event"-column, so if the title is changed later on in the "Events"-List, the attendees-list will automatically update the referring entries in its "Event"-Column and I can show the "Register"/"Unregister"-Button (apart from having a correct mapping, which is necessary for sure ;) ).

It would be great if someone could give me a solution/hint how to do this programmatically for I don't get it to work.

Best regards, Dominik

p.s. I am german so I hope you understand my question. Feel free to ask for more details! I'll try my best to explain it in proper english.

edit 1: Here's my so-far addAttendee-method, too:

private void AddAttendee() {
try
{
//Using this because read-permissions are used for attendees
SPSecurity.RunWithElevatedPrivileges(delegate()
{
using (SPSite ElevatedSite = new SPSite(site.ID))
{
using (SPWeb ElevatedWeb = ElevatedSite.OpenWeb(web.ID))
{
SPList attendeeList= ElevatedWeb.Lists["Attendees"];
SPListItemCollection listItems = attendeeList.Items;
item["Name"] = currentUser.ID.ToString() + ";#" +
item["Event"] = currentEventItem["Title"];
item.Update();
...
}
}
});
}
catch (Exception ex)
{
...
}
}


As far as I know, changing field type from text to lookup is not supported in Sharepoint.

You should create new lookup field in the Attendees list and point it to Events list, either from Sharepoint list settings, or from code.

Basically, lookup field has the same structure as user field, which you populated in the final bit of your code - ID#;Title. Also, lookup field value is represented by SPFieldLookupValue class, so it can be retrieved like that:

return new SPFieldLookupValue((string)item["EventLookup"]);


You can set lookup field value by assigning new SPFieldLookupValue object to SPListItem property:

item["EventLookup"] = new SPFieldLookupValue(1, "Great Event");


where 1 - Event list item Id and "Great Event" - Event list Item title.

If I also may add some remarks to your code examples. As far as I can see, you are searching attendees for the event by iterating through SPList.Items object and comparing a string value of "Attendee" field with custom string value.

Such a search can return wrong and slow results, because usually user lookup field contains user display name, not a Login Name. I'd recommend using CAML query instead:

SPQuery qry = new SPQuery();
qry.Query =
"  <Where>"
+"    <And>"
+"      <Eq>"
+"          <FieldRef Name='Event' />"
+"          <Value Type='Text'>"+ currentEventItem.Title +"</Value>"
+"      </Eq>"
+"      <Eq>"
+"          <FieldRef Name='Attendee' LookupId='TRUE' />"
+"          <Value Type='Integer'>"+ currentUser.ID.ToString() +"</Value>"
+"      </Eq>"
+"    </And>"
+"  </Where>";
SPListItemCollection listItems = spList.GetItems(qry);
bool foundUsers = listItems.Count > 0;


The query above searches by Event field (type "text" as in your example) and by Attendee user Id without user login Name. Also it retrieves only items, that fall whithin the query where clause.

• Thanks for your advice, i'll try it out in just a few hours :) Actually, my bike was stolen the night before so I have to go to the police first to make a report. More later on. – Dominik Dec 5 '12 at 9:54
• just a few more remarks: I have to use the LoginName property for it is the only available name in productive environment. Apart from that: So it is possible to just delete the string column "Event" in "Attendees"-List, make another one (lookup) and does it get the value automatically if i add another item or do i have to add it with code? – Dominik Dec 5 '12 at 10:05
• last comment: got it to work, sorry for the dumb question in the last comment, wasn't really in the mood to think over it ;) thank you! – Dominik Dec 5 '12 at 11:40
• You are welcome =) I personally don't like strings in code. I try to shove them into constants, or use strict Ids instead. =) I once had an issue with LoginNames and Titles. Part of my project's code was searching user by their Title, assuming it was LoginName. It really was - until user profile sync was activated =) So at once, all users from AD was updated and titles started to contain real user names instead of login. It ruined some user search logic in various places, so my rule of thumb - in code try to use only concrete data i.e. something that cannot suddenly change =) – Egor Emelianov Dec 6 '12 at 8:17