Share via

SharePoint 2013: How to develop Filtered Views for External Lists using Business Data Connectivity Service

  • Article

There are scenarios where we need to interact with LOB Data Sources containing Lakhs of records, and it is not feasible to interface all the data into SharePoint at once without significant performance loss.

But this business scenario can be taken care by developing Filtered Views for External Lists using BDC.

With this simple Walk-through we are going to investigate the Filtered Views for External Lists which provides a mechanism of interfacing Large Volume of LOB data into SharePoint using “External Content Types” now onwards we call them ECTs.

By Implementing Filtered Views we can achieve high performing solutions with “No Code Solution” Approaches.

Now let’s start our journey to see BDC Filtered Views in Action.

Start SharePoint Designer and navigate to the required site

https://howtodowithsharepoint.files.wordpress.com/2014/06/1.png?w=450&h=150

Click “External Content Types” in the Site Objects Panel on the left

https://howtodowithsharepoint.files.wordpress.com/2014/06/2.png?w=450&h=279

Click “External Content Type” in the Ribbon Bar at the top

https://howtodowithsharepoint.files.wordpress.com/2014/06/3.png?w=450&h=347

Enter the following information on the ECT Definition Page shown on the right

  1.  Name: <Enter any Name of your choice>
  2.  Display Name: <Enter any Display Name of your choice>
  3.  Namespace: <Enter any Namespace of your choice. Make sure it must be a unique value>

Then click “Click here to discover external data sources and define operations” as shown below

https://howtodowithsharepoint.files.wordpress.com/2014/06/4.png?w=450&h=161

Alternatively you can click “Operations Design View” in the Ribbon Bar at the top

https://howtodowithsharepoint.files.wordpress.com/2014/06/5.png?w=450&h=234

Create New Connection to LOB System by clicking “Add Connection” Icon in “Operation Design View”

https://howtodowithsharepoint.files.wordpress.com/2014/06/6.png?w=450&h=302

  1. Enter Database Server Name
  2. Enter Database Name
  3. Name of the Connection which will be shown in Data Source Explorer
  4. When it comes to User Authentication, we can choose any of the three available modes as per the requirements. In this walkthrough we are going to configure an Application ID with the name “SQLServer” under SharePoint Secure Store Service and use it here for User Authentication.

Right-click the table you want to map in BDC model, (in our case it is “Customers” Table) and select “Create All Operations”.

https://howtodowithsharepoint.files.wordpress.com/2014/06/7.png?w=390&h=300

Click Next.

https://howtodowithsharepoint.files.wordpress.com/2014/06/8.png?w=450&h=338

You will see a couple of errors visible under “Errors and Warnings” section. We need to be carefully gone through all the Errors/Warnings and make sure to fix them all before proceeding any further.

The errors shown in the following screen shot can be fixed by making the following modifications in BDC Model:

  1.  Customer ID Field must be selected as Identifier as this column represents the Primary Key Field in the LOB System.
  2.  Customer ID field can either be “Required” or “Read-Only”, so we need to choose any one of the options as required
  3.  There is atleast one Field for which “Show in Picker” option must be selected, this option is necessary to enable Picker Values for fields of type “External Data”

https://howtodowithsharepoint.files.wordpress.com/2014/06/9.png?w=450&h=338

We can easily fix the issues by making following modifications in the model:

Select “CustomerId” column on the left

  1.  Select “Map to Identifier”
  2.  Un-Select “Required” Checkbox (this is applicable in our scenario as Customer ID will be generated automatically by the LOB System)
  3.  Select “Show in Picker” Checkbox

Though it is absolutely optional but we should modify the “Display Name” for the column as this is the name which will be displayed to the users as List Fields when they create and access the External Lists referring this ECT.

https://howtodowithsharepoint.files.wordpress.com/2014/06/10.png?w=450&h=338

Follow the same steps for other columns as necessary.

Then click Next.

It is always a good idea to apply filters on the data fetched from LOB systems using BDC Model Definition.

Based on Microsoft recommendations on BDC Models, having atleast a “Limit Filter” applied on the data improves the overall performs of the solution to the great extent as this filter avoid large result sets to interface into SharePoint.

In order to add the Filters click “Add Filters Parameter” button

https://howtodowithsharepoint.files.wordpress.com/2014/06/11.png?w=450&h=336

Now click “Click to Add” Link as shown below

https://howtodowithsharepoint.files.wordpress.com/2014/06/13.png?w=450&h=339

Specify the name of the Filter as “LimitFilter”

Select Filter Type as “Limit”

Filter Field will be selected by Default to the Identifier column in our case it is “CustomerId”

Click OK.

https://howtodowithsharepoint.files.wordpress.com/2014/06/14.png?w=450&h=344

Set the default value for the filter parameter as applicable

Click Finish.

https://howtodowithsharepoint.files.wordpress.com/2014/06/15.png?w=450&h=338

Great! We are done with creating all the required data operations.

All the created data operation can be seen on to the right side under “External Content Type Operations” section as shown below

https://howtodowithsharepoint.files.wordpress.com/2014/06/16.png?w=450&h=225

BDC offers us a very powerful feature of creating any number of “Read List” Operations within the same BDC Model Definition, this feature provides us the base for creating Filtered Views.

We can create as many Read List operations based on different filter criteria, like one operation could be created with Limit Filter to limit the number of records displayed from LOB System, other one could be created with Comparison filter to match a specific value with a specific column, or might be we can create a Read List Operation with Wildcard filter to match a pattern with a specific column and so on.

Now we will create a new Read List Operation based on a Data View already present in the LOB system. This data view takes a parameter of “Wildcard” type and return the results matching the pattern.

Expand the Views Folder then select the view on which this new Read List Operation will be based. Right-click and select “New Read List Operation”

https://howtodowithsharepoint.files.wordpress.com/2014/06/17.png?w=329&h=300

Configure Read List Operation as we did in the previous steps.

https://howtodowithsharepoint.files.wordpress.com/2014/06/18.png?w=450&h=338

Add a new Filter as we did before, but this time we are going to create a filter of type “Wildcard”

https://howtodowithsharepoint.files.wordpress.com/2014/06/19.png?w=450&h=332

https://howtodowithsharepoint.files.wordpress.com/2014/06/20.png?w=450&h=339

Here we can see that the BDC Model Editor is intelligent enough to identify the Input Parameter required by LOB System to provide the data, in our case this parameter is “@CustomerIdWc” .

Also we can make changes to the Display Name for this parameter, this display name will be shown to the users when they get into the External List Views created by this BDC model.

Configure rest of the Read List Definition as we did in earlier steps.

Click Finish. This completes the definition of new Read List Operation with Wildcard Filter.

https://howtodowithsharepoint.files.wordpress.com/2014/06/21.png?w=450&h=338

https://howtodowithsharepoint.files.wordpress.com/2014/06/22.png?w=450&h=143

We can revisit the connection Properties by clicking on “Connection-To-Northwind” link as shown below, in case we want to make any further changes to them.

https://howtodowithsharepoint.files.wordpress.com/2014/06/23.png?w=450&h=368

https://howtodowithsharepoint.files.wordpress.com/2014/06/24.png?w=366&h=300

Save the BDC Model Definition inside Business Connectivity Service Instance by clicking save Icon in the Tool Bar as shown below

https://howtodowithsharepoint.files.wordpress.com/2014/06/25.png?w=292&h=300

With this we are all done with the development of External Content Type (ECT) that is capable of supporting Filtered Views for any External List which is consuming this ECT.

But before we proceed any further we need to provide the necessary permissions to ECT by navigating “Business Data Connectivity Service” Instance

Go to Central Admin => Manage service applications

https://howtodowithsharepoint.files.wordpress.com/2014/06/26.png?w=351&h=300

Click Business Data Connectivity Service Instance, it will display all the ECTs saved under this instance as shown below

https://howtodowithsharepoint.files.wordpress.com/2014/06/27.png?w=450&h=146

Select the required ECT and Click on “Set Object Permission” in Ribbon Bar

https://howtodowithsharepoint.files.wordpress.com/2014/06/28.png?w=450&h=191

Add required Users or Group and assign them the proper permissions.

“Execute” permission must be granted at the minimum in order to get the data in External Lists using this ECT.

https://howtodowithsharepoint.files.wordpress.com/2014/06/29.png?w=450&h=444

Now let’s test the ECT by creating a new External List and associating it with this ECT as shown below
Browse Site => Site Content => Click “Add an app”

https://howtodowithsharepoint.files.wordpress.com/2014/06/30.png?w=450&h=240

Choose “External List” Template

https://howtodowithsharepoint.files.wordpress.com/2014/06/31.png?w=450&h=344

Choose appropriate ECT using ECT Picker and Click Create.

This will create an External List based on ECT we created.

https://howtodowithsharepoint.files.wordpress.com/2014/06/32.png?w=450&h=242

https://howtodowithsharepoint.files.wordpress.com/2014/06/33.png?w=450&h=135

As soon as the list is created, default View of the List will be displayed.

Note that this default view will always be based on default “Read List” operation as defined with the BDC Model for the ECT.

https://howtodowithsharepoint.files.wordpress.com/2014/06/34.png?w=450&h=389

Now if we got to Ribbon Bar “List” Section and look for the Dropdown list for all the defined views for the External List (remember each view is mapped to one of the Read List Operations defined within the BDC model for ECT).

In the following screen shot we can see one more View which is mapped on a “Read List” Operation supported by Wildcard Filter.

https://howtodowithsharepoint.files.wordpress.com/2014/06/35.png?w=450&h=144

If we choose the view “Get Matching Items”, it will filter out the result sets to limit only those customers where Customer ID matches the Pattern provided to the Wildcard Filter as value.

In order to assign value to Wildcard Filter Parameter, edit “Get Matching Items” View and provide a value to Wildcard Parameter under “Data Source Filters” section.

https://howtodowithsharepoint.files.wordpress.com/2014/06/36.png?w=450&h=161

Specify the pattern and click OK.

And sure enough you will get all the record where Customer ID starts with “Alf”.

https://howtodowithsharepoint.files.wordpress.com/2014/06/37.png?w=450&h=107

By this Walk-through we can derive the following conclusions:

  1. Filtered Views are really a very powerful way of dealing with the large volume of LOB data while keeping the performance of the solution to a high.
  2. As there are any number of Read List Operations can be defined within the same BDC Model, it presents us with an opportunity to create as many Filtered View as required in order to deal with the LOB data.
  3. This solution is presenting with “No Code Solution Approach” so can be developed easily and require lesser number of testing iterations.

Hope this will help someone in need.