When you define external content type you are recommened in sharepoint designer to set a limit ​filter which essentially controls the number to items being returned to external list. The standard limit for this type of filter is 2000. You could potentially increase the threshold but that's not advisable.

So if you are creating a solution that surfaces the data in external list, and when your data grows more than 2000 items, you will be able to see only 2000 items in your external list. Now, if you're using sahrepoint object model list APIs to find/update/delete item in your external list, and eventually to sql database, the thing to consider is what if you need to update/delete/read an item that is no longer visible in external list (becasue of the threshold being met)??

Well, with proper planning and smart design, you could build a BCS solution that performs well even within this limit. This is where filters come in handy.

If RowID is primary key in SQL table, we create a RowID comparison filter which is setup as shown in figures below.


My requirement is to display a specific number of latest items by default, if I need any predated item then the we define the query to use this filter criteria and look for record. With this, even though the item may not be loaded in external list, the filter query will retrieve item from sql table, and we can then perform read/update/delete operation on it.

 Below is the query in where we define filter criteria to retrieve an item with specific RowID.

Leave a Reply

Your email address will not be published. Required fields are marked *