This post serves as a follow-up to Juan’s 11/19 posting entitled The Notion of Associations and the External Item Picker. This posting will focus primarily on tooling these External Content Type (ECT) associations using SharePoint Designer (SPD) 2010, with a supplemental focus on Profile Pages and the BCS Web Parts (namely Business Data Item, Business Data Item Builder, Business Data List, Business Data Related List, and Business Data Actions Web Parts).

For a thorough understanding, I encourage you to read this post and to watch the supplemental 2-part instructional video, as well as download the provided walkthrough guide which explains each step in detail.

In Juan’s post, he walks through the essential steps to create an association. In the guide provided with this posting, we delve in to thorough detail of each of those steps and decisions. So, to not duplicate all of this existing information, I’ll use this post merely to call extra attention to the critical steps in creating an association using SharePoint Designer 2010:

Supported Associations in SharePoint Designer 2010

The types of associations that can be created using the SharePoint Designer 2010 External Content Type Designer are: one-to-many associations based on a Foreign Key. You can also create self-referential associations (relating instances of the same External Content Type) based on a Foreign Key.

To create many-to-many associations without Foreign Keys, or associations with multiple related External Content Types, you need to design them manually using an XML editor or in Visual Studio 2010.

To see the Visual Studio 2010 External Content Type designer and view a demo creating associations, watch Boris Scholl’s video Using the SharePoint Business Data Connectivity Designer in VS 2010.

Database-based ECT vs. WCF-based ECT Associations

For database-based ECTs, you create the association on a table - the same table used to create the other operations.

For Web service or WCF-based ECTs, you’ll create the association on the appropriate Web method.

You can add filter parameters to a database-based ECT association, but not on a WCF-based association.

You can’t create a Reverse Association on a database table.

Step zero: Which ECT do I create the association on?

When you get to the point where you want to create an association, you’ll already have created the two External Content Types (with corresponding Read Item and Read List operations). The SPD External Content Type Designer allows you to declaratively create an association just as you would any other operation. However, this begs the question: which of these two ECTs do I open up in SPD to create the association on?

The answer is fairly straightforward: open the ECT which has the Foreign Key to the other ECT. As an example, consider the “Customer-Order” scenario where a Customer places multiple Orders. Each Order has a field (in addition to fields like Price, Quantity, Date, etc.) which lists the name of the Customer that placed the order. This field is the Foreign Key field, and because this information is contained on the Order, you would open the Order ECT in SPD and create the association there.

For a database-based ECT, right-click on the same table as you did when you created the other operations, and build the association on that table. For Web service or WCF-based ECTs, right-click on the method designed for the association (it may be named obligingly, such as “GetOrdersForCustomer”).

Reverse Association

To see a list of items of one type (which have a Foreign Key), based on a specified item of another type (“one-to-many” association), you just need an Association (aka forward Association). This will be the case the majority of the time.

A Reverse Association can traverse from one of the items in the list, back to the one specified item.

In other words, an Association in SPD gets multiple items of the current ECT (the ECT it’s built on), whereas a Reverse Association gets a single item of the related ECT. The benefit of using a Reverse Association is that the creator-wizard will also implement the Foreign Identifier mappings on the other ECT operations automatically for you.

Guide

For a thorough walkthrough, with details and explanations of each of the steps in the association-creator wizard in SharePoint Designer 2010, download the supplemental guide Tooling Associations in SharePoint Designer.

Videos

In addition to the walkthrough guide, this 2-part video will take you through a visual demonstration of the associations-creation experience.

BCS – Associations and Web Parts (Part 1)
  • Associations
  • SharePoint Designer 2010 – Associations wizard
BCS – Associations and Web Parts (Part 2)
  • ECT Profile Pages
  • BCS Web Parts

Thanks for reading.

- Russell Palmer, Program Manager