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.