BCS and Filtering/Paging - External List vs Business Data List Web Part

Question

I am trying to learn BCS for use in an in-house application. To keep it simple at this point I am limiting myself to a single table even though the table has FK references for grouping and some one-to-one relations for columns that can have more than a single value.

On my test database the single table contains 25k rows but in production this is expected to grow to several million. While a user will never look at thousands of rows at a single time it is possible that could be working with hundreds of rows. (Or start with thousands and dynamically apply filters in order to drill down to the small subset that really need.)

My questions have to do with filtering and paging and how to display the list of rows. I understand that when the ECT is created the query is limited to 2000 rows in the return set. All of the examples and postings I have read say to "create filters to limit the number of rows returned" yet none of them really go into a practical way to use the filter. Almost all of the examples add one (sometimes two) wildcard or exact match filters to a field or two.

The article, http://lightningtools.com/blog/archive/2010/06/25/sharepoint-2010-external-list-paging-ndash-server-side.aspx, talks about server-side paging for external lists but there is a Paging filter that can be applied to the ECT. (I do have to mention that it does not seem to work for me because the external list will not display and there is an exception in the log regarding not uniqueidentifier conversion to int problem...)

The other question I have is how to properly display the list of rows and allow the user to apply filters. Creating an External List gives me the standard SharePoint View that allows me to apply filters via the dropdown in the column header. The problem is that I see no way to enter values for the filters I applied in the ECT. And where does the filter get applied when I select a value(s) from the column header dropdown?

Whereas the Business Data List Web Part gives me an area where I can type in values my for ECT filters and it displays the SharePoint gridview for further filtering. I also noticed that the Web part displays more columns than the External List. (It seems that my external list gridview is missing any column that has a uniqueidentifier data type.)

Any insight or links to concrete postings/examples are greatly appreciated.

Thank you.

Answer 1

I can only answer one part of this question at the moment. When you create an External List on your ECT, you can set the filter values for your ECT filter parameters if you go to List->Modify view->Filters. You can then enter your values for the list view here.

Answer 2

Getting BCS right is a tricky thing, and information on this seems really hard to come by, so I will do my best to point you in the right direction.

You create filters as part of the process for building your Read List operation. Although the wizard(if you use SPD to do this) will tell you you should create a Limit filter, you will be better served by creating some actual usable filters.

Think about how your users will interact with data. Chances are you have some filtering scheme in mind. For example, you may have millions of customers but for a particluar view you want to display only those in a certain zip code. In the wizard for your Read list operation you'll come to a page called Filter Parameters Configuration. Click the "Add Filter Parameter" button to add a filter. A little dialog will pop up where you can fill out the data. It also has a place for a default value, I would add a default value for each filter parameter you set up(you can set multiple filters as well).

A read List operation is analogous to setting up a view in a normal SharePoint list. When you set up filters on your "view", the view will only pull in the list items the default values of th efilters will allow. So if you don't set up these default values in your filter parameters, your list view will always appear to have no data.

In order to consume the filters you'll need to set up either a custom code-based web part(using the BDC API), or a custom Xslt List View, and set those filters based on a query string or some other technique. When using a list view, you can set up the filter consumers by clicking the "finder" button in the ribbon. The resulting wizard will detect your ECT filters and allow you to set up an XSL parameter(like a query string parameter) where you can hook everything up. The only thing now is to create some sort of control that will allow the user to select a zip code and load the page with the proper query string parameters.