SharePoint 2010 Filtered Lookup Column
Western Grains Research Foundation (WGRF) Research Project Management Tool & Database is implemented as a SharePoint 2010 multi-tenant site. The “database” contains a large number of lists. Many of these lists are linked to each other through lookup columns to provide referential integrity. These lookup columns are used instead of choice columns as these lists are very dynamic; with entries being added and changed frequently.
However, some of the Lookup tables have a very large number of entries which can make it difficult to locate a specific Lookup item. The Lookup tables have Choice columns to categorize the Lookup items.
We would like to filter / reduce the number of items returned from a Lookup table to just those items which are for a specific category. The 2 key elements to this solution are that the Calculated Column value is empty when the condition is not met and empty values are not returned from the Lookup list.
Joy Agnew has IsResearcher = Yes
Bob Anderson has IsBoard = Yes
Tracy Antonenko has IsWGRF = Yes
The following 2 web articles were very helpful:
How to Make a Filtered Lookup Field
Filter the Options of a Lookup Column
It can be easily doable using Out-Of-The-Box only
1) No InfoPath forms required
2) No SharePoint Designer required
4) No Visual Studio Code required
Use a Calculated field column in the parent list and write the formula like this
Use this calculated field in the child list as the lookup field
In addition to your "Deactivated" field, create a Calculated field named "ActiveTitle" (or whatever you want to call it). Use the following formula:
The ActiveTitle field will be empty when the Deactivated field is set. When you configure your lookup column, tell it to use the value from ActiveTitle instead of Title. Empty values will not show up in your lookup list, so you will end up with only the values that are not Deactivated.