SharePoint 2007, 2010, 2013 – Filtered Lookup Field
UPDATE:
I’ve created a YouTube video that walks you through how to implement this Filtered Lookup functionality.
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.
I’ve created a YouTube video that walks you through how to implement this Filtered Lookup functionality.
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:
- Create a new Calculated Column in your source list (Job List) that your Lookup Column will be reading from.
- Give the column whatever name you’d like, I’ll call mine “ActiveTitle”
- 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).
- Here is a good resource for examples on different conditions for calculated columns in SharePoint: http://msdn.microsoft.com/en-us/library/bb862071(v=office.14).aspx)
- Click “OK”
- Go to your list that you want the Lookup Column in. In this scenario it is the “Field Report” List.
- Click “Create Column”
- Give your new column a name of your choice. I’ll call mine “Job Name”
- Select “Lookup Column” from the list of Column types
- In the “Get Information From” Dropdown, select the List which you want to pull information from. In our scenario it is the “Job List”
- In the “In This Column” Dropdown, select the Calculated Column we just created, “ActiveTitle”
- Now you will see that it is only returning the non-blank values from that column and you have your filtered column
THANK YOU! This worked for me!!!
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
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!
Thank-you so much. This works so well.
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
This comment has been removed by a blog administrator.
Thanks for the reference Paul! Glad this worked for you 🙂
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
Hi Rita.
Try and replace the "," (comma) with ";" (semi-colon)
I've read that site regional settings can affect the syntax used in SP
Very nice and thanks for the great tip April 🙂
NOTE this will not work if the child list (the list you're looking up on) has more items than the list view threshold.
A beautifully simple solution. Thank you.
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,
Hello, may you solve this issue?
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…