Overcoming the Composite Key BDC Issue with the Data Form Web Part

July 7, 2009 at 2:43 pm | Posted in Uncategorized | Leave a comment
The Business Data Catalog allows you to relate Entities in pretty much the same way that you would create a relationship within any Relational Database Management System (RBDMS) such as Microsoft SQL or Microsoft Access.  The problem is that you can run into some issues with associations where composite keys are used.  The problem that we are describing is not a problem with BDC Meta Man, but a problem with the Business Data Catalog.  In this article we will explain one way without programming where the issue can be overcome.

The requirement that I have is to display a One to Many join where the One side is part of a composite key.  I need to display Orders, Order Details and Products on a Web Part page.  There is no issue at all with the association between Orders and Order Details since the OrderID field in the Orders table is unique and set as the primary key.  The problem occurs when creating an association between Order Details and Products as the ProductID field in the Order Details table is not unique on its own.  However, my requirement is to be able to click an Order to display Order Details, and then click an Order Detail to see the product details on that particular order.  The relationship is as shown in Figure 1 from the BDC Meta Man screenshot:

BDC Meta Man Entity Association Image

BDC Meta Man Entity Association Image

 

 

 

 

 

 

 

 

 

 

Figure 1 – An Association between Orders -> Order Details -> Products

When attempting to import the generated Application Definition File for the above scenario, you will receive an error stating that the ProductID field is not an identifier.  You can see an example error message in Figure 2.

Error when importing the Application Definition File

Error when importing the Application Definition File

 

 

 

 

 

Figure 2 Application Definition File Import Failed Error Message

One way to resolve this issue is to use BDC Meta Man to generate the Application Definition File without the associations.  Your design surface would look like the image in Figure 3.

BDC Meta Man with Associations

BDC Meta Man with Associations

 

 

 

 

 

 

 

 

 

 

Figure 3 – The Entities without an Association

The Import of this application definition file will not fail as you can see in Figure 4.

Import Successful

Import Successful

 

 

 

 

 

Figure 4 – A successful Application Definition File Import.

The desired result would be to link a Business Data List Web Part to a Business Data Related list Web Part using Web Part connections allowing the user to select a Order Detail Line to see the filtered results. Figure 5 displays the Business Data List Web Part and Business Data Related List Web Part working successfully between Orders and Order Details.

BDC List & Related List Web Parts Connected

BDC List & Related List Web Parts Connected

 

 

 

 

 

 

Figure 5 Successfully related Business Data List & Related List  Web Parts.

In order the achieve the above with the Business Data List and Business Data Related List web parts, you must have an association.  Therefore, it will not be possible to join these two web parts in the usual way.  If you attempted to use the Business Data Related List Web Part on an Entity without an association, you would see the error message in the Business Data Type picker as shown in Figure 6.

Error when Relating Web Parts

Error when Relating Web Parts

 

 

 

 

 

 

 

 

 

Figure 6 – If no associations exist an error will occur when selecting an entity.

It crossed my mind whether we could join two Business Data Related List Web Parts using standard web part connections.  However, unfortunately that does not work, and the option to join the web parts together would be greyed out both in the SharePoint browser window and in SharePoint Designer.  Figure 7 displays the greyed out Web Part connections wizard in Microsoft SharePoint Designer.

Web Part Connections Unavailable Web Part Connections Unavailable

Figure 7 – Unavailable Web Part Connections.

To overcome the problem, we can use the Data Form Web Part to replace that of both the Business Data List and the Business Data Related List Web Parts.  Using SharePoint Designer, you simply have to configure the Data View for two different data sources; Order Details, and Products.  You can then add these two Data Form Web Parts onto the Web Part Zone using SharePoint Designer. Figure 8 displays the Business Data Catalog Data Source section in the Data Source Library window in SharePoint Designer.

Inserting the Data View Web Part

Inserting the Data View Web Part

 

 

 

 

 

 

Figure 8 The Business Data Catalog Data Source Section in SharePoint Designer

Once you have two Data Form Web Parts configured to show the Order Details and the Products added to the page, you can use SharePoint Designer to create the Web Part connection between the two web parts.  This is done by right clicking on the Order Details web part and choose web part connections. You can see these two web parts along with the Web Part connections wizard in Figure 9.

Connecting two Data View Web Parts in SharePoint Designer

Connecting two Data View Web Parts in SharePoint Designer

 

 

 

 

 

 

 

 

 

 

Figure 9 – Connecting two Data Form Web Parts

The reason that this solution works is due to the fact that you can map the columns that are related manually as is shown below in Figure 10.

Mapping the Composite Key Fields

Mapping the Composite Key Fields

 

 

 

 

 

 

 

 

Figure 10 – Mapping the Product ID from Order Details to the ProductID in Products.

Once you have saved the changes to the SharePoint Web Part page, you can test the results within your browser.  The ProductID field in the OrderDetails Web Part is a hyperlink that when clicked will filter the results within the Products Table.

Two Successfully connected Web Parts

Two Successfully connected Web Parts

 

 

 

 

 

 

Figure 11 – The end result of two connected Web Parts without the need for an association.

There is always a trade-off! There are many many things that you can do with the DFWP in SharePoint Designer, but you do lose the flexibility of making changes within the browser.

Advertisements

Leave a Comment »

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: