You can add a special type of column to a native list or library called an external data column. This column lets you combine a column of data from an external data source with other columns in a native list or library. Because an external data column is added to a native list or library, all related list or library features work, including versioning, approvals, check-in and check-out, and file attachments.
For more information, see Find content about external data.
What do you want to do?
Before you begin
Working with external data requires several pre-requisite tasks to enable secure access to the data. The following information can help you plan your next steps. Also, if you experience problems trying to work with external data, this information can help you identify the issue. To access external data, you or an administrator must do the following:
Prepare the external data source An administrator may need to create an account and provide permissions to the data source to ensure that the right people have access to the data and that the data does not end up in the wrong hands. In an external database, the administrator may also want to create specific tables, views, queries, and so on to limit the results to just what is needed and to help improve performance.
Configure SharePoint services and accounts An administrator must activate Business Data Connectivity Services and Secure Store Service.
Configure Secure Store Services An administrator must do the following: determine the best access mode for the external data source, create a target application, and set the credentials for the target application.
Configure Business Data Connectivity Services An administrator must ensure that the user who creates the external content type has permission to the Business Data Connectivity metadata store and that appropriate users have access to the external content type that the external list is based on.
Create an external content type A user must define an external content type which contains information about connections, access, methods of operation, columns, filters, and other metadata used to retrieve the data from the external data source.
Understand examples of using external data columns
The following sections describe some practical uses of adding an external data column to a list or library.
Customer contracts and contact information You have a document library that contains customer contracts. However, all your customer information is maintained in a Line-of-Business application (such as SAP or Microsoft CRM) which has a data source that is external to SharePoint products. You would like to track the customer’s contracts in the document library, but ensure that related customer information is up-to-date and not unnecessarily duplicated. You can do this by adding one or more external data columns to the library to associate the appropriate customer name and phone number with each contract stored in the library. The external data column makes it easy to enter or update the correct information and automatically retrieves the customer information from the external data source. In this scenario, you could also create a structured Word document that promotes the values of the external data columns to a Word document so you can edit the values in the Document Information Panel or by using Word content controls.
Basic product data with pictures You have created a picture library that contains photos of the products your organization sells. You add an external data column to the library that accesses the product name, product code, and current price from an external database. However, you ensure that when the external content type is created, that the external data is read-only. Your company rules and processes require any changes to product information be made through the database system. But now more people in your organization can easily see all the common product information together in one library view.
Create an external data column in a list or library
Note: Creating an external data column requires Contribute permission or higher on the list or library.
-
Navigate to the SharePoint site, and then navigate to the list or library.
Note: You cannot add an external data column to an external list.
-
Click List or Library > List Settings or Library Settings.
-
In the Columns section, click Create Column.
-
Enter the name of the column, such as Customer, and then under Column Types, click External Data.
-
Under External Content Type, click the External Content Type Picker, and then select the appropriate item from the list of external content types, for example, CustomerCRM.
-
Decide how you want to handle any actions defined for the external content type by doing one or more of the following:
-
To display a menu of one or more actions, click Display the actions menu.
-
To make the external data column perform the default action when selected, click Link this column to the Default Action of the external content type.
-
-
Under Add a column to show each of these additional fields, click Select all fields or select one or more specific field names, such as CustomerName and PhoneNumber.
Tip: By default, a prefix is added to each column name based on the external data column name. It’s a good idea to define a brief column name, such as CustID, especially if you add many additional columns. If Quick Edit is enabled for the list or library, you can use Quick Edit to change the column names.
Update an external data column
Note: Updating an external data column requires Contribute permission or higher on the list or library.
-
Navigate to the SharePoint site, and then navigate to the list or library.
-
Add or edit an item in the list, or the properties of the document library.
-
In the external data column text box, for example customer name, click the External Data Item Picker.
The author of the external content type needs to define how the External Data Item Picker works by selecting an appropriate identity field and creating one or more filters so that you can quickly locate the correct value. If this is not the case, contact the author of the external content type or your administrator for assistance.
-
In the dialog box, select the appropriate entry, for example Nancy Davolio, and then click OK.
The data is updated in the external list and the external data source.
Refresh an external data column
For performance reasons, the data in an external data column is cached on a server or service and may not display the most recent values from the external data system. To ensure that the external data displayed is current, you can refresh the external data column. However, be careful doing a refresh operation because it may take a long time depending on how the external data is set up.
Note: Refreshing an external data column requires contribute permission or higher on the list or library.
-
Navigate to the SharePoint site, and then navigate to the list or library.
-
On the column header of the external data column, click Refresh.
-
On the Refresh page, decide whether you want to refresh just the current folder, or the current folder and all of its subfolders.
-
Click OK.
