Creating Comparison and Wildcard Filters for BCS in SharePoint 2013 using Meta Man

By

In this walk through we are going to show you how to create by Meta Man comparison and wildcard filters for External Content Type in SharePoint 2013. 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. We will consider an example for MS SQL but the same way you may work with any other data source supported by Meta Man.

  1. Prerequisites:
    • SharePoint Server 2013
    • Meta Man (it doesn’t necessarily need to be installed on the SharePoint server but it requires .net Framework 4.5 installed on the machine where you are going to run Meta Man from)
    • MS SQL server with any sample database (for this walk through we are going to use the NorthWind sample database )
  2. Open Meta Man and choose to connect to a SQL Server data source. Select or enter the database name you wish to work with and press arrow in order to connect to selected database.Connect to MS SQL server
  3. Upon successfully connecting to SQL Server you’ll see another window. In order to get a list of tables click “Tables” item in the list on the left pane. Now we can see listed all the tables for this particular database.List of tables
  4. We will be working with table “Territories”, to add it to the Model just drag “Territories” onto the “Diagram” Drag a table on the design surface
  5. When you drop the table you will be shown the following dialog, which allows to configure the external content type before creation.  In this example we’ll accept all default values and click “Create” button.Configure the external content type
  6. You should have the following External Content Type on your Diagram Created External Content Type
  7. Right click on the created external content type and choose “Configure Entity” menu item. Configure External Content Type
  8. In the entity configuration dialog go to Methods tab.Go to Methods tab 
  9. Make sure that “Finder” method is selected and click on “Add” button in the “Filters” sectionClick "Add" in the filter section
  10. Configure a wildcard filter for “Territories” table’s Finder method. In order to get by default all records where “TerritoryDescription” start with letter ‘B’ we’ll set default value “B%”. Click on “Save” button. Configure BCS wildcard filter
  11. Using the above mentioned steps, define a comparison filter for the “Territories” table. In order to get by default all records where “RegionID” is equal 1 we’ll select comparison operator “Equals” and set default value to one. Click on “Save” button. Configure BCS comparison filter
  12. We’ll combine created filters using “And” logical operator and click “Update”Combine created BCS filters
  13. Now simply click “Generate” button and your BDC model file will get generated.Generate BDC model file
  14. Now we need to import our BDC model file. Open up SharePoint 2013 Central Administration. Navigate to Manage Service Application->Business Data Connectivity Service. Click “Import” button on the ribbon.Import generated BDC model file
  15. Use the browser button to locate our BDC model file, or type in the path and filename yourself. Leave all the other options as they are for the time being, and click “Import”Locate our BDC model file
  16. SharePoint 2013 will now go through and validate your BDC model file is correct, once it is done you will get a success page.Imported BDC model file
  17. Browse to the site where you want to add the External List. Click “Site Contents”->”add an app”.Add an app to SharePoint site
  18. Select “External List”Select “External List”
  19. Enter a name for External list and click on the icon to show the available External Content Types External list name
  20. Select our generated Northwind_MetaMan.Territories External Content Type, click “OK” Select External Content Type
  21. Click “Create”Create External Content Type
  22. Your filtered external data from MS SQL database is now displayed in the SharePoint External List Filtered external data from MS SQL database is displayed on SharePoint 2013 page
  23. You can change default filter values by going “Modify View” on the ribbonModify View
  24. Go to “Data Source Filters” and set new values. Click “OK”set new filter values
  25. External list now shows another filtered records from MS SQL databaseAnother filtered external data from MS SQL database is displayed on SharePoint 2013 page

We hope this walkthrough will be useful for you. If you have any questions feel free to email them to support@lightningtools.com

<Dmitry Kaloshin/>