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.

Source List

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:

My Demo List

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
    Customize Form
     
  2. Once you have the form opened in InfoPath, click Data Connections (located within Data tab)
    Data Connections
     
  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”
      Disable Auto Retrieval
       
    3. Click “Finish”
       
  4. Add another data connection to your source list
    1. Click “Add”
      Click Add
       
    2. Select “Create a new connection” > “Receive Data”. Then click “Next”.
      Receive Data
       
    3. Select “SharePoint library or list”. Then click “Next”
      SharePoint library or list
       
    4. Enter the URL of site where the Source list is located in. Then click “Next”
       
    5. Select your Source List. Then click “Next”.
      Select Source List
       
    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.
        Filter 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”
        Steps Before Clicking 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
      Data Source field
       
    3. Click the icon next to Entries
      Icon next to Entries
       
    4. Select “Filter Data”
      Filter Data
       
    5. Add your filter criteria here. Click “Add”. Then select your filter criteria.
      Add 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.
Advertisements

4 responses to “Filtered Lookup Field with No Code

  1. Hi,
    Good solution, congrats.
    Is there a way to do something similar but in a LIBRARY form (upload/edit doc?
    The problem I’m facing is that I cannot find a way to edit the library form as we do with the List forms.

    Thanks un advance.

    • Francisco, you’re right. Forms for Document Library cannot be customized with InfoPath. What you need to do is customize the DispForm.aspx, NewForm.aspx, and EditForm.aspx of the Document Library. You can do this either via SharePoint Designer or in the browser. There are a lot of resources on how to do this. Disclaimer – it’s not as easy as working in InfoPath.

  2. I’ll bet if you go back to your “Demo List” and view previous items that now have Inactive Lookup values, you will not be happy.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s