Previously I have discussed SharePoint’s Business Connectivity Services specifically the External Content Type (ECT) and the External List.  Basically an ECT is a mapping of all the data that you want to surface from an external source into SharePoint.  An external list is where that ECT is surfaced into once you get it into SharePoint.  An external list is very similar to a regular SharePoint List, but some features are not available.  Some features that you don’t have include the ability to add or delete columns, quick edit, and most of the social specific features.  You can find a complete list from Microsoft here.

Today I am going to illustrate to you how to create an ECT and an external list while making use of the secure store entry I showed you how to create previously.  Today’s demo is going to be a completely “no-code” solution.  My intention is to illustrate some code based example in a later blog post, but today we are strictly doing this without writing any code.

Configure the ECT Header Information

  1. Open up SharePoint Designer 2013 and connect to your site.
  2. On the Navigation section on the left click on External Content Types.
  3. Now on the Ribbon, click External Content Type.

NewECT_Create

  1. Click on the text beside Name and enter a name for your ECT.  This will be your internal name.
  2. By default it will set the Display Name to the same, but you can change that if you wish.
  3. Enter the Namespace you wish to use.  It defaults to the site you are connecting to, but is not necessary to maintain.  Can be any unique string you wish to use.
  4. Next select the Office Item Type.  This will allow Designer to pre-configure the ECT for you to be used by various Office applications.  For example, if you selected contacts, it could be imported as contacts into your Outlook application.  However, the vast majority of Item Types will be Generic Custom List.
  5. When finished, your config screen should look like something similar to this:

NewECT_Config

  1. Click on “Click here to discover external data sources and define operations” to setup your connection to the database.
  2. Click on Add Connection
  3. You will notice you are given three choices of data to connect to.  OData is missing.  This is because, while SharePoint is a producer for OData, SharePoint Designer can’t create connections to it.  Any OData connections must be developed.  Select SQL Server and click OK.

NewECT_ConnectType

  1. The next screen is for configuring your data connection to your external data source.
    • Database Server: <Instance and database name the external data is stored on>
    • Database Name: <Name of database containing data>
    • Name: <Display name of ECT>
    • Next select the Authentication options you wish to use to connect.  I provide an overview of these here.

NewECT_ConnectDBConfig

  1. You will be prompted for the username and pwd to connect.  Ensure you use the account that has access to the external source.

 

Map Parameters

  1. Now that you have created a connection to the external source, navigate down to the table you have connected to (click on the plus sign + to open the object).  For example, Click on the + beside the name you gave the ECT.  Then click on the + beside Tables.  Your tables will be displayed.
  2. You will see a warning under External Content Type Operations that we haven’t set up any operations on the ECT yet.

NewECT_NoCRUD

  1. Right click on the data connection we previously configured and select Create All Connections.

NewECT_NewCRUD

  1. Click Next.
  2. Click Next until you reach the Parameters configuration page.
  3. Highlight the Primary Key or primary field of your external data.  Click on Map to Identifier.  An ECT requires the primary ID field (like a primary key).
  4. Optional: Select a field that you expect to be used as a data field for another list and click on Show in Picker.
  5. Optional: If some of your fields aren’t named very nice, you can update how a user will see them without affecting their connection to the data by updating the Display Name field.

NewECT_NewCRUDParameters

  1. Click Next.

 

Add Filters

Filters are not a required step, but adding at least a Limit filter to ensure your queries to the data don’t take a long time is wise.

  1. Click on Add Filter Parameter.
  2. Beside Filter: click [Click to Add]
  3. Fill in the filter configurations
    • New Filter: Name of the filter
    • Filter Type: Limit
    • Filter Field: Usually the Identifier of the external data.

NewECT_RowLimitConfig

  1. Click OK.
  2. The default value is actually a combo box and not just a drop down box.  Which means you can manually type in the number you wish to limit it to.  Place whatever number of rows you feel is sufficient.
  3. Click Finish.

VERY IMPORTANT:  There’s been a few times where I have finished with all these wizards and tried to make use of my ECT; only to find it wasn’t there.  None of this work is published to your site until you click on the save button.

 

Create External List

  1. On your site, click on Site Contents and then Add an App.
  2. Scroll down until you see External List

ExternalList_SelectApp

  1. Type in the name of your list and then click on the farthest right button beside the External Content Type field to open the selection window.

ExternalList_Config

  1. Select the ECT your just created and click OK.

ExternalList_SelectECT

  1. Click Create.
  2. Your list will now build out the list based on the ECT you created and the data contained within the external source.

ExternalList_BuildingList

ExternalList_Completed

 

So as you can see you can do quite a lot with no code solutions around BCS and ECTs.  Stay tuned as I plan to illustrate soon how to complete these steps using a code based solution.