Blog

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.

 

Employee=IF(IsWGRF="Yes",[Long Name],"")

Director=IF(IsBoard="Yes",[Long Name],"")

Researcher=IF(IsResearcher="Yes",[Long Name],"")

 

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

http://sharepoint.stackexchange.com/questions/18247/how-to-make-a-filtered-lookup-field

 

Filter the Options of a Lookup Column

https://social.msdn.microsoft.com/Forums/office/en-US/641ac447-f616-4e68-ad4f-8715f4b5ca45/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

3) No JavaScript/Jquery/xslt Required

4) No Visual Studio Code required

 

Use a Calculated field column in the parent list and write the formula like this

=IF(Status="Active",Title,"")

 

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:

=IF(Deactivated,"",Title)

 

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.