Share via

Tooling Associations in SharePoint Designer 2010

  • Article

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

ToolingAssociationsInSPD.pdf

Comments

  • Anonymous
    May 05, 2010
    I want to create an association between two External Content Types.  My first ECT, Customers, has a key identifier aa CustomerID.  My second ECT, Contacts, has a key identifier as CustomerID and ContactNumber.I've created both in SPD, now I'm trying to create an association from CustomerID to Customer. SPD says 'The related identifier CustomerID is mapped to field CustomerID which is already marked as an identifier of this External Content Type'. So fields which are already part of the identifier can't be used for associations?Can you please explain?
  • Anonymous
    May 07, 2010
    That's correct, the architecture where a field is shared as a Primary Key and also as the Foreign Key is not supported for association creation with the External Content Type designer in SharePoint Designer 2010.You'll have to adjust the BDC Model XML by hand, or set up a simple Web service for your database which circumvents the second Primary Key requirement.http://msdn.microsoft.com/en-us/library/ee557945(office.14).aspx