Leale Solutions’ Blog

Using Filtered Lookup Lists

Posted: August 6th, 2014 | Author: | Filed under: 'How to' | Tags: , , | No Comments »

Ok, so, you create a reference list that will hold all… say… document types for a company… this list will hold every document type in one list… you add a document category too, thinking.. ‘Cool, I’ll just use this lookup list in all of my document libraries and filter by the category…’

Yeah… about that…

Not going to happen…

There is no filtering ability where lookup column are concerned.

filtering ability

We could create individual lists containing documents types for each category, but, that defeats the object of what we’re trying to do. We want to manage all of the document types in one list…

So, a work around…

In your reference list, you’re going to have to create calculated columns for each ‘filter’ you want to use… So, one calculated column for each category.

reference list

Name it something meaningful ;) so, if this filter will be ‘Employee Documents’ call it something like EmployeeDocs…

Now for what makes it work.
In the formula, we’re going to use the IF function (here is a link to the Microsoft Documentation).

The syntax is:
=IF([ColumnToTest]TEST, WhatHappensTrue, WhatHappensFalse)

Example:
=IF([Document Category]=”Vendor”,Title,”")

This would place the contents of the Title column in the New column if the category is Employee, otherwise will leave empty.

This gives us a list like :

list

Now, when using this new column as a lookup, it will only include entries with something in them, it will not include the empty entries.

empty entries

Add a Lookup column the usual way, selecting the reference list as the target, then select your ‘filter’ column as the data returned

selecting reference

Of course, the way that SharePoint allows access to lists only from the same site, means that if we wanted to create this list and access it across all sites in the collection, we would now need to add the filter columns to the Site Columns.

A bit clunky, and a bit of a pain to initially set up, but, once setup it pretty much takes care of itself.

Share Button

About author

Steven is a seasoned technologist providing innovative solutions for almost 2 decades. Steven “cut his teeth” as a Database Administrator and developer, even coauthoring multiple published works. His leadership experience in corporate roles of Project Manager, Director and CTO as well as a strong analytical background facilitated a seamless transition to the role of entrepreneur. He is cofounder and COO of Leale Solutions beginning in 2007. His qualities and skillset of effective communications, integrity and transparency permeate through every aspect of Leale Solutions.


Leave a reply

  • XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


  • 4 × = twenty eight

Check our recent posts: