I’m going to show you a no-code, out-of-the-box, simple way to create a filtered lookup column which will work with SharePoint 2013 On Premises and Online.
A client comes up with the following requirement:
We have two lists
- List A (Title, IsSelectable)
- List B (Title, Lookup to List A)
We want to filter lookup values so that only selectable items are shown to users to choose from.
Use a calculated column in List A and write the formula like this:
Tip: if you receive a syntax error try and replace the “,” (comma) with “;” (semi-colon). Site regional settings can affect the syntax used in SharePoint.
- Out-Of-The-Box only
- No InfoPath forms
- No SharePoint Designer
- No Visual Studio code
Time to implement: 3 minutes.
If you should change an item to be no longer selectable then in the list where you have used it its value disappears. That behavior could be ok for most scenarios but be aware of it.
There is a solution on Codeplex which creates a custom site column to handle this. However that solution is not compatible with SharePoint Online because created using server side code.