40

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?

Improve this question
0

6 Answers 6

Reset to default
59

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.

Improve this answer
6
  • 1
    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
    Commented Aug 24, 2011 at 13:14
  • Great solution! Saved us loads of time, and rework :D
    – Mahendran Govender
    Commented Nov 8, 2012 at 9:59
  • 7
    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
    Commented Feb 17, 2014 at 16:16
  • This approach works with one exception. A historic expense record may need to hold a reference to a now defunct department. New expense records should not see the department in the dropdown, but the historic records already pointing to it should. SharePoint doesn't handle this situation.
    – M. Lanza
    Commented Feb 10, 2017 at 20:36
  • 1
    @Mario, to address that check the workaround by Coralie
    – Abdul Hameed
    Commented Mar 15, 2017 at 5:44
8

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/

Improve this answer
6

Re the problem raised by lwbecker2, so long as you have an additional lookup column that is based on the full lookup list (ie not the 'filtered' column) then you will be able to still see the original department in the linked list.

For example, Department linking to the column ActiveTitle will be blank but Department:Title will still have the original title. Simply create a view that displays the full lookup column.

Improve this answer
1
  • This is fine for a view, but when editing an existing item in the list, the dropdown shows "(None)" if it links to an inactive record. If the user saves this record without changing the lookup value, the old linked record is removed from this lookup.
    – DRVR
    Commented Mar 12, 2019 at 1:31
4

Yes you can use CAML:

http://abstractspaces.wordpress.com/2008/05/05/caml-query-lookup-field-by-id-not-by-value/

or you can use this:

http://filteredlookup.codeplex.com/

or

How to make a filtered lookup field

Improve this answer
3

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

Cascading drop down in SharePoint using REST API (for SP 2013, 2016 versions & SP Online)

Hope this helps you!

Improve this answer
2
  • 1
    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
    Commented Feb 16, 2012 at 22:42
  • Note that the JavaScript at that link will not work in SPO Modern pages and JSON column formatting. JSLink and Content Editor web parts have been removed. As they caveat there, it only works if everything is made classic and you have a content editor web part capability (some orgs are creating custom ones just for this very reason).
    – vapcguy
    Commented May 9 at 18:29

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