In my earlier blog entries, you saw how to create an External Content Type to connect to a SQL Server table and how to display that data in an External List. Did you know that you can also use that external data in a custom list?
I’m going to assume you still have your external content type and begin by going to any site in the web farm. Remember that the external content types you define are stored in Central Administrator and work across the entire web application.
Open your site and create a new custom list (This will also work for libraries. With your list open, click on List Settings.
In the Columns section, you see all the defined columns for the list/library. Beneath the list are several options that you can perform by clicking on them. The first option is: Create Column. Clicking this option opens the New Column dialog. For the column name, you can use any name unique to your list/library. The important part is to select the column type: External Data. When you select this type, the properties in the Additional Column Settings include the field External Content Type that lets you select, by clicking the second button to the right of the field from the available External Content Types you have defined so far. Immediately beneath this property is a dropdown list of the fields from the external content type. This field will be displayed as the value for this column. Dropping down a little further is a checkbox to add all fields from the External Content Type to the list. Alternately, you can use the checkboxes to the left of each field name to custom select the fields you want to include in the list. Why do this? When you select a record from the external data for this column, SharePoint populates the other fields choosen to appear in the list with data values from the same record.
Clicking OK at the bottom of the column properties page saves the definition of the external data column.
Now if you return to your custom list and open it, you will see that the default view includes not only the custom columns defined for the list, but also all the external columns selected from the external content type.
Go to the items ribbon and add a new item. The default form that SharePoint builds for the list/library appears in a popup window. You will notice that all of the standard custom fields appear in this dialog, but only the field for the external data column defined in the column definition appears. You can directly enter a value into the external data column field directly and then click the first icon to the right of the text box to check if the external data table has a record with that value, or you can click the second button to display a list of records from the external data source. Note that this list uses a horizontal scroll bar to let you see all the columns in the external data. To select a record, click anywhere within it and then click the OK button. This returns you to the New Item dialog which you can click ‘SAVE’ if you have entered all the data for this item.
When SharePoint displays your list again, you will see not only the data you just entered, but also the data for all the associated fields.
Next time, we will look at the pros and cons of using a custom list with external data and how to build a customNew Item form for this list.