Lookup filters in SharePoint works out of the box!

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.

About: Marijn

Marijn Somers (MVP) has over 12 years experience in the SharePoint world, starting out with SP2007. Over the years the focus has grown to Office 365, with a focus on collaboration and document management. He is a business consultant at Balestra and Principal Content Provider for "Mijn 365 Coach" that offers dutch employee video training. His main work tracks are around user adoption, training and coaching and governance. He is also not afraid to dig deeper in the technicalities with PowerShell, adaptive cards or custom formatting in lists and libraries. You can listen to him on the biweekly "Office 365 Distilled" podcast.


8 thoughts on “Lookup filters in SharePoint works out of the box!”

  1. There is a problem with this ..When i add a new item to the parent list it show a default value
    -376433184 in place of “” .
    Any idea to come over the problem

  2. Thanks for sharing Laura, I thought we couldn’t use TODAY in a calculated column?
    Or does TODAY() work? I have a bunch of calculations that rely on Modified column and a continual workflow which modifies each item using Info Management Policy.

  3. I’m pretty sure you can’t use Today() in calculated columns like that. It doesn’t get updated in real time so any new event will always be AFTER the date this column was created. Therefore no event will expire, or something like that. I’d definitely test this out a bit more

  4. Thanks a lot! It saved my day. I was able to create conditional calculated column to fill in a dropdown lookup column which otherwise would have been a nightmare using other solutions like designer/jquery

    =IF(AND([StartDate]>(TODAY()-14),StartDate]<(TODAY()+14)),Title,"")

Leave a Reply

%d bloggers like this: