Lookup filters in SharePoint works out of the box!

May 11th, 2014 | Posted by Marijn in collaboration | events | money maker | Office365 | quick tip | sharepoint

Every now and again SharePoint still manages to amaze me. One would think that after 8 years of intensive SharePoint activity, I would have learned all tricks of the trade. Yesterday, I had another eye-opener that I was not prepared for (in a good way)!

The clients question was: I have a list with titles from presentations (fields are presentation title, date). I have another list with attendees (fields are name, presentation). Those 2 lists are coupled with a lookup field. When you add yourself in the list of attendees, you have to choose a presentation to attend.

presentations list

The tricky thing was: how can we make sure that you can register yourself as an attendee only for the presentations that still have to come ?
That would mean a filtered lookup. I was getting ready to add spservices on the tenant so I could work with filtered lists. But what can only be described as a chance encounter, I googled to see if there wasn’t another solution, perhaps something new that was just released and I didn’t know it.

And then I found this msdn link, where a member “SPGuy2012” (can’t find his name behind the profile) who has this genious solution! His solution has:

1)No InfoPath forms required

2)No SharePoint Designer required

3)No JavaScript/Jquery/xslt Required

4)No Visual Studio Code required

The trick behind it? A simple calculated column.

If you add an ‘if’ clause on the calculated column, that sends “” when negative, and use that column as the lookup field, you only get the fields that have values.

Bam. Boem. It struck me. Genious. Simple. Easy for end users. Love it. It works SP 2007, SP 2010, SP 2013 and Office365.

Solving the attendee issue with the lookup to future events

Back to our problem. We add a calculated column to the presentations list with this formula:

=IF(Date>TODAY(),Title,””)

formula calculated column filter

The ‘IF’ formula has 3 parts:

– the query ( if date > today() )

– if it is true (show title)

– if it is false (show “”)

We change the lookup field to this field, and behold: only future events are shown in the dropdown!

filtered lookup column with calculated column

Time to implement: 3 minutes.

Amazing. Mind = blown.

You can follow any responses to this entry through the RSS 2.0 You can leave a response, or trackback.

8 Responses



Leave a Reply

%d bloggers like this: