Creating SharePoint Server 2010 External Content Type Associations with SharePoint Designer 2010

Article
10/20/2016

Summary: Learn how to use Microsoft Business Connectivity Services (BCS) to create associations between external content types in Microsoft SharePoint Designer 2010 without using code.

Provided by: Russell Palmer, Microsoft Corporation | Goksel Genc, Microsoft Corporation

Contents

Introduction
Supported Associations Using SharePoint Designer 2010
Unsupported Associations Using SharePoint Designer 2010
Creating a Database-Based External Content Type Association
Creating a Reverse Association
Creating External Content Type Associations Based on WCF
Conclusion
Additional Resources

Introduction

This article is intended for developers of Microsoft Business Connectivity Services (BCS) solutions in Microsoft SharePoint Server 2010 who want to create associations between external content types declaratively—that is without using code. In this article, you will learn which kinds of associations are supported and unsupported, and how to create database-based associations, reverse associations, and external content type associations based on Windows Communication Foundation (WCF) using Microsoft SharePoint Designer 2010.

Need help understanding a concept? Read the relevant sections as follows:

For help on external content type associations, see:
- Supported Associations Using SharePoint Designer 2010
- Unsupported Associations Using SharePoint Designer 2010
- Creating a Database-Based External Content Type Association: step 3 and step 4
To determine whether you need a reverse association, see:
- Supported Associations Using SharePoint Designer 2010
- Creating a Database-Based External Content Type Association: step 3 and step 4
- Creating a Reverse Association: step 1
To determine which external content type you should create the association on, see:
- Creating a Database-Based External Content Type Association: step 3
- Creating External Content Type Associations Based on WCF: step 1

Supported Associations Using SharePoint Designer 2010

As a platform and object model, Business Connectivity Services provides many different association types. The SharePoint Designer 2010External Content Type Designer allows you to easily create a subset of all possible association types, by supporting the following: one-to-many associations based on a foreign key.

An example of the one-to-many type of association is the relationship between a musical band and an album that contains the band's music. One band can produce several albums, yet each album is always associated with the one band that created it.

A foreign key must be present in the external system, which means that the album's metadata (title, price, release date, and other information) must contain a unique piece of information that identifies that one band.

Unsupported Associations Using SharePoint Designer 2010

The following associations are unsupported using SharePoint Designer 2010.

Many-to-Many Associations (No Foreign Keys)

The SharePoint Designer 2010External Content Type Designer cannot create an association where an item of one type has no information to identify a related item of another type.

An example of this type of association is the relationship between a musical genre and a musical artist. The "classical" genre can never contain a finite list of all classical artists—it is just the name of the classification. One artist might also be classified under many genres: "rock," "pop," "alternative," "classical," and others. For some businesses, you may have to create such a list.

Users must manually develop these associations in the XML of the BDC Model files by using an XML editor, or by using another external content type designer, such as Microsoft Visual Studio 2010.

The SharePoint Designer 2010External Content Type Designer cannot create or edit an association where multiple external content types are required to identify a unique item.

An example of this is a meeting that is scheduled in a building that has many conference rooms. An item of this type is identified through two different related items: "room" and "time." If you specify one conference room in a building, and you continue to specify one point in time, you have specified the appointment that occurred in that room at the specified time.

Creating a Database-Based External Content Type Association

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
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
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
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.
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.
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
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
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.
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
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.
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
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
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
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
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

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

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

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
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
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
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
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
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.
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
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
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
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)

Additional Resources

For more information, see the following resources: