Adding Associations Between External Content Types

Article
07/24/2014

Applies to: SharePoint Server 2010

In this article
Most Common Types of Association: One-to-Many
Associations and the BDC
Supported Associations in SharePoint Designer 2010
Database-Based vs. Web Service-Based External Content Type Associations
Choosing an External Content Type on Which to Create the Association
Reverse Association
Viewing Associations in the User Interface

In real-world scenarios, you must frequently find related or complementary information for a specific business entity. For example, you might want to find additional information about a customer profile, such as the latest orders placed by that customer or the top five products the customer usually buys. Microsoft Business Connectivity Services (BCS) supports the ability to associate two external content types to support such scenarios. The concept of linking two related external content types is known as an association.

Most Common Types of Association: One-to-Many

The most common type of association is the one-to-many or master-detail association. Following the classic Customer-Orders example, when you define an association, Business Connectivity Services enables the following scenarios:

While looking at a customer profile, you can see the orders submitted by that customer (master-detail report).
While browsing an order, you can navigate to the details of the customer who placed it.
While creating an order, you must assign a customer for it, but you want to pick from a list of available and existing customers instead of guessing the customer's primary key (identifier) on the order creation form. After a customer is picked, you want to see the name of the customer instead of its primary key.
While browsing an order, you want to see more details about the customer who placed it (reverse lookup).

Associations and the BDC

Association is a method instance in the Business Data Connectivity (BDC) service. An association holds pointers to the parent and child external content types and a pointer to the business logic (a MethodInstance object) that enables a client to get the child external content type from the parent external content type. The traversal of an association is a method call on the external system.

An Association method instance contains slots for the identifier values of the SourceEntity instance, and a slot for the identifier values of the DestinationEntity instances. Therefore, you must tag enough input TypeDescriptor objects with identifiers that correspond to each identifier of each external content type that is a source in the Association in the Association method. The IdentifierName attribute on an input TypeDescriptor object signals BDC to insert the identifier value of the EntityInstance of relevance before executing the method. For example, in an AssociationMethodInstance, the user has probably provided the identifier values for the source EntityInstance that he or she wants to retrieve. BDC will insert that value into this slot after fully instantiating this parameter by using all the default values.

In the BDC model, the Association logic (MethodInstance) can be on any external content type, either source or destination, or an unrelated external content type, if the destination external content type belongs to the LobSystem that contains the Association method instance. Microsoft SharePoint Designer 2010 enables you to create associations; however, it requires that the association is defined on the destination external content type.

BDC also supports multiple source external content type scenarios and one destination external content type scenario. SharePoint Designer 2010 does not support this kind of association.

BDC also supports foreign keyless-associations. Foreign keyless-associations are not first-class associations. Consider a Web method that returns Customer, and one of the fields of a Customer is the key to the Address of the Customer. Also consider another Web method that returns Address if given its key. You can use this method as a Customer-AddressAssociation method (possibly with a SpecificFinder of Address). Foreign key pseudo associations determine that the target of this method is an Address, fetch the key of Address from the Returned Customer, and then call this association Method to return the Address automatically.

Warning

Although the BDC model supports foreign key pseudo associations, SharePoint Designer 2010 does not. Therefore, you cannot model foreign key pseudo associations by using SharePoint Designer.

Supported Associations in SharePoint Designer 2010

The types of associations that you can create in SharePoint Designer 2010External Content Type Designer are one-to-many associations and self-referential associations, which are related instances of the same external content type. All these associations are based on a foreign key.

To create many-to-many associations without foreign keys, or associations with multiple related external content types, you must design them manually by using an XML editor or by using Visual Studio 2010. To see the External Content Type Designer in Visual Studio 2010 and view a demonstration about how to create associations, watch Boris Scholl's video Using the SharePoint Business Data Connectivity Designer in VS 2010.

Database-Based vs. Web Service-Based External Content Type Associations

For database-based external content types, you create the association on a table, which is the same table used to create the other operations. For Web service-based external content types, you create the association on the appropriate Web method. For more information, see the following topics:

Warning

You cannot create a reverse association on a database table. To learn more, see Reverse Association.

Choosing an External Content Type on Which to Create the Association

By the time you are ready to create an association, you will already have created the two external content types (with corresponding Read Item and Read List operations). The SharePoint DesignerExternal Content Type Designer enables you to create an association declaratively just as you would any other operation. Create the association on the external content type that has the foreign key to the parent external content type. For example, consider the Customer-Order scenario where a customer places multiple orders. Each order has a field that lists the name of the customer who placed the order, in addition to other fields such as Price, Quantity, and Date. This field is the foreign key field, and because this information is contained on the order, you would open the Order external content type in SharePoint Designer and create the association there.

For a database-based external content type, right-click the same table you did when you created the other operations, and build the association on that table. For more information, see How to: Add an Association between Two Database Table-Based External Content Types. For Web service-based external content types, right-click the method designed for the association. For more information, see How to: Add an Association Between Two Web Service-Based External Content Types

Reverse Association

Most of the time you must have a forward association to show the related items (detail) of a parent item (master) based on a foreign key. However, sometimes you must show the master item for a specific item, such as showing the customer that a specific order belongs to. To do this, you must create a reverse association. In other words, an association in SharePoint Designer gets multiple items for the external content type whereas a reverse association gets a single item for the related external content type.

Viewing Associations in the User Interface

After you create an association between two external content types (see How to: Add an Association between Two Database Table-Based External Content Types, you can view these associations in the user interface in SharePoint Designer 2010 and in the SharePoint site in the following locations:

External list forms: Associations are most visible on the external list forms. For example, an external item picker can be used to select a customer for the order. You can now pick a customer from an existing list of customers for this form. If the external list is synchronized to Outlook or to SharePoint Workspace, a rich client version of the picker is shown on the forms, which you can use to create orders also.

Figure 1. External Item Picker

In both cases, you can show a friendly display name on the external item picker control upon customer selection to display something more meaningful than the number that identifies the customer in the external system; for example, the customer name, or whatever field that is tagged as the title field inSharePoint Designer.
Profile pages: Notice how after you define an association, the orders for the customer appear on the customer's profile page. In the same way, in the profile page for Orders, if there is a reverse association defined, you can use it to navigate and see more customer details.

Figure 2. Orders on the customer profile page
Business Data Web Parts: The Business Data List and Business Data related list Web Parts take advantage of associations and can enable interesting scenarios such as the one shown in Figure 3, in which the Business Data List part on top lists a set of customers and upon customer selection shows the related orders on the Business Data related list part.

Figure 3. Business Data Web Parts