Overcoming the Composite Key BDC Issue
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.
Requirement
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 below from the BDC Meta Man screenshot:
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 below
The Resolution
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 below.
The Import of this application definition file will not fail as you can see below:
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. The image below displays the Business Data List Web Part and Business Data Related List Web Part working successfully between Orders and Order Details.
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 below.
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. The below image displays the greyed out Web Part connections wizard in Microsoft SharePoint Designer.
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. The below image displays the Business Data Catalog Data Source section in the Data Source Library window 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 the below image.
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
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 as shown below.
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.
