Comparison and Wildcard Filters for Business Connectivity Services

In this walk through we are going to show you how to create Comparison and Wildcard Filters a Business Connectivity Services External Content Type in SharePoint Designer 2010. These filters will let you narrow down 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.

Pre-requisites..

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

How to..

  1. Open SharePoint Designer 2010
  2. Connect to your SharePoint Site
  3. Click on ‘External Content Types’ from the Site Objects listimage
  4. Click ‘External Content Type’ (ECT) from the ‘New’ section of the ribbon
  5. Give your ECT a meaning full name – You just need to click on the existing text to editimage  –> image
  6. Click the link in the External Content Type Operations section to ‘discover external data sources and define operations’image
  7. Click ‘Add Connection’ and choose ’SQL Server’
  8. Enter your connection credentials and click OKimage
  9. Expand the tree view to show the ‘Products’ Tableimage
  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. On the ‘Filter Parameters Configuration’ screen click ‘Add Filter Parameter’image
  13. When the properties section display’s change the ‘Data Source Element’ to Name and click on ‘(Click to Add)’image
  14. Enter the Filter Information as the following. We are going to create a Wildcard Filter on the Product name. A Wildcard Filter gives the search options of
    Starts With, Ends With, Contains and Equals so it will suit us searching for a product by nameimage
  15. Click ‘OK’
  16. Change the Default Value to *
  17. Click ‘Add Filter Parameter’ again, this time we will leave it to filter on the Product ID. Click the ‘(Click to Add)’ hyperlink
  18. Give the Filter a name – such as Product ID, click ‘OK’. We do not need to change anything else as by default the filter it will give us is a Comparison Filter with an Equals Operatorimage
  19. Click ‘OK’
  20. Change the Operator to ‘OR’
  21. Click Finish
  22. Click ‘Save’10
  23. The ECT will be saved to the BDC Metadata Store11
  24. Once the document has been saved we can navigate to our SharePoint Site
  25. Add a ‘Business Data List’ Web Part to your page12
  26. Click on the icon to bring up the ECT Picker
  27. Select the AdventureWorks Products ECT
  28. Click ‘OK’
  29. The Web Part will display the filter optionsimage
  30. Enter a Value i.e Bike and press ‘Retrieve Data’ – This will bring back all products with the word ‘Bike’ in themimage
  31. Take note of one of the Product IDs, Change the filter to ‘Product ID’ and enter the ID and press ‘Retrieve Data’ – this will bring back the matching Product image

<phill/>