Creating Limit Filters in SharePoint Designer 2010

 Business Connectivity Services, SharePoint 2010.

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

    image thumb Creating Limit Filters in SharePoint Designer 2010

  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

    image thumb 1 Creating Limit Filters in SharePoint Designer 2010  –> image thumb 2 Creating Limit Filters in SharePoint Designer 2010

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

    image thumb 3 Creating Limit Filters in SharePoint Designer 2010

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

    image thumb 4 Creating Limit Filters in SharePoint Designer 2010

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

    image thumb 5 Creating Limit Filters in SharePoint Designer 2010

  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

    image thumb 6 Creating Limit Filters in SharePoint Designer 2010

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

    image thumb 14 Creating Limit Filters in SharePoint Designer 2010  

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

     image thumb 9 Creating Limit Filters in SharePoint Designer 2010

  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

      -top-width: 0px; display: inline; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" height="158" alt="Create Limit Filter" src="http://lightningtools.com/blog/images/lightningtools_com/blog/WindowsLiveWriter/CreatingmultiplefindersinSharePointDesig_97C7/image_thumb_7.png" width="244" border="0" />

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

    image thumb 8 Creating Limit Filters in SharePoint Designer 2010

  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’

    10 thumb Creating Limit Filters in SharePoint Designer 2010

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

    11 thumb Creating Limit Filters in SharePoint Designer 2010

  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

    12 thumb Creating Limit Filters in SharePoint Designer 2010

  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.

    image thumb 11 Creating Limit Filters in SharePoint Designer 2010 

 

<phill/>

  1. Even i too have missed you at the UK Best Practices. LOVE the comic. The naming I always had fun with was explaining the move from SPS 2001 and the Web Storage System (WSS) to Windows SharePoint Services (WSS)….

     
  2. The 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….
    Thanks…

     
  3. striker

    If you edit the filter do you have to re-create the list in order to apply the filter?

     
  4. Uday Kiran

    Tell me one thing If I want auto populate the values in the fields from one or more table and relationships,How I can achieve this???

     

Leave a Comment

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

*

* Copy This Password *

* Type Or Paste Password Here *

*