SharePoint 2013: Develop Filtered Views For External Lists Using Business Data Connectivity Service

There are scenarios where we need to interact with LOB Data Sources containing a lack 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 of 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 provide a mechanism of interfacing Large Volume of LOB data into SharePoint using “External Content Types;” from now on we will 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,

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

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

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 on “Click here to discover external data sources and define operations” as shown below,

Alternatively you can Click on “Operations Design View” in the Ribbon Bar at the top,

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

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 on the Table you want to map in BDC model, (in our case it is “Customers” Table) and select “Create All Operations”.

Click Next.

You will see a couple of errors visible under “Errors and Warnings” section. We need to carefully go 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 at least 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”.

We can easily fix the issues by making the 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.

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 at least a “Limit Filter” applied on the data improves the overall performance of the solution to a great extent as this filter avoids large result sets to interface into SharePoint.

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

Now click on “Click to Add” Link as shown below,

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.

Set the default value for the filter parameter as applicable,

Click Finish.

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

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