This walk through only applies to BDC Meta Man 4.0.0.7 and later.
Here is how to get a table with a composite key working as the parent entity within an association.
Here are the tables we will be using to demonstrate this
Table : Customers
CustomerId : int32 -> composite key field
ContactId : int32 -> composite key field
CustomerName : varchar(64)
PhoneNumber : varchar(64)
Table : Orders
OrderId : int32 -> primary key column
CustomerId : int32
OrderAmount : decimal
The issue arises if our Customer entity has identifiers for both CustomerId and ContactId as it will try and pass both of these values to the Orders entity to filter the results coming back. This will cause an error as the Orders entity is only expecting to receive a CustomerId. Using BDC Meta Man version 4.0.0.7 however we can pick which columns we want to be identifiers using the following steps:
1, Connect to Microsoft SQL Server
2, Expand the database that contains the tables you wish to use
3, Drag and drop the Customer table on the design surface
You’ll see that our Entity on the design surface has 2 columns highlighted. This indicates that the table has two columns that make up a composite key and our entity therefore has 2 identifiers! Not good for creating an association. So lets fix this…
4, Right click on the Entity and choose Edit from the drop down menu
5, On the first tab you will see the identifiers for the Entity listed. Deselect ContactId and then click the Save button to save and close the Entity Management screen.
You’ll now see that the Customer Entity on our design surface only has CustomerId highlighted as it now only has one identifier.
6, Drag and drop the Orders table onto the design surface to create our Orders Entity.
7, Drag and drop from the CustomerId column in Customers to the CustomerId in Orders
8, Now we can generate our application definition file by setting the filename and path using Configuration -> Settings and then pressing the play button to generate
9, Once you have imported your application definition file into your Shared Service Provider you will be able to go to your SharePoint site and add the Business Data List and Business Data Related List Web Parts. One these are added, configured up and connected you’ll be able to select a row of data in the Customer web part and the orders will filter correctly as only the CustomerId Identifier column is passed across
Hopefully this walk through has shown you how you can now change the identifiers for an Entity and work around issues presented by composite keys. If you have any questions please email them over to support@lightningtools.com
