Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I have a list of departments. This list will change over time adding new departments and "deactivating" others (because of structural changes).

I would like to use this list as a look-up for other lists, but I would only want to look-up the active departments (can be detected by a bit field "Deactivated") which should be false, when the department is active.

I'm sure this can be done, but how?

share|improve this question
add comment

5 Answers

up vote 13 down vote accepted

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. Let me know if you have any trouble.

share|improve this answer
    
BTW - I had also tried the FilteredLookup codeplex solution that Ryan suggested. There was some push back from our architecture council because it was a 3rd party solution and we couldn't guarantee how easy it would be to migrate from 2007 to 2010. Granted, the codeplex solution is more flexible with it's cross-site lookup and CAML support. But if all you need to do is filter out the inactive values, my solution allows you to use native fields to accomplish this fairly easily without the need to install anything. –  ronx Aug 24 '11 at 13:14
    
Great solution! Saved us loads of time, and rework :D –  Mahendran Govender Nov 8 '12 at 9:59
    
I was looking into this solution for something I am doing, and it appears that if you have prior items that have been set with Active Depts, and then those Depts are marked as InActive (or Active = FALSE ) then the fields in the other list get replaced with "" after the fact. If that is the desired behavior then this solution works, if you would like to retain the original dept that was selected, this will approach will change that value and might not be what is desired depending on your requirements –  lwbecker2 Feb 17 at 16:16
add comment

Codeplex - SharePoint Filtered Lookup Field

This project creates a custom SharePoint lookup field that offers new functionalities to default SharePoint lookup field by allowing filters to be applied to retrieved data. Applied filters can be either dynamic CAML queries or pre-defined list views residing in source lists

http://filteredlookup.codeplex.com/

or for 2010

http://sp2010filteredlookup.codeplex.com/

share|improve this answer
add comment

Here in the below link, you can see the step by step explaination for creating a filtered lookup columns in sharepoint. Cascading Lookup Columns in SharePoint Hope this helps you!

share|improve this answer
    
We prefer inclusion of the essential parts of the answer here, and to provide the link for reference. See answer for general guidelines. Please pay particular attention to this if you are linking to your own blog. –  SPDoctor Feb 16 '12 at 22:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.