Creating related external BCS lists in SharePoint 2010

SharePoint 2010 allows you to create external BCS lists which can surface the data from external data source. As it often happens, external data sources may have various inter-related entities where foreign keys represent relationship between those entities.
In this post, I’ll demonstrate how you can create a simple external list which uses 2 inter-related lists Products and ProductModel and let’s our user to pick a product model using a pick list like this.

First, I assume you have a SharePoint 2010 environment set up with BCS configured; also, you have access to at least two related table in SQL database, in my case we’ll be using Products and ProductModels. When a new product is entered, user gets to chose a product model.

We’ll use SharePoint Designer 2010 for this simple demo.

First we, create a Model BCS connection. If you’re not sure how to do that, check out my screencasts on the topic:

Nothing special here, just creating a simple entity and all of the operations, Create, Delete, Update, Read, ReadList.

Next, we create the same model for the Products table in our SQL.
When all of your operations have been created, right click on the table again to create a new association as shown below:

On the wizard window showing next, click Browse to select associated content type (model):

For your input parameters, you will specify ProductModelID and ensure it’s mapped to identified as shown below:

For the rest of the steps, click Finish and accept all the defaults, we won’t need the rest of them for our simple demo.
Save your model. Navigate to Central Administration -> Manage Service Applications … and ensure you have give your current user enough permissions to access both of our newly created models (content types):

That’s all; now all you need is to create an external list instance which is going to use Products external content type. When a new or existing product is selected, a user will be given and option to pick a related entity value from the pick list rather than entering an ID.

Enjoy!