How do I Filter a Lookup field based on a 2nd columns value

Question

I have List_A = { Title, Type} & List_B = { Title, myLookUpField }

I want myLookUpField = Title, IF(Type=="Red")

I know....

List_B.Fields.AddLookUp("myLookUpField", List_A.ID, false);
SPFieldLookUp myCol = new SPFieldLookUp(List_B.Fields,"myLookUpField");
myCol.LookUpField = List_A.Fields["Title"].InternalName;

Im not sure how to properly restrict it to only accepting ListItems from List_A where Type is set to "Red"

Filtered Field? calulated Field? addDependent lookup? associated field?

Answer 1

I would venture the following solution (there may be an easier solution, if so I'd love to know it also):

Create a custom field type with Visual Studio that extends SPFieldLookup (this video helped me to get started down that path: http://www.youtube.com/watch?v=VPhYXgKRKq8 ).

Your custom field type can filter the lookup dropdown by a "related field" value in the field control class. You can also add validation if you wish to ensure the selected value does indeed meet the filter criteria... Here is a link to more info on the SPFieldLookup class: http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spfieldlookup.aspx

It seems daunting at first but once you understand how custom fields can be created by extending SharePoint field types (like SPFieldLookup) it is actually quite straightforward.