External lists are the preferred method of displaying external content. These can be created using the browser, SharePoint Designer, or Windows PowerShell. Depending on the operations that you have defined on the ECT, you can create, read, update, and delete individual external content data, such as a specific customer, order, or employee from the external system. You can also add an XLV Web Part or a DFWP to a page that displays data from an external list.

Remember, the external content is not stored in SharePoint content databases; therefore, an external list cannot replicate all the same functionality as an internal SharePoint list. For example, you cannot use the Datasheet view, associate RSS feeds, or set item-level permissions; however, you can export the list data to Excel (a new feature with SharePoint 2013). In the Excel workbook, the data is linked to the external list, and therefore, when a user chooses to refresh the Excel workbook, the data is retrieved from the external system. As with internal SharePoint lists, this is a one-way synchronization process; that is, when a user modifies or deletes data in the Excel workbook, the data is not changed in the external system. And when a user synchronizes the workbook with the external system, all modifications in the Excel workbook are lost.

SharePoint does not have native control over the external content and does not know when data is in the external system; however, with the help of a developer, workflow can be triggered when data in the external system changes and alerts can be sent.

To create an external list using the browser, follow these steps:

External data columns enable you to add external content to a standard SharePoint list or library. You create an external data column as you would any other column; that is, on the List or Library tab, click the Create Column command and then, on the Create Column page, enter a column name and then select External Data as the column type. In the Additional Column Settings section, to the right of the External Content Type text box are two icons: the Check ECT icon, which you use when you type the name of the ECT in the check box, and the Select ECT icon, that, when clicked, opens the External Content Type Picker dialog box.

Once an ECT is selected, the Additional Column Settings section contains a list of properties associated with the ECT, as shown in Figure 22-4. In the Select The Field To Be Shown On This Column drop-down list, select the column that your users will usually associate with the external data. If the external system is a CRM system, then this may be the company name or contact name. You can then choose to add one or more fields from the external content to become columns in the list or library, such as the customer’s email address or phone number. You can then choose to add the fields to all content types, as well as adding it to the default view.

Figure 22-4. The Additional Column Settings section contains a list of properties associated with the ECT.

It is only when you add a new list item and click the Select External Item(s) icon that the BDC server run time will connect to the external system to retrieve data to populate the Choose dialog box, as shown in Figure 22-5. The dialog box will only show those fields which had the external item picker check box selected when you configured the ECT operations. When the external item picker check box is not selected for any field, all fields are displayed in the Choose dialog box.

Figure 22-5. Choose an entity from the external system to populate a column in a list.

When the new list item is saved, the external content is stored in the list in the SharePoint SQL content database, unlike the external list, which only contains a link to the ECT definition. To update the data in the list, you can click the Refresh icon to the right of the external data column name, as shown in Figure 22-6. A webpage is displayed that warns you that this operation could take a long time. You can choose to sync this folder only or this folder and all subfolders. If you click OK, the BDC server run time connects to the external system to return the necessary data. By copying the external content in the list, it has inherited all list type operations, such as views, filters, and the ability to be used to trigger list workflows.

Figure 22-6. Refresh the external content stored in External Data columns.

To the right of the items in the external column is an icon and a down arrow; this is the External Data Action menu, as shown in Figure 22-7. The menu provides links to pages that display information relevant to the ECT item, such as displaying all the values for all the properties of the ECT item, known as the profile page, or by using the postal code property of the ECT item displaying a map of that location.

Figure 22-7. Use the External Data Action menu to view all the pages relevant to the ECT item.

Creating external data actions External data actions can be created with the SharePoint Central Administration website by following these steps:

Business data web parts SharePoint 2013 ships with seven generic external data web parts, six of which are functionally the same as they were for the business data web parts in SharePoint Server 2007: Business Data Actions, Business Data Connectivity Filter, Business Data Item, Business Data Item Builder, Business Data List, and Business Data Related List. The seventh is the Chart Web Part, which was introduced in SharePoint 2010.

These web parts can be used to display any data using any ECT without writing any code. After they are configured, they will automatically be named after the entity data they are displaying. The web parts that display data from the external systems query the metadata cached on each web front-end server, and then the instance data is retrieved from the data source. To use the business data web parts on a site, you must activate the SharePoint Server Enterprise Site Collection features at the site-collection level.

Surfacing external data in Word When an external data column is added to a library, the values in the external column can be made available in a .docx Word file by inserting a Quick Part onto a document, enabling you to embed BCS data. The first column to select would be the External Data column’s display name (for example, Customer), as shown in Figure 22-8. You can only choose fields from the ECT that you choose to display in the library.

Figure 22-8. Use the Quick Parts menu on the Insert tab to embed BCS data into the Word document.

The document properties are inserted onto the page as controls, as shown in Figure 22-9, with two icons that allow you to select data from the external system.

Figure 22-9. Quick Parts are added as controls on the page.

Follow these steps to create a BDC model for an SQL Server database:

Once the external system definition is defined, you can now create an ECT and specify the CRUD operations that you wish to execute on that external system. Many organizations use ECTs rather than the Data Sources gallery for security and logistic reasons. The ECT can be defined once and stored centrally in the BDC metadata store, but used many times for all sites and site collections depending on the permission settings of the ECT. The schema and authentication methods need only be explained to a small number of ECT designers. This is as opposed to the Data Sources gallery, where the data source definition would have to be created for each site where you want to use the external content.

Follow these steps to create an ECT using SharePoint Designer:

The ECT is just one of the BDC metadata store objects for which you can set permissions, as described in Table 22-2. These permissions affect the interface between the SharePoint server and the presentation layer; that is, they do not define the authentication and security settings between the SharePoint server and the external system—those security settings are defined in the external system definition. The other objects present in the BDC metadata store that have their own access control list (ACL) are the BDC model and external system definitions. These permissions can be set by using the SharePoint Central Administration website or Windows PowerShell.

$bdcproxy = Get-SPServiceApplicationProxy | where {$_.displayname –eq $BDCname};
Get-SPBusinessDataCatalogThrottleConfig –ServiceApplication $bdcproxy ‘
   –Scope Global –ThrottleType Connections;
Scope        : Global
ThrottleType : Connections
Enforced     : True
Default      : 200
Max          : 500

Get-SPBusinessDataCatalogThrottleConfig –ServiceApplication $bdcproxy '
   –Scope Global –ThrottleType Connections | Set-SPBusinessDataCatalogThrottle Config '
   -Enforced:$False;

$dbrule = Get-SPBusinessDataCatalogThrottleConfig –ServiceApplication $bdcproxy '
   –Scope Database –ThrottleType Items;
$dbrule | Set-SPBusinessDataCatalogThrottleConfig –Maximum 2000000 –Default 5000;

If you are fortunate to have a development environment, you may have created your external system definition and ECTs in that environment. If the testing was successful, you will now have to deploy these BDC definitions to the production farm by first exporting the appropriate BDC model from the BDC service application in the development environment and then importing the BDC model into the correct BDC service application in the production farm. You can use the SharePoint Central Administration website, a Windows PowerShell cmdlet, or SharePoint Designer to export the BDC model.

If you created the External System definition and ECT using SharePoint Designer then you should use that tool to export them. The BDC model, SharePointDesigner-<databasename>-<userid>-<guid>, created by SharePoint Designer, does not link to the ECTs that are created by SharePoint Designer. Therefore, you cannot use the SharePoint Central Administration website to export ECTs developed with SharePoint Designer.

If the BDC model is to be used by Office 2013 applications, then you can only export them in the correct format by using SharePoint Designer. The permissions you have configured on the BDC model, the external system, and ECT can also be included in the exported BDC model.

You can use the SharePoint Central Administration website or a Windows PowerShell cmdlet to import a BDC model. You cannot use SharePoint Designer to import BDC models.

When you export a BDC model, make changes and then import it into a BCS application; you should update the version number for the ECT. By default, the version number is set to 1.0.0.0. Changing the third or second number of the version number indicates a small change, such as adding a new method or changing connection information. Changing the first or second number of the version number signals a “breaking change” to BCS, such as adding a new field to the Read Item operation (Specific Finder method) or changing the identifier field. Breaking changes usually cause external lists that were defined using the previous version of the BDC model to fail. Therefore, before making an ECT widely available, ensure that your ECT meets all your business needs and is tested thoroughly.

To export a BDC model using SharePoint Designer, follow these steps:

To export a BDC model or the resource information in a separate file using the Central Administration website, follow these steps:

To import a BDC model using the Central Administration website, follow these steps:

Once the BDC model is imported, SharePoint 2013 separates the external system and the ECT information. You should review both these objects and set permissions according to your requirements. You should then check that an external list can be created from the ECT, and external content can be included in an external data column in a list or library.

You can also use the Delete command on the Edit tab to delete BCD models, external systems, or ECTs.