Business Connectivity Services has been available for Microsoft SharePoint On-Premises for the last three versions of SharePoint since it was introduced in its Business Data Catalog form for Microsoft Office SharePoint Server 2007 Enterprise. We can also use Office 365 Business Connectivity Services in SharePoint Online.
If you use Office 365 Enterprise E3 or E4, or you use Education E2 licenses, you will be able to make use of Business Connectivity Services.
The power of Business Connectivity Services is often overlooked and considered to be just another method of displaying data within an External List. BCS however offers a great deal more than that.
What does Business Connectivity Services offer within Office 365?
- BCS External List
- BCS Business Data Web Parts
- BCS External Data Column
- BCS Office Integration
- BCS External Data Search (Hybrid Search Required)
- BCS App Scoped External Content Types
BCS External List
The BCS External List provides Office 365 users with the ability to view data, as well as Create, Update and Delete data via a SharePoint list. Users can build views with different filters and sort criteria. Each Read List (Finder Method) that is created automatically creates a view within the External List, when the External List is provisioned. There are some list features that users will be used to such as Workflows, Item level permissions and multi row editing which are unavailable since the data is linked to and not imported.
Users can manipulate the data as though it is SharePoint list content which means that the out-of-the-box feature will be intuitive for them to use.
If you do not want users to be able to modify the data, then you can omit the Creater, Updater and Deleter methods from the External Content Type.
External Lists can be provisioned via SharePoint Designer 2013 or by creating an app within Office 365.
Figure 1 – External List in Office 365
BCS Business Data Web Parts
There is a suite of Business Data Connectivity Web Parts available for insertion on web part or wiki pages within SharePoint Online. The Web Parts include:
- Business Data List Web Part
- Business Data Related List Web Part
- Business Data Item Web Part
- Business Data Item Build Web Part
- Business Data Filter Web Part
Business Data List Web Part
The Business Data List Web Part can be easily configured to display data from a Finder Method within an External Content Type. Using the web parts property toolpane, you will be able to set the External Content Type to use e.g. Customers, and the select the view e.g. Customers_Read_List.
Each view can be customized by selecting Edit View. You will be able to select the columns that you wish to display, set the Title column, Grouping, Filtering and Sorting options.
The Business Data List Web Part can consume filters from other web parts such as the filter web parts, as well as provide a row of data to other consuming web parts such as the Business Data Related list and Business Data Item Web Parts.
Figure 2 – Connected Business Data List and Related list web parts
Business Data Related List Web Part
The Business Data Related List Web Part an consume a connection from the Business Data List Web Part as well as provide a connection to other instances of the Business Data Related List Web Part. An association (join) must be present between the two External Content Types to allow the Business Data Related List Web Part to work. Once connected, you will be able to select a row from the Business Data List Web Part and see the related data in the Business Data Related List Web Part.
Business Data Item Web Part
The Business Data Item Web Part is automatically provisioned on BDC Profile pages and is designed to show a single row of data in columnar format. However, you can add the Business Data Item Web Part to any page, and it can consume a row of data from the Business Data List or Related List Web Parts.
Business Data Item Builder Web Part
The Business Data Item Builder Web Part is a hidden web part automatically provisioned to profile pages and has the job to consumer the identifier of a selected row, and pass that identifier to the Business Data Item Web Part.
Business Data Filter Web Part
The Business Data Filter Web Part is especially useful to add to any Business Data Dashboard and enables you to provide connections and pass filter values to all BDC web parts.
BCS External Data Column
The BCS External Data Column is a very powerful way to collect meta data within your SharePoint list items or Documents. The column type can be added to any SharePoint list or library. Whilst provisioning the column, you will be able to specify which columns from an external content type you wish to store and display as meta data. Users can then provide consistent meta data via lookups to their External Data when uploading or creating SharePoint content.
Figure 3 – Document Library with a External Data Column
The data that is looked up from the external data source is then stored along with the SharePoint item. Should the data change in the external data source, the change will not be reflected in the meta data unless manually refreshed.
The External Data Column provides powerful Rich Client Office Integration allowing for Microsoft Word Quickparts to provide updates via Word documents.
BCS Office Integration
Via the BCS External List, External Data can be exported to Microsoft Excel or Microsoft Outlook. Depending on the Office Type property when the External Content Type is created, you will be able to import the data into Outlook as Contacts, Tasks, Events or Posts.
Figure 4 – Microsoft Word 2013 document with the Quick Parts looking up data from the External Data Column
BCS External Data Search (Hybrid Search Required)
Within SharePoint On-Premises, the Enterprise search can index external data via Business Connectivity Services allowing for External Data to be search upon as though it were SharePoint content.
Whilst this feature is not available for Office 365, you can use Hybrid Search. Once configured, Office 365 will be able to display search results for any on-premises content source including Line of Business Data from Business Connectivity Services.
BCS App Scoped External Content Types
BCS App Scoped External Content Types can be created within Visual Studio 2012 and above. Although you will not be required to write any code. Once deployed a External List app is created and can be used to Create, Update and Delete data.
Figure 5 – Building an App Scoped External Content Type in Visual Studio
SharePoint Designer 2013 can be used to create External Content Types for Microsoft SQL Azure tables, views or stored procedures. In order for authentication to be successful, you will need to configure a Secure Store Application which passes the credentials to the SQL Azure server.
All of your required methods such as the Finder, Specific Finder, Creater, Updater and Deleter can be created from within SharePoint Designer. Filters can be set, and we recommend building a Limit Filter for External Lists, as well as Wilcard filters to overcome the External Data Column item picker limitation of 200 rows.
Once created, the ECT is saved and External Lists can be created either using SharePoint Designer or by using the browser.
Figure 6 – Building an External Content Type in Microsoft SharePoint Designer 2013
Building External Content Types with Lightning Tools BCS Meta Man
Using BCS Meta Man, you can build connections to external data using OData or SQL Azure. The BDC Model File can then be imported into SharePoint Online allowing you to utilise the data throughout the BCS suite of features. Below, you can see BCS Meta Man configuring a connection to a SQL Azure table of data.
Figure 7 – BCS Meta Man connecting to SQL Azure database and generating the BDCM file.
BCS Meta Man can be used for SharePoint 2010, 2013 on-premises as well as Office 365. External Data connections include OData, Oracle, SQL Server, SQL Azure, Salesforce, ODBC and WCF services.
Download a trial of BCS Meta Man from: https://lightningtools.com/products/bcs-meta-man-2010-and-2013/
<Brett/>
