SharePoint 2007, 2010, 2013 - Filtered Lookup Field

I'm going to show you a no-code, simple way to create a Filtered Lookup Column in SharePoint which will work with any version of SharePoint.

There is a solution on Codeplex which creates a custom Site Column to handle this but as far as I know it's not compatible with SharePoint 2013 and had a few quirks in other versions based on the user comments.  There are also some documented ways to accomplish this with jQuery and custom c# code.  However, I have found for most cases this is an effective, fast, no-code way to accomplish the same thing.  This solution works based on the fact that a Lookup Column in SharePoint will not return blank values.  

Scenario:  A client comes up with the requirement to have a Lookup Column in their "Field Report" SharePoint List that points to their "Job List".  This Job List has every job they work on along with a "Job Status" column which determines if the job is Active, Completed, Etc.  Creating a Lookup Column is no problem, but they wanted to take it a step further and only show items from the Job List which were an Active Status in the Lookup Column of the "Field Report" List.  

Solution:  
  1. Create a new Calculated Column in your source list (Job List) that your Lookup Column will be reading from.
  2. Give the column whatever name you'd like, I'll call mine "ActiveTitle"
  3. Insert your condition into the condition box.  In my case I will be using an "If" condition to check the Job Status.  If it's Active I want to display the Title of the item and if it's anything else, make it blank (see screen shot below).
  4. Click "OK"
  5. Go to your list that you want the Lookup Column in.  In this scenario it is the "Field Report" List.
  6. Click "Create Column"
  7. Give your new column a name of your choice.  I'll call mine "Job Name"
  8. Select "Lookup Column" from the list of Column types
  9. In the "Get Information From" Dropdown, select the List which you want to pull information from.  In our scenario it is the "Job List"
  10. In the "In This Column" Dropdown, select the Calculated Column we just created, "ActiveTitle"
  11. Now you will see that it is only returning the non-blank values from that column and you have your filtered column



Comments

  1. This comment has been removed by a blog administrator.

    ReplyDelete
  2. http://www.azu.mn/?p=239
    The SharePoint Lookup Plus Field type is a “super” lookup field type that brings added capabilities that implementers found lacking in out of the box SharePoint 2013 Lookup fields:

    Cascading fields (“master-details”)
    Cross-site lookups
    Filtered lookups (filter lookup by views & CAML)

    check it
    http://www.youtube.com/watch?v=70-hXWY6ARY

    ReplyDelete
  3. Sounds great - filtering on the Lookup Source!
    Do you know of a way to filter from the Destination?

    Example:
    List A - is "Apple", "Banana", "Cherry"
    List B has a lookup reference to A
    I can ensure List B enforces unique values (so no two items in B will be "Cherry")
    However, if A is over 30 pieces of fruit, it may be confusing - because they'll have to scroll quite a bit.
    Thus: how would I filter the lookup values so that, when adding a new item to B, I only see unused items?
    Everything I've read says it can't be done without code or a third-party solution.
    If you have any ideas, please let me know!

    ReplyDelete
    Replies
    1. Jason, I am looking for the same thing. Did you ever find a solution? I don't see a response here and calculated IF statements won't filter out multi choice options...

      Delete
  4. Thank-you so much. This works so well.

    ReplyDelete
  5. Nice post! This saves me having to customise, I reference your post on my blog. http://blog.sharepointsite.co.uk/2014/07/sharepoint-2013-ootb-lookup-list.html

    thanks paul

    ReplyDelete
    Replies
    1. Thanks for the reference Paul! Glad this worked for you :)

      Delete
  6. Hi,

    I'm using the following formula:
    =IF([Estado do Planeamento]="Em Elaboração", [Nome],"")

    and the following error is ocurring:
    The formula contains a syntax error or is not supported.

    I think the formula it's ok, and i don't know what's happening :(

    Thanks,

    Rita

    ReplyDelete
  7. Hi Rita.

    Try and replace the "," (comma) with ";" (semi-colon)

    I've read that site regional settings can affect the syntax used in SP

    ReplyDelete
  8. Very nice and thanks for the great tip April :)

    ReplyDelete
  9. NOTE this will not work if the child list (the list you're looking up on) has more items than the list view threshold.

    ReplyDelete
  10. A beautifully simple solution. Thank you.

    ReplyDelete
  11. Hello. this works fine, "more less", the problem is that when you change the Task to "Not active" then in the list you have been using the calculated column the value disappear so you lost this information,

    ReplyDelete

Post a Comment