In this walk through we are going to show you how to create Limit Filters for your Business Connectivity Services External Content Types in SharePoint Designer 2010. These filters will let limit the amount of records being returned by your back end data source which will not only improve performance, but also make your users lives easier as they have less data to work with and process.

One important thing to note is that a Limit Filter on it’s own will just limit the items returned, this means that without another filter type you can only access a subset of your data. For example if you want to limit the amount of Products returned by a query to 100, you would add a Limit Filter and add another such as a Wildcard Filter, this will mean you will get a maximum of 100 Products which match the Wildcard filter returned. For information on how to add a Comparison or Wildcard Filter check out our blog post here: Creating Comparison and Wildcard filters for BCS in SharePoint Designer 2010

Pre-requisites..

• SharePoint Designer 2010
• A Data Source – We will be using the ‘AdventureWorks’ Products table

How to..

1. Open SharePoint Designer
2. Connect to your SharePoint Site
3. Click on ‘External Content Types’ from the Site Objects list

   

4. Click ‘External Content Type’ from the ‘New’ section of the ribbon
5. Give your External Content Type a meaning full name – You just need to click on the existing text to edit

     –>

6. Click the link in the External Content Type Operations section to ‘discover external data sources and define operations’

   

7. Click ‘Add Connection’ and choose ’SQL Server’
8. Enter your connection credentials and click OK

   

9. Expand the tree view to show the ‘Products’ Table

   

10. Right Click on the Table and choose ‘New Read List Operation’
11. Change the ‘Operation Name’ and ‘Operation Display Name’ to “All Products and Information”, Click Next
12. You will see that SharePoint Designer gives you a warning letting you know you should add a Limit Filer

    

13. On the ‘Filter Parameters Configuration’ screen click ‘Add Filter Parameter’

      

14. When the properties section display’s change the ‘Data Source Element’ to ProductID and click on ‘(Click to Add)’

     

15. Enter the Filter Information as the following. We are going to create a Limit Filter on the Product ID. A Limit Filter lets you choose the number of rows you want returned from the database

     

16. Click ‘OK’
17. Change the Default Value to 100 – 100 rows will be returned from the Database

    

18. *As per the note at the top of the page, normally you would add another Filter Type here*
19. Click Finish
20. Click ‘Save’

    

21. The ECT will be saved to the BDC Metadata Store

    

22. Once the document has been saved we can navigate to our SharePoint Site
23. Add a ‘Business Data List’ Web Part to your page

    

24. Click on the icon to bring up the ECT Picker
25. Select the AdventureWorks Products ECT
26. Click ‘OK’
27. The web part will display the Product information, if you page through the results you will notice that it will only go to item 100.

     

<phill/>