SharePoint lookup fields are not the most friendly when working with a large list of data. What I mean is, if you have a list of 1000 items that you need to choose from using a lookup, you get a not so fun experience as seen below.
Trying to find the right item here can be a real pain. This is not a new pain however, and people have worked around this. A great example can be found here: http://ilovesharepoint.codeplex.com/ These folks have created a lookup field that allows searching and filtering of the lookup data.
My needs were somewhat different, and so I set out to create a custom field. Specifically, I needed a field that would let the end user filter the lookup items based on metadata tags applied to the lookup items.
Let's use an imaginary scenario: We are a special breed of tourist, we travel the world looking for ice cream. We document our travels using SharePoint. Every trip we take, we document by adding an item to a list of trips. One of the fields in this list is a lookup for an ice cream vendor. This lookup points to a list of ice cream vendors from around the world, and that list has grown to be quite large. We do however have metadata on those ice cream vendors, and you would like to only see relevant ice cream vendors based on the country they are located in and the flavor of ice cream they sell.
Introducing the Metadata Filtered Lookup Field. This field inherits from a regular lookup field, but presents a different user interface. It lets the user enter a few tags which then filter the list of options to choose from. The field looks at the target list, figures out what metadata is being used to tag the items and presents the user with a tag select box for each term set that is in use. In our example, this means that choosing an ice cream vendor will let us enter countries and flavors as filter criteria. See below.
We now enter a term, click refresh, and we see the options that have been tagged with this term. So we see three ice cream vendors that sell banana ice cream.
We can add some more terms, click refresh and see that more options are showing up. So we see that there are six vendors selling banana or chocolate ice cream or are in Germany. This is important, the tags are ORed together, so the result set grows as more tags are added.
Next we select a few items (in a singe select lookup, only one is selectable at a time) and click save.
Looking at the list item, we can now see that we had selected "Ice Cream Heaven", "The Parlour" and "Das Cream".
Cool no? Let's look at one more thing: What happens when we edit this item?
If we open up the same item, we will notice that a bunch of tags have been pre-populated, along with some checked and not checked items. What is up here? The design decision I made was that an editor should always be able to see the already selected items. Otherwise it would be very difficult to un-select items. So what happens here is that the all the tags of the selected items are used to pre-populate the metadata selection boxes. Since these tags can also be used with other items, the result set that we see also includes some unselected items. This may seem strange at first, but it is actually a useful feature since the unselected items are likely to be related to the selected items, and thus likely items the user is looking for!
I have packaged this field up as a wsp solution that is available at Codeplex: http://metafilteredfield.codeplex.com/ The source is also available and if someone wants to work on this I will be happy to give you access to the repository.
There is one limitation to the field that I have noticed. The out of box lookups allow you to specify 'additional fields' that are created in the list. This field also has that option, but it doesn't work at the moment.
Lastly, I have tested this to a certain degree, but make sure to do your own tests, especially performance tests before you deploy this into production.