Creating an External Content Type for a Related Item

Most normalized databases have several tables with relationships to other tables, which are models for real-world information. A customer, for example, can place one or more orders with a company. An order can contain one or more products, and a single order may be delivered in one or more shipments.

A normalized database, to accurately model this information, can define relationships between these tables. A one-to-many relationship would exist between a Customers table and an Orders table. Both tables would contain a CustomerID field. In the Customers table, each individual record would have a unique CustomerID. In the Orders table, there might be several records with the same CustomerID. This represents that one customer has placed multiple orders.

The AdventureWorksLT database contains this exact relationship between the Customers and Orders tables. With Business Data Connectivity, there is an extra step involved with defining an External Content Type with this type of relationship.

The following steps address how to create an External Content Type for Orders, where a relationship to another content type exists:

1. In SharePoint Designer, in the Site Objects pane on the left side, click the External Content Types option.
2. In the External Content Types Ribbon, click the New External Content Type button.
3. In the External Content Type Information, type BCS Order for both the Name and the Display Name.
4. In the External System field, click the Click Here to Discover External Data Sources and Define External Content Types link.
5. The AdventureWorksLT data source should already be available from having completed the previous set of steps. Expand the AdventureWorksLT node and the Tables node. Expand the SalesOrderHeader node and the Columns node to view the columns in the SalesOrderHeader table. Notice the CustomerID column.
6. Right-click SalesOrderHeader, and select New Read Item Operation.
7. In the first part of the Read Item Wizard—Operation Properties—type Sales Order Read Item as both the Operation Name and the Operation Display Name. Click Next.
8. As with the CustomerID field for the Customer external content type, the SalesOrderID field is automatically recognized as the identifier for the external content type. No changes are necessary on this screen. Click Next.
9. The last portion of the wizard—Return Parameters—defines which columns from the database will be available for consumption. No changes are necessary on this screen. Click Finish.
10. Next, right-click on SalesOrderHeader, and select New Association. This is the operation necessary to establish the relationship between a customer and an order.
11. The Association Creation Wizard is divided into four sections, as shown in Figure 31.4. In the first, Association Properties, type SalesOrder Customer Association as both the Name and the Display Name.

    Figure 31.4. Create an association between related external content types.

    

12. Next to Related External Content Type, click the Browse button. The BCS Order (the current one) and BCS Customer (created earlier) external content types should both be available. Select BCS Customer and click OK. Below, the field CustomerID is automatically selected because both tables have the field with the same name. Thus SharePoint Designer recognizes that this is the likely field on which to establish the relationship. Click Next.
13. In the second part of the wizard—Input Parameters—click the CustomerID field under Data Source Elements. Then check the box to the right labeled Map to Identifier and click Next.
14. In the Filter Parameters section, no changes are necessary. Click Next.
15. In the Return Parameters section, no changes are necessary. Click Finish.
16. In the Data Source Explorer of SharePoint Designer, right-click the SalesOrderHeader table and select New Read List Operation.
17. In the Operation Properties portion of the wizard, type SalesOrder Read List as the Operation Name and the Operation Display Name. Click Next.
18. In the Filter Parameters section, click Add Filter Parameter. In the Properties section for the new filter, change the Data Source Element to CustomerID. For the Filter parameter, click the Click to Add link. Make no changes in the Filter Configuration dialog box, and click OK. For the Default Value property, enter 1. Click Next.
19. In the Return Parameters section, select SalesOrderID field in the Data Source Elements section, and select the box in the Properties section labeled Show in Picker. Repeat this for the OrderDate, ShipDate, and TotalDue fields. Click Finish.
20. Click the Save icon in the upper-left corner of SharePoint Designer (or use the Ctrl-S keys) to save the External Content Type.

At this point, there are now two external content types in the BCS Metadata Store in SharePoint central administration. These external content types are now available for use in the SharePoint sites.