In an earlier blog post we saw how to create External Content Types in SharePoint Designer. Today we are going to take this a step further and create two External Content Types. These two tables, Department and Employee, have a one to many relationship so we can create an association between them. When the user selects a Department in the Business Data List Web Part the filtered Employees will be returned in the Business Data Related List Web Part.
Please check out our previous article and go ahead and create your Department External Content Type and Employee Content Type.
You should now have your two External Content Types in SharePoint Designer:
Creating your BCS Association.
Now your two External Content Types are created we can create the association. With SharePoint Designer we start with the ECT that is going to be the child entity in our Parent->Child relationship.
1, Click on the Employee External Content Type to bring up it’s summary view.
2, Click on the Operations Design View button which is in the ribbon
3, Move down to see the Employee table in the Data Source Explorer and right click on this table. From the context menu pick ‘New Association’
4, A wizard will pop up which help us configure our association. Firstly we have to click the Browse button so we can select the other External Content Type we’d like to relate this one to
5, Upon clicking the Browse button select the Department External Content Type from the popup and click OK
6, It works out that the DepartmentId Identifier from our Department ECT will map to the DepartmentId field in our Employee ECT. If you weren’t so lucky that the names matched exactly then you’ll need to use the drop down to select which fields create the association.
Click the Next button towards the bottom right of the form
7, Unfortunately we still have to create the Input parameter manually (even though it figured out the previous step all by itself).
Select DepartmentId from the list on the left, then tick the checkbox to ‘Map to Identifier’, and finally pick DepartmentId from the Identifier drop down (it’ll be the only value to select)
Click the Next button
8, We don’t want to add any Filters so we can just click Next again
9, Return Parameter Configuration – on this screen just select the fields you want your Association method to bring back. I’m just leaving my as is and clicking Finish
10, Now we simply need to Save our External Content Type by clicking the save button in the top left corner of SharePoint Designer
11, Now we can test our Association in SharePoint. Navigate to your SharePoint site and put the page into edit mode. Place on the page a Business Data List Web Part and Related List Web Part.
12, Select the Department External Content Type for the Data List Web Part, and Employee for the Related.
13, We need to connect the two web parts together, so while the Employee web
part is in edit mode use the web part menu to add the connection
14, And with the connection done, you can now click on the icon that appears next to each row of data in the Department list (they have replaced radio button with this icon!), and the Employees will filter accordingly depending on the Department you selected.
Across the two blog posts we’ve shown you how to create External Content Types and also create a BCS Association across two related External Content Types in SharePoint Designer. Make sure you subscribe to our RSS feed as we’ll have plenty more BCS type blog posts coming soon.
