The Notion of Associations and the External Item Picker

In real world problems there is a frequent need to find related or complimentary information for a specific business entity. For example, while looking at a customer profile, I would love to see additional information about it such as what are the latest orders placed by that customer, or the top 5 products it usually buys or any other relevant information for it, you name it. Business Connectivity Services supports the ability to associate two External Content Types (ECTs) to support such scenarios. This blog post introduces the concept of associations in BCS, how the concept is materialized in the product UI and a quick example on how you can define a simple association between two ECTs using SharePoint Designer 2010 (SPD).

The most common type of association is the one-to-many or master-detail one. Following the classic Customer-Orders example, by defining the association BCS will enable the following scenarios:

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

It’s really easy to create an association between two ECTs. As a prerequisite we need to start with two existing ECTs, in this case Customer and Orders. Then you need to do define a navigation method on the ECT that contains the foreign key, in this case the Order:

1. Right click Orders table and select ‘New Association’

2. A wizard will pop-up in SPD prompting to select to which ECT to create the association, we need to select Customers by clicking on ‘Browse’, then we click ‘Ok’ and ‘Next’

3. Configure input parameters for the navigation method, by specifying the identifier of the associated ECT, in this case the CustomerID. Make sure to select the identifier in the data source elements panel and then hit on the ‘Map to Identifier’ check box on the right.

4. Skip the filter configuration

5. Configure output parameters for the navigation method. The navigation method is meant to return the Orders for the specified Customer so we need to map the return identifier, in this case the OrderID.

6. Click on ‘Finish’.

7. Create BCS artifact, such as external lists, profile pages etc.

Once this is complete we’ll notice the associations are visible in the UI in several parts of BCS components:

1. On the external list forms: The most visible presence of associations will be that on the order creation form, an external item picker will be placed to select a customer for the order. Users now are enabled to pick from an existing list of customers for this form. If the external list is synchronized to Outlook or to SharePoint Workspaces, a rich client version of the picker will be shown on the forms to create orders as well.

On both cases it’s possible to show a friendly display name on the external item picker control upon customer selection to show something more meaningful that the number that identifies the customer in the external system, for instance the customer name, or whatever field that is tagged as the title field in SPD.

2. On the profile pages: Notice how once an association is defined, the orders for the customer will appear on the customer’s profile page. By the same token, in the profile page for Orders, if there is a reverse association defined user will be able to navigate and see more customer details.

3. Business Data 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 the figure below in which the Business Data List part on top lists a set of customers and upon customer selection it shows the related orders on the Business Data related list part below.

So in this post we reviewed how to define association between ECTs on BCS and also saw them in action through external lists, profile pages and business data parts. This topic is just the tip of the iceberg of the association’s concept; on subsequent posts we’ll review more complex scenarios around associations such as self-referential associations, multiple associations, associations between ECTs of different external systems, composite identifier associations and more.

-Juan Balmori Labra, Program Manager