How to get all possible values for SPFieldLookup

Question

I have a lookup field in sharepoint which just references another list. I wonder how do I programatically enumerate all possible values for this field? For example, my lookup field "Actual City" refers list "Cities" and column "Title", I have 3 cities there. In code I would like to get list of all possible values for field "Actual City", smth like (metacode, sorry):

SPFieldLookup f = myList["Actual City"];
Collection availableValues = f.GetAllPossibleValues();
//this should return collection with all cities a user might select for the field

Answer 1

I think there is no explicit method returning what you want. But the SPFieldLookup class stores all the info you need to request this information manually: LookupField and LookupList

So you could retrieve the information by getting it form the list you lookup field uses. To make it reusable you could implement it as a Extension Method. So the next time you could really call f.GetAllPossibleValues();.

Answer 2

As I understand you want to query all values that are in use?

If so, you would have to query items where Actual City is not null, query would look something like:

<Where><IsNotNull><FieldRef Name='Actual City'/></IsNotNull></Where>

Then, for each queried item you would

List<SPFieldLookupValue> result = new List<SPFieldLookupValue>(returnedItemCount * 5);

foreach (SPListItem item in queriedItems) {
  object lookup = item["Actual City"];
  SPFieldLookupValueCollection lookupValues = new SPFIeldLookupValueCollection(
    (lookup != null) ? lookup.ToString() : ""
  );
  foreach (SPFieldLookupValue lookupValue in lookupValues) {
    if (!result.Contains(lookupValue)) {
      result.Add(lookupValue);
    }
  }
}

Or you could use HashTable where LookupId would be string and LookupValue would be int id and then check if HashTable.ContainsKey(lookupId)... must be faster to find an integer in hashtable rather than string in list, but the resource intensive part is to probably query all items where that field contains some value and then loop...

Answer 3

If you want to enumerate all possible values, that means you basically want to get all the Title field values from all the items in the Cities list. I don't think there is a method like GetAllPossibleValues() in SharePoint, but you can either just list all the items in Cities and get their titles, if there's just a few, or use a CAML query if there's plenty.

Answer 4

I wrote some code to handle this for my project just the other day. Perhaps it will help.

    public static List<SPFieldLookupValue> GetLookupFieldValues(SPList list, string fieldName)
    {
        var results = new List<SPFieldLookupValue>();
        var field = list.Fields.GetField(fieldName);

        if (field.Type != SPFieldType.Lookup) throw new SPException(String.Format("The field {0} is not a lookup field.", fieldName));

        var lookupField = field as SPFieldLookup;
        var lookupList = list.ParentWeb.Lists[Guid.Parse(lookupField.LookupList)];
        var query = new SPQuery();

        query.Query = String.Format("<OrderBy><FieldRef Name='{0}'/></OrderBy>", lookupField.LookupField);

        foreach (SPListItem item in lookupList.GetItems(query))
        {
            results.Add(new SPFieldLookupValue(item.ID, item[lookupField.LookupField].ToString()));
        }

        return results;
    }

Then to use it, your code would look something like this:

        var list = SPContext.Current.Web.Lists["My List"];
        var results = GetLookupFieldValues(list, "Actual City");

        foreach (SPFieldLookupValue result in results)
        {
            var value = result.LookupValue;
            var id = result.LookupId;
        }