Wednesday, October 15, 2008

How to get look up column value with ID?

Hi guys,

Recently we are facing problem that how to get/retrieve direct id of look up field from the list (for multi select and single select both).
For implementing this we are facing some challenge.

We are using SPQuery are fetching data from a list.

If the list contains Look up field then you observed that
1) If the look up is single select then it will show you the text value of the referenced field( means text of the field)

2) If the look up is multi select then it will show you text value and ID of that list with “;#” separated but the problem is it will not give you ID of first field.

Most of the time we are using this method
SPList spList = spWeb.Lists[strListName];
SPQuery spQuery = new SPQuery();
spQuery.Query = strQuery;
SPListItemCollection spItemCollection = spList.GetItems(spQuery);
if (spItemCollection.Count > 0)
{
dtResult = spItemCollection.GetDataTable();
}


But the return data table has the problem mention above.

So after checking a lot on the net we cannot find anything.
Then after checking with reflector we came to know that SPListItemCollection.GetDataTable() method will check the field type, if type is lookup or user then it will remove first ID and “;#” from the field result.

So sadly we cannot do anything directly with this method.

But we can find the ID;#Value by SPListItemCollection only

SPListItemCollection spItemCollection = spList.GetItems(spQuery);

String strLookUpValue = Convert.ToString(spItemCollection[0][“Look up field display name”]);


So this way you can find out what you are looking for.

Here is the snippet for the same.
SPList spList = spWeb.Lists[strListName];
SPQuery spQuery = new SPQuery();
spQuery.Query = strQuery;
SPListItemCollection spItemCollection = spList.GetItems(spQuery);
if (spItemCollection.Count > 0)
{
dtResult = spItemCollection.GetDataTable();
for (int i = 0; i < spItemCollection.Count; i++)
{
for(int j = 0;j < spItemCollection[i].Fields.Count;j++)
{
if (spItemCollection[i].Fields[j].Type == SPFieldType.Lookup && !spItemCollection[i].Fields[j].ReadOnlyField && !spItemCollection[i].Fields[j].Hidden)
{
dtResult.Rows[i][spItemCollection[i].Fields[j].InternalName] = Convert.ToString(spItemCollection[i][spItemCollection[i].Fields[j].Title]);
}
}

}
}


Please keep in mind this method will cause performance issue if your query returns bulk data but will be very usefull for specific query where data can be countable on fingers. :)

2 comments:

FAAF said...

you can use this API to read lookup column from SPListItem

public static int ReadLookupId(SPListItem item, string columnName)
{
SPFieldLookupValue lookupField = new SPFieldLookupValue(item[columnName].ToString());
return int.Parse(lookupField.LookupValue);
}

SharePoint Kings said...

yes you can also refer this in one of our article

http://www.sharepointkings.com/2009/04/spfieldlookupvalue-and.html




Share your SharePoint Experiences with us...
As good as the SharePointKings is, we want to make it even better. One of our most valuable sources of input for our Blog Posts comes from ever enthusiastic Visitors/Readers. We welcome every Visitor/Reader to contribute their experiences with SharePoint. It may be in the form of a code stub, snippet, any tips and trick or any crazy thing you have tried with SharePoint.
Send your Articles to sharepointkings@gmail.com with your Profile Summary. We will Post them. The idea is to act as a bridge between you Readers!!!

If anyone would like to have their advertisement posted on this blog, please send us the requirement details to sharepointkings@gmail.com