Creating a Database-Based External Content Type Association

1. Open your site by using SharePoint Designer 2010, and then go to the External Content Type Designer view.
   
   
   Figure 1. SharePoint Designer 2010 External Content Types list page
   
   
2. Create external content types, which you can base on a combination of tables, views, stored procedures, web methods, Microsoft .NET Framework methods, and so on, as shown in Figure 2.
   
   
   Figure 2. Creating external content types
   
   
3. In SharePoint Designer 2010, open the external content type that contains the foreign key of the related external content type. For example, you might create two external content types, X and Y, as shown in Figure 3.
   
   
   Figure 3. Two related external content types
   
   If X contains a foreign key that holds a value that specifies a Y external content type item, open X in SharePoint Designer, and navigate to Operation Designer, as shown in Figure 4.
   
   
   Figure 4. Operation Designer
   
   
4. Determine the association that you want to create.
   An association can retrieve and display items of one type when you select an item of another type, based on their relationship, as shown in Figure 5.
   
   
   Figure 5. Two types of associations in SharePoint Designer
   
   In most scenarios, the association is all that is required. (Reverse associations are discussed in Creating a Reverse Association.)
   

   Note:
   The association that is labeled Association in Figure 5 can also be thought of as a forward association. As described, it is typically all that is needed to create an association between two entities.

5. In Operation Designer, expand your data source in the Data Source Explorer.
   Creating an association for databases (a Microsoft SQL Server data source connection) is slightly different from creating one for a WCF service or .NET Framework type connection. This example starts with databases and continues with WCF in Creating External Content Type Associations Based on WCF.
6. If you want to create an association on a table, right-click the same table that you used when you created the other operations for that external content type (see Figure 6).
   
   
   Figure 6. Selecting a table in Data Source Explorer
   
   
7. Click New Association.
   The Association wizard opens. It contains an Association Properties section and three Parameter sections: Input Parameters, Filter Parameters, and Return Parameter, as shown in Figure 7.
   
   
   Figure 7. Association wizard
   
   
8. You are now asked to select a related external content type. (See the Errors and Warnings pane for guidance throughout the wizard.) Click Browse, and in the External Content Type Selection dialog box, select the external content type that you want to associate with the current external content type, as shown in Figure 8. This should be the external content type that you currently have open in SharePoint Designer.
   
   
   Figure 8. External Content Type Selection dialog box
   
   

   Note:
   The current external content type is appended with (Current) for guidance.

9. After you select a related external content type, a table appears (see Figure 9). This table lists all identifiers that are present on that external content type.
   
   
   Figure 9. Identifiers present on the external content type
   
   
10. For this association to work in all user scenarios (that is, those that use the External Content Type Picker), each operation on the current external content type (Read Item, Read List, Create, or Update) must have all occurrences of the Foreign Key field marked with a foreign identifier. As instructed in the text above the table in Figure 9, select a field in the list of all fields on the current external content type, which should be mapped to the identifier.
    

    Note:
    Typically, this field is the Foreign Key field. If the names are the same, it is mapped for you, and the error message will disappear.

11. On the next page of the wizard, shown in Figure 10, you configure the parameters for the input to the association. In many cases, the names of the fields and columns are the same; therefore, this step resembles the previous step, in which you set up the properties.
    
    
    Figure 10. Input Parameters Configuration page
    
    
12. On this page of the wizard, you configure the "input" to the association (for example, an item that is specified or provided through a Web Part). You must map the data source element (typically the Foreign Key column) to an identifier of the related external content type as input to the association. In the Data Source Elements pane on the left side, select the data source element in the list. In the Properties pane on the right side, select the Map to Identifier check box. In the Identifier list, select the appropriate identifier, as shown in Figure 12.
    To view the name of the external content type and the name of its identifier, see the Errors and Warnings pane, as shown in Figure 11.
    
    
    Figure 11. Errors and Warnings pane showing external content type and identifier names
    
    
    
    
    Figure 12. Selecting the identifier
    
    
13. In the Filter Parameters section, you can create a filter to screen what is returned from the association (see Figure 13). This is not required, and typically is not done. One possible example of using a filter here could be to filter on an association to an Employee, filtering Part Time staff from Full Time staff.
    
    
    Figure 13. Filter Parameters Configuration page
    
    
14. Similar to the Input Parameters section, the Return Parameter section enables you to configure the data that is returned (for example, if an item of one type is specified, you can configure the list of items that is returned that are associated or related to that item—such as all Orders for a Customer).
    On this page, map the field of the current external content type to the identifier of the current external content type, if it is not done already (see Figure 14). In some cases, this page is configured for you.
    
    
    Figure 14. Return Parameter Configuration page
    
    
15. Click Finish to save your external content type. The association is now enabled in Web Parts, the Picker, and the cache.
    
    
    Figure 15. External Content Type Operations
    
    

You can view the association by creating a profile page, as shown in Figure 16.


Figure 16. Profile page

Note:
When creating a profile page, the association is displayed in the Related List Web Part, which is set up on the page automatically. In this case, all surveys are displayed for the customer who filled them out. If another customer is selected and displayed in the profile page, the list of survey items on the page changes accordingly (see Figure 17).

Figure 17. Profile page for customer and related surveys

Creating a Reverse Association

To create a reverse association, you must have a stored procedure. You cannot create a reverse association on a table for the current external content type. If you already created an association, a reverse association is not necessary to enable associations in Web Parts and the Picker.
You should note that an association is a way to return multiple items of the current type (the external content type that you have open in SharePoint Designer). A reverse association, however, is a way to return a single item of the related external content type (not the external content type that you are creating operations on).
You cannot create a reverse association on the table for the current external content type because it would return information based on that table, which does not provide information about the related external content type. A stored procedure can bridge this gap by implementing the necessary query.
The benefit of creating a reverse association is that the wizard handles the foreign identifier mappings on the other external content type operations. For off-lining an external list to Microsoft Outlook 2010 or Microsoft SharePoint Workspace 2010, you can create a simple [ 1 . . 1 ] association with a reverse association to enable the Picker, item retrieval for the cache, and so on.
An example of a simple "one-to-one" foreign key–based association is a music album to an album cover.

Creating External Content Type Associations Based on WCF

 

1. To create associations for WCF service connections, just create the association on the appropriate web method. Again, you create the association on the external content type that contains the foreign key. In this example, you know that in the metadata for each product there is a subcategory for that particular product. Therefore, you open Product in SharePoint Designer and create an association there.
   For example, you might want to create the association GetProductsofSubcategory between the following (see Figure 18):
   
   • External Content Type Product (Identifier = ProductId and FK Field = ProductSubcategoryKey)
   • External Content Type SubCategory (Identifier = ProductSubcategoryID)
   
   
   Figure 18. Creating a new association
   
   
2. The rest of the wizard is the same as in the previous example. This example demonstrates a scenario in which the name of the identifier on the related external content type (ProductSubcategoryID of SubCategory) is different from the name of the Foreign Key field on the current external content type (ProductSubcategoryKey of Product).
   

   Note:
   Because the names ProductSubcategoryID and ProductSubcategoryKey are different, this matching cannot be done for you on the Association Properties page. You must select the correct field from the Field list, as shown in Figure 19.

   
   
   Figure 19. Association Properties page
   
   
3. Select the field on the current external content type (Product), which indicates the foreign key to the related external content type (SubCategory)—again, this is ProductSubcategoryKey. This is mapped to the ProductSubcategoryID identifier of Subcategory.
   
   
   Figure 20. Selecting ProductSubcategoryKey field in Association Properties
   
   
4. On the Input Parameters page, you must configure the input to the association. Under Errors and Warnings, the message states that you must select a data source element. The element that you select must represent the identifier of the input to the association.
   
   
   Figure 21. Selecting data source elements in Input Parameters
   
   
5. Again, the association that you are creating is GetProductsofSubcategory. The input to the association is the Subcategory, and what you are "getting" (returning) are the Products. Because Subcategory is the input, in the Data Source Elements pane, select the Subcategory identifier and map it, as shown in Figure 22.
   
   
   Figure 22. Mapping ProductSubcategoryID
   
   
6. You cannot create filter parameters for WCF service–based external content type associations, so there is no corresponding wizard page. Click Next to go to the Return Parameter page.
7. Finally, to return products, you must configure the return parameters for Product. As stated earlier, the identifier for Product is ProductID. Under Errors and Warnings, the error message guides you to map this identifier, as shown in Figure 23.
   
   
   Figure 23. Mapping the identifier in Return Parameter Configuration
   
   In this particular web service web method, the creators of the service renamed the data source element to ProductKey instead of ProductID, as shown in Figure 24.
   

   Note:
   You will have already mapped the other operations (for example, the Read Item operation) similarly on the Return Parameter Configuration page of the wizard.

   
   
   Figure 24. Read Item operation
   
   
8. Map the return parameter identifier by selecting the Map to Identifier check box, and then selecting the identifier in the Identifier list, as shown in Figure 25.
   
   
   Figure 25. Mapping the return parameter
   
   
9. Click Finish to save the external content type. The association is now enabled in Web Parts, the picker, and the cache, as shown in Figure 26.
   
   
   Figure 26. Association is enabled
   
   
10. Again, create a profile page to view the working association, as shown in Figure 27.
    
    
    Figure 27. Profile page showing the working association
    
    

Conclusion

This article, intended for experienced users of Microsoft Business Connectivity Services (BCS) in Microsoft SharePoint Server 2010, describes how to create associations between external content types declaratively (that is, without using code). The article addresses the following associations for external content types:

• Supported associations in Microsoft SharePoint Designer 2010
• Unsupported associations in SharePoint Designer 2010
• Database-based associations
• Reverse associations
• Associations based on Windows Communication Foundation (WCF)