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:
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:
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