Imagine we have two tables, Employees and Departments. There is a foreign key relationship where each Employee has a DepartmentId column value set. We are going to create External Content Types for each of our tables.
But when we go to add a new Employee item through the external list, the department column shows as just a textbox and expects us to remember the correct DepartmentId and type it in. How do we get this to be a lookup column so we can select the Department we wish to assign from a list? Here is how…we’ll setup our External Content Types to use an item picker control in the add/update dialogs.
1) Open up your SharePoint designer and go to External Content Types tab.
2) Create two external content types which are connected by a foreign key(e.g. Employee and Department tables). Also, please make sure you have selected Create all Operations for each external content type.
3) When the two external content types have been created, we need to create an association between them. Double click on the Employee external content type and navigate to “Operations Design View”. Right click on the Employee table in the data source explorer and choose “New Association”
4) In the Association configuration window click on Browse button and select the Department external content type.
5) Click on the next button. In the “Input Parameters Configuration” window choose DepartmentId and tick the “Map to Identifier” checkbox.
6) Click Finish button and then press CTRL+S to save the modifications.
7) Open up your SharePoint site and create new external list on employee type.
8) Now if you edit any record or try to add new item you’ll see Item Picker in the add/edit dialog.
9) You can click the browse button and see the values.
<hrayr/>
