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

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 Walkthrough 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

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 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”

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.

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 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 operation can be seen on to the right side under “External Content Type Operations” section as shown below

BDC offers us a very powerful feature of creating any number of “Read List” Operations with in 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”

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

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

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.

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.

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

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

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

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

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.

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 on “add an app”

Choose “External List” Template

Choose appropriate ECT using ECT Picker and Click Create.

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

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.

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 with in 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.

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.

Specify the pattern and click OK.

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

By this Walkthrough 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 with in 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…

by Prashant Bansal via Everyone's Blog Posts - SharePoint Community