Hi this is Juan Balmori with another post for the BCS blog. This is a follow up to my previous post, about the external item picker. There is a problematic scenario that you may face: You are searching for an external item on the picker dialog that you are 100% sure it exists on the external system but the dialog does not seem to work properly and is not finding it. Worse it’s not even filtering by the specified search criteria.

For example in the following image I am searching for an ‘Artist’ whose name is ‘Kim’ and evidently exists as you can see on the search results but the picker is:

1. Effectively showing Kim
2. But is also showing a bunch of additional results that don’t even meet the search criteria
3. On top of that, I get a strange error message telling me that ‘Search criteria cannot be met as Filters are not defined for the External Content Type. Contact your system administrator’

What’s going on?

Well, as the error message is suggesting, the problem is that in this example the “Artist” External Content Type (ECT) does not have a filter defined and when an ECT lacks a Filter, the default behavior is to show the first 200 items and we cannot filter/narrow the search results. And you are going to get that warning.

Moreover, imagine a scenario in which you have thousands of items to pick from; you will need to have a good set of filters defined on your ECT so that your users can find the items they are searching for, and/or quickly narrow their search to the item they are looking for.

So as a general rule of thumb, I strongly recommend you to always define at least one filter on your ECTs.

Fortunately, filter definitions can be done in SharePoint Designer (SPD) and it’s really easy to do it, I will describe how. Filters need to be defined on the read list operations (aka finders, as named in the BDC metadata model). There is a specific dialog to configure a filter for the read list operation as you can see on this image. (Double-click on the read list operation and then hit ‘next’ to get here)

Then follow these instructions:

1. You need to click on the ‘Add Filter Parameter’ button
2. Click on the ‘(Click to Add)’ link on the Properties panel to configure the picker. You’ll find the following dialog:
   
3. Give the filter a name on the New Filter text box
4. Select a filter field to be used. In this example I want to filter by ‘Artist Name’ so I select that field.
5. Define the type of filter to use. For a complete list of filter types and how they work please refer to this article. It’s worth mentioning two frequently used filters types: Comparison and wildcard. For this example, the artist name can have multiple words. (i.e. ‘Franz Kohl’), let’s see what happens when I use each filter type:
   • Comparison: If you use the ‘equal’ operator we’ll expect the user to type the full artist name to find items. Hence it will only filter if the user types ‘Franz Kohl’, but will not work with either just ‘Franz’ or with just ‘Kohl’
   • Wildcard: If you use the ‘wildcard’ type then the filter will be send to the external system with wildcards. In this case it will filter if the user types ‘Franz’ or ‘Kohl’ (we’ll return all the artists that have the string ‘Franz’ as part of the name or vice-versa).
6. Finally, identify what to do if the filter value is null. You need to select this option if you have an external list associated with this ECT. Otherwise you’ll see no items when browsing the list (because the filter value will be null).
7. You can add more filter parameters as needed and add a AND/OR behavior to it. For instance, we are interested to filter by last name and zone.

One very important note regarding the design experience: when integrating specifically with databases, our tools generate the necessary queries to enable the designed filters. If you are using other types of BCS data sources such as web services, WCF or .NET assemblies you need to make sure that your filters are part of their API, before you can model them in SPD.

Here is an example of how the picker dialog is going to look like when we select a ‘wildcard’ type of filter on the artist name and we search for ‘Karen’

Once multiple filters are defined on the ECT, the external item picker algorithm to apply those filters is the following: it appends the resulting items of each filter of each read list operation of the ECT. So to be more efficient we recommend not having a high number of filters/finders on each ECT as this can potentially result in calling a high number of operations on the ECT.

In summary, the most important piece of information for this post is: Always define filters on your ECT, especially when you are going to use it for picking purposes.

Thanks for reading!

- Juan Balmori, Program Manager