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 list
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 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. On the ‘Filter Parameters Configuration’ screen click ‘Add Filter Parameter’
13. When the properties section display’s change the ‘Data Source Element’ to Name and click on ‘(Click to Add)’
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 name
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 Operator
19. Click ‘OK’
20. Change the Operator to ‘OR’
21. Click Finish
22. Click ‘Save’
23. The ECT will be saved to the BDC Metadata Store
24. Once the document has been saved we can navigate to our SharePoint Site
25. Add a ‘Business Data List’ Web Part to your page
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 options
30. Enter a Value i.e Bike and press ‘Retrieve Data’ – This will bring back all products with the word ‘Bike’ in them
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 

<phill/>