Why SharePoint 2013 Calculated Columns will Save your Life – Filtering Lookup Values

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.

Scenario

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.

List A and B

Solution

Use a calculated column in List A and write the formula like this:

=IF(IsSelectable,Title,””)

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.

List A and B - Filter Applied

Pros

  • Out-Of-The-Box only
  • No InfoPath forms
  • No SharePoint Designer
  • No JavaScript/JQuery/XSLT
  • No Visual Studio code

Time to implement: 3 minutes.

Cons

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.

Alternative Solutions

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.

References

MSDN

StackExchange

Balestra

April Dunnam

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s