SharePoint Use Cases

  • Home
  • About
  • Our Tools
  • Contact me
SharePoint, SharePoint 2010

Creating filtered lookups in SharePoint 2010 with InfoPath 2010

Toni Frankola - January 6, 2010

One of the usual challenges with SharePoint 2007 was to build a lookup field between two lists but also apply a custom filter to the values being displyed.

Let’s consider a simple scenario: You have two lists on your site: Projects and Project Tasks. You want to link each task in Project Tasks list with a project in Projects list. This was simple even in v2007, you only had to create a lookup column and you were good to go.

But what if you wanted to link tasks with only active Projects (each project has Yes/No column indicating if it is active), that was a bit more challenging.

Let’s check how you can solve the problem with SharePoint 2010 (Enteprise edition only). Here is what you need to do:

1. On a SharePoint site create one custom list Projects with two columns: Title and Active (Yes/No column). You can add additional columns if you need them.

2. Use Tasks list list template and create a list Project Tasks. Add one additional Lookup column connected to Projects list (do not select “allow multiple values”)

SharePoint is going to create all the required forms for you. The default form for Project Tasks will look like this. The problem is, it shows inactive projects.

1-inactiveproject

To change this, follow these steps:

1. Open your list. In the List Tasks > List Ribbon click Customize Form button. You must use IE to use the button. You will perform form customization with InfoPath 2010.

2-customizeinfopath

2. In the InfoPath form designer right click on Project field and click on Drop-Down list properties option.

3-drop-down-list-box-properties

3. On the newly opened dialog click Add button

4-add-new-connection

4. You need to create new connection to receive data from Projects list. Select Create a new connection to: Receive Data. Click Next >.

5-receive-data

5. Select SharePoint library or list as your source. Click Next >.

6-sharepoint-list-or-library

6. Type in the URL of your SharePoint site. Click Next >.

7-sharepoint-site

7. Select Projects list you create before. Click Next >.

8-select-list

8. From the fields list, select the following fields: Title, and Active. Select to sort by Title. Click Next >.

9-select-fields

9. Click Next button a few times to finalize this wizard. Once completed you will be taken back to properties dialog.
Select ID field as value field. Click the button Next to Entries field, then Select a Field or Group dialog should appear.

10-drop-down-list-box-properties

10. On the Select a Field or Group dialog click on Filter Data… button

11-filterdata

11. On the filter dialog, create a filter as shown on picture below. This will force drop down to load only active projects.

12-specify-filter-conditions

Close all dialogs and save the changed form. When you go back and try to create a new item in your list, projects dropdown will only show active projects.

13-no-active-projects

Conclusion

This article describes how you can create a filtered lookup for SharePoint 2010. It is easy to use and no code is required to make it work. A pure end user solution. There are some limitations: it does not resolve the problem of updating old Tasks that have already been assigned to inactive projects. It also works with Enterprise edition. In future blog posts I will try to create some alternatives for SharePoint foundation.

Tags | filtered lookup, lookup, no code, sharepoint 2010
 0
Share Now

Toni Frankola

Entrepreneur, IT consultant, speaker, blogger, and geek. Co-founder of syskit.com.

You Might Also Like

SharePoint

Top Blogger Web Part – No code required

May 28, 2009
SharePoint

SharePoint 2010 – Sneak Previews

July 13, 2009
SharePoint

FREE SharePoint 2010 eMagazine has been published

January 20, 2010
SharePoint

Configure Item Level Permissions for Document Libraries – Part 2 – SharePoint 2010 edition

March 22, 2010

3 Responses

  • John Developer March 17, 2010 at 1:00 pm

    Hi,
    very nice tutorial. i found this site, when i was looking for creating sharepoint filtered lookup field from visual studio. i post link to the blog where is detailed description of creating such as field, for people with the same problem who find this site like me.

    http://sharepoint-brandon.blogspot.com/ – Sharepoint Filtered Lookup Field

  • David Christie January 26, 2015 at 5:21 pm

    In step 11, the solution compares a column in the lookup list with a constant. Is it possible to replace this constant with the value of another field on the initial form in some clever way?

  • Kathy November 17, 2017 at 11:28 pm

    Got all the way to the end before I realized it was for 2010, there is no filter option in 2013

  • Comments are closed.

    Previous Post Live Online SharePoint Saturday EMEA is on the Move
    Next Post FREE SharePoint 2010 eMagazine has been published

    Archives

    • December 2021 (1)
    • June 2021 (1)
    • January 2021 (1)
    • July 2019 (1)
    • June 2019 (2)
    • March 2019 (1)
    • February 2019 (3)
    • October 2018 (2)
    • August 2018 (2)
    • June 2018 (1)
    • April 2018 (1)
    • February 2018 (1)
    • January 2018 (1)
    • June 2017 (1)
    • April 2017 (1)
    • February 2017 (1)
    • November 2016 (2)
    • August 2016 (1)
    • July 2016 (2)
    • March 2016 (1)
    • February 2016 (2)
    • January 2016 (1)
    • November 2015 (2)
    • December 2014 (1)
    • November 2014 (2)
    • July 2014 (2)
    • June 2014 (2)
    • May 2014 (1)
    • April 2014 (2)
    • March 2014 (1)
    • February 2014 (4)
    • January 2014 (2)
    • December 2013 (1)
    • September 2013 (1)
    • July 2013 (1)
    • June 2013 (1)
    • May 2013 (1)
    • April 2013 (3)
    • February 2013 (1)
    • December 2012 (1)
    • November 2012 (1)
    • September 2012 (1)
    • August 2012 (1)
    • July 2012 (2)
    • June 2012 (4)
    • April 2012 (1)
    • February 2012 (1)
    • January 2012 (1)
    • November 2011 (2)
    • October 2011 (2)
    • September 2011 (2)
    • July 2011 (3)
    • May 2011 (2)
    • April 2011 (1)
    • March 2011 (4)
    • February 2011 (3)
    • January 2011 (4)
    • December 2010 (4)
    • November 2010 (3)
    • October 2010 (3)
    • September 2010 (4)
    • August 2010 (1)
    • July 2010 (4)
    • May 2010 (2)
    • April 2010 (2)
    • March 2010 (3)
    • January 2010 (2)
    • December 2009 (3)
    • November 2009 (5)
    • October 2009 (2)
    • August 2009 (7)
    • July 2009 (3)
    • June 2009 (4)
    • May 2009 (3)
    • April 2009 (5)
    • March 2009 (8)
    • February 2009 (5)
    • January 2009 (8)
    • December 2008 (9)
    • November 2008 (8)
    • October 2008 (7)
    • September 2008 (4)
    • August 2008 (6)
    • July 2008 (5)
    • June 2008 (3)
    • May 2008 (4)
    • April 2008 (6)
    • March 2008 (4)
    • February 2008 (3)
    • January 2008 (6)
    • December 2007 (1)
    • November 2007 (3)
    • October 2007 (3)
    • September 2007 (2)
    • August 2007 (1)
    • July 2007 (12)
    • June 2007 (2)
    • May 2007 (3)

    Connect Us

    All postings on this blog are provided “AS IS” with no warranties, and confer no rights. All entries in this blog are my opinion and don’t necessarily reflect the opinion of my employer.

    • Home
    • About
    • Our Tools
    • Contact me

    Copyright (c) Toni Frankola 2008. - 2019.