Create an external content type for a Business Connectivity Services on-premises solution in SharePoint 2013

SharePoint 2013

We are in the process of combining the SharePoint Server 2013 and SharePoint Server 2016 content into a single content set. We appreciate your patience while we reorganize things. See the Applies To tag at the top of each article to find out which version of SharePoint an article applies to.


Applies to: SharePoint Foundation 2013, SharePoint Server 2013

Topic Last Modified: 2016-12-16

Summary: How to create and configure an external content type for the Business Connectivity Services (BCS) on-premises scenario deployment in SharePoint 2013.

These procedures walk you through building an external content type for Business Connectivity Services using SharePoint Designer 2013 without writing any code. You will learn how to discover a SQL Server database, connect to the database table, and then return the required data. You will create an external content type named Customers that is based on the Customer view in the AdventureWorks sample database. This article uses the procedures in How to: Create external content types for SQL Server in SharePoint 2013 Preview. You must open that article and perform the steps there using the parameters given in the matching sections of this article.

The sections in this article match the sections in the How to: Create external content types for SQL Server in Sharepoint 2013.
This is Step 7 in the Business Connectivity Services On-Premises scenario deployment procedures.

Define general information
  1. Open How to: Create external content types for SQL Server in SharePoint 2013 Preview

  2. Create a new external content type named AWcustomers with a display name of AdventureWorks Customers.

Define general and Office behaviors
  1. Set the Office Item Type to Contact. The Office Item Type determines the Outlook behavior you want to attach to the external content type. In this case, this AWCustomer external content type behaves like a native Contact Item in Outlook.

  2. In the Offline Sync for External List checkbox, make sure Enabled is selected, which is the default.

If you disable this option, then the SharePoint Connect to Outlook ribbon command is not available for an external list.
Create a connection to the external data
  1. Add a connection using SQL Server as the External Data Source Type.

  2. In the Set the Database Server box, enter <The name of the database server> and in the Set the Database Name box, enter AdventureWorks2008R2. Optionally, in the Name box, enter AdventureWorks Sample Database.

  3. Select Connect with Impersonated Custom Identity.

  4. In the Secure Store Application ID box, enter AWTargetAppID.

If you are prompted to enter a user name and password for AWTargetAppID it may be because when you created the SharePointGroupAccount SQL login, you did not uncheck the User must change password at next login option. To fix this, you must change the password via SQL query ALTER LOGIN <LoginName> WITH PASSWORD = '<originalpassword>'
Select a table, view, or routine and Define Operation
  1. In the AdventureWorks Sample Database select the vIndividualCustomer view and right click Create All Operations.

Create All Operations is a convenient way to define all basic methods of operations (Create, Read, Read List, Update, and Delete).
Always read carefully the messages in the Errors and Warnings pane. They provide useful information to confirm your actions or troubleshoot any issues.
Add columns
  1. In the Parameters Configuration dialog box, by default all columns are selected. To remove unnecessary columns, clear the checkboxes next to the following columns: Suffix and Demographics.

  2. For the BusinessEntityID select the Map to Identifier value.

    Uncheck the Required box to prevent it from being updated but select the Read Only checkbox, which is needed to retrieve items so you can update other fields.
Map Outlook fields and set up the external item picker control
  1. For the FirstName, LastName, EmailAddress, and PhoneNumber fields, do the following:

  2. Click and highlight the field.

  3. Under properties, in the Office property dropdown, select the appropriate matching field: FirstName to First Name (FirstName), LastName toLast Name (LastName), and PhoneNumber to Primary Telephone Phone Number (PrimaryTelephonePhoneNumber), EmailAddress to EmailAddress1 (Email1Address).

    Unmapped fields, depending on the number, are displayed as extended properties. For two to five fields they are listed as Adjoining meaning that they are appended to the form region at the bottom of an Outlook form's default page. For six or more fields they are listed as Separate and are added as a new page to an Outlook.
  4. For the following fields, BusinessEntityID, FirstName, LastName, and EmailAddress click and highlight the field, and then under Properties, click Show in Picker.

Define filters
  1. Create a Comparison filter named ByRegion, use CountryRegionName for the value.

  2. Under Properties, next to Default Value, enter Canada.

  3. Create Limit filter named AWLimit, use BusinessEntityID for the Filter Field

  4. Set the default value to 200

Click the Errors and Warnings pane and make sure there are no more errors or warnings.
Set the Title field for an external list and complete the external content type
  1. Set BusinessEntityID as the Title and save the external content type.

Link to Step 8 Configure permission on an external content type for a Business Connectivity Services on-premises solution in SharePoint 2013 of the Business Connectivity Services On-Premises scenario deployment procedures.