Pages

09 April, 2012

Filtering lookup columns

Good day all, I bring you another tweak for Sharepoint. In  Sharepoint 2010 and Cascade Lookups I've shown you how to filter a lookup column based on the value of another. Now, what if you want to filter a column, based on a query of the source list?
There are plenty of options over the web on how to do it using third-party tools. In my opinion it is always good to avoid that. Why? Well in the past I had huge problems with  third-party tools, specially restoring sites with those features. 

So, now, I always try to avoid third-party tools unless I've no other option.
Check how I filter columns based on queries in 6 easy steps:


Problem description

Ok you have two lists connectec by a lookup column:

List 1: Parent list
List 2: Child list connected to List 1 by ColumnA column

And now, when you are adding or editing a new item in List 2, you want to allow only to pick List 1 Active items.

Solution

1. Open your site in Sharepoint Designer
The first step is to open your Sharepoint site in Sharepoint Designer 2010.
In the Site Objets pane select Data Sources, there your should have one data source for each List in the site.

2. Duplicate List1 data source
Right click in your parent list data source, ours List 1, and click in Copy and Modify


In General tab give your data source a unique name and define in Source tab the filter you want. In our case "State Equals 'Active'", like this:

3. Create new custom forms
In your child list, our List 2, create  new custom forms, for New and Edit item. Repeat steps 4 to 6 for each form. Don't forget to make the new custom forms the default ones.

4. Delete the Form Field of ColumnA
Edit your form and delete the Form Field associated to ColumnA

5. Insert a new data source
In the Insert tab, insert a new data source and pick the one you created in step 2.

6. Insert a Sharepoint Drop Down control
After inserting the data source, go again to Insert tab and insert a Data View DropDownList from Sharepoint controls.
Ok now that you added everything needed, you just need to configure the newly added field to map the fitlered data source. Pick the field and click Data Fields ... link
In data field, pick the lookup column.
In data source, pick the data source added in step 5
For the display text, pick the field you want to be displaied to the user
For the field value, pick the value passed to the lookup column, tipically ID field.

Ok save everything and go to your site. You should be getting something like this:
ColumnA only shows items from List 1 which State field has the value Active.

Hope you enjoyed it and if you want to see this working, download the following site template Filter Lookup Site Template

NOTE: This is an english template, if that is not your Sharepoint installation language you will need to install the English Language Pack, check it how in here.

27 comments:

  1. Sounds very interesting. Does this work also in Sharepoint 2007?

    ReplyDelete
    Replies
    1. Hi Michael, thanks for stoping by.

      Saddly can't try if it works on Sharepoint 2007, but it should. The function Copy and modify a data source should also be available in Sharepoint Designer 2007.

      Delete
  2. This works excellent, however when I use this on the Edit form I get an error: "An unexpected Error has occured.
    Web Parts Maintenance Page: If you have permission, you can use this page to temporarily close Web Parts or remove personal settings. For more information, contact your site administrator."

    ReplyDelete
    Replies
    1. Hmm ... let me take a look at it. I'll return soon.

      Delete
    2. Ok, I've tried this in Edit Form and it worked like a charm, can you give me more details about your case.

      Delete
  3. Great, this is exactly what I was after. Worked perfectly for me (using SPF 2010) thank you very much indeed for posting!!

    ReplyDelete
  4. Hi Pedro

    Thanks for posting this - works great!
    I'm looking forward to dig into the rest of your posts ;)

    Best regards,
    Patur

    ReplyDelete
  5. when you edit current inserted record how to set lookup field,Because i set these fields but getting hyperlink on it… if you have any solution about that problem post the comment…

    ReplyDelete
    Replies
    1. Hi Mani, take a lookup at this article http://howididit-sharepoint.blogspot.pt/2012/08/removing-html-tags-drop-down.html.

      Delete
  6. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. My solution was in the previous post.

      Delete
    2. Hi Joshua, nice to see you here.

      Delete
  7. Hi,
    I gave this a try and for the most part it works great. My only issue is when I go back and edit a record that has a value that was active and now is not.
    Using your example, I have a record that has List1/Item1
    Now List1/Item1 is inactive. When I edit the record I thought the dropdown would only have the active values but instead it has the <a onclick="OpenPopUpPage('http://Mysharepoint/page/_layouts/listform.aspx?PageType=4&ListId ..... as the first choice then the correct choices. Could I be doing something wrong? thanks

    ReplyDelete
    Replies
    1. Hi, that's true I've managed to solve it in this post: http://howididit-sharepoint.blogspot.pt/2012/08/removing-html-tags-drop-down.html

      Delete
    2. Excellent! thanks! works great!

      Delete
  8. Hi, I opened a sharepoint online (365) site on SPD and when I select the dropdown the right arrow and the option Data Fields are not showing. Maybe because I'm using SP Online?
    There is another way to configure the data field?

    ReplyDelete
    Replies
    1. If you don't have any way to access the data fields you could achieve the same using javascript and webservices.

      I've been using javascript to filter the dropdown list options, also. Yet it turns to have some problems in clientes machines where the internet security is set to very high.

      I'll try to post today a new article showing how to do the same filtering using javascript.

      Delete
  9. Thank you for this!!! I've almost got it working the way I need...but I can't figure out how to allow for multiple selections on the newly-added dropdown list. Is this possible or is there a different control I should be using to mimic the standard selection list boxes?

    By the way, I'm using 2007 and you were right that the steps you listed translate from 2010 - though everything is in a different place or under a different menu obviously :-)

    Once again, thanks!!

    ReplyDelete
    Replies
    1. I've almost got it working. I was also trying to "allow multiple values" and couldn't figure out how to do it.

      I have a list for issues where I want to have one or more related tasks from another list. I only want the choices to show tasks that have not been completed. It is possible to have multiple tasks related to one issue. So I want it to look like the Predecessors field on the standard task list.

      Thanks for any help you can give. Cheers.

      Delete
  10. This worked perfect! I played with jquery all day long... this took 2 minutes!!

    Thank you soooo much!

    ReplyDelete
  11. So I said this worked perfect, but that was for the New Item form. When i do this to the edit form, it doesn't retain what was selected when I created the item. It's blank... any ideas?

    ReplyDelete
    Replies
    1. Hi, by the way you describe the issue. It might be something in the databinding in the New Form. Could you please send me screenshot of it?

      Delete
  12. Looks promising but when I go to add the SharePoint control (drop down list) I can't. The HTML and ASP.Net controls work fine, but not the SharePoint one, any ideas?

    ReplyDelete
  13. Hi Pedro,
    Is it possible to use a list box control, instead of a drop down list, and set it up to allow multiple values?

    ReplyDelete
  14. Hi Pedro,

    Do you have any inputs on how to filter a list column using Javascript? My client wants to filter a report (that is pulled from a list), based on one of the columns of a list !

    ReplyDelete
  15. Thanks and that i have a swell provide: Who Repairs House Siding home remodeling services

    ReplyDelete