Filtered Lookup Field with No Code

SharePoint Lookup field allows you to render items from another list and displays them in a drop-down choice format. When working with a larger dataset in the source list, you may want to apply a filter to the Lookup field so that it displays only relevant items.

With SharePoint Server 2010 Enterprise, you can do so using InfoPath.

As an example, I have a “Source List” with an “Active” column. With this post, I will demonstrate how to exclude the last 2 list items below that are marked as inactive.

In another list called “My Demo List”, I have a Lookup field referring to the “Source List” item. By the end of the post, “My Demo List” would have excluded the inactive Source List item like displayed below:

So, here’s how I filtered the Lookup Field:

1. Go to the corresponding list with the Lookup Field. Then click “Customize Form” on the ribbon
   
    
2. Once you have the form opened in InfoPath, click Data Connections (located within Data tab)
   
    
3. Disable automatic retrieval for the data connection of your source list:
   1. Find the source list of your Lookup field values. Then select “Modify”
   2. Deselect “Automatically retrieve data when form is opened”
      
       
   3. Click “Finish”
       
4. Add another data connection to your source list
   1. Click “Add”
      
       
   2. Select “Create a new connection” > “Receive Data”. Then click “Next”.
      
       
   3. Select “SharePoint library or list”. Then click “Next”
      
       
   4. Enter the URL of site where the Source list is located in. Then click “Next”
       
   5. Select your Source List. Then click “Next”.
      
       
   6. Select 2 of the following fields:
      • The field that you want to display in the drop-down. In this example, I want to display “Title”.
      • The filter that you want to apply. In this example, I want to filter against the “Active” field.
        
         
   7. Click “Next” twice
       
   8. Before clicking “Finish”, do the following:
      • Rename your data connection for future references
      • Be sure that this field “Automatically retrieve data…” is selected
      • Then click “Finish”
        
   9. Close the Data Connections window
       
5. Change Data source of Lookup field and apply filter
   1. Right click on Lookup Field. Then select “Dropdown List Box Properties”
   2. In “Data source” field, select your new data connection
      
       
   3. Click the icon next to Entries
      
       
   4. Select “Filter Data”
      
       
   5. Add your filter criteria here. Click “Add”. Then select your filter criteria.
      
       
   6. Click Ok until the Drop-Down List Box Properties is closed.
       
   7. You’re almost done at this point. You can click Preview to see how you did.
       
   8. If all is kosher, click “Quick Publish” to save your changes.