How to create External Data Column in SharePoint 2010

In this article we will be seeing how to create external data column in SharePoint 2010. MOSS 2007 provided the Business Data List Column type,  in SharePoint 2010 Business Data List Column is renamed as External Data Column which enables users to add data from the external content types to standard SharePoint lists.

Creating an External Content Type:

Before we integrate external data to the SharePoint, we need to create an external content type.  You can use the SharePoint designer to create an external content type. In this example, we will be using  SharePoint Designer 2010. Following are the steps involved to create an External Content Type in SharePoint Designer 2010.

Steps Involved:

1. Open the SharePoint Designer 2010.
   
   
   
   
2. Click Open Site; enter the Site Name in the Open Site dialog box.
   
   
   
   
3. Select External Content Types in the left Navigation.
   
   
   
   
4. Click to create a new External Content Type as shown below.
   
   
   
   
5. The External Content Type dialog will be displayed. 
   
   
   
   
6. Enter the Name and Display Name for the external content type as shown in the above figure. 
7. Select the Office Item Type as Generic List from the dropdown list. 
8. Next click the link, Click here to discover external data sources and define operations to integrate the existing customer database. 
9. The Operation Designer dialog will then be displayed. Click Add a Connection to connect to the database. 
   
   
   
   
10. Select "SQL Server" as your Data Source Type.
    
    
    
    
11. Enter the details about your connection to your SQL Server, Database Name customerdetailsdb.
    
    
    
    
12. When the connection is made, your Data Source Explorer will be filled with the database you have specified.
13. Now choose the table customers which we are going to connect to the SharePoint.
    
    
    
    
14. Right click the table and select the option, Create All Operations so that you will be able to read, select, update and delete rows from the database table.
15. Now the Operation Properties window will pop up which shows the details of the operations that can be performed in the database table.
16. Click Next to get to the Parameters page, Select the field that you want to act as an Identifier. In my case I have selected CustomerID as an identifier.
    
    
    
    
17. Click Finish. 
18. You'll be presented with a list of operations that your External Content Type can do, as shown in the below figure.
    
    
    
    
19. After completing all these steps save the External Content Type. 

Creating an External Data Column:

External data column is used for retrieving data from the external data source by creating external content type. When you create External data column in the SharePoint 2010 list you need to specify the external content type.

Steps Involved:

1. Select the List in the SharePoint site(Here I am choosing Sample list that I have created in the SharePoint site).
2. Go to List Tools => List => Create Column.
   
   
   
   
3. Enter the column name and select the type as External data.
4. Choose Customer Name in the "Select the field to be shown on this column".
   
   
   
   
5. In the External Content Type section click on the External Content Type picker.
6. Choose the content type that we have created.
   
   
   
   
7. Click ok.
8. External Data column is created successfully.
9. Click on Add a new item.
10. In the External data column that we have created, click the "Select External Items "button.
11. See the below one, you will be getting the error.
    
    
    
    
12. How to resolve the above error. Check this link http://www.c-sharpcorner.com/UploadFile/anavijai/4310/.
13. Now you will be able to pick the value from the External data picker.
14. Click ok.
15. A new item is added to the Sample list.
    
    
    

Summary:

Thus the SharePoint External data column is created based on external content type that we have created using SharePoint Designer 2010 to get the values from the external data source.